Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Adding Another Parameter to MDX Query

Answered Adding Another Parameter to MDX Query

  • Monday, February 04, 2013 4:04 AM
     
      Has Code
    Sign In to Vote
    0

    I have the following MDX query which I inherited:

    WITH 

MEMBER [Time].[Year - Month].[YTD] AS AGGREGATE(YTD(StrToMember(@SelectedMonth, CONSTRAINED)))

--< We need to OT over the individual months in YTD >--
MEMBER [Measures].[Overtime Hours] AS 
  IIF([Time].[Year - Month].CurrentMember = StrToMember(@SelectedMonth, CONSTRAINED), 
    [Measures].[Employee Overtime Hours], 
    SUM(YTD(StrToMember(@SelectedMonth, CONSTRAINED)), [Measures].[CALCED OT]))

MEMBER [Measures].[Overtime Hours %] AS '[Measures].[Overtime Hours] / [Measures].[Available Hours minus PTO]'
	
SELECT 
  {
    [Measures].[Employee Hours],
    [Measures].[Employee Hours %],
    [Measures].[Overtime Hours],
    [Measures].[Overtime Hours %],
    [MEasures].[Available Hours]
  } ON COLUMNS,
  NON EMPTY CROSSJOIN(
    FILTER(
IIF(@SelectedDiscipline = @SelectedDepartment,
      DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER),
      DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)
), 
      NOT INSTR([Employee].[Department].CurrentMember.MEMBER_NAME, "Freelance")),
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    {
                	-- PTO Utilization Category excluded
		-- PRODUCTION Utiltization Category included with detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[PRODUCTION]},
			[Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS),
		-- ADMIN Utiltization Category rolled-up witout detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[ADMIN]},
			{[Employee Hours Time Category].[Time Category].[Admin]})
                   },
                   {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]}
          ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [OLSON BI] 
CELL PROPERTIES 
  VALUE
,BACK_COLOR
,FORE_COLOR
,FORMATTED_VALUE
,FORMAT_STRING
,FONT_NAME
,FONT_SIZE
,FONT_FLAGS;

    The query takes two parameters - Discipline and Department. The available values for Department depend on the value of the Discipline chosen. This is handled by an MDX shared dataset. What I need to do is add another parameter. This parameter is @Employee - and can have multiple values. These values will depend on the value that is chosen for the Department. The hierarchy is DISCIPLINE - DEPARTMENT - EMPLOYEE. So the member would look like [Employee].[DISC-DEPT-EMP].[Employee Full Name].

    I'm guessing it would need to be added after the IIF statement where [Employee Level] and [Employee Bill Rate] are selected, just not sure how to modify the query.

    This is how the MDX query looks that populates the available values for Employee Full Name:

    WITH 

MEMBER [Measures].[ParameterCaption] AS [Employee].[DISC - DEPT - EMP].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Employee].[DISC - DEPT - EMP].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Employee].[DISC - DEPT - EMP].CURRENTMEMBER.LEVEL.ORDINAL 

SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[ParameterValue]
   ,[Measures].[ParameterLevel]
  } ON COLUMNS
 ,[Employee].[DISC - DEPT - EMP].[Employee Full Name].ALLMEMBERS ON ROWS
FROM 
(
  SELECT 
    StrToSet
    (@Department
     ,CONSTRAINED
    ) ON COLUMNS
  FROM 
  (
    SELECT 
      StrToSet
      (@Discipline
       ,CONSTRAINED
      ) ON COLUMNS
    FROM [OLSON BI]
  )
);

    Any input would be greatly appreciated!!

    Thanks!

    A. M. Robinson

    • Changed Type ansonee Monday, February 04, 2013 4:44 AM
    •  
     

All Replies

  • Monday, February 04, 2013 5:33 PM
     
      Has Code
    Sign In to Vote
    0

    This is the new query I cam up with, but when I execute it I get the folloing error:

    The DISC - DEPT - EMP hierarchy is used more than once in the Crossjoin function.

    Here is the modified query:

    WITH 

MEMBER [Time].[Year - Month].[YTD] AS AGGREGATE(YTD(StrToMember(@SelectedMonth, CONSTRAINED)))

