Cross Tab with Drilling on dimensions and hyperlink on measure

[former_member225163]

Requirement:

Requirement is to create a summary view using cross tab to help data analysis by drilling up\down on both dimensions and then getting detail report of the summary by clicking on measure value.

Tech to know:

DrillFIlters([Dimension]): Returns the drill filters applied to an object in drill mode

DrillFIlters(): If you do not specify any object, the function returns all drill filters applied to the document.

Steps to achieve the requirement:

1. Created custom hierarchy for the two dimensions involved in cross tab at universe level.
2. Created report with cross tab and pulled the two hierarchy dimensions in row and column.
3. Enabled drill functionality in report for achieving the drill up\down on the dimensions.
4. When drill is enabled, by default link to drill also comes on measure. In order to avoid it, a variable of type measure has been created and used in the cross tab instead of direct measure.
5. Now, detail report needs to be linked with measure in the cross tab.

Challenge: When user clicks on measure, it needs to pass the dimension values of respective level to the child report.

Two cases exist in scenario. Those are:

Case 1: Clicking on measure after refreshing the report without drilling on dimension.

Case 2: Clicking on measure after drilling on dimension.

1. E.g., Let’s assume Country > State > City hierarchy exists. First user drills on this hierarchy and moves to City level then if user clicks on measure City value needs to be passed to the child report.

This requirement was achieved using Drillfilters() function with specific logic in opendocument and Child report filter condition is designed to handle all the three levels of hierarchy which come as input from Master report.

Please check below logic used to identify which level of hierarchy needs to be passed as parameter to child report:

If(DrillFilters([State]) <> “”;[City]; If(DrillFilters([Country]) <> “”;[State];[Country])

Let’s see how the above formula satisfies the two cases mentioned above.

Case 1: Clicking on measure after refreshing the report without drilling on dimension.

Expected Result:  Top Level of hierarchy needs to be passed to child report.

How it works: As per the logic, the value for DrillFilters([State])  and DrillFilters([Country])  will be Null. Hence, [Country] will be passed to the child report for the first time.

If(DrillFilters([State]) <> “”;[City]; If(DrillFilters([Country]) <> “”;[State];[Country])

Case 2: Clicking on hyperlink after drilling on dimension.

Expected Result:  The Level in which data is rolled up after drilling up\down of hierarchy needs to be passed to child report.

How it works: As per the logic, if user clicks on [Country] then DrillFilters([Country]) value  will not be NULL hence [State] value will be passed to the Child report

If(DrillFilters([State]) <> “”;[City]; If(DrillFilters([Country]) <> “”;[State];[Country])

If user again clicks on [State] then DrillFilters([State]) value will not be NULL hence [City] value will be passed to the Child report

If(DrillFilters([State]) <> “”;[City]; If(DrillFilters([Country]) <> “”;[State];[Country])

This logic is wrapped in the opendocument and used to achieve the requirement.