Make reports with no longer existing measures editable for a viewer license
We have some year based measures that our viewer users use in their reports. For example these measures are always provided for the last three years (e.g. list price 2020, 2021 and 2022). If we now include the new list price for 2023, we delete the 2020 list price from the cube. If a viewer subsequently wants to open their report that still includes the 2020 list price, that report can no longer be opened. We administrators then have to adjust the report or use the Stukture Analyzer to adjust the measure List Price 2020 to List Price 2021 (or possibly List Price 2023, depending on the situation) so that the user can open his report again. It would be nice if it were possible for viewers to still open these reports and adjust them themselves accordingly, even if a certain measure no longer exists and the report is then empty if necessary. This measure can be displayed in red, for example.
1 reply
-
Hi Dominique,
Rather than try to modify the analytic platform to cater for the fact that Measures in use by users are deleted deliberately, let's look at a more intelligent way to create the calculated measures so that they respond dynamically to the data and the passing of time. We can even enhance them further by allowing the user to select the base Year of their choosing.
You mentioned Cubes, so I m assuming you are using SQLServer Analysis Services, either OLAP or Tabular. Either way, we can use Formulate to create our Measures.
Let's assume we have a Dimension Attribute called [Data].[Date Year].
Create a Parameter that contains all the dates. If you select the topmost checkbox, at runtime, the Parameter will be populated with all the Members of this Attribute:
Set the default selection to Last. This will always then default to your last year.
Save the Parameter, we'll call it Base Year.
Create your first Measure. This will be for whatever Year is selected by the year at run time, defaulting to the Last Year.
I've created some arbitrary calculation, obviously this will be whatever your calculation is. I've selected the Measure, Sales and the Base Year Parameter as the slice. We'll save this as the Measure Base Year Sales, but I would like my Caption on reports to refer to the Year in question (2023, 2022 etc.). We can create a dynamic caption to do this, whcih will be the name of the member the Parameter points to, concatenated with some other text. for example:
In case you find it hard to read:
"(" + [Data].[Date year].@[33c672c1-e217-4c10-a91e-501899777b60].name + " Sales)/2"
Save the Measure as Base Year Sales.
Duplicate the Measures and rename it as Base Year -1 sales, then edit the slice to use the "*fx" functions on the parameter by clicking on the *fx label as shown in the red box below:
Select the Lag option and set the value to 1.
This will select the Year before the selected Base Year.
We also need to reflect this in the dynamic caption code:
Save the Measure as Base Year -1 Sales.
Repeat the above steps to create a Base Year -2 Sales, making the Lag function equal to 2 for the Data Point and the dynamic caption.
Now create your report:
I've added Countries to the rows and Measures Base Year Sales, Base Year -1 Sales and Base Year -2 Sales to the columns.
My parameter will appear automatically to drive the selected year. Note the dynamic captions, reflecting the Base Year selected and the -1 and -2 years.
As a User I can select 2022 as my base year and have my report show 2021 and 2020 (2022-1 and 2022-2)
If I select 2021, then as there is no 2019, it simply drops off the report, no error or anything:
I hope the above has been useful to you, not least in that by adopting a more thoughtful and dynamic approach to creating your Year based measures, you can avoid the tedious maintenance of creating new measures and deleting old ones each year and also avoid your users encountering errors when running reports that refer to the deleted items. And we don't have to change the platform! Wins all round!
Hope that helps.
Ian