none
Display columns values that don't exist and set a rows value based on criteria

    Question

  • Here is my script:

    --


    with

    member [Measures].[Credit Hours] as


    iif

    (isempty([Measures].[Attempted Credits]),"0",[Measures].[Attempted Credits]),format_string="#,#"


    member

    [Measures].[Change] as

    case

    when

    isempty((parallelperiod([Term].[Academic Year],1,[Term]),[Measures].[Attempted Credits])) then "N/A"


    else


    (([Measures].[Attempted Credits]) - (

    parallelperiod([Term].[Academic Year],1,[Term]),[Measures].[Attempted Credits]))

    /

    (

    parallelperiod([Term].[Academic Year],1,[Term]),[Measures].[Attempted Credits])


    end

    ,format_string="Percent"


    select


    filter(([Academic Year]),

    ([Academic Years Ago].[6]


    or

    [Academic Years Ago].[5]


    or

    [Academic Years Ago].[4]


    or

    [Academic Years Ago].[3]


    or

    [Academic Years Ago].[2]


    or

    [Academic Years Ago].[1]))


    on

    columns,

    {[Measures].[Credit Hours],[Measures].[Change]}


    on

    rows

    from


    [Student Classes]


    where

    (

    [Academic Level].[COLLEGE CREDIT]

    ,[Enrolled].[YES]

    ,[PCS].[11 BACC/TRANSFER]

    ,[Subject and Course].[GERM]

    ,[Dual Credit].[YES]

    )

    Only want data for Academic Years Ago of 1,2,3,4,5,6 and in this output the data only exists for one Academic Year (2011-2012), but I need to view all Academic Years for the 6 Academic Years Ago because I'm doing a change percent from the previous academic year (parallelperiod).  If the Academic Year does not exist, I need it displayed as a column and the row value for Credit Hours to be zero,   I also need the 6th Academic Years Ago row value for Change to be "N/A".

    Thanks.

    Sue

    Wednesday, July 27, 2016 4:29 PM

Answers