I am passing a value into CallContextBPMData.Number01 on the Printing form for Open DMR Status. In a copy of the DMROpenStatusReport I manually joined the DMRHeader dataset to the CallContextBPMData as follows
="SELECT T1.RptLanguageID,
T1.CommentText,
T1.Company,
CAST( T1.DMRNum as nvarchar ) as DMRNum,
T1.LotNum,
T1.IUM,
T1.PartDescription,
T1.PartNum,
T1.PurPoint,
T1.Calc_ActionLog,
T1.Calc_TransactionLog,
T2.Number01
FROM DMRHead_" + Parameters!TableGuid.Value + " T1" +
" INNER JOIN CallContextBPMData_" + Parameters!TableGuid.Value + " T2 ON T1.DMRNum = T2.Number01"
The SSRS generates and runs without issue. The value I am passing to Number01 is visible in the report (just to verify it passed) however the report is not restricting the rows to the DMRNum equal to Number01.
Essentially this report should only show 1 DMR as I am only passing 1 value in Number01 but it shows all open .
Just to make sure we’re not overlooking something simple… is there a reason you wouldn’t want to setup a filter or option field on the report and just filter it that way?
Your Query above looks okay. But make sure you modify the real dataset in the SSRS Report. Based on your INNER JOIN it shouldn’t be possible to have multiple. Are you maybe running Epicor’s Base SSRS or perhaps you didn’t upload your changes.
I actually did try to add a filter to the dataset by saying DMRNum = =“CallContextBPMData_” + Parameters!TableGuid.Value + “.Number01” but it didn’t do anything.
I still had the join there in addition to the filter…
I actually added a field to the report with the value I was passing just to make sure it was getting to the copy of the SSRS. So basically with the Join in place I say my CallContextBPMData value printing on every page of the report…but not limiting it.
So I may have misunderstood something. Where you expand the datasets in the SSRS report I did not have the CallContextBPMData Dataset there. I figured joining/referencing it in one of the existing datasets query would work. I was seeing the value passed when I created a textbox First(Fields!Number01.Value, “CallContextBpmData”) so I figured it was working. I ended up syncing the existing RDD to the new RDL and it brought in a ton of fields and such. I noticed the Datasets now contained the CallContextData. i basically copied and pasted the modified queries back into the DMRHead and eventually got it to just limit the results to 1. I am guessing I can manually as the CallContextData rather than syncing all the garbage from Epicor. What’s is the process to do that?
This is something you don’t want to do, that alone may have broken it all. Syncing shouldn’t be done, its good for BAQ Reports but not out-of-the-box RDLs.
I started again without syncing the data from the Report Style. I just renamed the working RDL file and copied the Base SSRS report which created a new RDL. I went into that RDL and manually added a new dataset called CallContextBpmData. In the query for that dataset I added the following
=“SELECT T1.[Number01]
FROM CallContextBpmData_” + Parameters!TableGuid.Value + " T1".
Then I went to the dataset for DMRHead and basically reused what I had from the old report
=“SELECT
T1.RptLanguageID,
T1.CommentText,
T1.Company,
CAST( T1.DMRNum as nvarchar ) as DMRNum,
T1.LotNum,
T1.IUM,
T1.PartDescription,
T1.PartNum,
T1.PurPoint,
T1.Calc_ActionLog,
T1.Calc_TransactionLog,
T2.Number01
FROM DMRHead_” + Parameters!TableGuid.Value + " T1" +
" INNER JOIN CallContextBPMData_" + Parameters!TableGuid.Value + " T2 ON T1.DMRNum = T2.Number01"
This is acting the same way my precious attempt was prior to syncing the data from the Report Style. It is showing ~100 results. What am I missing? I also added the textbox to the report and set to the following
=First(Fields!Number01.Value, “DMRHead”)
This is the field from CallContext I passed into the report and joined to the DMRHead. Its is displaying the correct information on every page (just the same DMRNum repeated 100 times).