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.
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.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.