--< We need to OT over the individual months in YTD >--
MEMBER [Measures].[Overtime Hours] AS 
  IIF([Time].[Year - Month].CurrentMember = StrToMember(@SelectedMonth, CONSTRAINED), 
    [Measures].[Employee Overtime Hours], 
    SUM(YTD(StrToMember(@SelectedMonth, CONSTRAINED)), [Measures].[CALCED OT]))

MEMBER [Measures].[Overtime Hours %] AS '[Measures].[Overtime Hours] / [Measures].[Available Hours minus PTO]'
	
SELECT 
  {
    [Measures].[Employee Hours],
    [Measures].[Employee Hours %],
    [Measures].[Overtime Hours],
    [Measures].[Overtime Hours %],
    [MEasures].[Available Hours]
  } ON COLUMNS,
  NON EMPTY CROSSJOIN(
    FILTER(
IIF(@SelectedDiscipline = @SelectedDepartment,
      DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER),
      DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)
), 
      NOT INSTR([Employee].[Department].CurrentMember.MEMBER_NAME, "Freelance")),
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    {
                	-- PTO Utilization Category excluded
		-- PRODUCTION Utiltization Category included with detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[PRODUCTION]},
			[Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS),
		-- ADMIN Utiltization Category rolled-up witout detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[ADMIN]},
			{[Employee Hours Time Category].[Time Category].[Admin]})
                   },
                   {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]},
                    {StrToSet(@Employees), [Employee].[DISC - DEPT - EMP].[Employee Full Name]}
          ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [OLSON BI] 
CELL PROPERTIES 
  VALUE
,BACK_COLOR
,FORE_COLOR
,FORMATTED_VALUE
,FORMAT_STRING
,FONT_NAME
,FONT_SIZE
,FONT_FLAGS;

    No idea what the issue is...

    A. M. Robinson


    • Edited by ansonee Tuesday, February 05, 2013 2:42 AM format
    •  
     
  • Tuesday, February 05, 2013 12:20 AM
     
      Has Code
    Sign In to Vote
    0

    It seems to be complaining about this 

    IIF(@SelectedDiscipline = @SelectedDepartment,
      DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER),
      DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)
)

    Is there any way you can try to replace the above with a calculated set ?

    Thanks! Josh Ash


    • Edited by Josh Ashwood Tuesday, February 05, 2013 12:22 AM
    •  
     
  • Tuesday, February 05, 2013 2:36 AM
     
     
    Sign In to Vote
    0

    Josh:

    Thanks for the response. That's pretty much what I figured. Unfortunately, I inherited this particular report...

    I was working on trying to find a workaround but no such luck yet.

    Sadly, I'm not all that good with MDX - kind of an intermediate level individual.

    Do you think you might be able to provide a hint or two on how this could be converted to a calculated set?

    Thanks again! 

    A. M. Robinson

     
  • Tuesday, February 05, 2013 3:44 AM
     
      Has Code
    Sign In to Vote
    0

    Well you could try this, sorry I haven't had time to actually verify the syntax but hopefully you get the idea.

    WITH SET [WorkAroundSet]
AS
IIF(@SelectedDiscipline = @SelectedDepartment,
      DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER),
      DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)
)

MEMBER [Time].[Year - Month].[YTD] AS AGGREGATE(YTD(StrToMember(@SelectedMonth, CONSTRAINED)))

--< We need to OT over the individual months in YTD >--
MEMBER [Measures].[Overtime Hours] AS 
  IIF([Time].[Year - Month].CurrentMember = StrToMember(@SelectedMonth, CONSTRAINED), 
    [Measures].[Employee Overtime Hours], 
    SUM(YTD(StrToMember(@SelectedMonth, CONSTRAINED)), [Measures].[CALCED OT]))

MEMBER [Measures].[Overtime Hours %] AS '[Measures].[Overtime Hours] / [Measures].[Available Hours minus PTO]'
	
SELECT 
  {
    [Measures].[Employee Hours],
    [Measures].[Employee Hours %],
    [Measures].[Overtime Hours],
    [Measures].[Overtime Hours %],
    [MEasures].[Available Hours]
  } ON COLUMNS,
  NON EMPTY CROSSJOIN(
    FILTER(
	  [WorkAroundSet], 
      NOT INSTR([Employee].[Department].CurrentMember.MEMBER_NAME, "Freelance")),
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    {
                	-- PTO Utilization Category excluded
		-- PRODUCTION Utiltization Category included with detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[PRODUCTION]},
			[Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS),
		-- ADMIN Utiltization Category rolled-up witout detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[ADMIN]},
			{[Employee Hours Time Category].[Time Category].[Admin]})
                   },
                   {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]},
                    {StrToSet(@Employees), [Employee].[DISC - DEPT - EMP].[Employee Full Name]}
          ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [OLSON BI] 
