report builder - expression query with filter and parameter

greg lipman 61 Reputation points
2021-03-31T07:36:07.37+00:00

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
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,772 questions
0 comments No comments
{count} votes

Accepted answer
  1. Joyzhao-MSFT 15,561 Reputation points
    2021-04-01T05:56:40.46+00:00

    Hi @greg lipman ,
    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful