DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Hello, I have a DB of professors and informatikon related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)Amount of e_books (COUNT book_id)
————–
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
————–
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
————–
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
————–
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
—————————
Dimensions:
dimPROFESSORS
– prof_id
-surname
-name
-gender
dimPROJECTS
– proj_id
-type name
-name
dimPUBLICATIONS
– pub_id
-type name
-name
dimE_BOOKS
– book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years
For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032——————- 30 ————————–1—————-1
1070——————-90 ————————–2—————-2
1111——————-0 —————————1—————-0
1137——————-0 —————————1—————-0
1234——————-1404————————–9—————-9
1721——————-504————————–7—————-7
2661——————-85 ————————–5—————-5
…——————–…—————————…—————-…
6999——————- 20—————————1—————–1
9956——————-50—————————5—————–5
Unknown—————-(empty)———————(empty)———–0
Grand Total———— 2421————————11—————–11 Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
Any help would be really appreciated!
]]>