Cross Tab - Data Issues

[learnflower]

Hi ,

I have created Crosstab report which is having different Views in the same report .
1.>Data Differing in both Cross tab for same  [AYear]-1
I having 2 data issues in this For first crosstab report needs to display Data depends on the Year and Year-1 and 2nd cross tab report needs display Data depends on the   [AYear]-1 , [AYear]-2 , [AYear]-3,[AYear]-4 . I am taking AYear in the parameter and giving filters to first Crosstab

[CR_Code]='28' ,
[AYear]=?P_ADate? or[AYear]=?P_ADate?-1 ,
[Notified Year]=[AYear] ,
[PUYear]=[Notified Year]  and for the second cross tab I am taking the filters
[CR_Code]='28' ,
[AYear]=?P_ADate?-1or [AYear]=?P_ADate?-2 or [AYear]=?P_ADate?-3 or [AYear]=?P_ADate?-4 ,
[Notified Year]=[AYear] ,
[PUYear]=[Notified Year]
My data is differing for the  [AYear]-1 in both the cross tabs . Can u Please help on this

2.> I want to display the year and data as zero if the data is not available for the conditions mentioned for the crosstab

Please help me on this issues .

[blom0344]

You should take both generated query definitions and their respective output as tabular data. We cannot check through the forum why data is different.

When applying a format to the data you can define which symbol to display for 'missing' data

[learnflower]

Hi blom,

Thanks my first issue is resolved but for the second issue for missing data , i need to display the zero is not working can suggest me something on this.

regards

[blom0344]

What do you mean by missing data? Absent values (no value for intersection)  or non-existent rows/columns?

[tjohnson3050]

Have you tried using the data format property for missing values?

[learnflower]

blom ...

What i mean by missing data is for the particular filter i am not getting the data for particular year means i need to display the year in the row and values as zero can i do it .. this what i want to do

[HalfBloodPrince]

You need to creat Outer join in the quries for that.

[learnflower]

I have alreday tired this Outer join method, but its not working  .... halfbloodprince

[blom0344]

No, outer joins are usually not enough. you simply miss intersections to build the crosstab you want. Relational models are weak compared to dimensional models in this respect.

My preferred solution - though a bit of a workaround - is to build a union. First set fetches the 'real' data, whereas the second set fetches a crossjoin of the dimensions involved with a dummy zero.

The union set will then have ALL intersections required without changing the fact data (as zero + value = value)

So as an example:  When you want data from all customers for all months in a given range then the second set will be nothing more than the cartesian product of customer and time dimension.

The ultimate report can then show both customers without sales as well as months with no sales (as all intersections exist)