I’m new to SSRS Reports & Report Data Definitions in Epicor. That being said the following SQL returns the correct data set I would like as a subreport similar to raw materials in the job traveler.
Select PartNum, PartDescription
FROM part
WHERE PartNum
IN ( Select *
FROM String_split(
(Select PartsList_c
FROM JobHead
WHERE ( ( Company = 'XYZ' ) AND ( JobNum = '000046-1-1' ) ) ) , ','));
PartsList_c is a delimited list of phantom parts which would be preferred on our shop floor as a work order.
My disconnect is how to define this within Epicor Report Data Definition or any other means. As I said this portion of Epicor is new to me, and I would like to keep it as simple Epicor as possible…
Just having trouble making the BAQ my string looks like this and I need to split it into rows of data to then compare with partNum and basically lookup the description. I can write the SQL and do simple BAQ’s this is a tad tougher for me and this can have a varied number of phantom BOM parts depending on the Configurator options chosen.
Do you want this to be a subreport in the job traveler?
If so you can just make sure that the Job Traveler dataset includes the PartsList_c and then built the SSRS Subreport using the Company and the JobNum as Parameters for the subreport. This is not using a BAQ at all but is making a SQL call on the subreport. The only thing to be aware of that the db definition is defined in the report itself so if you replicate this in Prod and Test Epicor system you will need to change the db definition to use the correct DB.
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 +") , ',' ))"
