MDX - include both empty and non empty

1
votes

I have an mdx query that I'm using in an SSRS report. I would like to only return hours that are > 50, but I would like to display all years regardless of whether there is data for that year. I can get the > 50 filter to work, but then I lose the years where there is no data. I have tried putting the years on the "Pages" axis which then returns all years, but my rows axis no longer respects the filter when I do this. I've also tried putting the years dimension outside of the NONEMPTY clause, but again it does not respect the > 50 hours filter.
Here is a sample of what I've got so far:

SELECT 
  [measures].[Hours] ON COLUMNS
 ,
    [Date].[Year].[Year].ALLMEMBERS
  * 
    {
      Filter
      (
        NonEmpty
        (
          CrossJoin
          (
            [Client].[Client].[Client].ALLMEMBERS
           ,[Department].[Department].[Department].ALLMEMBERS
           ,[Title].[Title].[Title].ALLMEMBERS
           ,[Person].[Person].[Person].ALLMEMBERS
          )
         ,[Measures].[Billable Hours]
        )
       ,
        [Measures].[Billable Hours] >= 50
      )
    } ON ROWS
FROM TestCube;

Desired result would be to still show the 2017 column even if there is no data, and only show hours > 50 for the columns that have data.

Any help on this would be much appreciated. I did find a way around it by creating a calculated member that checks for hours > 50, otherwise returns null. Just wondering if there is a better way to do this.

Thanks, Hillary

2
reporting-servicesmdx
what would the results look like? – whytheq

2 Answers

0
votes

You could try adding OR ISEMPTY([Measures].[Billable Hours]) into the filter condition:

SELECT 
  [measures].[Hours] ON COLUMNS
 ,
    [Date].[Year].[Year].ALLMEMBERS
  * 
    {
      Filter
      (
        NonEmpty
        (
          CrossJoin
          (
            [Client].[Client].[Client].ALLMEMBERS
           ,[Department].[Department].[Department].ALLMEMBERS
           ,[Title].[Title].[Title].ALLMEMBERS
           ,[Person].[Person].[Person].ALLMEMBERS
          )
         ,[Measures].[Billable Hours]
        )
       ,
        [Measures].[Billable Hours] >= 50
            OR ISEMPTY([Measures].[Billable Hours])
      )
    } ON ROWS
FROM TestCube;
0
votes

You can move the algorithm into a calculated measure. It's faster. The filter function is slow.

with
Member [Measures].[Billable Hours 50More] as
IIF([Measures].[Billable Hours] >= 50, [Measures].[Hours], NULL)    

select 
[Measures].[Billable Hours 50More] on 0,
[Date].[Year].[Year].AllMembers * NonEmpty([Client].[Client].[Client].AllMembers * [Department].[Department].[Department].AllMembers * [Title].[Title].[Title].AllMembers * [Person].[Person].[Person].AllMembers, [Billable Hours 50More]) on 1

from TestCube