We help IT Professionals succeed at work.

Two Crosstabs w/Two Different Data Soures within Same CR Rpt

ShadowIT
ShadowIT asked
on
Hello Experts,

I am attempting to display two crosstabs within the same CR (no subreports) that have two different datasources that are unrelated.  However when I do this one of the crosstabs skews the results of the other.

I have tried unlinking them (I realize this is not supported) and relating them by a date field and/or Region fields they both happen to share within the underlining Access 2007 Dbase.  I have also tried Not Enforcing Join and placing the crosstabs in different sections.

Is there a way to accomplish this without using a subreport?

Thank you
Comment
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I can't really think of a way.

You could try a UNION command like

SELECT A.Value, A.RowValue, A.ColValue, 0 as BF1
FROM A
UNION ALL
SELECT 0 as Value, B.RowValue, B.ColValue, B.Value
FROM B

Use A.Value, A.RowValue, A.ColValue for one
Use A.RowValue, A.ColValue, BF1 for the other

mlmcc

Author

Commented:
UNION won't work because the tbls I am using as datasources aren't exactly the same.   They have a Date and Region field in common but the other remaining fields don't match each other.

Also I don't really want them related, I want to display two crosstabs that have differing datasource within the same rpt.

Thanks though...
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
A union can work.

You just list the fields in table 1 and table 2 in a single select.  You then change the table2 fields to default values  with 0 as table2Value
Union that to another select created similarly but change table 1 fields

Crystal will use the field names from the first select

Just use the table1 fields in one crosstab and table2 fields in the othe cross tab

The only issue will be what the 0s and the "" from table 1 will do to the table 2 cross tab but I believe you can suppress blank or 0 rows.

mlmcc
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Here is a small example

mlmcc
Q-27476321.rpt
Q-27476321.mdb
 > I have also tried Not Enforcing Join and placing the crosstabs in different sections.

 FWIW, I wouldn't expect that to help.  If you include a table in a report, but don't actually use any fields from that table, then CR will normally not include it in the query, since you're not really using it.  The Enforce options override that behavior and tell CR to include an unused table in the query, even though it's not used in the report.  Since you're presumably using the tables in question in your cross-tabs, the Enforce options won't have any effect.


 As for the UNION idea, I haven't looked at mlmcc's sample report, but the basic idea is that if you have one query with field1 and field2, and another query with fieldA and fieldB, you could combine them using something like this:

SELECT
1 AS Type,
field1,
field2,
0 AS fieldA,
0 AS fieldB,
FROM table1

UNION

SELECT
2 AS Type,
--field1
0,
--field2
0,
fieldA,
fieldB,
FROM table2


 Depending on your report, it might be possible to group the report on the TYPE column and put your cross-tabs in separate group header/footer sections, with each section suppressed based on the TYPE.  That way you wouldn't have to deal with each cross-tab including the records that were meant for the other cross-tab.

 James
I'm being kind of picky here, since it's not real code, but, just for the record, there should not be a "," after fieldB in my 2 example SELECT statements.

 James

Author

Commented:
I get it...very clever...thanks so much.  

SELECT tblPurchases.Supplier, tblPurchases.Purchase, tblPurchases.PurchaseDate, tblPurchases.SupplierState, "A" as t2Customer, 0 as t2Sales, "" as T2Date, "" as T2State
FROM tblPurchases
UNION
SELECT "A" as t2Supplier, 0 as t2Sales, "" as T2Date, "" as T2State, tblSales.Customer, tblSales.Sales, tblSales.SaleDate, tblSales.CustomerState
FROM tblSales