Skip to Content
0

Dynamic appearance of report based on Input control selection for Relational Hierarchy columns

Nov 11, 2016 at 05:56 PM

114

avatar image

Hi All,

I have a requirement to make report dynamic based on input control selection. I have

Region>District>County>Unit>Worker Hierarchy set up in IDT. Our report is the summary level report which had measure value on other columns aggregated based on the this Hierarchy dimension. Now the report requirement is if the user select Region then the drop down for other District, County Unit and worker should only populate related to that region but now it is showing all the values for each. This will be confusing for the user to identify which District or county belongs to that particular Region and if selected wrong then the measure column will display as N/A.

And also we need to populate the table colum with which last value in Hierarchy been selected. For Eg: If Region is selected then it should populate Regions with its corresponding Measure value. If Region and District is selected then it should populate District for the Region in same Column and display measure value for it. If Region, District and County is selected then County should be populate in the same column where Region and District was populated like dynamically. Is this possible to achieve?

Please need some idea on it.

Thank you,

Chandan

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Mahboob Mohammed Nov 11, 2016 at 07:44 PM
0

Hi Chandan,

To address your 1st requirement, that the list of values displayed in 2nd Input Control should be restricted by the selection in 1st Input Control, a feature has been added to BO 4.2 SP3. Go go the below link, scroll down and checkout Group of Input Controls.

https://uacp2.hana.ondemand.com/viewer/09fe9f205ee047d48d58665649fc4fae/4.2.3/en-US/29dd53d886db4b6395cad8a3422c5e1d.html

There is no default feature available to address your 2nd requirement, but an Idea was created on SAP Idea Place, and its status is For Future Consideration. Please login and Vote Up on it.

https://ideas.sap.com/D2655

I can think of a workaround for 2nd requirement, but it maybe very tedious and may not be 100% of what you want.

Thanks,

Mahboob Mohammed

Share
10 |10000 characters needed characters left characters exceeded
Chandan Ghimire Nov 18, 2016 at 04:05 PM
0

Hi Mahboob,

Thanks for the valuable information. I am still struggling to get an idea how to come up with the second requirement and this is very important for me now to figure that out. Thought of putting some logic to empty the column and then use Show/hide option based on empty parameter but no luck.

Anybody any idea would appreciate a lot.

Thanks and Regards,

Chandan

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member
Nov 20, 2016 at 10:26 PM
0

As Mahboob suggested, you would need 4.2 SP3 to define Group of Input controls to cover the 1st requirement.

Now, as to the 2nd requirement, another SP3 feature can be useful here - Cell Reference

It's true you cannot directly retrieve the current selection from an input control (though you could try to parse the output of ReportFilterSummary function). But your task is actually simpler - you need to determine if anything was selected at all or not selected at all, and you can do it with a formula like that:

=If(Count([Region];Distinct) <> NoFilter(Count([Region];Distinct));1;0)

You put this formula in a new table consisting of a single cell, and make sure only Region input control affects this table. Next, you create a reference to that cell, named [IsRegionFiltered].

Do the analogous for District and County, creating [IsDistrictFiltered] reference, and [IsCountyFiltered] reference. So you have three extra helper tables, each affected only by its own corresponding input control, and indicating 1 or 0 depending on whether the input control has a selection or not. You can even hide these tables not to distract the user, the most important is that you have the references to the cells, which you use later on to create a calculated dimension in your main table:

=If([IsCountyFiltered]=1;[County];If([IsDistrictFiltered]=1;[District];If([IsRegionFiltered]=1;[Region];"All")))

Hope that you got the idea and that it fits your requirements.

Share
10 |10000 characters needed characters left characters exceeded