Crosstab Heading - Cognos 8

[meenatikare]

Hi,

I am creating a crosstab report with Year-Month as rows and Country as Column. I have a multiselect prompt for Country. Irrespective of whether the data is there for a county or not, columns for the selected county should appear. Is there a way to get this done.

Thanks in advance

 

[Vivarto]

If a value is not in the dataset it does not appear in the crosstab.

(I would check for previous posts as I would not be surprised if this hasn't been asked before.)

Solution: Ensure all required data values appear in dataset. How you do this is where it gets interesting. Effectively you need to 'manufacture' rows. I tend to use a combination of outer and cartiesian joins to achive this in hand written SQL but you're working in Framwork Manager so the task gets more complicated.

If you write SQL along the lines of:

Select country, month-year
From d_country, d_date (note there is no join criteria)

you'll get every country / month-year combination - (cartesian join) Lets say you're only interested in 2006 and Europe (your multi-select) then you could restrict the rows generated:

Select c.country, d.month-year
From d_country c, d_date d
where c.region = 'EUR'
and d.cal_year = 2006

Assuming 12 countries and 12 months you'll get 144 rows. Next join this 'spine' to the facts, something along the lines of:

SELECT spine.country, spine.month-year, nvl(f.amount,0)
From
(Select c.country, d.month-year
From d_country c, d_date d
where c.region = 'EUR'
and d.cal_year = 2006) spine
left outer join fact_table f
on f.country = spine.country
and f.month-year = spine.month-year

You'll now get 144 rows some of which will have data others 0 values as there is no matching country / month-year in the fact table. Your cross-tab will be a 12x12 grid of all countries and all months with data in the cells where values exist and zero's where it doesn't.

That's the theory and it's an approach I've used many times outside FM to get the desired result. I have to emphasis I've not come across this requirement myself in ReportNet and so there may be a simpler approach I'm not aware of.

If I had this requirement I would start by setting up a namespace in FM just for this report and modeling full outer joins between the two dimensions and fact table and seeing if this gives you what you want - sorry I can't give you a definitive answer but this this should give you some ideas to start with

 

[meenatikare]

Viverto, thanks for the reply.
I will try this out

 

[meenatikare]

I implemented the same, by using a union query.
This is how I did it..

I created one query with the data that I wanted.. ie Year-Month, County and Measure (Query1).
Say the data I get through this query for 2007-01 is

2007-01,Country1,120
2007-01,County3,357

I created one more query to get all the distinct country and year-month with 0 value as the measure by overriding the query(Query2). For example for 2007-01,this query gives

2007-01, Country1,0
2007-01, Country2,0
2007-01, Country3,0
....
....
....
Similary for all the selected countries in the prompt.


Created one union query with Query1 and Query2. And changed my measure to total(Measure) so as to get a single value for each combination.
So the Ouput for this query considering the above case is,

2007-01, Country1,120
2007-01, Country2,0
2007-01, Country3,357

This union query when associated with a crosstab gave me the desired output.

Not sure whether this is the optimal solution. But as I don't have access to the Framework manager, guess I need to settle with this.

 

[Vivarto]

Good solution!