CELL PROPERTIES 
  VALUE
,BACK_COLOR
,FORE_COLOR
,FORMATTED_VALUE
,FORMAT_STRING
,FONT_NAME
,FONT_SIZE
,FONT_FLAGS;


    Thanks! Josh Ash


    • Edited by Josh Ashwood Tuesday, February 05, 2013 3:44 AM
    •  
     
  • Tuesday, February 05, 2013 3:47 AM
     
     
    Sign In to Vote
    0

    Thanks Josh...I'll give it a try and see what we can do!

    I appreciate it! Let you know how things work out...

    A. M. Robinson

     
  • Tuesday, February 05, 2013 3:26 PM
     
     
    Sign In to Vote
    0

    I gave it a try and unfortunately it threw the same error complaining about the hierarchy being used more than once in the CROSSJOIN function.

    Any other options you can think of off hand? I'm kind of at a loss!

    Thanks!

    A. M. Robinson

     
  • Tuesday, February 05, 2013 10:03 PM
     
      Has Code
    Sign In to Vote
    0
    What I'd try is just the single a call to the following without the iif statement to prove that's actually where the problem is. 
    DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)

    Thanks! Josh Ash

     
  • Monday, February 18, 2013 4:11 PM
     
      Has Code
    Sign In to Vote
    0

    Josh:

    Tried your modifid query, and it still tosses out the CROSSJOIN error. Here's what I modified it to:

    WITH SET [WorkAroundSet]
AS
--IIF(@SelectedDiscipline = @SelectedDepartment,
      DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)--,
     -- DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)
--)

MEMBER [Time].[Year - Month].[YTD] AS AGGREGATE(YTD(StrToMember(@SelectedMonth, CONSTRAINED)))

--< We need to OT over the individual months in YTD >--
MEMBER [Measures].[Overtime Hours] AS 
  IIF([Time].[Year - Month].CurrentMember = StrToMember(@SelectedMonth, CONSTRAINED), 
    [Measures].[Employee Overtime Hours], 
    SUM(YTD(StrToMember(@SelectedMonth, CONSTRAINED)), [Measures].[CALCED OT]))

MEMBER [Measures].[Overtime Hours %] AS '[Measures].[Overtime Hours] / [Measures].[Available Hours minus PTO]'
	
SELECT 
  {
    [Measures].[Employee Hours],
    [Measures].[Employee Hours %],
    [Measures].[Overtime Hours],
    [Measures].[Overtime Hours %],
    [MEasures].[Available Hours]
  } ON COLUMNS,
  NON EMPTY CROSSJOIN(
    FILTER(
	  [WorkAroundSet], 
      NOT INSTR([Employee].[Department].CurrentMember.MEMBER_NAME, "Freelance")),
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    {
                	-- PTO Utilization Category excluded
		-- PRODUCTION Utiltization Category included with detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[PRODUCTION]},
			[Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS),
		-- ADMIN Utiltization Category rolled-up witout detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[ADMIN]},
			{[Employee Hours Time Category].[Time Category].[Admin]})
                   },
                   {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]},
                    {StrToSet(@Employees), [Employee].[DISC - DEPT - EMP].[Employee Full Name]}
          ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [OLSON BI] 
CELL PROPERTIES 
  VALUE
