none
Dynamic Filtering and Calculation in SSAS cube RRS feed

  • Question

  • Hello everyone,

    I am quite new to SSAS and looking for some help. I want to filter the data and perform some calculations within my SSAS cube based on the a date value selected by user. I have a dimension table containing contract related informations e.g. 

    Contract    SignedOn     StartDate     Price     Profit   RecordValidFrom RecordValidUntil
    1             10.01.2018   10.01.2018   200        5         10.01.2018        12.01.2018
    1             10.01.2018   10.01.2018   300       10        13.01.2018             NULL
    Formula for calculated column is e.g. Price+Profit*(ParameterDate-StartDate)
    In my fact table I have. 

    FactId ContractID   Price   Profit   CalculatedColumn
    1            1             200     5              at run time
    2            1             300    10             at run time

    Requirment is that the user selects a date as a parameter and the 'CalculatedColumn' gets populated based on the value selected in parameter + RecordValidFrom<= ParameterDate & RecordValidUntil >= ParameterDate. 

    E.g. if the user selects 11.01.2018 as parameter then record 1 should get displayed and the calculated column should have 200+(5*1) = 205

    if the user selects 15.01.2018 as parameter then record 2 should get displayed and the calculated column should have 300+(10*5) = 350. 

    I can achieve this through SSRS. However, I wanted to know if I can pre-calculate this within the cube instead of doing it in SSRS. 

    Any help in this regard would be highly appreciated. Thanks in advance. 

    Best Regards,

    Ali


    Tuesday, November 27, 2018 2:38 PM

