using case statement in filter

[sujitgouda]

Hi All,

I am trying to pass a parameter to a filter and using the below case statement to execute the report based on the value of the parameter.
What i want to achieve is; if Paramdate = First Day of Month Then use reporting date = paramdate else use the range for settled date from (passed paramdate to start of the month) to run the report.

Parameter Name:   ?paramReportingDate?

case
when ?paramReportingDate?  = _first_of_month(?paramReportingDate?) then [Sales Reporting (Query)].[Trade Details].[Reporting Date] = ?paramReportingDate?
else [Sales Reporting (Query)].[Trade Details].[Settled Date]  between _first_of_month (?paramReportingDate?) and ?paramReportingDate?
end


I am getting an parsing error everytime :(

Please help me.

[Rosanero4Ever]

Hi,

you can use the following statement:

Code Select Expand

( ?paramReportingDate?  = _first_of_month(?paramReportingDate?) and  [Sales Reporting (Query)].[Trade Details].[Reporting Date] = ?paramReportingDate? )
OR
(
[Sales Reporting (Query)].[Trade Details].[Settled Date]  between _first_of_month (?paramReportingDate?) and ?paramReportingDate?
)


I hope this help you

[sujitgouda]

That did help in resolving the parsing error.

But the output when I use only Filter 1 and Filter 2 are not matching for paramDate - 20160218

Filter 1: [Sales Reporting (Query)].[Trade Details].[Settled Date]  between _first_of_month (?paramReportingDate?) and ?paramReportingDate?

Filter 2: (
(
?paramReportingDate?  = _first_of_month(?paramReportingDate?) and  [Sales Reporting (Query)].[Trade Details].[Reporting Date] = ?paramReportingDate?
)
OR
(
[Sales Reporting (Query)].[Trade Details].[Settled Date]  between _first_of_month (?paramReportingDate?) and ?paramReportingDate?
)

)

[BigChris]

Presumably Filter 2 is giving you higher results than Filter 1. They're different filters, so you're going to get different results.For example, anything where the ReportingDate is the 18th of Feb, but the Settled Date is later than the 18th of Feb (using your data) would be included in Filter 2 but not in Filter 1.