Hi All
Been ask by finance if its possible to add another column which shows the name of user who posted that transaction, I understand you can get this in other reports but the information on those reports don’t have the right information.
any pointers would be great
You can duplicate the report and add the required column to it.
KR
SRakesh
How would i do this finance run the report and save it as a csv where do i need to start looking to add this?
Hi Dean,
Locate the report in Report Data Definition. Create a copy of this and check if you need to add any data sources and create any relationships between the tables.
Then do your SSRS design to add this column in
https://erphelp113100.zendesk.com/hc/en-us/articles/24037004330381-Designing-an-SSRS-Report
The problem with this (and some other reports) is Epicor didn’t link “tables” to the RDD… they just used various background queries. So you have to try to figure out where to make the connections.
I walked through this one this morning… here are the steps:
Open GLedger report style, then in Actions menu, chose “Copy Report Style”.
Rename your new copy (if you want).
Right-click in the Data Definition field (Should say “GLLedger”) and “open with” Report Data Definition.
The RDD opens… go up to your Actions Menu and choose “Duplicate Report” and give it a new name like “GLLedger_XXX”.
You need to add a new Data Source to your RDD. “New”… “New Table”.
Search for and add the GLJrnDtl table.
Quick tip… I always rename my added tables in ALL CAPS, so they stand out in the tree view. If I ever revisit this RDD in the future, I instantly know I added that table.
Next, review the “exclusions” under the GLJRNDTL table you just added. You’ll want to uncheck the fields you want to add. Need to at least include (uncheck):
Save.
Next, go to “New”… and select “New Relationship”.
We’re going to link GLJrnDtl to Epicor’s generic “Detail” data source. Below are the relationship fields I used. Take note that Epicor’s Detail table uses all lower-case letters in “journalcode”.
That should be it for the RDD.
Go back to Report Style… right-click on the Data Definition field and choose refresh list. You should hopefully see your new RDD in the list now. Pick that.
Go up to your action menu and choose “Download SSRS Report”.
Open the downloaded file in Report Builder.
Add this to the bottom of your query… AGAIN… make sure you’re using all lower-case letters when you reference the T3 table’s journalcode:
LEFT OUTER JOIN GLJRNDTL_" + Parameters!TableGuid.Value + " T4 ON T3.Company = T4.Company AND T3.journalcode = T4.JournalCode AND T3.JournalNum = T4.JournalNum AND T3.JournalLine = T4. JournalLine
Also add T4.PostedBy up top in the query.
Add “PostedBy” as a query field.
Add the PostedBy field to your report layout within the Detail group.
Save.
In Report Style > Actions > Upload SSRS Report.
Test it in General Ledger (Report)… make sure you select your new style when you go to print/preview.
wow thank you so much i will dig into this is amazing.
Thank you again.
Great example of adding a columns to any report. This is a good one for experts corner