SSRS running from SSAS - Muliple Date Filters with null value

0
votes

I have created a SSRS report from a SSAS cube, it has 2 date filters on the report as well as other filters, I have a number of issues with the date parameters

  1. If a date range parameter is set to null, say the user only wants to filter on date range 1 then the report does not bring back any data,
  2. If both date ranges have been set this the data returning includes rows outside of the date ranges selected

I have used the Query designer to create the code but I have amended the date parameter expressions to pass the date mdx string.

SELECT NON EMPTY { [Measures].[Total Amount Debit], [Measures].[Holiday Value], [Measures].[Total Amount Credit], [Measures].[Canx Local Amount], [Measures].[Canx Amount] } ON COLUMNS, NON EMPTY { ([Holiday ID].[Hierarchy].[Holiday ID].ALLMEMBERS * [Holiday ID].[Surname].[Surname].ALLMEMBERS * [depparture_date].[Hierarchy].[Start Date].ALLMEMBERS * [Compensation_Types].[Compensation Type].[Compensation Type].ALLMEMBERS * [Supplier_Deduction].[Supplier deduction].[Supplier deduction].ALLMEMBERS * [Component_Type].[Hierarchy].[Supplier Name].ALLMEMBERS * [Voucher Used].[Voucher used].[Voucher used].ALLMEMBERS * [task_created].[Hierarchy].[CREATED].ALLMEMBERS * [Booked_Date].[Booked Date].[Booked Date].ALLMEMBERS * [CR_Task ID].[Task ID].[Task ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOMEMBER(@FromBookedDateBookedDate) : STRTOMEMBER(@ToBookedDateBookedDate) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromtaskcreatedHierarchy) : STRTOMEMBER(@TotaskcreatedHierarchy) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CompensationTypesCompensationType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CompoMadeCompensationmade, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@SupplierDeductionSupplierdeduction, CONSTRAINED) ) ON COLUMNS FROM [Customer_Relations])))))

WHERE ( IIF( STRTOSET(@CompoMadeCompensationmade, CONSTRAINED).Count = 1, STRTOSET(@CompoMadeCompensationmade, CONSTRAINED), [Compo_Made].[Compensation made].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Can anyone help with how I handle null values in the date parameters?

Thanks

1
datereporting-servicesparametersnullssas

1 Answers

1
votes

In your dataset parameters (right click on your Dataset-> choose properties-> choose properties), for parameter @FromBookedDateBookedDate use an expression like : 

=IIF(isnothing(Parameters!Your_FromBookedDateBookedDate_Report_Parameter.value),”[depparture_date].[ BookedDate].[ALL]”, ”[depparture_date].[ BookedDate].[“&
Parameters!Your_FromBookedDateBookedDate_Report_Parameter.value &”]” )

Remember: the above expression result must match to your cube level’s attribute. Because possibility is cube might have data values like 20140131