Reporting from data returned using UNION statement

[thiagarr]

Hi,

I use CR 9 with Oracle 9i. The following is the output obtained from an SQL query with 3 UNION statements. The SQL output is as follows:


PERIOD CODE USAGE
1 USAGE 12911
2 USAGE 14762
3 USAGE 13657
4 USAGE 10356
5 USAGE 6301
6 USAGE 1703
1 Mkt 8.14
2 Mkt 8.87
3 Mkt 8.95
4 Mkt 8.8
5 Mkt 7.3
6 Mkt 7.27
1 BTM 1.06
2 BTM 1.06
3 BTM 1.06
4 BTM 1.06
5 BTM 1.06
6 BTM 1.06
1 EGSH 0.05
2 EGSH 0.05
3 EGSH 0.05
4 EGSH 0.05
5 EGSH 0.05
6 EGSH 0.05


The values in the BTM and EGSH fields may not be the same though most of the time, they are.

I want to create a report from this data and would like to summarize it as follows:

PERIOD USAGE Mkt BTM EGSH New field
1 12911 8.14 1.06 0.05 14406
2 14762 8.87 1.06 0.05 16471
3 13657 8.95 1.06 0.05 15238
4 10356 8.8 1.06 0.05 11555
5 6301 7.3 1.06 0.05 7031
6 1703 7.27 1.06 0.05 1900
76156 8.221666667
Sum Average



The BTM and EGSH column values will be used in oher calculations to obtain the last column - New field as follows:

New field = USAGE * BTM / (1-EGSH)

I was trying to create the report and because of the repeating values, I can only get the USAGE values using a group on PERIOD but have difficulty in getting the other values in the report to show as required.

I was also looking at Cross-Tab but, it does not seem to be possible to have SUM and Average in the same Cross-Tab.

Any help would be appreciated.

Thank you.

Thanks and regards,

TR

 

[synapsevampire]

The basic loik can be accomplished using a crosstab, with Period as the row, Code as the column, and the minimum(usage) as the summary field.

Bothe the total and the average can then be accomplished in the report footer as Running Total fields.

-k

 

[thiagarr]

K,

Thank you very much for the Running Total suggestion. That worked very well.

Now, I have one more issue to resolve - how do I calculate the report column called "New Field" (the rightmost one) in my post? I am able to show the running total for the New Field using the relevant running totals and formulas but not the entries against each Period.

Any help would be appreciated.


Thanks and regards,

TR

 

[synapsevampire]

What is it?

-k

 

[thiagarr]

K,

Thank you for your help.

It is a new calculated field based on the values of USAGE, BTM and EGSH for each period using the formula

New field = USAGE * BTM / (1-EGSH)

I would like to show this field to the right of the report as per my original post.



Thanks and regards,

TR