Alternative to subreport

[elsenorjose]

CR XI R2
MySQL 5.1
Windows XP SP2

Table structure:

Market
Station
Program
Bytes
ListeningDuration

My report is grouped by:

GH1 Market
GH2 Station
GH3 Program

For each group, I have the following summaries:

Total Bytes (Sum of Bytes)
Total Duration (Sum of Listening Duration)

My client has requested that I place the summaries for each program type before everything else, i.e., in the report header. So the sample output would be something like:

Total Bytes Total Duration
Program Type1 1,500,000 3,650,000
Program Type 2 2,650,000 7,000,000
etc....

I am currently using a linked subreport to display this data in the header but it results in an extremely slow report since the table with this data is over 12 million rows. Is there a way to create these summaries in the report header? Thank you.

 

[synapsevampire]

Create a subreport for the report header.

As the datasoruce, create a Command Object of:

select 'Bytes' MySource, Program, Bytes MyValue from table
UNION ALL
select 'Duration' MySource, Program, ListeningDuration Myvalue from table

Now create a crosstab from this data with program as the row, and MySource as the Column, and MyValue as the summarized field.

Hopefully MySQL supports a UNION Query, those toydatabases tend to have some basic gating limitations...

-k

 

[elsenorjose]

Thanks SV. MySQL does indeed support UNION so this should work.