topic Re: Date Range filtering for report using OLAP Cube in SAS Web Report Studio

Date Range filtering for report using OLAP Cube

kuro_kuro — Mon, 09 Apr 2012 10:18:58 GMT

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.

Re: Date Range filtering for report using OLAP Cube

yonib — Tue, 10 Apr 2012 05:26:06 GMT

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.

Re: Date Range filtering for report using OLAP Cube

kuro_kuro — Tue, 10 Apr 2012 05:54:11 GMT

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.

Re: Date Range filtering for report using OLAP Cube

yonib — Tue, 10 Apr 2012 08:24:32 GMT

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;