question

greglipman-4888 avatar image
0 Votes"
greglipman-4888 asked Joyzhao-MSFT edited

report builder - expression query with filter and parameter

Run is not happy with
[Dim Policy].[Pol Eff Date].CurrentMember.MemberValue >= Parameters!FromPolEffFullDate.Value

when I change to
[Dim Policy].[Pol Eff Date].CurrentMember.MemberValue >= CDate('1/1/2017')
it works

Parameter I tried datetime and text and everything
not sure i understand how Eval is done on that script


SELECT
NON EMPTY
{ [Measures].[Vwp],[Measures].[Inc Amt],[Measures].[Loss Ratio],[Measures].[Vep],[Measures].[EP Loss Ratio]}
ON COLUMNS, NON EMPTY { ([Dim Coverage].[Coverage Cd].[Coverage Cd].ALLMEMBERS [Dim Mileage].[Mileage Cd].[Mileage Cd].ALLMEMBERS [Dim Producer].[Producer Cd].[Producer Cd].ALLMEMBERS [Dim Program].[Program Cd].[Program Cd].ALLMEMBERS [Dim Zip].[Zip Cd].[Zip Cd].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS FROM [pandl]

WHERE
(
Filter( [Dim Policy].[Pol Eff Date].AllMembers,
[Dim Policy].[Pol Eff Date].CurrentMember.MemberValue >= Parameters!FromPolEffFullDate.Value
and [Dim Policy].[Pol Eff Date].CurrentMember.MemberValue <= CDate('1/5/2017')
)
)

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @greglipman-4888 ,
You cannot use expression Parameters!FromPolEffFullDate.Value in your dataset query except it’s dynamic query like this: =“select {} on 0 from”+ “cube”

To pass a parameter value from SSRS to MDX, you could refer to:
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/7ae1c5ef-98de-468a-bc28-b7c3ed4ad377

But I would suggest you generate query with parameter automatically via drag and drop, and then you will see how SSRS handle these kinds of query.

To write MDX query with parameter by yourself , you could refer to:
SSRS: How can i use a Parameter in MDX Query?
Total confusion around using Time as a filter paramete
How can we parameterize top N in MDX

Hope this helps.
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.