locked
Proclarity not working with Calculated measures RRS feed

  • Question

  • Hi All,

    I am currently involved in creating Calculated Measures for my BI solution in which i encounter a problem. My calculated measure is not fetching values in proclarity & but works fine in SSAS & Excel.

    Following is the code for my Calculated Measure

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Product Count]
    AS Aggregate(STRTOSET(Security.SecurityEngine.GetClientId("[Product]", UserName)), [Measures].[Product Count]),
    FORMAT_STRING = "#,#", 
    VISIBLE = 1;

    In the above measure  "Security.SecurityEngine.GetClientId("[Product]", UserName)" is the .NET function which return the clientid the current user has access to. When i browse the cube in the SSAS the measure worked perfectly but in proclarity no data is returned. When i browsed using EXCEL the measure is working fine.

    In some cases the .NET function returns only one set like 

    {[Product].[Client Id].&[1]} so the calculated measure looks like

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Test Product Count]
    AS Aggregate(STRTOSET("{[Product].[Client Id].&[1]}"), [Measures].[Product Count]),
    FORMAT_STRING = "#,#", 
    VISIBLE = 1;

    In this case the calculated measure works perfectly in both SSAS & Proclarity where as if the .NET function returns tuple with more than one set like 

    {[Product].[Client Id].&[1], [Product].[Client Id].&[2]}. Here the calculated measure looks like 

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Test Product Count]
    AS Aggregate(STRTOSET("{[Product].[Client Id].&[1], [Product].[Client Id].&[2]}"), [Measures].[Product Count]),
    FORMAT_STRING = "#,#", 
    VISIBLE = 1;

    And in this case the calculated measure is not working in Proclarity but works fine in SSAS & Excel.

    I dont know whether any configuration is req at Proclarity level???

    Can any one pls help me on this.

    Thanks in Advance.

    Cheers :)
    Mani.
    Tuesday, September 1, 2009 3:13 AM

All replies

  • Hello Manikandan,

    In ProClarity professional can you try clearing the 'use non-empty crossjoin' box in the File>Options properties page and see if that helps?
    Microsoft ProClarity | This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 16, 2009 2:55 PM