Add AP Invoice Description to General Ledger SSRS Report

I’ve been asked to add the AP Invoice Description to the General Ledger report for PJ journal entries. The only actual table in the RDD is the Company table. I tried adding the GLJrnDtl and APInvHed tables to the RDD and then joining them to the Header dataset in the report definition. I added the Description of the APInvHed (with an alias since there are multiple description fields in the various sources) to the actual query along with the list of fields for the dataset but it never displays any values.
What am I missing?

Thank you

I see what you are saying now. I had this same problem awhile back because the tables in the RDD are temp tables created by Epicor. So even though you reference them with relationships on a phsyical table. You still wont see the data since its being lost upon run time. What I had to do was go into the expression of the dataset and add an extension with query syntax. But in your case the reason why you are not receiving data my guess. Is that I am assuming you joined the Temp Header table via “GLACCOUNT” on “ERP.GLJrnDtl”. The problem is that if you look into that table it does not record information for the “APINVOICENUM” field. So you have no reference back to the “ERP.APInvHed” table. I do not see any other reference you can join back on to tie in the invoice number to get the description.

image

image

FROM GLedgerLevels_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN Header_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company
AND T1.GLAccount = T2.GLAccount
LEFT OUTER JOIN Detail_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company

I don’t have any relationship set up for the 2 new tables (although I tried that also with the relationship set to output). I see 2 new data sources in the report for these tables.

You could make a sub-report that accepts the AP Invoice info (Company, Vendor, Invoice), and displays the info from that Invoice

This might be harder than I thought as determining the Vendor and Invoice from the JEDesc could have some short commings.

But it is possible (a first - and far from complete pass at it)

The hard part is determining what part of the Desc is the Vendor name, and what part is the Invoice. You can’t even assume the vendor name starts at the first non number. Invoice 12345 from Vendor “3M Industries”, would show as “IV:123453M Industries”.

Which would decode as Invoice 123453, from vendor “M Industries”

I think you are going to have to try and link the GL entry to the APInvcDtl, via the TranGL table

@DCorriera - here’s the tables to use if you want to do it via the RDD.

Add table TranGLC, linking it to Detail

  • Related fields: Company, FiscalYear, JournalCode, JournalNum, JournalLine
  • TranGLC.table Criteria of TranGLC.RelatedToFile = ‘APInvExp’
  • TranGLC.Key1 is the VendorNum
  • TranGLC.Key2 is the Invoice

Add table APInvcDtl, linking it to TranGLC

  • Related fields: Company, TranGLC.Key1 = VendorNum, TranGLC.Key2 = InvoiceNum

then you could add the fields from the APInvcDtl table to the Report

Can I link GLJnlDtl to the Detail datasource on Company, GLAccount, JournalCode, JournalNum and JournalLine and then link APInvHead to GLJnlDtl on Company, Invoice and Vendor?

I do not see FiscalYear in the Detail datasource?

I think JournalNum is reset to 1 each fiscal year. Which means that JournalCode-JournalNum-JournalLine is NOT unique. You might have to skip that if its not in any of the existing RDD tables, and figure out another way to make sure the TranGLC record is the right one.

There may be another solution in a different thread. Some users have modified the posting rule so the journal entries have the description that you want instead of trying to rebuild a description. Paul Gardner did a talk on this at the 2017 Insights. Maybe someone can dig up the handout…

Mark W.

Just curious … what are people’s thoughts on changing posting rules and compliance concerns?

Generally, not a fan of changing posting rules. Try to use the system as clean as possible…

…however, changing the description from “Periodic Posting” to something more useful does make auditing a bit easier.

Mark W.

We have some parts that are set up with the Primary UOMs as EA. (each). These parts are actually a case of the product. There is also a CS (case) UOM on these parts that has a Conversion Factor of 1. Some trading partners order with a UOM of CS and the quantity is the number of cases they are ordering. Some trading partners are ordering with a UOM of EA. and the quantity is the number or units within the case they are ordering so the actual number of the part that should be shipped is equal to UOM EA. divided by the number of units in a case.

For example one trading partner is ordering 10 CS and another trading partner is ordering 120 EA. of the same product. Both of these equate to 10 of the part to be shipped since the part is a case of 12 individual items.

Is there a way to set up Customer Specific UOMs or some other way to set this up in the system so that the correct number of parts get shipped?

Doreen Corriera

NAC Marketing Co. LLC

95 Executive Drive, Suite 14

Edgewood NY 11717

631-396-8713

Hi Calvin,
i tried your method, however i get the error message while running the new RDD.
i think it it because it had conflict with table relationship “Detail2OneTimeGLDetail” as it had the same relationship field with “TranGLCtoDetail”

Can you help me further? Thanks.

One thing I’d do is make the Table Relationship have a type of Output. This will make sure it gets the original records even when a matching record cannot be found in TranGLC.

Also, did you add the Table criteria to TranGLC?

Edit

I tried to duplicate this and now I’m seeing the same error. Give me some time to work it out.

Hi Calvin,
attached is my setup of RDD for your kind perusal. i have set accordingly based on your advice,
however i still get the error messages. Thanks.

Hi Calvin,
Just to ask whether you have found any solution to my problem?
Many thanks in advance.