BookmarkSubscribeRSS Feed
kuro_kuro
Calcite | Level 5

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.



3 REPLIES 3
yonib
SAS Employee

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
Calcite | Level 5

Thank You.

Yes I have tried it and it is work. Smiley Happy 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
SAS Employee

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;


sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3615 views
  • 0 likes
  • 2 in conversation