Cross Tab Data displaying extra rows and columns

[learnflower]

 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.

[blom0344]

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

[learnflower]

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.

[blom0344]

Did you check the tabular data?

[learnflower]

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

[learnflower]

Please help me on this its really urgent

[blom0344]

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

[absriram]

As blom0344 suggested in the first reply, add ELSE clause to your all your case statements.

E.g.

Code Select Expand

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

[learnflower]

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.

[blom0344]

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

[learnflower]

But this is the requirement , can anybody help me on this .

This really needed ..  :(

[blom0344]

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?