locked
Pass parameters from performancepoint filter to MDX query RRS feed

  • Question

  • Hi,

    I have deployed my Reports(SSRS) in performance point dashboard,and i have created dashboard filter to connect to the report filter. In that i need to filter the filter values depends on the current performancepoint login username. I am using MDX query for getting the filter values depends on the current login username. But the MDX query is not working for me.

     And also i need to know about if there is any possible way to pass any values from performance point filter  to  SQL server using mdx  MDX?

     

     

    Monday, February 21, 2011 1:37 PM

Answers

  • Here is how you can make sure if it is working or not.

    Create a Analytical Grid in PPS dashboard designer. Paste this:

    WITH MEMBER [MEASURES].[MyUsername] AS MID( Username, InStr (Username, "\") +1)

    Select [Measures].[MyUsername] on columns,

    <<Any dimension, just for testing>> on rows

    From [<<your cube>>]

    Switch to Design tab on report and you should have a username excluding the domain part.

    Alternatively you can switch Username with CUSTOMDATA(). I have tested and both work.


    http://dailyitsolutions.blogspot.com/
    Tuesday, March 15, 2011 8:04 AM

All replies

  • Please provide a little more information.

    Are you using dimensional security?

    Are you using CUSTOMDATA() to get current login?

    What is your SSRS data source SQL database (query/SP) or Analysis services cube?

    You can populate reports based on current user's context but that would depend on your underlying implementation structure.

    Other thing you can try is run a Profiler trace on SSAS and check the MDX query passed.


    http://dailyitsolutions.blogspot.com/
    Tuesday, February 22, 2011 9:07 AM
  • In addition to what Umair listed, what version of the PerformancePoint product are you using?  This will make a difference to determine how you have the authentication setup so that you are passing the context of the current user. 

    Also need to know is this a single server environment that has everything installed or is it setup in a distributed environment?


    Dan English's BI Blog
    Tuesday, February 22, 2011 12:05 PM
  • Hi Umair

    I have used customdate() also. but its not working in dashbord filter editor.

     But when i try in SQL Environment it has given the values .I want to take the clinicname details for the current login username.

    My query is

    select

     

     

    non empty { [Measures].[CLINIC ID]}on columns,  

    filter([CLINIC].[CLINIC].[CLINIC].allmembers

    ,([USER].[User Name].&[mid(

    username

     

     

     

    , instr(username, "\")+1)]))} on rows  

    from Sale 

    The above query is giving the values for current login username details. But in Dashboard filter editor the username and customdata() functions are not working.

    Please give a solution. i am  lagging in this for last one month

     

    Thanks

    Bala

     
    Friday, March 11, 2011 10:50 AM
  • Hi Dan

     

    I am using 2010 server for performance point and the database  and reports are in different server.

    Thanks

    Bala

    Friday, March 11, 2011 10:59 AM
  • IIF(
        EXISTS (
            [Items].[buyer].children, strtomember("[Items].[login id].["+CUSTOMDATA()+"]")).item(0),
            EXISTS (
                [Items].[buyer].children, strtomember("[Items].[login id].["+CUSTOMDATA()+"]")
            ),
        [Items].[buyer].[all]
    )

    Here is what i did in PPS 2010 filter MDX. What the above MDX does is checks buyer attribute for current logged in user. If buyer and current logged in user match the filter value is set to current user else all buyers are listed.

    Here is an article from Nick that helped me a lot. Follow the analytical report part in this report to debug your filter values.

    http://nickbarclay.blogspot.com/2008/01/pps-data-connection-security-with.html


    http://dailyitsolutions.blogspot.com/
    Sunday, March 13, 2011 7:13 AM
  • Hi

    Thanks for your valuable information.

    I am lagging on getting current username.

    can u please tell me the what is the  error in the below  code.

    [USER].[User Name].&[Mid("vcaantech\igor.pavlovich", InStr(1,"vcaantech\igor.pvlovich", "\") + 1, 128 )]

    [USER].[User Name].&[Mid(customdata()", InStr(1,customdata(), "\") + 1, 128 )]

     

    i need only igor.pavlovich as username. And my custom data also giving the value like vcaantech\igor.pavlovich.

    But in my cube the username is just like  igor.pavlovich.

    Thanks

    Bala

    Monday, March 14, 2011 2:50 PM
  • Here is how you can make sure if it is working or not.

    Create a Analytical Grid in PPS dashboard designer. Paste this:

    WITH MEMBER [MEASURES].[MyUsername] AS MID( Username, InStr (Username, "\") +1)

    Select [Measures].[MyUsername] on columns,

    <<Any dimension, just for testing>> on rows

    From [<<your cube>>]

    Switch to Design tab on report and you should have a username excluding the domain part.

    Alternatively you can switch Username with CUSTOMDATA(). I have tested and both work.


    http://dailyitsolutions.blogspot.com/
    Tuesday, March 15, 2011 8:04 AM
  • Hi,

     I got the result by your valuable sugesttion. Now again i have some problem my query is

    select { }on columns, IIF(exists (filter([EMPLOYEE].[EMPLOYEE BY WORKED CLINIC].members ,(StrToMember("[USER].[User Name].[" + LCase(Mid(customdata(),InStr(customdata(),"\")+1)) + "]")))AS ORDERS) .item(0) , ORDERS , [EMPLOYEE].[EMPLOYEE BY WORKED CLINIC].members ) on rows

    from sale

     

    for this query its taking 10 sec to execute. I need to minimize the time as within 1 sec .There is any possible way please let me know.

    Thursday, March 24, 2011 11:05 AM