Date Range filtering for report using OLAP Cube

[kuro_kuro]

Dear All,,

I have a problem when creating date filtering. The olap cube can be filtered with multiple selection and I have tried it. But my user want to use range. For example from November 2011 until January 2012. I tried already to use stored process and MDX but not sucessfull. I created prompt from information map and used it in Web Report Studio.

Hopefully, anyone could help me to solve the problem. Thank You.

[yonib]

Hi Kuro,

My suggestion to you is to create a stored process with MDX and prompt filter that the user could choose the range he wants.

For example:

*  Stored process prompt dictionary:

*  ____________________________________

*  PERIOD_S

*       Type: Text

*      Label: Choose start range*       Attr: Visible

*  ____________________________________

*  ____________________________________

*  PERIOD_E

*       Type: Text

*      Label: Choose end range*       Attr: Visible

*  ____________________________________

*  ____________________________________

/*Create Macro Vars For Each Member in the Hirarchy*/

data _null_;

call symputx('y_s',put(year("&period_s"d),4.));

call symputx('q_s',compress('Q'||put(qtr("&period_s"d),2.)));

call symputx('m_s',put("&period_s"d,yymmn6.));

run;

data _null_;

call symputx('y_e',put(year("&period_e"d),4.));

call symputx('q_e',compress('Q'||put(qtr("&period_e"d),2.)));

call symputx('m_e',put("&period_e"d,yymmn6.));

run;

proc sql;
      connect to olap (user="corp\sasprod" pass="{sas001}c2VwdGVtYmVy" host="sasprod" port=5451) ;
      create table prem as
      select *
      from connection to olap
      (

SELECT
     { [Measures].[STPBRTSUM] , [Measures].[PLMLY] , [Measures].[PLM], [Measures].[PLMYTD],[Measures].[PLMLYYTD] }  

ON COLUMNS  ,
      crossjoin( {[Agent].[Sub-Deal].[SUB_AGENT_NM].Members } ,
    {[Period].[All Period].[&y_s].[&q_s].[&m_s]:[Period].[All Period].[&y_e].[&q_e].[&m_e]}) ON ROWS
      FROM
    [Production]
      WHERE
     ([CalculationType].[All Calculation Type].[פרודוקציה - תוספת] )
      );
      disconnect from olap;
quit;

I hope this helps you.

[kuro_kuro]

Thank You.

Yes I have tried it and it is work. However, when I tried it to make slicer, error is came out :

ERROR: An MDX syntax error occurred

ERROR: Sets are not valid on the slicer axis

I did because I do not want to show it as a dimension.
Do you have any suggestion? Thank you very much.

[yonib]

Hi,

You can not slice by two different member from the same dimension.

My adivse is to create a new member that aggregate the members you want into 1 memeber and then use it to make a slicer:

data _null_;

call symputx('y_s',put(year("&period_s"d),4.));

call symputx('q_s',compress('Q'||put(qtr("&period_s"d),2.)));

call symputx('m_s',put("&period_s"d,yymmn6.));

run;

data _null_;

call symputx('y_e',put(year("&period_e"d),4.));

call symputx('q_e',compress('Q'||put(qtr("&period_e"d),2.)));

call symputx('m_e',put("&period_e"d,yymmn6.));

run;

proc sql;
      connect to olap (user="corp\sasprod" pass="{sas001}c2VwdGVtYmVy" host="sasprod" port=5451) ;
      create table prem as
      select *
      from connection to olap
      (with member MEMBER [Period].[All Period].[filter] as

                  'aggregate({[Period].[All Period].[&y_s].[&q_s].[&m_s]:[Period].[All Period].[&y_e].[&q_e].[&m_e]}')

SELECT
     { [Measures].[STPBRTSUM] , [Measures].[PLMLY] , [Measures].[PLM], [Measures].[PLMYTD],[Measures].[PLMLYYTD] }  

ON COLUMNS  ,
       {[Agent].[Sub-Deal].[SUB_AGENT_NM].Members }
    ON ROWS
      FROM
    [Production]
      WHERE
([Period].[All Period].[filter] )

);
      disconnect from olap;
quit;