Creating % calculation in Cross Tab using Query Calculation.

[stevekoz]

First of all new to forum. So hi everyone.

Second, i'm a novice user of Cognos so learning as i go.

I am using report studio to create a report as neither Analysis or Query enable to chop and change the report outlay as needed by my business.

I have created the following cross tab for a KPI results table, with a graph sitting over it.

The structure is as -

                                              KPI 1         KPI 2          KPI 3          KPI 4
Year | Month | Location         KPI Data    KPI Data    KPI Data     KPI Data
Year | Month | Location         KPI Data    KPI Data    KPI Data     KPI Data
Year | Month | Location         KPI Data    KPI Data    KPI Data     KPI Data
Year | Month | Location         KPI Data    KPI Data    KPI Data     KPI Data
Year | Month | Location         KPI Data    KPI Data    KPI Data     KPI Data
          Total Month                 Total          Total          Total           Total
Total YTD                               YTD            YTD            YTD             YTD

I apologise for naff diagram of table layout - i have attached a copy of how it looks in Cognos.

The report is to replace a current manual extraction of similar data in a simple excel sheet as part of a rollout of Cognos 8.4 as a new BI tool. 

The Columns for KPI include all the results for the specific KPIs - hopefully as the diagram above shows.

I need to create a calculation of % difference of the KPI data for say KPI 3 against KPI 2.

So if it were excel it would be a simple KPI 3/KPI 2 (as a %). 

I can't seem to find a way of doing this in the tool - the calculation insert is blanked out.

I can insert a Query Calculation but although i've managed to write a couple of filters i'm unsure how to write a calculation.

Apologies, this may be a really novice question. But as said i'm utterley new to using this and working without training play as i go really. #

Any questions (im sure) ask - i know i probably haven't explained myself extremely well.

thanks.

[navissar]

Not sure I followed, but if I did, it's just a matter of dragging a query calculation in and setting it to [kpi3]/[kpi2]

[stevekoz]

Hi. thanks for the reply. Sorry.

First off can you see the file attachment, may be better than the example i've created above.

Not sure how to make it clearer (my fault sorry)

First i start with the dimensions of by Year/ by Month/by Location/KPI Name/KPI Summary (all data)
I then pivot on crosstab so i have rows -

By Year | By Month | By location
and my pivot in columns = KPI Name

With the Measure "KPI Summary" to pull in all relevant results for above "KPI Names"

When i run the report it extracts all the relevant different KPI's within the dimension "KPI Name" - so extracts and breaks down to KPI 1,. KPI 2 etc etc.

When pulling in a query calculation. How do i define in the calculation that my dimension in column A "KPI Name" includes "KPI 2" and "KPI 3" and then do the calculation as you've stated KPI 3/KPI 2.

[stevekoz]

Right i have figured out how to define multiple values of my query in the expression definition.

So i can now define percentage ('KPI 3'/'KPI 2')

However i keep getting an sql error now. UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult"

The data in teh report though validates without error. Its only when i run it do i get this error.

[blom0344]

1. Validating the report does not test the queries associated , but simply checks the xml definition

2. You need to make sure that your new query calculation is defined properly. So in your case you may need to code something like : total([KPI 3])/total([KPI 2]) with the aggregate set to 'calculated'

[nbailey]

I am also having this issue.  I have rolled up the same aggregate into 2 separate columns one for each year. (academic year 2011 and 2010).  our aggregate is student id number.  I am trying to determine the percentage change of students enrolled in 2010 against those enrolled in 2011.  I have used a multi-query report ( curr year and prior year) with a union on the 2 querys, creating a single query with one amout field which get assigned to each column based on the academic year.  How do I create this percentage that I need?  see attachments for the report example.   thanks in advance for any help !!!


   

[stevekoz]

1. Validating the report does not test the queries associated , but simply checks the xml definition

2. You need to make sure that your new query calculation is defined properly. So in your case you may need to code something like : total([KPI 3])/total([KPI 2]) with the aggregate set to 'calculated'

I have tried the above.

Firstly i have managed to define KPI 3/KPI 2 by choosing the relevant multiple values in the available query components .  So that is now being pulled in to the expression definition as follows -
('Near Miss Close Out', 'Near Miss')

So now i have those defined. Trying above i get an error.
Total ('Near Miss Close Out'/ Total ( 'Near Miss'))

I know it must be how i'm writing it. Sorry. I can see where i want to get too. And i know i am close but having never used this tool before its this last step i'm falling down on so i appreciate the help.

[stevekoz]

uploaded a new example. screen print of where i am with expression definition - not far.

In ref to the screen print. I need to get % value of Near Miss Close Out/Near Miss

[blom0344]

What are you doing? The idea is to build new components within the query, so your calculations should reference other dataitems (from the model). 

If KPI 1 , KPI 2 , KPI 3,  KPI 4  are values belonging to one dataitem ( [KPI_ITEM]  as I suspect then the calculation would be something like:

Code Select Expand


TOTAL(CASE WHEN [KPI_ITEM] = 'KPI 2' THEN [KPI DATA] ELSE 0 END) /
TOTAL(CASE WHEN [KPI_ITEM] = 'KPI 3' THEN [KPI DATA] ELSE 0 END)



[barrysaab]

stevekoz,could you please post your image clearly,it is very hard to see now.Thanks

[stevekoz]

Solved thank you.

Apologies for being thick - i have never used Cognos before, nor am i a programmer or anything like that so this is a steep learning curve. Thanks for the assistance. Appreciated. Sure i'll be back asking dumber questions in the future.

[barrysaab]

Glad you worked it out.Could you please share your final solution in detail.Thanks