CODEWITHSUNDEEP
reporting-servicesssas
user3895074
user3895074

Reputation: 1

SSRS running from SSAS - Muliple Date Filters with null value

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

How I handle null values in the date parameters?

Upvotes: 0

Views: 1011

Answers (1)

Aftab Ansari
Aftab Ansari

Reputation: 946

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

Upvotes: 1

Related Questions