Sage 100

Looking for advise on how to create a Crystal Report pulling data from 3 companies. I have created 3 sub-reports and 3 silent ODBC DSN's. it works great when I am in the report designer. When I attach it to the Custom Reports menu, it only brings in the data for the active company on all 3 sub-reports. Version 2015. I should also add that they don't have SI and it's not an option at this time. TIA

As you see, hanging it on the Sage menu is not an option as only one will pull. We have solved this issue with Access pass through queries. You have to set up a unique SOTAMAS ODBC connection for each company then create a PTQ for each company's tables. You can link your new Crystal report to the Access PTQ's and it should work when running from the Sage Menu. If you want to get more involved, you can create Union queries to merge the three PTQ's into one for Crystal to pull from or even create a Select query to do the heavy lifting (instead of Crystal). We have been creating consolidated reports since 2009 this way. Send me a PM if you want more details....

@JeffSchwenk THAT would be a great training session for a MOTM's event next year.

Thanks Jeff. You lost me at ""As you see"" :). I'll contact you later today.

Also drag/drop the report to the menu itself and have them run it from an icon. Downside? Has to be done for each person.

@MadelineStefanou - This was a MOTM topic in 2014. @DougJenkins and I met at the 2013 DC summit. I did a webinar for him and his staff about a month later and they took off with the concept so well that he did the v2014 SOTM session on PTQ's and Excel Pivot tables. Back in 2013, we had a 90M webinar where I presented PTQ's, Pivot tables and other topics. It was recorded and is available for playback at the link below. Warning - There were a number of technical difficulties encountered. Audio doesn't start until 13:55 into the session. The actual PTQ discussion doesn't start in earnest until 31:06 Pivot table discussion starts at 1:06.00 https://attendee.gotowebinar.com/recording/8562381197547797506 If you have some time, it is a good highlight of the potential.

Kenny the traditional restriction with Custom Reports is the only ODBC DSN allowed has been SOTAMAS90 (although with subreports it should have worked). You can create 3 Access SELECT queries that point to your 3 silent connect DSNs. Get that working first. Crystal historically has had a native driver for Access and of course can use OLE DB. So what you do for each subreport is choose either OLE DB or that native driver when you add the DataSource (don't choose ODBC / RDO).

Thanks everyone. Jeff has given me some homework for this weekend. I will see how far I can go with it.