SSRS Report Modification - Help Needed

Epicor ERP 10
, ,
1

I was asked to add revision numbers on our Time Phased Mtl. Requirements Report. This means adding a PartRev_RevisionNum, JobHead_RevisionNum, and OrderDtl_RevisionNum. To do so I made a copy of the original report style and report data def. I linked in a few tables to get the revisions in:

PartRev linked on company and partnum
JobHead linked on company and jobnum
OrderDtl linked on company and ordernum
All of these tables were listed as the child and made output instead of def. only.

After building out the relationships to the main table and syncing the data set I downloaded the report and added in revision number for T3, T4 and T5. The issue was that none of these populated the report label at all. I would like to know how you get the RDD to populate out the 3 revision numbers I’m after.

2

My experience with syncing the dataset via the report style screen causes more problems than it is worth. I do not recall the various versions I tried it on, but became frustrated quickly enough.

I elect to update the SQL query manually inside of SSRS.

6 Likes
3

@cfinley,

I do not believe that I can access the SQL directly because we are on Gov Cloud. Thanks for the recommendation though.

4

You can modify the SQL query that the SSRS Report uses to query the Temp Tables.

image

Within Report Builder > Dataset > Right Click > Modify. You can click the Fx icon next to the query to modify the SQL directly. Regardless of being On Prem or Cloud.

5

@cfinley,

That is new! I’ll give that a shot!

1 Like
6

@cfinley,

I’m setting this up as the revision number. Is this formatted correctly?

T4.[OrderDtl.RevisionNum] as OrderDtl_RevisionNum

7

T4 AND OrderDtl are redundant. Only use one or the other

OrderDtl.RevisionNum as OrderDtl_RevisionNum

OR

T4.RevisionNum as OrderDtl_RevisionNum

The second solution is assuming that you have properly aliased your table

LEFT OUTER JOIN OrderDtl T4
1 Like
8

@cfinley,

Below is how I currently have my joins created.

=“SELECT T1.BalanceQty,T1.DueDate,T1.ExceptionReason,T1.PartDescription,T1.PartNum,T1.ReceiptQty,T1.RequiredQty,T1.SourceName,T1.SugOrderDate,T1.Calc_DspJobStatus,T1.Calc_DspLeadTime,T1.Calc_DspMaximumQty,T1.Calc_DspMinimumQty,T1.Calc_DspMinOrderQty,T1.Calc_DspPartClass,T1.Calc_DspPartType,T1.Calc_DspSafetyQty,T1.Calc_DspContractID,T1.IUM,T1.ContractID,T1.SortByDate,T1.BuyForJob, T1.RequirementFlag, T1.SourceFile
, T1.[AssemblySeq], T1.[Company], T1.[JobNum], T1.[LeadTime], T1.[MtlSeq], T1.[OrderLine], T1.[OrderNum], T1.[OrderRelNum], T1.[Plant], T1.[POLine], T1.[PONum], T1.[PORelNum], T1.[PrintMe], T1.[TFOrder], T1.[Calc_Company], T1.[Calc_DspIUM], T1.[Calc_DspPartBinOH], T1.[Calc_DspPartDescription], T1.[Calc_DspPartNum], T2.[DimCode], T2.[DimDesc], T2.[DimOnHandQty], T2.[DUM], T2.[LotNum], T2.[OnHandQty], T3.[RevisionNum], T4[RevisionNum] as OrderDtlRev
FROM Tphse_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN LotDim_" + Parameters!TableGuid.Value + " T2 ON T1.[Calc_Company] = T2.[Company] AND T1.[PartNum] = T2.[PartNum]
LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T3 ON T1.[Company] = T3.[Company] AND T1.[JobNum] = T3.[JobNum]
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T4 ON T1.[Company] = T4.[Company] AND T1.[OrderNum] = T4.[OrderNum]
LEFT OUTER JOIN PartRev_" + Parameters!TableGuid.Value + " T5 ON T1.[Company] = T5.[Company] AND T1.[PartNum] = T5.[PartNum]
"

Thanks for giving me a walkthrough on this stuff! It seems a little tedious but extremely helpful! Additionally, would i need to make a new field for it on the fields section below query?

1 Like
9

It seems a little tedious but extremely helpful

That’s Epicor :slight_smile:

Yes you will. That is the last step. You will just put the name of the field from the SQL query twice. In your example it would be OrderDtl_RevisionNum in both columns.

1 Like
10

@cfinley,

