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