Answers

  • You cant precreate this measure in cube as you dont know whet date values the SSRS report would be passing to it beforehand

    Your only options are either to calculate this using MDX query executed from SSRS utilizing the entered parameter values

    or retrieve whole data and do the filtering in SSRS inside dataset

    The former approach would be the better one from performance perspective as you'll be limiting the data coming from cube to include only the data within the range user chose from the report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh

    Hi Visakh,

    thank you for answering...What i am currently doing in SSRS is that I have set the default parameter value to today's date and on my dataset I have setup a filter when the report is loaded...i.e. when the report is run it only returns those records from the cube which are valid according to today's date. 

    the user's can then select any other date within the parameter and the filtering + calculation will be done on the basis of that...

    is this a correct approach? 

    Yes

    This is the correct approach. Recommended way is to pass down dates as parameters to MDX query and do filtering from cube itself rather than doing it inside SSRS (I hope you're doing it this way anyways!)

    See similar examples given below

    https://jsimonbi.wordpress.com/2011/03/22/using-a-date-parameter-in-ssrs-with-mdx/

    https://www.mssqltips.com/sqlservertip/3451/calendar-date-picker-for-mdx-based-sql-server-reporting-services-reports/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by M. Ali Shah Thursday, November 29, 2018 11:03 AM
    Thursday, November 29, 2018 10:37 AM

All replies

  • Hi,

    Can anyone please help here? Thanks.

    Wednesday, November 28, 2018 2:19 PM
  • Hi M.Ali Shah,

    So, are you using SSAS Tabular model or multidimensional model? What's the primary key in DimContract table?

    Please share your current expression (Dax or MDX).

    Regards,

    Pirlo Zhang 




    MSDN Community Support&lt;br/&gt; Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact &lt;a href=&quot;mailto:MSDNFSF@microsoft.com&quot;&gt;MSDNFSF@microsoft.com&lt;/a&gt;.

    Thursday, November 29, 2018 3:06 AM
  • You cant precreate this measure in cube as you dont know whet date values the SSRS report would be passing to it beforehand

    Your only options are either to calculate this using MDX query executed from SSRS utilizing the entered parameter values

    or retrieve whole data and do the filtering in SSRS inside dataset

    The former approach would be the better one from performance perspective as you'll be limiting the data coming from cube to include only the data within the range user chose from the report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 29, 2018 6:10 AM
  • You cant precreate this measure in cube as you dont know whet date values the SSRS report would be passing to it beforehand

    Your only options are either to calculate this using MDX query executed from SSRS utilizing the entered parameter values

    or retrieve whole data and do the filtering in SSRS inside dataset

    The former approach would be the better one from performance perspective as you'll be limiting the data coming from cube to include only the data within the range user chose from the report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh

    Hi Visakh,

    thank you for answering...What i am currently doing in SSRS is that I have set the default parameter value to today's date and on my dataset I have setup a filter when the report is loaded...i.e. when the report is run it only returns those records from the cube which are valid according to today's date. 

    the user's can then select any other date within the parameter and the filtering + calculation will be done on the basis of that...

    is this a correct approach? 

    Thursday, November 29, 2018 10:33 AM
  • You cant precreate this measure in cube as you dont know whet date values the SSRS report would be passing to it beforehand

    Your only options are either to calculate this using MDX query executed from SSRS utilizing the entered parameter values

    or retrieve whole data and do the filtering in SSRS inside dataset

    The former approach would be the better one from performance perspective as you'll be limiting the data coming from cube to include only the data within the range user chose from the report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh

    Hi Visakh,

    thank you for answering...What i am currently doing in SSRS is that I have set the default parameter value to today's date and on my dataset I have setup a filter when the report is loaded...i.e. when the report is run it only returns those records from the cube which are valid according to today's date. 

    the user's can then select any other date within the parameter and the filtering + calculation will be done on the basis of that...

    is this a correct approach? 

    Yes

    This is the correct approach. Recommended way is to pass down dates as parameters to MDX query and do filtering from cube itself rather than doing it inside SSRS (I hope you're doing it this way anyways!)

    See similar examples given below

    https://jsimonbi.wordpress.com/2011/03/22/using-a-date-parameter-in-ssrs-with-mdx/

    https://www.mssqltips.com/sqlservertip/3451/calendar-date-picker-for-mdx-based-sql-server-reporting-services-reports/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by M. Ali Shah Thursday, November 29, 2018 11:03 AM
    Thursday, November 29, 2018 10:37 AM
  • Hi M.Ali Shah,

    So, are you using SSAS Tabular model or multidimensional model? What's the primary key in DimContract table?

    Please share your current expression (Dax or MDX).

    Regards,

    Pirlo Zhang 

    Hi Pirlo,

    thanks for answering...i am using multidimensional model...i have updated the example as below with keys

    ContractKey    Contract    SignedOn     StartDate     Price     Profit   RecordValidFrom RecordValidUntil
    1                         1             10.01.2018   10.01.2018   200        5         10.01.2018        12.01.2018
    2                        1             10.01.2018   10.01.2018   300       10        13.01.2018             NULL

    In my fact table I have. 

    FactKey ContractKey   Price   Profit   CalculatedColumn
    1              1             200     5              at run time
    2              2             300    10             at run time

    Formula for calculated column is e.g. Price+Profit*(ParameterDate-StartDate)


    Thursday, November 29, 2018 10:43 AM
  • You cant precreate this measure in cube as you dont know whet date values the SSRS report would be passing to it beforehand

    Your only options are either to calculate this using MDX query executed from SSRS utilizing the entered parameter values

    or retrieve whole data and do the filtering in SSRS inside dataset

    The former approach would be the better one from performance perspective as you'll be limiting the data coming from cube to include only the data within the range user chose from the report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh

    Hi Visakh,

    thank you for answering...What i am currently doing in SSRS is that I have set the default parameter value to today's date and on my dataset I have setup a filter when the report is loaded...i.e. when the report is run it only returns those records from the cube which are valid according to today's date. 

    the user's can then select any other date within the parameter and the filtering + calculation will be done on the basis of that...

    is this a correct approach? 

    Yes

    This is the correct approach. Recommended way is to pass down dates as parameters to MDX query and do filtering from cube itself rather than doing it inside SSRS (I hope you're doing it this way anyways!)

    Thank you Visakh for the help. I guess I am doing the filtering within the report as I have setup the filter on Dataset properties and I do not see that as a parameter in MDX query. You have however, pointed me in the right direction...i now need to check how can I set RecordValidFrom <= ParameterDate AND RecordValidUntil >= ParameterDate in the filter... 

    Thursday, November 29, 2018 11:03 AM
  • You cant precreate this measure in cube as you dont know whet date values the SSRS report would be passing to it beforehand

    Your only options are either to calculate this using MDX query executed from SSRS utilizing the entered parameter values

    or retrieve whole data and do the filtering in SSRS inside dataset

    The former approach would be the better one from performance perspective as you'll be limiting the data coming from cube to include only the data within the range user chose from the report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh

    Hi Visakh,

    thank you for answering...What i am currently doing in SSRS is that I have set the default parameter value to today's date and on my dataset I have setup a filter when the report is loaded...i.e. when the report is run it only returns those records from the cube which are valid according to today's date. 

    the user's can then select any other date within the parameter and the filtering + calculation will be done on the basis of that...

    is this a correct approach? 

    Yes

    This is the correct approach. Recommended way is to pass down dates as parameters to MDX query and do filtering from cube itself rather than doing it inside SSRS (I hope you're doing it this way anyways!)

    Thank you Visakh for the help. I guess I am doing the filtering within the report as I have setup the filter on Dataset properties and I do not see that as a parameter in MDX query. You have however, pointed me in the right direction...i now need to check how can I set RecordValidFrom <= ParameterDate AND RecordValidUntil >= ParameterDate in the filter... 

    The posted link shows how to pass down date parameters through MDX

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 29, 2018 11:41 AM