CRN - Aggregate Summaries in Crosstabs 1

[stacybost]

I have a crosstab report. When I add summaries to the crosstab, they are all added with one aggregate function, i.e. 'Total'. However, the crosstab contains percentages, which should be calculated rather than totaled. When I try to change the aggregate function of fields in the crosstab, I get the "QE-DEF-0103 Cross joins are not permitted" error. If I change the Query Property 'Cross Product Allowed' = 'Allow' then the query takes forever to run.

Has anyone been able to use mulitple summary aggregate types in crosstabs? Or do you have any other suggestions? A sample of my crosstab, with inaccurate totals (one % and one average) is below.

Thanks,
Stacy


Period 12, 2004
Checklist 1 Checklist 2
column1 column2 column3 column4 column1 column2 column3 column4
John Smith Jane Doe 2 2 100% 1.0 2 2 100% 1.0
John Doe 10 9 90% 1.1 10 10 100% 1.1
Ken Barbie 3 3 100% 1.0 3 3 100% 1.0
Matt Smith 9 9 100% 1.0 9 9 100% 1.0
Ron Brown 5 5 100% 1.0 5 5 100% 1.0
Steve Black 11 11 100% 1.0 11 11 100% 1.1
Total 40 39 590% 6.1 40 40 600% 6.2

 

[Prasad678]

Hi,
If that doesn't work, then you can try replacing the TMOD with a TSET having the original TMOD and a new TMOD which gives summary info.

Prasad

 

[donb410]

I had to allow Outer Joins also to get my report to run that was reporting this error. Thanks for the tips - it got me going with some things to try and I eventually got
the answer!

Don

Thanks,
Don
(Perpetual Innovations, LLC)

http://www.perpetualinnovations.com

 

[stacybost]

I did eventually get this crosstab to work as I wanted. What I did was this...

-- For the columns that were totals, I changed the aggregate function to 'Automatic'.
-- For the calculated percentage columns, I made the aggregate function 'Calculated' and changed the calculations to: total(DataItem1)/total(DataItem2).
-- I added totals to the crosstab with summary type 'Calculated'.

This worked for me in this situation. I did not have to allow cross-joins.

Prasad, did I miss a post? I noticed you said "if that doesn't work..."

Thanks,
Stacy