SQL Server Performance

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Discussion in 'SQL Server 2005 Analysis Services' started by freedomfee, May 9, 2007.

  1. freedomfee New Member

    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?

  2. freedomfee New Member

    Any help would be really appreciated!

Share This Page