I’m very new to epicor in general and I’m tasked with modifying some SSRS reports. I’ve done a lot of digging and research but getting stuck in a couple of spots.
I am specifically trying to modify the built in Scheduled Shipments Report. I have…
Copied the standard report style and data definition
Ran the Generate for Design
Opened the report in Report Builder
Made a couple minor modification to the report
Successfully previewed and published those changes
Now, i’m stuck trying to add new table/fields to the report and modify the report parameters.
Right now the report is showing the ShipVia code and they want the ShipVia description
There is a Sort By parameter and they want a different one - Customer/Due Date/Part there is already one in there for Customer/Part/Due Date
If there is already documentation about this, I’m not not able to find it.
Also, one other side question. Since we are on the cloud based epicor, is there a way to see the database structure/schema? How would I figure out what table I need to join to, to get the ShipVia description for example. I believe it’s in ShipViaList, but i’m not sure.
Hi Anthony, that’s going to be a bit tricky especially since you are Cloud, and have no access to the Database. But you can find workarounds using BAQ as way to look into data structures. If you open you RDL you will see that the main table is OMR50 which is not a real table, but the data in that table is real so you can still build your joins in the RDD. If you add the ShipVia table you will just join to the OMR50 on Company and ShipViaCode. In the RDL you will need to add a join to the new ShipVia Table same as in the RDD and add the Description field from the ShipVia table.
For your second issue with the parameters, I personally have not had any luck trying to change the parameter option in canned reports. I’m sure that’s even an option for you as a Cloud customer. I would simply create a new style associated with the new parameters that you want. You can start with the existing one and just add/change the group ordering… Hope that helps.
Thanks for your help!
I have now tried to add ShipVia on the RDD but not able to add the relationship to Company and ShipViaCode. All I get to choose from on the ShipVia table is the RptLanguageID.
I still tried to update the query in the RDL without that relationship but running the report failed trying to execute the OMR50 dataset query. This is what I changed the query too…
=“SELECT T1.Company,T1.CustID,T1.CustName,T1.FirmRelease,T1.IUM,T1.JobNum,T1.OrderHeld,T1.OrderLine,T1.OrderNum,T1.OrderRelNum,T1.PartDesc,T1.PartNum,T1.Qty,T1.ReqDate as ReqDate,T1.ShipToName,T1.ShipToNum,T1.ShipVia,T1.StagedQty,T1.WhseCode, T2.JobNum as JobProd_JobNum,T2.ProdQty, T3.Description as ShipViaDescription
FROM OMR50_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.OrderLine = T2.OrderLine AND T1.OrderNum = T2.OrderNum AND T1.OrderRelNum = T2.OrderRelNum LEFT OUTER JOIN ShipVia_"+ Parameters!TableGuid.Value + " T3 on T1.Company = T3.Company AND T1.ShipVia=T3.ShipViaCode"
Alex, thank for all your help with this. Everything seems to be in place but not actually getting any data on the report for the Ship Via Description field. Is there anything else I could be missing?
I’m using the same query posted before on the OMR50 dataset in the RDL and verified the T1.ShipVia codes do exist in the Ship Via Maintenance. T3.Company, T3.ShipViaCode and T3.Description are always blank. The only other thing I’ve modified was to add ShipViaDescription as a query field in the dataset properties.
That’s very odd, it looks correct. All your fields are enabled in the ShipVia table right? And just to make sure you have a new custom style and that style is pointing to this new custom RDD correct?
@acarlson, how did you preview the report? Did you do this in Report Builder? I am a cloud client too, I have generated for design an XML Data Definition Data Set and downloaded locally through the “Server File Download”. I am not very familiar with Report Builder and perhaps I am not Googling the correct terms, but when I open the RDL file in Report Builder, how do you point the data source to the XML file to preview the report populated with the sample data?
After you generate for design, you have to go to your epicor system monitor in the task bar. From there you can find the report you just generated and in the Action menu you can Design SSRS Report. That will popup a new window where you can go to the Action menu and Download, this will link up the generated data with the local copy of you rpt file. After you’ve made you’re changes in the report builder you come back to this window and in the Action menu there is a Preview. Once you’re happy with the change, you can then Publish from the Action menu.
I am getting closer with your advice. When I open the SSRS Report Design, everything is greyed out. I would have expected details from the report I highlighted on system monitor to pull through when I clicked actions, Design SSRS Report, but they are not (and not letting me manually populate the fields either).
I think I figured out why the SSRS Report Design was not bringing in the report details. We are multi-company, so because I generated the report in 1 company, my system monitor was based from a different company (the system monitor does show reports from all of our companies). I had to make sure my system monitor was based in the same company where the report was generated for design. Then when I went to action, Design SSRS Report, the report details populated Design SSRS fields.
@acarlson Are you able to get a preview to work in Report Builder? I keep getting an error when I go to Run in Report Builder.
Hello. Has anyone had issues with the report not “publishing”??? We have an active case and were told two weeks ago that it is being worked on in SaasOp!!! We cannot publish any changes or any new BAQ’s!!! Any help is greatly appreciated.
