I have a requirement to display the data in the below format
Jan Feb --- --- --- Prv_Month Prv_Mon_Week_1 Prv_Mon_Week_2 Prv_Mon_Week_3 Prv_Mon_Week_4 YTD
Branch A 123 123 --- --- --- 123 123 123 123 123 123
Branch B 123 123 --- --- --- 123 123 123 123 123 123
Branch C 123 123 --- --- --- 123 123 123 123 123 123
-- --- --- --- --- --- --- --- --- --- --- ---
-- --- --- --- --- --- --- --- --- --- --- ---
-- --- --- --- --- --- --- --- --- --- --- ---
Branch Z 123 123 --- --- --- 123 123 123 123 123 123
123 would be the Revenue (Measure). Also in this case Prv_Month would be July, Prv_Month_Week_1 would be 1st week of July, Prv_Month_Week_2 would be 2nd week of July,Prv_Month_Week_3 would be 3rd week of July,Prv_Month_Week_4 would be 4th week of July,Prv_Month_Week_5 would be 5th week of July (5th week might not exist for all months and if it exists we need to display it in the report, so it would be dynamic) and at the end we need show the YTD Total for Revenue i.e total Revenue as of July. This report would always pull the data for the current_year (I can put a filter for the current saying [YEAR] = EXTRACT(YEAR,current_date).
I initially started off with a crosstab...I have been trying to get this to work since past 2 days but no luck! Can some one please put in your thoughts as in how to achieve this?
Thanks in Advance!!!
You do not mention where your attempt fails. Crosstabs work best if the dataset is in typical normalized form , which in your case would be:
dataset:
Branch Datebucket Revenue
Normalized sets are often created by creating sets of union queries which ultimately generate 1 query set to work on.
What could work is:
Query1: Branch / Regular months current year / revenue
Query2: Branch / Previous months split into weekly sets / revenue
Query3: Branch / 'Current year' /Total revenue for the current year
Union these 3 queries into a fourth query (query4) and use this for creating the crosstab.
The resulting crosstab will be extremely simple as you can build a continuous crosstab with just 2 dimensions (Branch / Datebuckettype / Revenue)
Hi, I initially started off with Query1 & Query2. The data items in Query 1 & Query 2 are different and ofcourse a few are same and when I union these 2 queries the resultant query i.e Query 3 has the dataitems from Query 1 only and it does not contain those different query items from query 2. Can you please put in your thoughts regarding this?
Thanks in advance!
Ok- I am trying my report by following this KB article:
http://support.cognos.com/knowledgebase/googlesearch?load_kb_document=1&dr=kb1&uniqueid=126098
I will let you know how it goes...
Thanks!