Crosstab Column Reference

[technomorph]

Hi,

I am creating a crosstab like the following using a relational source:

		Month 1	Month 2	Month 3
Product Line	Product	10	100	1000
	Product	10	100	1000
	Product	10	100	1000
Product Line	Product	10	100	1000
	Product	10	100	1000
	Product	10	100	1000

My question is how do I create a calculated column which references the data from another column e.g. literally just show the data from Month 1 as in the following:

		Month 1	Month 2	Month 3	Month 1 Ref
Product Line	Product	10	100	1000	10
	Product	10	100	1000	10
	Product	10	100	1000	10
Product Line	Product	10	100	1000	10
	Product	10	100	1000	10
	Product	10	100	1000	10

Before anyone asks the above is a precursor to doing something more complex.

Sounds like it should be simple, but crosstabs in C8 don't play nicely against relational data. Crosstabs in Impromptu have the facility to reference a column directly as a report data item, but this facility is not available in C8.

Any suggestions welcome.

Cheers

[TheDarkKnight]

Hi techomrph,
i tried from side and i cud not find any issue in referencing a data item in crosstab as in ur exmple in C8.

Cud u throw some more light on ur req.

Cheers!!!
TDK

[technomorph]

So what did you do?
Cheers

[MFGF]

Hi,

Are Month 1, Month 2 and Month 3 separate query items in the package, or are they all values from the same query item?  If the former, just drag in a Query Calculation from the toolbox as your fourth column, give it a name, and for the expression drag in the [Month 1] item from the query.  For the latter you will need a slightly more complex expression - along the lines of

if ([Month] = 1) then ([your measure value]) else (0)

After this, you can always go back and edit the expression if you want it to do something more complex.

Regards,

MF.

[technomorph]

The columns are linked to one data item [Month]. The expression you suggested is the obvious choice, however it just returns 0. Other expressions I've experiemented with are:

total([measure] for [Product], [Month]) - returns a value, but not sure from where derived
total(currentMeasure for [Product], [Month]) - syntax error

Looking at the SQL generated, it doesn't closely resemble that of the crosstab, so I suspect there is some local processing going on.

Cheers

[CognosPaul]

MF's solution will work if you set the Aggregate Function to Total.

[technomorph]

If this was the case, then the following would also work:

total([measure] for [Product], [Month])

...but it doesn't.

[CognosPaul]

The total([measure] for [Product],[Month]) wouldn't make sense as a way of catching a specific month.

Consider the following resultset:


Month | Product | Measure
------+---------+---------
    1 |       1 |      10
    1 |       2 |      10
    1 |       3 |      10
    2 |       1 |      20
    2 |       2 |      20
    2 |       3 |      20
    3 |       1 |      30
    3 |       2 |      30
    3 |       3 |      30
    4 |       1 |      40
    4 |       2 |      40
    4 |       3 |      40


The query that MF suggested would result in:

Month | Product | Measure | Month1Only
------+---------+---------+-----------
    1 |       1 |      10 |        10
    1 |       2 |      10 |        10
    1 |       3 |      10 |        10
    2 |       1 |      20 |         0
    2 |       2 |      20 |         0
    2 |       3 |      20 |         0
    3 |       1 |      30 |         0
    3 |       2 |      30 |         0
    3 |       3 |      30 |         0
    4 |       1 |      40 |         0
    4 |       2 |      40 |         0
    4 |       3 |      40 |         0


The problem is the SQL that Cognos generates. If the Aggregate Function is set to the SQL will be incorrect; looking it over I can't even find the expression I wanted in the SQL. However by setting the Aggregate Function correctly the SQL then becomes (modified to make it easier to read):

Code Select Expand

select
   Fact.Product "Productkey"
   , Time.Month "Monthkey"
   , sum(Fact.Measure) "Measure"
   , sum(case  when Time.Month = '1' then Fact.Measure else 0 end ) "Jan"
from
   Fact
   INNER JOIN Time
      on Fact.Time = Time.ID
group by
   Fact.Product
   , Time.Month

[cognostechie]

Technomorph - What Paul means to say is that one you create a Query calculation, set the Aggregrate function of the column heading to Total. Not the column body but the header (label) of the column.

The aggregrate function of the column body would probably be set to Total automatically. That would still show 0s like you said. Once you set the aggregrate function of the column heading to Total, it should show the data. I have a report like this and it works.

[technomorph]

Thanks for the feedback guys. The experience I have had is that crosstabs don't behave like a normal  list object. When checking the SQL generated it is clear that there is some local processing going on.

Because the crosstab uses the Default Measure property which applies across all columns irrespective of whether it's a defined column data item or a calculated query data item, for the latter it of course only makes sense to apply the aggregate property on the column header (otherwise it wouldn't be possible to set the property independently as needed across the different column types.)

For this reason I don't see how setting the aggregate property of the column header to Total or defining the calculation to be total([measure] for [Product], [Month]) is any different. I have used this approach for many list reports. Either way, neither approach is working for my report.

cognostechie, could you forward over the XML for the crosstab report that's working for you?

Cheers

[cognostechie]

Here it is. I have attached the report output and XML.

[cognostechie]

Didn't know it takes only one at a time.

Here is the Report Output.

By the way, moderators - how to put the screen shots in the body of the message without having to attach it?

[cognostechie]

Because the crosstab uses the Default Measure property which applies across all columns irrespective of whether it's a defined column data item or a calculated query data item,

By the way, you can have more than one measure in a Crosstab inspite of setting one measure as the default measure. I have Crosstabs that show Current Hires, Last Years's Hires, Differences, YTD Hires etc all in different rows. The columns are Regions. So inspite of having the Current Year's Hires as the default measure, it overrides that measure by the ones specified in Query calculations.

Ex:

                                                   CA    NY   NJ
Current Year Employees Hired            100   200  150
Last Years Hires                                50   70    200
Diff                                               50     130   -50

In this case, all 3 are different measures

[MFGF]

By the way, moderators - how to put the screen shots in the body of the message without having to attach it?

First, upload the image to an external site such as imgbb.com (which is free). Then copy the IMG tag for the image and paste it into your message body.

MF.