Hi ,
I have problem in the crosstab columns and rows which is based on some conditions following my rows are based on the Region_code . My crosstab consists of 5 rows and few columns. First row consists of all the Region_code (10,11,12,13) , second row is based on Region_code =10, third row is based on Region_code =11, fourth row is based on Region_code =12 , fifth row is based on Region_code =13 .
expression we have used for the rows :
First row:
CASE
WHEN (Region_code in (10,11,12,13)) THEN (' All Region')
END
second row :
CASE
WHEN (Region_code ='10') THEN (' aaa')
END
third row :
CASE
WHEN (Region_code ='11') THEN (' bbb')
END
fourth row :
CASE
WHEN (Region_code ='12') THEN (' ccc')
END
fifth row :
CASE
WHEN (Region_code ='13') THEN (' ddd')
END
We are getting 2 rows of data for each row
for eg : for First row:
I am getting proper data and along with it another row with rowname blank and with some data.
And this is happening for all the remaining rows and columns .
Please guide us on this.
The missing ELSE part in your case will default to null and these will be shown. Use a filter to fetch only region_codes 10,11,12,13
Hi blom,
I have given the filters which fetch only region_codes 10,11,12,13 , still the problem is present.
Please tell me what shall I do.
Did you check the tabular data?
Tabular data is coming fine , but while running the report the display of the data is getting 2 rows of the data for a particular row
Please help me on this its really urgent
I am a bit at a loss what you are trying to achieve here. Each row in the rowset will have a specific Regioncode , but your CASE constructions make little sense to me.
If you want seperate results for the regioncodes and a fifth result for the group of 4 regioncodes then the easiest way is to build the crosstab against a union set:
Set1: [filter : Region_code in (10,11,12,13)]
'All Region'
Set2: [filter : Region_code in (10,11,12,13)]
CASE
WHEN (Region_code ='10') THEN (' aaa')
WHEN (Region_code ='11') THEN (' bbb')
WHEN (Region_code ='12') THEN (' ccc')
WHEN (Region_code ='13') THEN (' ddd')
END
As blom0344 suggested in the first reply, add ELSE clause to your all your case statements.
E.g.
CASE
WHEN (Region_code in (10,11,12,13)) THEN (' All Region')
ELSE cast([Region_Code],char(10))
END
This will tell you what those null values are.
Sriram
http://www.cognosonsteroids.com
Hi All,
I need my output to be as the attached image , That is my I am taking each dataitem for each Region code . So please guide me by taking the attached image as refrence.
You are possibly trying too much within one crosstab this way. AFAIK you can use multiple measures for the fact by dragging them under each other or side by side. You are trying to do both at the same time which may be the cause of the multiple rows generated.
My 2 cents - that is the way we build these things - is to use union sets to make sure data distribution in the columns is driven by dimensional values instead of seperate measure definitions. Our unions may have sometimes as much as 10 sets, but we still build much more complex reports than your example
But this is the requirement , can anybody help me on this .
This really needed .. :(
We made a number of suggestions but throwing the 'requirement' phrase at us will not help. Did you understand the notion of using seperate sets to build the layout you need?