SSRS passing parameter to SSAS

0
votes

I have an SSRS report that passes a datetime parameter with a datepicker to my analysis service. The value field in my cube is datetime.

I get the error:

the strtoset function expects a tuple set expression for the 1 argument. a string or numeric expression was used.

This is the syntax passed to my SSAS server:

SELECT NON EMPTY { [Measures].[Transactions Count] } ON COLUMNS FROM ( SELECT ( STRTOSET(@PaymulDateAlternateTimeKey) ) ON COLUMNS FROM [Transactions]) WHERE ( IIF( STRTOSET(@PaymulDateAlternateTimeKey).Count = 1, STRTOSET(@PaymulDateAlternateTimeKey), [Paymul Date].[AlternateTimeKey].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Can anyone help?

2
sqlreporting-servicesparameter-passingssasanalysis

2 Answers

1
votes

I found the solution. STRTOSET is okay, if you create the full path (SSAS) in your required format. In my case I had to edit the FX in the parameter section of the dataset (SSRS - Report Builder) to:

="[Paymul Date].[AlternateTimeKey].&["+format(Parameters!PaymulDateAlternateTimeKey.Value,"yyyy-MM-dd")+"T00:00:00]"

From there on you can edit as if you wish. DateAdd, CDate, Now() or Today(). Just be aware that you have to build the whole string with the full path.

-1
votes

According to the error message this part went wrong : STRTOSET(@PaymulDateAlternateTimeKey)

StrToSet expect a string expression that can specify a set. So a datatime type is not qualified.

Are you trying to pass multivalued parameter? If so, you'd need to follow the method, mentioned like in Passing Multiple Member Key values in Parameter