crosstab aggregated columns - from data base only, not from report.

[arroju_venkat]

Hi,

I have a question like this, could you please help me.

In crosstab, measurable values are calculated [    total([AVH])/count (distinct [ARR_NO])     ]. ..But   some of values are missed in the report, because  the value of AVH or AAR_NO for the particular cell is 'zero', not both values are zero. So blank cells are being displayed.

Moreover the calculation looks like an average, but the No.of records for the total([AVH]) is not equal to the No.of records for the count (distinct [ARR_NO]) .

Now I need  two aggregate columns for rows  & columns.(Vertical & Horizontal)  to this crosstab from data base only, not from the report output .. Because some cells may be blank , but contains  a value, either total([AVH]) or count (distinct [ARR_NO].

aggregate column 1 : total([AVH])/count (distinct [ARR_NO])  for rows
aggregate column 2 : total([AVH])/count (distinct [ARR_NO])  for columns.


Thanks,
Venkat.

[rockytopmark]

I have accomplished this with the use of a Join Query, that joins your data query and a new dim query to provide all the possible combination of row/column intersections.

This new query will be constructed just like your original data query, only that it will not source measureable information, just the dimensional.

Then you create a Join Query.  Join on the dimensions, which should be in both queries. This join query will use a derivation to produce the measure for your crosstab.... use an if then else ...

If ([data query].[measure] is not null)
Then ([data query].[measure])
else(0)

set the Crosstab to use the Join Query.

This technique will provide all possible intersections and 0s where you are seeing nulls now, and will allow proper aggregtion.