Search code examples
iccube-reporting

Is there a possibility to dynamically disable/hide manual filter items in a filter list in icCube dashboards?

We use a set of dashboards for all of the clients; in the start HTML we switch the underlying model to the model for which the user is authorized.

The dashboards make plenty of use of filter lists that have hierarchy levels as selection items. See for example the screenprint enclosed.

All works fine,

except ...

One of the clients has asked to remove certain filter items from several lists, as their model does not have these hierarchy levels filled with data and all is "n/a" or "-". But if we make an exception for them and save a copy of the dashboard set specifically for them we 'kill' the idea of a global re-usable set of dashboards for all the clients. And we prefer not to do that.

We have tried to set-up a static SET in the schema definition, but that does not work as it does not allow to combine hiearchy items.

What other options could we pursue to hide certain filter elements for specific clients?

example of a filter for which we want to dynamically hide items

Solution

  • A "manual" filter once created is not going to send any request to the server so there is no way to filter the selected items. So instead you should use a filter using a customized MDX that is sending back the required measures for the filter widget.

    Something like this:

    WITH
  MEMBER ic3Name       AS strToLevel([Filters].currentMember).name
  MEMBER ic3UName      AS strToLevel([Filters].currentMember).uniqueName
  MEMBER ic3PName      AS strToLevel([Filters].currentMember).hierarchy.name
  MEMBER ic3Measure    AS 0
  MEMBER ic3IsSelected AS false
  MEMBER ic3FilterName AS [Measures].[ic3Name]
  MEMBER ic3Key        AS [Measures].[ic3Name]
SELECT
  {[Measures].[ic3Name],[Measures].[ic3UName],[Measures].[ic3PName],[Measures].[ic3Measure],[Measures].[ic3IsSelected],[Measures].[ic3FilterName], [Measures].[ic3Key]} ON 0, 
  { report-xyz-filter-xyz } ON 1
FROM [Cube]

    The trick is creating the set of levels report-xyz-filter-xyz in your schema: to bypass the MDX limitation (a set is a list of members from the same hierarchy), this set is going to contain 'fake' calculated members (cannot be [Measures] as the MDX query cannot contains [Measures] on the both axis). The evaluation of those members is a string representing the unique name of a level.

    create measure [Filter].[report-xyz-filter-xyz Region]  as [Customers].[Geography].[Region].uniqueName, VISIBLE=0
create measure [Filter].[report-xyz-filter-xyz Product] as [Product].[Product].[Product].uniqueName, VISIBLE=0
....

    Then :

    create set report-xyz-filter-xyz  as { ... }

    There you're free to have one definition of the set per customer schema creating the extensive list of those fake calculated memebers or better to have a single definition using an MDX expression to set up (filter) the right list of those fake members according to the 'current' customer.

    Not tested so you might need to adapt it but I guess you get the general idea.

    Hope that helps.