,BACK_COLOR
,FORE_COLOR
,FORMATTED_VALUE
,FORMAT_STRING
,FONT_NAME
,FONT_SIZE
,FONT_FLAGS;

    Hate to be a nuisance, but would love to somehow get this thing behaving properly...! Vexing to say the least!

    Any other suggestions??

    A. M. Robinson

     
  • Monday, February 18, 2013 9:43 PM
     
     
    Sign In to Vote
    0

    Is there a particular reason why "[Employee].[DISC - DEPT - EMP].[Employee Full Name]" is added to the set: {StrToSet(@Employees), [Employee].[DISC - DEPT - EMP].[Employee Full Name]} ?

    I think that may be where the error lies...have you tried just doing {StrToSet(@Employees)} ?

    Hope that helps..

     
  • Monday, February 18, 2013 10:59 PM
     
     
    Sign In to Vote
    0
    You need to declare what hierarchy the names can be found in

    A. M. Robinson

     
  • Tuesday, February 19, 2013 5:57 PM
     
     
    Sign In to Vote
    0
    Did you try it without the hierarchy in there and just having {StrToSet(@Employees)}?
     
  • Tuesday, February 19, 2013 6:00 PM
     
     
    Sign In to Vote
    0

    If I understand your code right, and the value of the parameter that is being passed, your query would like:

    ....

    {StrToSet("{[Employee].[DISC - DEPT - EMP].[Employee Full Name].[John Doe]}"), [Employee].[DISC - DEPT - EMP].[Employee Full Name]}

    ....

    which is not going to work.

     
  • Tuesday, February 19, 2013 10:18 PM
     
     
    Sign In to Vote
    0
    I'm going to be passing in more than one employee name...

    A. M. Robinson

     
  • Tuesday, February 19, 2013 11:10 PM
     
      Has Code
    Sign In to Vote
    0

    Ok, I see what's going on now....do you need to have the employees returned in your data set, or is it juts a filter that's applied in the report?

    If it's just a filter you could try something like:

    .....
    NON EMPTY CROSSJOIN(
    FILTER(
	  [WorkAroundSet], 
      NOT INSTR([Employee].[Department].CurrentMember.MEMBER_NAME, "Freelance")),
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    {
                	-- PTO Utilization Category excluded
		-- PRODUCTION Utiltization Category included with detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[PRODUCTION]},
			[Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS),
		-- ADMIN Utiltization Category rolled-up witout detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[ADMIN]},
			{[Employee Hours Time Category].[Time Category].[Admin]})
                   },
                   {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]}
          ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM 
    	(SELECT {StrToSet(@Employees), [Employee].[DISC - DEPT - EMP].[Employee Full Name]} ON COLUMNS
    	 FROM [OLSON BI] )

     
  • Tuesday, February 19, 2013 11:22 PM
     
     
    Sign In to Vote
    0

    Actually, yes...I would need to return the employee name(s) in the result set.

    Thanks for any help you can provide...!

    A. M. Robinson

     
  • Tuesday, February 19, 2013 11:27 PM
     
      Has Code
    Sign In to Vote
    0

    Ok, actually I think that would be handled here:

    IIF(@SelectedDiscipline = @SelectedDepartment,
      DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER),
      DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)
),

    Assuming that the Employee is the next level after Department, it should return a list of the employee(s), filtered with the employee(s) selected in the parameter. 

     
  • Wednesday, February 20, 2013 12:43 AM
     
      Has Code
    Sign In to Vote
    0

    Thanks!

    I used your query and it works fine in report designer, but for some reason when I try to run the report in Reporting Services, I get an error:

    An MDX expression was expected. An empty expression was specified.

    Here's the query:

    WITH SET [WorkAroundSet]
AS
IIF(@SelectedDiscipline = @SelectedDepartment,
      DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER),
      DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)
)

MEMBER [Time].[Year - Month].[YTD] AS AGGREGATE(YTD(StrToMember(@SelectedMonth, CONSTRAINED)))

--< We need to OT over the individual months in YTD >--
MEMBER [Measures].[Overtime Hours] AS 
  IIF([Time].[Year - Month].CurrentMember = StrToMember(@SelectedMonth, CONSTRAINED), 
    [Measures].[Employee Overtime Hours], 
    SUM(YTD(StrToMember(@SelectedMonth, CONSTRAINED)), [Measures].[CALCED OT]))

MEMBER [Measures].[Overtime Hours %] AS '[Measures].[Overtime Hours] / [Measures].[Available Hours minus PTO]'
      
SELECT 
  {
    [Measures].[Employee Hours],
    [Measures].[Employee Hours %],
    [Measures].[Overtime Hours],
    [Measures].[Overtime Hours %],
    [MEasures].[Available Hours]
  } ON COLUMNS,

