February 26, 2018 at 5:23 am
Hi All
I have a dateset for parameter that is filtered to only have the months where we have transactions or values for. In this case it is 3 months which will appear as multivalue drop down (Oct-17,Nov-17,Dec-17). i tried number of ways using combination of MDX and expressions to set the default as the Max or last date of the month. Which should be Dec 2017. How can I do this? Note the dataset was built using MDX designer
February 26, 2018 at 8:01 am
vilenvmo - Monday, February 26, 2018 5:23 AMHi AllI have a dateset for parameter that is filtered to only have the months where we have transactions or values for. In this case it is 3 months which will appear as multivalue drop down (Oct-17,Nov-17,Dec-17). i tried number of ways using combination of MDX and expressions to set the default as the Max or last date of the month. Which should be Dec 2017. How can I do this? Note the dataset was built using MDX designer
Not sure exactly what you mean by "I have a dataset for parameter". If you are using a dataset as a source for a parameter, that query would have to be able to be added with a maximum value specified, to the default value specification, based on what you're saying. I think you need to determine that maximum value by in some way ranking all the supplied values and creating an additional column to hold that ranking value, and then use a pair of columns from that query, maybe... Not sure it can be done without some form of hard coded value...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 26, 2018 at 8:14 am
Hi Steve.
I am an SSRS newbie. Thanks for your response here is what I tried thus far in parameters > Default Value> exp>
="[Date].[Date].&[" & YEAR(DateAdd("m",-1,NOW()))*100
+MONTH(DateAdd("m",-1,NOW())) & "]"
But issue is aggregations such as max doesnt work here. Im trrying to do it in MDX ....as per below, so still no luck...my ultimate objective is what ever dates as in "MMM-YY" format , default parameter to select latest or max date/lastdate.
WITH MEMBER [Measures].[Caption]
AS VBA!Format
(
Cdate([Date].[Date].CurrentMember.Name)
,"MMM-yy"
)
MEMBER [Measures].[Key]
AS [Date].[Date].CurrentMember.UNIQUE_NAME
//MEMBER [Measures].[SortMe]AS [Date].[Date].CurrentMember.UNIQUE_KEY
MEMBER [Measures].[Risk] AS [Measures].[Total Risks]
MEMBER [MaxDate] as Max([Measures].[Caption])
SELECT NON EMPTY {[Measures].[Caption], [Measures].[Key],[Measures].[Risk],[Measures].[MaxDate]
//, [Measures].[SortMe]
} ON 0
, {[Date].[Year Month].[Year Month]
* [Date].[Date].[Date] } ON 1
FROM [Model]
WHERE ([Date].[DayofMonth].&[1])
March 2, 2018 at 10:43 am
Why are you trying to format the data within MDX? SSRS is where that formatting needs to be. Values coming from your MDX query that are actually dates instead of character strings, might give you a better chance...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply