distinct count (yet again)

[blom0344]

I've read all the topics on the use of the distinct count, which in my case I need for defining measures in factless fact tables.
What works best it seems:

(1) add query item to query subject
(2) define object as such (just the expression, no function)
(3) set usage to fact
(4) set regular aggregate to 'Count distinct'
(5) set semi aggregate to unsupported (C8 will otherwise enforce this anyway)

However, in Report Studio the fact has initially an aggregate function of 'total' (in a new report) which leads to crazy figures. (= shocked customers)
Resetting it to 'count distinct' solves this again. (Is this a bug? FM 8.2)

List reports pose no problem, but crosstabs return an error when a dimension from a referenced table is used.
Can't find a good reference how to solve this..

Anyone any ideas?

[Suraj]

Crosstabs always behave little differently as it tries to display an intersection of rows and columns vs. list that just aligns all value in a row.
We've had situation where list has data but crosstab is completely blank. Playing with aggregate and rollup aggregate properties finally displayed the data we wanted.
For some measures, we actually had to set 'Attribute' as data type in FM so that we could get correct results in report studio.
It may be a bug or just how crosstab behaves.

[blom0344]

Crosstabs always behave little differently as it tries to display an intersection of rows and columns vs. list that just aligns all value in a row.
We've had situation where list has data but crosstab is completely blank. Playing with aggregate and rollup aggregate properties finally displayed the data we wanted.
For some measures, we actually had to set 'Attribute' as data type in FM so that we could get correct results in report studio.
It may be a bug or just how crosstab behaves.

The actual problem arises when the report is run. Then an SQL error is generated. However the same data fetched in list format is no problem.

I did not know that one can actually use an attribute in the body of a crosstab. Are you sure that that is possible?

[Suraj]

Hmmm...
If I remember correctly, we did that for some as we were getting wrong rollups.
And I think we used attributes as measure but not to display the attributes itself but to count/summarize them.