This is my first time creating a topic here, so if I’ve done anything wrong please correct me.
We are running Epicor cloud and I have been asked to add a column to an SSRS report. I’ve already copied the data definition and default report style and made what I thought were the necessary changes.
I pointed to my new data definition within the report style maintenance screen and when I go to download the report so I can add the new data field as a column, I am unable to see the added data source.
So, how can I go about adding the new data source to the report? I thought pointing to the new data definition and downloading the report for editing would do the trick. If I need to set it up within the report, I’m unsure how to accomplish this. I haven’t been able to find much helpful information out there, so any help is appreciated.
Report being edited: SOBacklog
Field being added: PONum
Data source being added: OrderRel
Relationship created between: BKlog and OrderRel
Relationship created on: Company, OrderNum, OrderLine, and OrderRel
You did all the right steps. Now just open your report in report designer and look at the field list for the data source. In the left column open Datasets, choose your data source. Right Click it and go to Dataset properties. Go to Fields. Click Add and type in the name of the field you want to include (it should be the same as the name you added in RDD.) Click OK and go back up to the Query properties and click on the fx button beside the Query Expression. This is the SQL that pulls data into your report. This must also include the new field name you added. Review the structure of the SQL code and locate the end of the select list. Add your field in with a comma at the end of the list using the previous fields as a syntax reference.
Save it all up. make sure it passes the upload test, then give it a shot. Review and report any errors generated in the System Monitor.
Good luck!
In my screenshot you can see where I tried to add PONumber under the bklog dataset. I still do not see an OrderRel dataset so I added it as OrderRel.PONum. I also tried adding it to one of the lines as you can see on the actual report. I’m not getting anywhere though, the report is coming out unchanged when I try to preview the custom report. Anything I’m obviously doing wrong?
One thing I’m failing to understand is why the report in Report Builder doesn’t look the same as the report when it prints.
I’m not sure this can be done. The RDD is one of the ones that Epicor has created the dataset behind a curtain. I know of no way to change this type except with the SDK. You might have to re-create your own version unfortunately.
So, the first time I made this it disappeared and wouldn’t let me add relationships. Second time it’s now letting me. Do I want it to be setup as Output? And anyway you could explain why the parent/child matters?
The BkLog is the main table that is being returned, so you want that to be the parent to restrict the OrderRel to only relevant records. Otherwise, you will pull back every OrderRel record in the table before even going to the BkLog.
Now that I wrote that, if all you want is PONum and nothing else, it would be better to use the OrderHed table and just join on Company and OrderNum. Less processing.
Okay, I went ahead and did OrderHed like you mentioned. Thanks a lot.
One question I have for you, if I change the data definition and then create a new style and connect it to that data definition, then I download/export the report, should I be able to see the added datasets?
This convoluted way to add fields to reports will hopefully be different in the final iterations of Kinetic. I hate having to remember all these steps! You’re on the right track now!
Thanks! Sorry, got pulled into some other projects. I’ve tried this and am still failing. Could you explain what the TableGuid is and how it relates to T1 and T2? The first error I got said I had a problem near the join, but I believe it had something to do with extra quotations. Now getting a ton of errors I don’t know what to do with.
If you copy and paste from someplace, double check the quotations used as they sometimes convert to fancy ones that the system does not recognize. I believe @ckrusen has a post about it somewhere on here.
When you run a report, there is an option for a retention period. You must select 1 day at the very least. If not, the data will be deleted from the database. When you submit a report to be run, the system creates the dataset and saves it to the SSRSReports database. The tables are created with a GUID so the system knows what tables to get the data from. When you run a report manually, you need to enter the GUID. T1 and T2 are just aliases for the tables so the developers did not have to type the table name every time.
Thanks for all that info. I think I was able to ensure that all of the quotations are good and normal. So basically, I’ve changed all of the stuff within Epicor and I’ve been told that I’ve done it correctly. Now in report builder I’ve edited the query and added the PONum field, I also went ahead and tried adding the PONumber to the report just to see if I can get it to spit it out (still getting errors).
Is that all I’m supposed to do? Does sequence of the relationships in Epicor matter at all here? Adding pictures in case anyone would like to look and see if I’ve made a mistake.
You may need a space in there " T1LEFT OUTER JOIN OrderHed_". Before LEFT.
Also, I am not sure if it matters, but I always make my Field Name the same as my Field Source. Change PONumber to PONum.
I caught that as well. I went ahead and changed the field name. I took out the added fields to the report just to see what happens and I’m still getting a failed query execution in regards to BKLog.
Do I need to add T2.PONum as the field source? Do you think it’s just not possible?
Now just make sure that the fields in both of those tables are not “excluded” in that very same screen you have up. Go to Table fields exclusions and make sure both company and ordernum are not excluded from those same two tables.
Yeah, I made sure nothing was excluded. Someone above mentioned that you may not be able to edit the SOBacklog because the BKLog table isn’t something we have access to. Any clue if there is truth to that? It’s either that or my query isn’t correct.
