x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 10 replies
  • Subscribers 118 subscribers
  • Views 1478 views
  • Users 0 members are here
Browse Forums
Announcements
268
topics
260
replies
Databases and Operating Systems
758
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
851
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
876
topics
3k
replies
Sage CRM for Sage 300
182
topics
410
replies
Zoe Samuel_1

Union report using multiple consolidated reports with different companies lists

Posted By

My client has 3 standard consolidation reports connected to 3 different containers which they want to put together in a union report. 

The reason there are 3 different containers and associated reports is because there are different modules activated for different companies. 

For example:

Report A - Associated to Company 1,2,3 - Modules Active: AP, GL, PO, IC

Report B - Associated to Company 4,5 - Modules Active: AP, GL

Report C - Associated to Company 6 - Module Active: GL

I can't create a single consolidated report because Company 4,5 errors out because they don't have PO or IC active, and Company 6 errors out because it doesn't have anything other than GL active.

The 3 standard reports works individually, but when I try to create a union report of them, I get the info from the Companies 1,2,3  3 times. What it appears to do is run Report A for Company 1,2,3, then run Report B for Companies 1,2,3 (instead of for Companies 4,5) and then run Report C for Companies 1,2,3 (instead of for Company 6).

I have contact Sage support. They advised that I am not using the Union report as it is intended, so they are unable to assist with this issue. 

If anyone has any suggestion, please advise.  

I have attached some simplified screen shots for reference. 

  • 0

    When you create the report in Report Manager, do you assign each sub-report to be rendered on its own sheet, or are they all going onto Sheet 1? Change them to sheet 1, 3, 4 (can't use sheet 2 for this) and see if that works for you.

  • 0

    Yes, if you look in my excel sample attached, I assigned 2 companies - standard report #1 (NRP, NUS) on sheet 1 and 1 company - standard report #2 (URH) on sheet 3. But when the report generated sheet 1 had the info from NRP & NUS, and sheet 3 also had the info from NRP, NUS instead of URH.

  • 0

    How are the reports configured in the report manager under "Database Consolidation List"? For the reports you want on sheet one, you should put NRP and NUS databases, and for the report you want on sheet 3, you should have URH.

  • 0

    Yes, I am using Consolidation Lists for each individual report, and then using the union report to join them. 

    But as a union report, NARP & NUS are showing up on both sheet 1 and sheet 3 even though sheet 3 should be URH based on the consolidation list of that report,

  • 0
    Perhaps try running the report with just the sheet one data on it (remove the sheet 3 report) and see if it renders properly. Then run it with only the sheet 3 data on it and see how that goes. If they both work properly, perhaps it is something to do with how your consolidated data connection is configured in the Connector.
  • 0

    Hi Zoe.

    I tested the principle in 300 just using SAMINC and SAMLTD. Where one report was set to SAMINC and SAMLTD and the other just set to SAMLTD. This might not be the best test case but things worked. I suggest setting up the same / or similar example on your side to try and rule out a problem with your consolidation setup.

    Also, try setting your first report to one company and see what happens then.  If it works then one option is to create six sub-reports, one for each company and combine that.

    Furthermore, it would help to get a view of the properties of each of your consolidation connections.

  • 0

    Hi Peter, Thanks for your testing, Can you verify that you included the AUDTORG as one of your fields just to verify you were getting the info from the 2 separate databases.  

    And I had started with your same concept, whereby I initially only had NARP and URH, I still had the info from NARP populating twice.

    I believe Sandra Smith included you in an email last week after she connected to my system to look. If you want to connect, I'd be happy to make arrangements. Please get my info from Sandra and I'd be happy to arrange a time.

  • 0

    Hi Zoe.

    I had the CONAME field included in both reports which brought back the individual companies.  But I also added the AUDTORG field to both sub-reports, although unlikely,  to check if there was anything funny happening at the field level.  AUDTORG brought through the respective companies for each sub-report.

  • 0 in reply to north49jm

    Hi North49JM,

    How do I make the different companies appear in different sheets pls. And from this conversion, i reckon a union report can be created from two union reports?

*Community Hub is the new name for Sage City