NON EMPTY CROSSJOIN(
    FILTER(
	  [WorkAroundSet], 
      NOT INSTR([Employee].[Department].CurrentMember.MEMBER_NAME, "Freelance")),
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    {
                	-- PTO Utilization Category excluded
		-- PRODUCTION Utiltization Category included with detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[PRODUCTION]},
			[Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS),
		-- ADMIN Utiltization Category rolled-up witout detail
		CROSSJOIN(
			{[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[ADMIN]},
			{[Employee Hours Time Category].[Time Category].[Admin]})
                   },
                   {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]}
          ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM 	(SELECT {StrToSet(@Employees), [Employee].[DISC - DEPT - EMP].[Employee Full Name]} ON COLUMNS	 FROM [OLSON BI] )
    Any thoughts?

    A. M. Robinson

     
  • Wednesday, February 20, 2013 3:28 PM
     
     
    Sign In to Vote
    0
    Are you providing a value to the @Employees parameter? Also, make sure you have a set specified for the default value of your parameter
     
  • Wednesday, February 20, 2013 3:43 PM
     
     
    Sign In to Vote
    0

    I was able to fix the error, but the results aren't appearing as expected.

    Even though I choose let's say only two employees out of a possible 12, all 12 employees are appearing in the report. It appears if the report is ignoring the actual parameter values in Employees.

    A. M. Robinson

     
  • Wednesday, February 20, 2013 3:47 PM
     
      Has Code
    Sign In to Vote
    0

    Try just using:

    FROM 	(SELECT {StrToSet(@Employees)} ON COLUMNS	 FROM [OLSON BI] )


     
  • Wednesday, February 20, 2013 3:54 PM
     
     Answered Has Code
    Sign In to Vote
    0

    I was able to run this past someone else and they came up with a solution that yields the expected results:

    WITH SET [WorkAroundSet]
AS
IIF(@SelectedDiscipline = @SelectedDepartment,
      DESCENDANTS(StrToMember(@SelectedDiscipline, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER),
      DESCENDANTS(StrToMember(@SelectedDepartment, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], AFTER)
)

MEMBER [Time].[Year - Month].[YTD] AS AGGREGATE(YTD(StrToMember(@SelectedMonth, CONSTRAINED)))

--< We need to OT over the individual months in YTD >--
MEMBER [Measures].[Overtime Hours] AS 
  IIF([Time].[Year - Month].CurrentMember = StrToMember(@SelectedMonth, CONSTRAINED), 
    [Measures].[Employee Overtime Hours], 
    SUM(YTD(StrToMember(@SelectedMonth, CONSTRAINED)), [Measures].[CALCED OT]))

MEMBER [Measures].[Overtime Hours %] AS '[Measures].[Overtime Hours] / [Measures].[Available Hours minus PTO]'
      
SELECT 
  {
    [Measures].[Employee Hours],
    [Measures].[Employee Hours %],
    [Measures].[Overtime Hours],
    [Measures].[Overtime Hours %],
    [MEasures].[Available Hours]
  } ON COLUMNS,
  NON EMPTY CROSSJOIN(
    FILTER(
        INTERSECT([WorkAroundSet], StrToSet(@Employees, CONSTRAINED)), 
      NOT INSTR([Employee].[Department].CurrentMember.MEMBER_NAME, "Freelance")),
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    {
                  -- PTO Utilization Category excluded
            -- PRODUCTION Utiltization Category included with detail
            CROSSJOIN(
                  {[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[PRODUCTION]},
                  [Employee Hours Time Category].[Time Category].[Time Category].ALLMEMBERS),
            -- ADMIN Utiltization Category rolled-up witout detail
            CROSSJOIN(
                  {[Employee Hours Time Category].[Utilization Category].[Utilization Category].&[ADMIN]},
                  {[Employee Hours Time Category].[Time Category].[Admin]})
                   },
                   {StrToMember(@SelectedMonth, CONSTRAINED), [Time].[Year - Month].[YTD]}--,
                  --  {[Employee].[DISC - DEPT - EMP].[Employee Full Name]}
          ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [OLSON BI]
    Thanks for the input, however!!

    A. M. Robinson

    • Proposed As Answer by vhrocca Wednesday, February 20, 2013 4:14 PM
    • Marked As Answer by ansonee Wednesday, February 20, 2013 4:22 PM
    •