passing subreport data to a crossing table in the main report

To the experts,

We have an example with two datasources. Both hold revenues and week numbers of two independent businesses, they are based on different tools and the data need to be combined in one crossing table. The main report uses odbc to connect to the 1st source and the subreport in the report header uses odbc to connect to the 2nd source.

We grouped the subreport based on concatenation and created a grouping array and a summary array,

Issue: the crossing table in the main report doesn't show the rowfield and columnfield that were used in the subreport's grouper formula.

What do we have to do?

The table we look for:
-rows: business-1 and business-2
-columns: week numbers (we used a formula based on revenue dates and another formula to convert the numbers to text)

Regards, Johnathan
john vAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do you mean in a single cross tab?

You can't do it unless you can somehow combine the 2 data sources.

Crystal can use 2 data sources for a report.  However when you try to join them the join and all filtering will be done in the report rather than in the database.  It is not recommended to use 2 sources but it is possible.

Can you use an intermediate database to collect the data into a single source say MS Access?

What are your 2 data sources

john vAuthor Commented:
The main report uses an odbc connecting to an SQL data source and the subreport uses another odbc connecting to a FoxPro data source. The sources don't relate and basically hold dates linking to revenues.
Within Crystal there are two starting points.
Database - database expert - data:
-the tree of source1 is on top and the tree of source2 is below
-in this menu tree2 is connected to tree1, but I presume Crystal can't do it different here
-like you said, it works for as long as the main data and sub data are processed on their own location

If it can be done any other way than with one single cross-tab, it is perfect.

I'm not familiar with the intermediate solution, can you explain it in detail?

john vAuthor Commented:
If it does not work with one cross-tab I have maybe found a compromis. I take a main report footer and create a cross-tab for source1 and a subreport-cross-tab for source2. Column grand totals are then disabled.

The issue then:
Cross-tab1 has 2 rows showing revenues for each weeknumber for 2015. Cross-tab2 has 3 rows showing revenues for not all weeknumbers for 2015. Cross-tab2 is related to a situation where in week4 the first business of 2015 was established. Of course we want to vertically align both cross-tabs. How do we change the summary formula in cross-tab2 so it will generate some nulls for the revenues in week 1, 2 and 3?

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

To get all weeks in a cross tab you have to have data for that week.

One way to add blank weeks is to add a calendar/dates table to the data source and join it to your data on the date.  

I think what you really want is something like this

Week 1 -  2015-01-01
Source1    P1    100  $200
Source1    P2     200   $300
Source2    X1     100    1000
Source2    X2     200    200

Week2 - 2015-01-08
Source1    P1    100  $200
Source1    P2     200   $300
Source2    X1     100    1000
Source2    X2     200    200


Open in new window

Is that what you want to see?

How about using groups
Group on the date (set for EACH WEEK)
In the group heard put the crosstab for source 1
In the group footer put the cross tab for source 2 (subreport)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
john vAuthor Commented:
that's awesome!
There is usually a way to get what you want once the actual requirement is known rather than trying to force a particular solution to work.

john vAuthor Commented:
The full integration within one single Crystal report however remains the solution for other business critical demands.
Can you elaborate in detail on the intermediate?

The basic idea is to use one or the other source database and create linked tables in it to the other source.

ie  Create linked tables in SQL to FoxPro or in FoxPro to SQL.

Not sure either can do it so the alternative is to use MS Access to create linked tables.

You could then create a UNION query.  Joining them won't work unless you have 1 entry per date.

In theory it will work however linked tables have their own issues in particular performance.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.