From what I’ve seen, you’ll need to do some trickery using XML.
I need to combine rows into a single field, and some folks on here showed me how. I still don’t fully understand how it works, but it took data from separate rows and combined them into single rows.
And you want to do the opposite.
I bring up the XML method, as I’ve seen a few solutions online that use it to do what you want. But I have no idea how to implement it in a BAQ.
@ckrusen Thanks for that. I saw many similar posts to join/concatenate but I think setting the Report’s Data Set with linked parameters will be the route I will use for this as I tried working out that XML methodology, but could not seem to find valid solution. Much easier to aggregate rows and put them together than pull apart 30-ish items. I will try a dataset methodology as @John_Mitchell suggested.
We needed something similar to this where they wanted to Print the Traveler and have it also print the Pick List. They are separate reports that can run on their own, but a customization calls the second report using the printer name and job list from the Traveler and prints it behind the scenes.
You could parse the parts list in a foreach and send a tilde delimited list to the second report as a parameter.
@gpayne That would be great I know the list as I posted above already is attached to the JobHead data set / Table view the foreach would be nice to see though
It is in there, the PartsList_c is a delimited list that I need to lookup to produce a Floor Work Order… The field is in the JobHead RDD Table already added the Parts in the RDD but I need to parse it out to a set/list of records for our Work Order from the single field PartsList_c.
Do you know VB? Just create a method in the code to split it and then call the method in the field on the report.
So, for whatever reason, SSRS does not recognize return characters in text boxes (think comment fields in Epicor). So, I found this in one of the out of the box reports and use it to create the return in comment fields on the report.
Public Function SplitComments(ByVal str As String) as String
Dim c() As String = Nothing
dim s as string = ""
c = str.Split(vbcrlf)
Dim i As Integer
For i = 0 To c.Length - 1
If i > 0 Then
s = s & vbCrLf & c(i)
Else
s = c(i)
End If
Next
return s
End Function
Ya, the parts list is only the part numbers and I need that query I showed above to get me the descriptions from the part table… The code I used already functions fine on the DB just not inside the report. Where should I look to tell me what is causing the problems? And can I not use String_split in the SSRS Query text? it works on the server fine. Only message I get is: Query execution failed for dataset ‘ConfigPartsList’. (rsErrorExecutingCommand)
While doing some security testing, I found that SSRS is smart enough to prevent things that could lead to SQL injection. I was doing this by tweaking the RDL’s query expression. So that might be an issue.
Use Multilookup to retrieve a set of values from a dataset for name-value pairs where each pair has a 1-to-1 relationship. MultiLookup is the equivalent of calling Lookup for a set of names or keys. For example, for a multivalue parameter that is based on primary key identifiers, you can use Multilookup in an expression in a text box in a table to retrieve associated values from a dataset that is not bound to the parameter or to the table. =Multilookup(Split(Fields!ProductCategoryIDList.Value, “,”), Fields!ProductCategoryID.Value, Fields!ProductCategoryName.value, “Categories”)
Where ProductCategoryIDList contains a comma separated list of codes “4,5,2,5”. The list will be converted by the Split function into an array. Each element of the array will be searched to match on the DestinationExpression from the Dataset, returning an array of values from the ResultExpression.
If you doing it as a subreport you can create a new parameter called @PartNum and have that be the SPLIT() of the concatenated field.
Found out my original attempt would have worked fine without the EOL chars in my string. Ugh wasted time looking for alternatives. My apologies like I said kinda new to SSRS & ReportBuilder.
@ckrusen no trickery needed. @jkane Yes, I know quite a lot of VB, but I did not feel it was needed for this. Also, I had no idea that EOL whitespace in the DataSet text type Query would throw a generic error and not give better error details especially since the Query Designer adds it. Go figure. @John_Mitchell your info worked just great as well. After adding the PART table to the RDD, I created a dataset with the following expression works just fine:
="SELECT PartNum,PartDescription FROM PART_" + Parameters!TableGuid.Value + " WHERE PartNum In (Select * FROM STRING_SPLIT( (SELECT PartsList_c FROM JobHead_" + Parameters!TableGuid.Value +") , ',' ))"
Thank you for the example code “Job Traveler n Pick LIst.cs”. Is this code inserted in the customized Job Traveler submission form? which will call the Pick List report to print/preview?
I am very impressed. I am not sure I fully understand the code yet. I have a need to do a similar customization from the Quote submission form and to also print/preview a job traveler which only need the job number. This will take me more time to understand unless you have a version with more comments.
I was going to do the Auto print BPM option but this only works for printing and not previewing.
@asmar This one is probably overkill for that. I was looking on the forum for an example using launch form options and found a thread of yours that used lfo to print a job traveler. If you got that to work I would reuse that code here.