That seemed to do the trick. No errors on that run! Issue now is that the order rev isnt populating the label i set for it. I believe that has to do with the Data Def maybe?

image

Is the current relationship I have set up for it. I have also tried using regular Company in the parent field and Definition only instead of Output, but that provides no change.

11

I would use company = company. Not Calc_company if possible

Have you unexcluded the field in the OrderDtl table inside of your RDD?

12

@cfinley,

Changed it to company and double-checked that they were added to the exclusions. They were. Thanks for the help thus far!

13

No problem. Not sure if you have access to SSMS to look at the temp tables that are generated, but I suspect at this point you maybe just miss named something inside of the report.

If you can look at the temporary table that epicor generated OrderDtl_“Insert your GUID here”. If you see a revision number in there, its a SSRS problem. If there is not data in the column, its a RDD problem.

1 Like
14

@cfinley,

we do not have access to SSMS unfortunately. I will go back and look at my RDD and report to see if anything sticks out.

15

Is the issue that you are possibly missing a period between the T4 and field as highlighted as I am not seeing one.

image
image706Ă—485 23 KB

1 Like
16

@bcrile,

At some point yes that had caused me a pretty decent headache. Below is the current standing of the code. And thanks for the help!

=“SELECT T1.BalanceQty,T1.DueDate,T1.ExceptionReason,T1.PartDescription,T1.PartNum,T1.ReceiptQty,T1.RequiredQty,T1.SourceName,T1.SugOrderDate,T1.Calc_DspJobStatus,T1.Calc_DspLeadTime,T1.Calc_DspMaximumQty,T1.Calc_DspMinimumQty,T1.Calc_DspMinOrderQty,T1.Calc_DspPartClass,T1.Calc_DspPartType,T1.Calc_DspSafetyQty,T1.Calc_DspContractID,T1.IUM,T1.ContractID,T1.SortByDate,T1.BuyForJob, T1.RequirementFlag, T1.SourceFile
, T1.[AssemblySeq], T1.[Company], T1.[JobNum], T1.[LeadTime], T1.[MtlSeq], T1.[OrderLine], T1.[OrderNum], T1.[OrderRelNum], T1.[Plant], T1.[POLine], T1.[PONum], T1.[PORelNum], T1.[PrintMe], T1.[TFOrder], T1.[Calc_Company], T1.[Calc_DspIUM], T1.[Calc_DspPartBinOH], T1.[Calc_DspPartDescription], T1.[Calc_DspPartNum], T2.[DimCode], T2.[DimDesc], T2.[DimOnHandQty], T2.[DUM], T2.[LotNum], T2.[OnHandQty], T3.[RevisionNum], T4.[RevisionNum] as OrderDtl_RevisionNum, T3.[RevisionNum] as JobHead_RevisionNum, T5.[RevisionNum] as PartRev_RevisionNum
FROM Tphse_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN LotDim_" + Parameters!TableGuid.Value + " T2 ON T1.[Calc_Company] = T2.[Company] AND T1.[PartNum] = T2.[PartNum]
LEFT OUTER JOIN JobHead_" + Parameters!TableGuid.Value + " T3 ON T1.[Company] = T3.[Company] AND T1.[JobNum] = T3.[JobNum]
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T4 ON T1.[Company] = T4.[Company] AND T1.[OrderNum] = T4.[OrderNum]
LEFT OUTER JOIN PartRev_" + Parameters!TableGuid.Value + " T5 ON T1.[Company] = T5.[Company] AND T1.[PartNum] = T5.[PartNum]
"

17

@dgreenEA

Not sure where you currently stand with this report. Is the report running? or are you getting an error in system monitor?

18

@cfinley,

Hey! The report is running just fine, no errors. The issue I’m facing is that the added rev numbers do not populate on the report.

19

Just to cover all the steps from start to finish, since you can’t look at the temp tables with SSMS. I want to cover all the bases.

  • Find the report style of the report you want to modify.
  • Create a copy of the report style and created a copy of the RDD from the original. Make sure the new report style points to the new/right RDD. This has burned me before
  • Add the table that has your field inside it, then create a relationship to link to it.
  • Unexclude the field in your table and click save.
  • Add the field manually into your SQL statement just as it was written in the RDD.
  • Add the field into the dataset so you can reference it in your report, and make sure it matches what is in your RDD AND what you wrote in manually in your SQL statement.

At this point if your are receiving no errors and the report is running, but you can’t see any data. Can you confirm there is data to be shown with a BAQ? This has burned me as well

1 Like