Crosstab over Relational DB: Create Column Variances

[adam_mc]

I have a user request for the following Crosstab (see below).

                                            PERIOD1   PERIOD2     (PERIOD1 - PERIOD2)
Product Type   Measure
A                      Sales                999           777                  222
                        Units                 999           777                 222
                        Margin              999           777                  222
B                      Sales
                        Units
                        Margin

Data is relational and PERIOD1 and PERIOD2 are values derived from a single calculation based on a range of Fiscal Weeks.
I can get the Crosstab to format as needed without the  (PERIOD1 - PERIOD2) variance column, but am having major difficulties adding this column.
Creating a Summary gives me the total of the two periods, but how do I get the differences for all measures?

Any thoughts will be greatly appreciated.
Thanks in advance,
Adam.

[navissar]

Hi Adam,
A very cool Cognos feature is that it creates a mini-cube for crosstabs; basically your relational data is being pivoted on Cognos servers and then saved as a tiny cube, which then becomes the data source for the crosstab. It can be a royal pain in an uncomfortable place when administrating the servers or when doing performance enhancement, but it's a blessing when you're trying to do dimensional stuff over relational.

Generally, what you want to do is to create your query like this:
1. Put in all the measures you want.
2. Create a data item that will calculate "Period 1", name it whatever you want. For our example I'll name it "per1". I'm not sure what your logic is precisely with the fiscal weeks, so I'll just use an example of filtering a specific year:
filter([namespace].[date dim].[year],[namespace].[date dim].[year]=2011)
See that? That's a dimensional function, but we can use it!
3. Create another data item, name it anything you'd like, to calculate "Period 2".For our example I'll name it "per2". In our example:
filter([namespace].[date dim].[year],[namespace].[date dim].[year]=2012)
4. Create a data item, name it "Period 1" and set its value to:
total(currentMeasure within set [Per1])
(Yes, this will work even though you're on relational)
Repeat that for "Period 2":
total(currentMeasure within set [Per2])
5. Create another data item named "Period 1 - Period 2" and set it up thusly:
[Period 1]-[Period 2]

You're basically done. Put your measures in your crosstab rows, and data items "Period 1", "Period 2" and "Period 1 - Period 2" in columns.
I'm attaching the XML of a report made in 10.2.1 (Don't have anything older, I'm afraid) based on samples.

Good luck!

[adam_mc]

Nimrod...

Thanks this works fantastically!

However, in taking it to the next level, I am encountering an additional problem.

I have the page layout defined as below (with the PERIODs defined as you suggested):


                                            TEST PERIOD  BASE PERIOD   TEST-BASE
Product Type   Measure
A                      Sales                999              777                  222
                        Units                 999              777                 222
                        Margin              999              777                  222
B                      Sales
                        Units
                        Margin
                                                   
     
       
However,  when I try and add another Member level (for TEST STORES and CONTROL STORES) above my PERIOD Level.
I am getting the following errror:

Invalid coercion from 'memberSet' to 'string' for '[PERIOD]' in 'filter([PERIOD],[PERIOD] = 'TEST PERIOD')'.

My Page Layout looks like below:

                                                        TEST STORES                               CONTROL STORES
                                            TEST PERIOD  BASE PERIOD            TEST PERIOD  BASE PERIOD                                                     
Product Type   Measure
A                      Sales                999              777                   
                        Units                 999              777                 
                        Margin              999              777                   
B                      Sales
                        Units
                        Margin

The TEST STORES and  CONTROL STORES calculations are defined as below (similar to the PERIOD definitions):

TEST STORES=filter ([STR GRP],[STR GRP]='TEST STORES')
CONTROL STORES=filter ([STR GRP],[STR GRP]='CONTROL STORES')

TEST STORES and CONTROL STORES are driven by two user entry prompts.

Again, any help will be greatly appreciated.
Thanks in advance,
Adam.

 

[navissar]

Yep, that's a bit over the edge. I'll take a few days to think about it and see if I can come up with something.

[adam_mc]

Interestingly it works as needed if I set the Page Layout as follows:

                                                                                    STR GRP
                                                        TEST STORES                               CONTROL STORES
                                            TEST PERIOD  BASE PERIOD            TEST PERIOD  BASE PERIOD                                                     
Product Type   Measure
A                      Sales                999              777                   
                        Units                 999              777                 
                        Margin              999              777                   
B                      Sales
                        Units
                        Margin


Where STORE GRP = CASE WHEN [Site] IN (?parm_TestStores?) THEN 'TEST STORES' ELSE 'CONTROL STORES' END
and STR GRP is the driver for the TEST STORES/CONTROL STORES calculation (see thread)

I can then, in theory, "hide" the STORE GRP Level and get the results I need.
However, I must admit, I don't think that this is the optimal or "prettiest" solution!

[raj_aries81]

Hi Adam,
A very cool Cognos feature is that it creates a mini-cube for crosstabs; basically your relational data is being pivoted on Cognos servers and then saved as a tiny cube, which then becomes the data source for the crosstab. It can be a royal pain in an uncomfortable place when administrating the servers or when doing performance enhancement, but it's a blessing when you're trying to do dimensional stuff over relational.

Generally, what you want to do is to create your query like this:
1. Put in all the measures you want.
2. Create a data item that will calculate "Period 1", name it whatever you want. For our example I'll name it "per1". I'm not sure what your logic is precisely with the fiscal weeks, so I'll just use an example of filtering a specific year:
filter([namespace].[date dim].[year],[namespace].[date dim].[year]=2011)
See that? That's a dimensional function, but we can use it!
3. Create another data item, name it anything you'd like, to calculate "Period 2".For our example I'll name it "per2". In our example:
filter([namespace].[date dim].[year],[namespace].[date dim].[year]=2012)
4. Create a data item, name it "Period 1" and set its value to:
total(currentMeasure within set [Per1])
(Yes, this will work even though you're on relational)
Repeat that for "Period 2":
total(currentMeasure within set [Per2])
5. Create another data item named "Period 1 - Period 2" and set it up thusly:
[Period 1]-[Period 2]

You're basically done. Put your measures in your crosstab rows, and data items "Period 1", "Period 2" and "Period 1 - Period 2" in columns.
I'm attaching the XML of a report made in 10.2.1 (Don't have anything older, I'm afraid) based on samples.

Good luck!

This approach really made my day..I liked the approach in arriving at variance between two crosstab columns which is not a straight forward calculation

Thanks
Raj

[john333money]

Hi and thanks for the really useful post. And of course, there's a but...

I've used the 'filter' technique  very successfully in the past but I've run into a small (i.e. very annoying) problem this time.
I have a crosstab with weekly values for a calculated measure (money divided by count giving a 'per count' individual value) and want to show the difference between the two final columns within the crosstab. And here's the but/ When I try to subtract one value from the other I get seemingly random values, or non-numerics.

The two column values I'm isolating use two data type variables each - the first is the filter called fin (and pen) (filter, [Week Num Relative], [Week Num Relative]= -1) and the second is the currentMeasure one (total(currentMeasure within set [Fin]). The results are spot on, but when I try to create another data item to subtract one from the other I get figured that don't tally with anything (the first row has a final column value of 94.03 and a penultimate one of 93.03 - but a difference of 76.74.....).

I am wondering if it is a result of the fact that the measure in question is the product of an earlier calculation, but any help wpuld be much appreciated.

John