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

Answer 1

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

Answer 2

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