June 11, 2014 at 12:30 am
Hi Experts,
I have created below query by designer.. Now i want to add @FromDate and @ToDate parameter but i am unable to set as i am getting data from SSAS cube.. i am doing the same thing first time.. but i know about SQL.. in SQL we have simple where DateColumn between @StartDate and @EndDate
but i don't know in mdx syntex.. Please help in the same.
SELECT NON EMPTY {
[Measures].[Total Gross Comm],
[Measures].[Total Net Comm],
[Measures].[NMI Sale],
[Measures].[MIRN Sale],
[Measures].[Number Of Sales] } ON COLUMNS,
NON EMPTY { ([Dim Date].[Year].[Year].ALLMEMBERS * [Dim Date].[Month].[Month].ALLMEMBERS * [Dim Date].[Month Year].[Month Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@DimDateWeekOfYear, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@DimSalesRepSalesRepName, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@DimRetailerRetailer, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@DimStatusStatus, CONSTRAINED) ) ON COLUMNS
FROM [Electricity Wizard DSV New]))))
WHERE (
IIF ( STRTOSET(@DimStatusStatus, CONSTRAINED).Count = 1, STRTOSET(@DimStatusStatus, CONSTRAINED), [Dim Status].[Status].currentmember ),
IIF( STRTOSET(@DimRetailerRetailer, CONSTRAINED).Count = 1, STRTOSET(@DimRetailerRetailer, CONSTRAINED), [Dim Retailer].[Retailer].currentmember ),
IIF( STRTOSET(@DimSalesRepSalesRepName, CONSTRAINED).Count = 1, STRTOSET(@DimSalesRepSalesRepName, CONSTRAINED), [Dim Sales Rep].[Sales Rep Name].currentmember ),
IIF( STRTOSET(@DimDateWeekOfYear, CONSTRAINED).Count = 1, STRTOSET(@DimDateWeekOfYear, CONSTRAINED), [Dim Date].[Week Of Year].currentmember )
) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Regards,
Krishna
June 11, 2014 at 12:46 am
In the query designer, you can also create filter expression using a range. Simply set the operator to range. You now have two values you can specify. When you mark them as parameters, these will be added to the query, which you can edit so it uses @StartDate and @EndDate.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2014 at 5:15 am
I did the same but that is not working..
June 11, 2014 at 5:15 am
krishnaoptif (6/11/2014)
I did the same but that is not working..
It is not working as in ... ?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2014 at 5:19 am
Yes.. i tried many times before writing this question.. but no luck.. that is why i have pasted my query here
June 11, 2014 at 5:29 am
This is a (modified) query of mine that uses a range. I constructed this query using the designer and the range operator, like I mentioned earlier.
At the end, I replaced one parameter with STRTOMEMBER(@DateYQMD, CONSTRAINED).LAG(2), so I have only one parameter @DateYQMD.
This query selects data from a cube and it accepts a month as a parameter. It returns all of the data between that month and 2 months back (hence the LAG(2).
SELECT
NON EMPTY {[Measures].[myMeasure1],[Measures].[myMeasure2]} ON COLUMNS
,NON EMPTY {([Date].[YQMD].[Month].ALLMEMBERS)} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOMEMBER(@DateYQMD, CONSTRAINED).LAG(2) : STRTOMEMBER(@DateYQMD, CONSTRAINED) ) ON COLUMNS
FROM [myCube])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply