How to calculate variance between 2 months after user selects from 2 prompt

[jamesqi]

User can select any month from prompt1 and another month from prompt2. Now I can show the sales amt for prompt1 and prompt2, but I cannot show the variance between these 2 columns.

Anybody has created a report like this? Please help.

James

[blom0344]

The query that fetches the months and sales data should have a filter like:

[month item] = ?parameter1? or [month item] = ?parameter2?

Where parameter1 is associated with prompt 1 and parameter2 is associated with prompt 2

You can then create 2 calculated items like:

Code Select Expand


CASE WHEN [month item] = ?parameter1?
THEN [SALES] ELSE (0) END


and use these in your variance calculation..

[jamesqi]

My data source is cube. I'll try your solution. Thanks, James

[blom0344]

Sorry, my solution was not aimed at a cube, just for a relational source..

[jamesqi]

I've done this report successfully. I use one query to get the amt for month1(for example, user chose 2008-May for prompt1). Assign 0 to month2 amt.

2008-May
Product line  Month1 amt  Month2 amt
A001 1,500  0
A002 4,358  0

Use another query to get the amt for month2(for example, use chose 2008-Jan for prompt2).  Assign 0 to month1 amt.

2008-Jan
Product line  Month1 amt  Month2 amt
A001 0  7,654
A002 0  2,345

Union these 2 queries to Query3. And create a calculated column to compute the variance.
Product line  Month1 amt  Month2 amt  Variance
A001 1,500  0 1,500
A002 4,358  0 4,358
A001 0  7,654 (7,654)
A002 0  2,345 (2,345)

Drag in a crosstab and get the resultant report.
       Month1 amt Month2 amt Variance
A001 1,500  7,654  (6,154)
A002 4,358  2,345  2,013
Total 5,858  9,999  (4,141)

It's working. Hope it helps when somebody creates this kind of reports.

[clion]

Hi,

I'm very new to COGNOS and try to self-study about some reporting options with my company's COGNOS 10.
My first report is very similar to what you guys discussed above. I need to use the Customer Name field, Year field (filter two different years), Month field (filter one or many months associated with Year), Product Category field (filter one or many categories) and Revenue field from a Package.

In the report user will select two different years (visible in the report), one or multiple months of associated years (not visible in the report), and one or multiple product categories (not visible in the report).
Then the report provides the list of customers in the row and first column is the revenue of first period/second column is the revenue of second period/third column is the variance of those two period.

I thought CrossTab report is the right choice, but I cant' get the variance part.
I read your postings above, but what I don't understand is mainly two things.
First, I'm not sure if I need to use Jamesqi's way or blom0344's way because I'm not sure if my data is in cube or not (not really understanding the difference actually).
Second, if I use the Jamesqi's way, how to put two different periods side by side in CrossTab column and show one period zero amount? Also, how to join  those two different queries in one? My join function didn't really work well.
If I use blom0344's way, where do I add the "case" function?

Thank you for your help in advance,   

[blom0344]

You are aware this is an 8 year old post you re-open and in Cognos8 instead of Cognos10 part of Cognoise?
Dimensional vs relational should be obvious from the symbols used within report Studio. A dimensional package will allow you to view the query in MDX , a relational package only in SQL. Adding a calculated item to a query should be no problem with the extensive help available on the subject

[clion]

Thank you for your reply. I'll open a new post next time. Rookie mistake.