I'm looking to move a series of reports from SSRS to Performance Point dashboards and had a question about MDX queries and parameters.

Here is one of the MDX queries:

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;

As you can see, there are four parameters: @SelectedDiscipline, @SelectedDepartment, @SelectedMonth, and @SelectedYear.

Within the SSRS report, the available values are derived from shared datasets.

My question is how do parameters work in a Performance Point chart/grid and how can I still leverage the shared datasets that comprise the parameters. The datasets are MDX queries. For example, here is the MDX for the @SelectedYear parameter's default values:

SELECT 'Calendar ' + CONVERT(varchar(4), YEAR(CurrentDate)) AS CurrentCalendarYear, 
     '[Time].[Year - Month].[Year].&[' + CONVERT(varchar(4), YEAR(CurrentDate)) 
     + '-01-01T00:00:00]' AS CurrentCalendarYearMDX, 
     '[Time].[Year -  Half Year -  Quarter -  Month -  Date].[Year].&[' + CONVERT(varchar(4), 
     YEAR(CurrentDate)) + '-01-01T00:00:00]' AS CurrentCalendarYearExMDX, CAST(DATENAME(month, 
     CurrentDate) AS varchar(10)) + ' ' + CONVERT(varchar(4), YEAR(CurrentDate)) AS CurrentMonth, 
     '[Time].[Year - Month].[Month].&[' + CONVERT(varchar(4), YEAR(CurrentDate)) 
     + '-' + RIGHT('0' + CONVERT(varchar(2), MONTH(CurrentDate)), 2) 
     + '-01T00:00:00]' AS CurrentMonthMDX, 
     '[Time].[Year -  Half Year -  Quarter -  Month -  Date].[Month].&[' + CONVERT(varchar(4), 
     YEAR(CurrentDate)) + '-' + RIGHT('0' + CONVERT(varchar(2), MONTH(CurrentDate)), 2) 
     + '-01T00:00:00]' AS CurrentMonthExMDX, 'Calendar ' + CAST(YEAR(LastMonth) AS varchar(5)) 
     AS PreviousMonthCalendarYear, '[Time].[Year - Month].[Year].&[' + CONVERT(varchar(4), 
     YEAR(LastMonth)) + '-01-01T00:00:00]' AS PreviousMonthCalendarYearMDX, 
     '[Time].[Year -  Half Year -  Quarter -  Month -  Date].[Year].&[' + CONVERT(varchar(4), 
     YEAR(LastMonth)) + '-01-01T00:00:00]' AS PreviousMonthCalendarYearExMDX, 
     CAST(DATENAME(month, LastMonth) AS varchar(10)) + ' ' + CAST(YEAR(LastMonth) AS varchar(5)) 
     AS PreviousMonth, '[Time].[Year - Month].[Month].&[' + CONVERT(varchar(4), YEAR(LastMonth)) 
     + '-' + RIGHT('0' + CONVERT(varchar(2), MONTH(LastMonth)), 2) 
     + '-01T00:00:00]' AS PreviousMonthMDX, 
     '[Time].[Year -  Half Year -  Quarter -  Month -  Date].[Month].&[' + CONVERT(varchar(4), 
     YEAR(LastMonth)) + '-' + RIGHT('0' + CONVERT(varchar(2), MONTH(LastMonth)), 2) 
     + '-01T00:00:00]' AS PreviousMonthExMDX
FROM (SELECT GETDATE() AS CurrentDate, DATEADD(month, - 1, GETDATE()) AS LastMonth) AS d

Any help would be appreciated!

Could the moderator of this forum PLEASE move this thread to the following forum:

TechNet - Monitoring and Analytics

Thank you!!



Is this forum moderated by anyone??

If I understand your question correctly, then you can convert all your shared datasets in to Custom Sets and build a filter from each dataset to pass values to the reports.

The parameters in PPS can be connected either by using Display Name (Attribute/Text) or by Member Unique Name (fully qualified member name). Connect the filters with the report using either, depending on you are handling them in report's MDX code.

I would suggest to simply reporting by moving more stuff to your OLAP side, will be a lot easier to manage and troubleshoot.

Finally you can use SQL Profiler to see exactly how PPS is communicating with Analysis Services and debug your queries.

