john v
asked on
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)
-(sub)totals
Regards, Johnathan
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)
-(sub)totals
Regards, Johnathan
ASKER
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?
Johnathan
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?
Johnathan
ASKER
Alternative:
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?
Johnathan
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?
Johnathan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
mlmcc
mlmcc
ASKER
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?
Johnathan
Can you elaborate in detail on the intermediate?
Johnathan
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.
mlmcc
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.
mlmcc
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
mlmcc