Answered by:
dynamic default values for parameter

Question
-
Hi , I am trying to write a query for the default values for month, and I am stuck in bringing out only the parameter values, the query I have , brings back the month on row and value on column, and I am trying to get the value out. and will ssrs accept it as a valid expression for a default value
WITH
MEMBER
[MEASURES].[PARAMETERVALUE]
AS
[Account].[Month Short Name].CURRENTMEMBER.UNIQUENAME
SELECT
{
[MEASURES].[PARAMETERVALUE]
}
ON COLUMNS
,
nonempty(
[Account ].[Month Short Name].lastchild.prevmember,[Measures].[AccountV]
)
ON
rows
FROM
[SCube]
;Thank you
I tried using the exp
=Parameters!MONTH.Value(0)
so as to return the first parameter in the parameter dataset , but i have the error
the expression that ref the parameter MONTH does not exists in the parameter collection
- Edited by I4QR1A Wednesday, November 11, 2015 11:43 AM
Wednesday, November 11, 2015 8:24 AM
Answers
-
Hi I4QR1A,
According to your description, you want to specify dynamic default values for a parameter based on SSAS data source. Right?
In Reporting Services, we can replace a default value of a parameter with a expression and concatenated string. One import thing is, you can't specify any member in the filter expression of Query Designer. Otherwise, if the generated dynamic value doesn't match the member in filter expression, the dataset will not return any data. Please refer to screenshots bleow:
Regards,
Simon Hou
TechNet Community Support- Proposed as answer by Amit-Tomar Thursday, November 12, 2015 8:59 AM
- Marked as answer by Simon_HouMicrosoft contingent staff Friday, November 20, 2015 7:50 AM
Thursday, November 12, 2015 8:29 AM
All replies
-
The way to do this is to create a second data set that just returns 1 row and use that for your default value
If you want the first non-empty month on the rows you could use the HEAD() function, but normally with days you would want to use TAIL() to get the most recent date with data.
eg.
WITH
MEMBER
[MEASURES].[PARAMETERVALUE]
AS
[Account].[Month Short Name].CURRENTMEMBER.UNIQUENAME
SELECT
{
[MEASURES].[PARAMETERVALUE]
}
ON COLUMNS
,
TAIL(
nonempty(
[Account ].[Month Short Name].lastchild.prevmember,[Measures].[AccountV]
)
)
ON
rows
FROM
[SCube]http://darren.gosbell.com - please mark correct answers
Thursday, November 12, 2015 3:44 AM -
Hi I4QR1A,
According to your description, you want to specify dynamic default values for a parameter based on SSAS data source. Right?
In Reporting Services, we can replace a default value of a parameter with a expression and concatenated string. One import thing is, you can't specify any member in the filter expression of Query Designer. Otherwise, if the generated dynamic value doesn't match the member in filter expression, the dataset will not return any data. Please refer to screenshots bleow:
Regards,
Simon Hou
TechNet Community Support- Proposed as answer by Amit-Tomar Thursday, November 12, 2015 8:59 AM
- Marked as answer by Simon_HouMicrosoft contingent staff Friday, November 20, 2015 7:50 AM
Thursday, November 12, 2015 8:29 AM