I can give the named sets approach a tryu. but how does one handle it if an MDX query for a shared dataset takes a parameter, that is based off another shared dataset?

For example, we have a DISCIPLINE parameter, which has this as its MDX query:

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].[Discipline].AllMembers ON ROWS 
FROM [OLSON BI]

We then have another dataset called DEPARTMENT. The available values of this parameter depend on what DISCIPLINE is passed in:

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 , 
DESCENDANTS(StrToMember(@Disciplines, CONSTRAINED), [Employee].[DISC - DEPT - EMP].[Department], SELF_AND_BEFORE) ON ROWS 

FROM [OLSON BI]

Then there's another dataset called EMPLOYEE which depends on the value of the DEPARTMENT being passed in:

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]
  )
);

Unfortunately, I'm not thinking this is at all possible in Perfromance Point. From what I can see, you don't have the ability to pass parameters into parameters. If I'm wrong, I would love to see an example of how someone handled something like this.

You can have dynamic queries in PPS meaning ability to pass parameters into parameters as long as you treat on parameter as a filter. For example:

If you create a PPS filter named Discipline as:

[Employee].[DISC - DEPT - EMP].[Discipline].AllMembers 

and connect it with you report's Department Axis, you can use the following to get what you want:

DESCENDANTS(<<SourceValue>>, [Employee].[DISC - DEPT - EMP].[Department], SELF_AND_BEFORE) 

Use this code in your filter connection formula on MemberUniqueName. This will give you the descendants of selected discipline.

Hope it helps.



I'm not quite sure what you mean by "connnect it to my Department axis". When creating the analytic grid, I'm choosing the Employee hierarchy, which consists of Discipline-Department-Employee.

When creating the actual filter in my dashboard, I do the following:

- I create a filter called Disciplines that is an MDX query: [Employee].[DISC - DEPT - EMP].[Discipline].AllMembers

- I have a simple report that is comprised of one measure and uses one dimension hierarchy: DISC - DEPT - EMP

- I drop the report into my dashboard. The dashboard consists of one zone.

- I then drag onto the dashboard my Disciplines filter. The filter type is a multi-select tree.

- I choose "Create Connection". For Get Values From I choose "Disciplines" - the name of my filter. For Send Values To I choose Hours By Employee - the name of the report in my dashboard. Under the Values tab for Connect To I choose the name of the hierarchy: [Employee].[DISC - DEPT - EMP]. For Source Value I choose Member Unique Name. In the Connection Formula box, I enter the statement you wrote:

DESCENDANTS(<<SourceValue>>, [Employee].[DISC - DEPT - EMP].[Department], SELF_AND_BEFORE) 

I deploy the dashboard to SharePoint. When the dashboard is viewed, the only thing that appears in the dropdown are the values for the available Disciplines. There are no Departments displayed at all. See the attached screenshot below:

If you are using a multi-select tree filter then you need not enter the Connection Formula since the tree will show all Disciplines and Departments.

Either create a new 'Member selection' filter and select your hierarchy [Discipline-Department-Employee] ; this is the simplest but will include the employees level as well.

OR use the following in your existing filter MDX; replace

[Employee].[DISC - DEPT - EMP].[Discipline].AllMembers

with

DESCENDANTS ( [Employee].[DISC - DEPT - EMP].[Discipline], [Employee].[DISC - DEPT - EMP].[Department] , SELF_AND_BEFORE ) 

The above syntax will give you top 2 levels from your hierarchy excluding employee level.

But I don't want to display all Disciplines and Departments and Employees. When I choose a certain Discipline, I only want the Departments associated with that Discipline, and depending on which Department is chosen, I only want to display the Employees associated with that Department.

I've got a report in SSRS that allows me to do this - I was hoping to mimic this in Performance Point.

Please have a look at cascading filters, they should fit into what you are trying to achieve.

Using cascading filters you will need 2 filters in PPS: Disciplines and Departments. In the member selection choose only the level relevant under auto select. In your dashboard connect the Disciplines filter with Departments filter using MemberUniqueName. Following is how changing a Quarter in first filter will load only the weeks under that quarter. 



Finally connect the Departments filter with the report using Descendants MDX to get only the Employee under the selected department. Hope this helps.