I am not sure I have access to those, as we are on a cloud installation.
oh, sorry, i missed that…
When I got SSRS errors, I would submit a ticket with the approximate date/time of the error.
Vote for improved monitoring/logging.
1 Like
I’m working on this right now. I’ve got a code fix and just have to do the testing in BAQ Reports as well as Dashboards. I think we just missed the latest 10.2.700 patch though.
Once I’m done, it will take a couple days to percolate through QA, etc. I will update here when it is done and which 10.2.700 patch it will be in. Thanks everybody for your feedback and keeping me honest. 
3 Likes
I just verified my fix is in 10.2.700.4. We tested that it works with Dashboards and BAQ Reports. If you see this issue anywhere else, let us know.
1 Like
For the Cloud customers, the corrected elements will be installed as part of the 10.2.700.2 Upgrade.
1 Like
Can anyone tell me how to use STRING_AGG once I do have SQL2017+? As noted above, I am utilizing a subquery to concatenate the work centers for a job into a single line. I use the FOR XML PATH and REPLACE. How can I perform a similar function using STRING_AGG? Would I just add a calculated field with this formula?
STRING_AGG(JobOper.OpSeq,", ")
Thanks!
Nate
1 Like
That’s correct. Then you do everything else the same, except you don’t need to mess with the replace crap anymore.
2 Likes
Wow, that is easier than the old method! In my case I needed distinct so still used a SubQ to pull just distinct OrderNum values.
select
[InvcHead].[Company] as [InvcHead_Company],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
(String_Agg(OrderNums.InvcDtl1_OrderNum,', ')) as [Calculated_SONum]
from Erp.InvcHead as InvcHead
left outer join (select distinct
[InvcDtl1].[Company] as [InvcDtl1_Company],
[InvcDtl1].[InvoiceNum] as [InvcDtl1_InvoiceNum],
[InvcDtl1].[OrderNum] as [InvcDtl1_OrderNum]
from Erp.InvcDtl as InvcDtl1) as OrderNums on
InvcHead.Company = OrderNums.InvcDtl1_Company
and InvcHead.InvoiceNum = OrderNums.InvcDtl1_InvoiceNum
group by [InvcHead].[Company],
[InvcHead].[InvoiceNum]
I think we have been updated so that we can use String AGG now. Where can I verify that this is true? I tried a BAQ utilizing string AGG and it popped an error. “Not a recognized built-in function name”
It should work in a BAQ, can you show your calculated field?
This one works.
select
[JobOper].[JobNum] as [JobOper_JobNum],
(String_Agg(JobOper.OpSeq,", ")) as [Calculated_Ops]
from Erp.JobHead as JobHead
left outer join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
and JobHead.JobNum = JobOper.JobNum
where (JobHead.JobNum = '12345/1')
group by [JobOper].[JobNum]
I see the single quote you used. I also tried that and got the same error.
Is there no other definitive way to know if we have been updated?
It turns out that our Live environment was updated before our Pilot environment. So the code works great in Live, but not in Pilot. At least I know how to use the syntax now.
String_Agg(JobOper.OpSeq,', ')
This has to be a calculated value, and the other fields in the query must be set to ‘group by’.
Thank you all!
Nate
1 Like
Is there a way to sort the JobOper.OpSeq number ascending or descending? Mine are random.
You can add an order by after the string_agg() command
STRING_AGG(cast(convert(varchar, PartOpr.OprSeq) + ‘~’ + PartOpr.OpCode as nvarchar(MAX)), ’ / ’ )
This is my calculated field in my BAQ.
Adding a sort after this does not pass “Check Syntax” no matter how I phrase it.
The other thing you can try is add the sort order in the Display Fields sort order. Normally you can’t do that in a sub query, but since it’s a single sub select, you can. See if that works.
What is the error?
Because SSMS returns data for you expression without errors if I fix quotes:
select STRING_AGG(cast(convert(varchar, PartOpr.OprSeq) + ‘~’ + PartOpr.OpCode as nvarchar(MAX)), ’ / ’ )
from Erp.PartOpr
Severity: Error, Text: Bad SQL statement.
Review the server event logs for details.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 45.1758 ms.