Hi Ben,

It's not clear from your description what the desired output should be.

Do you want the years between the start and end years where there is no data filled in with zeros or do you want the previous year value copied into the the years with with no data?

Also, how do you determine the starting year if the starting year has no premiums or claims?

Ian

Hi Ben

Or indeed the ending year if there have been no claims or premiums? Or does it not matter at the start and end, only the inner range?

There are numerous ways to approach this, but if you can supply the above information, we can zero in on the best one for you.

Cheers,

Ian

Hi Ben,

Have you also tried the "Show Empties" button on the Query Toolbar? This will force rows and/or columns to be displayed even when there is no data within them:

Hope that helps, but please update me on the information requested above.

Ian

Hi Ben,

OK, making some assumptions here:

1. When you say "Cube" data you are referring to SSAS OLAP or Tabular cubes;

2. You want to display the Range to be dynamic based on the first and last year that contains data;

3. You want any years within that range that have no data to be displayed, with blank or zero shown in the cell.

Here is one approach.

1. Here's a report showing my Incurred measure for each Accident year. As you can see, if I use show Empties to see the years that have no Incurred measure values, I see all my Accident years form the past to the future, as well as the years that have no Incurred value between the minimum and maximum years. I want to show only the years, min to max including empty years, but excluding years below min and above max.

2. Create a dynamic list that contains the first and last year that contains data:

The Nonempty filter removes all years where there is no value for Incurred Measure.

**NOTE: Where you want to filter by Client as the major driver, I would recommend you make the Client filter a parameter and include that parameter in the NonEmpty filter. It may be you have to treat the other slicer attributes the same way. It kind of depends on your data.

I use two streams, the upper stream selecting the First element in the list of Years with a value of Incurred, and the lower stream selects the Last Year with a value of Incurred. I then Union the two streams to give me a dynamic list which contains the first and last Year.

3. Build another dynamic list that is the RANGE of Years, starting from the First element (item(0)) and ending with the second element from the List (item(1)). Unfortunately you will need to resort to the Script mode to do this, using the List of two elements we created previously, the MDX RANGE operator, ":" (MDX uses this operator rather than a "RANGE" function like PQL) and the .item() suffix to pick the elements from the list we want (.item()  starts at 0):

[*f0af6193-59ed-4408-b5a8-90d50412d801*].item(0):[*f0af6193-59ed-4408-b5a8-90d50412d801*].item(1)

As you can see in the Preview pane, this gives us a list of Years from the Min to the Max and everything in between.

4. Use this List in your Discover view, making sure you have the "Show Empties" button selected to show the empty years within the range.

 

Hope my assumptions matched reality and that helps. Otherwise LMK.

Ian

Hi Ben,

While you can certainly use multi level Hierarchies as parameters, currently the Hierarchy Tree in the drop down widget visualisation is not supported for Parameters. You won't see it in the visual options. For example, I can select the top level and all descendants in my Dates Hierarchy for my Parameter:

But the visualisation options are limited:

However, all members from all levels are displayed and can be selected, just not  in the nice collapsible way you would like:

 

The explanation as to why is rather long winded and technical. It has been raised as an enhancement request in the Product Ideas Forum and we keep it under review.

Hope that helps.

Ian