I have a consolidated report that combines the invoiced sales data from two company files, and I would like to add a field to my table that simply indicates the company code where the data is coming from. Here is an example of what I have.

(((("AR_InvoiceHistoryDetail"
INNER JOIN "AR_InvoiceHistoryHeader"
ON "AR_InvoiceHistoryDetail"."InvoiceNo" = "AR_InvoiceHistoryHeader"."InvoiceNo" AND "AR_InvoiceHistoryDetail"."HeaderSeqNo" = "AR_InvoiceHistoryHeader"."HeaderSeqNo")
INNER JOIN "CI_Item"
ON "AR_InvoiceHistoryDetail"."ItemCode" = "CI_Item"."ItemCode")
INNER JOIN "AR_Customer"
ON "AR_Customer"."CustomerNo" = "AR_InvoiceHistoryHeader"."CustomerNo" AND "AR_Customer"."ARDivisionNo" = "AR_InvoiceHistoryHeader"."ARDivisionNo")
INNER JOIN "AR_Salesperson"
ON "AR_Customer"."SalespersonDivisionNo" = "AR_Salesperson"."SalespersonDivisionNo" AND "AR_Customer"."SalespersonNo" = "AR_Salesperson"."SalespersonNo")

I saw on another thread that you can add in the SY_Company table, but I'm not sure where that should go in my query. Any help would be greatly appreciated!

Views: 499

Replies to This Discussion

I put it at the end. Try the following:

(((((("AR_InvoiceHistoryDetail"
INNER JOIN "AR_InvoiceHistoryHeader"
ON "AR_InvoiceHistoryDetail"."InvoiceNo" = "AR_InvoiceHistoryHeader"."InvoiceNo" AND "AR_InvoiceHistoryDetail"."HeaderSeqNo" = "AR_InvoiceHistoryHeader"."HeaderSeqNo")
INNER JOIN "CI_Item"
ON "AR_InvoiceHistoryDetail"."ItemCode" = "CI_Item"."ItemCode")
INNER JOIN "AR_Customer"
ON "AR_Customer"."CustomerNo" = "AR_InvoiceHistoryHeader"."CustomerNo" AND "AR_Customer"."ARDivisionNo" = "AR_InvoiceHistoryHeader"."ARDivisionNo")
INNER JOIN "AR_Salesperson"
ON "AR_Customer"."SalespersonDivisionNo" = "AR_Salesperson"."SalespersonDivisionNo" AND "AR_Customer"."SalespersonNo" = "AR_Salesperson"."SalespersonNo")

LEFT JOIN "AR_Options" ON "AR_Options"."CompanyCode" = "AR_Options"."CompanyCode")

LEFT JOIN "SY_Company" ON"AR_Options"."CompanyCode" = "SY_Company"."CompanyCode")

Thank you Doug! Worked like a charm!

So I'm experiencing something strange. I have two company files that I'm pulling data from. The data is pulling correctly for both, but one of the company files is showing one of "test" company files which is not even part of the consolidation. How can that happen? Is that something that is happening in Sage Intelligence setup or in Sage 100?

Hi Matthew.

Is test an actual company in 100?  Have you looked at the database consolidation list in the properties of the report to see what's included there?

Peter,

The Database Consolidation List shows "EUR,MEX".

There is actually a "TST" company file that I had used in the past, but what is strange is that the invoice data is pulling in correctly from both the "EUR" and "MEX" files, but the "EUR" file is showing "TST" for the company code. The "MEX" file is displaying the correct company code. I've actually noticed this issue before when running the built in financial reports where my main company file "EUR" was showing up as the "TST" file.

Is the TST Showing in Sheet1 or the Pivot Table or both?

TST shows as the "Company Code" for all of the "EUR" data. So I have my invoice data and then I used the above SQL code to add the company code as a column. That is where I see "TST".

Can you upload a copy of the template the report generates?

I actually haven't built the report yet so it is just a basic table of data at this point.

Attachments:

Take a screen shot of the Properties Tab from the report like the one Scott posted above.

That's crazy!

Do this. Remove both company codes in the Database Connection List and click Apply. Then add "EUR" click Apply and run the report and let me know what happens. Logically we should see "EUR"?

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service