Filter data when parameter is not null

Question

I have a list in a Report, such list is based in a query that show something like this:

+------------+----------+------------+
|   Employee |   City   |   Client   |
+------------+----------+------------+
| Alex       | All City | All Client |
| Alex       | Chicago  | Google     |
| Alex       | NY       | Twitter    |

The Employee can have many cities and many clients, the list is dynamically refresh by prompts and its parameter value come from a Drill-Throught in a dashboard.

So, I need to show in my list just the different city and the different clients, excluding the "All" data, but if the parameter is null should not exclude the "All" data, because City and Client came from a hierarchy and if the "All" is excluded will be no data in my list.

If parameter is not null, my list should look like this:

+------------+---------+---------+
|   Employee |  City   | Client  |
+------------+---------+---------+
| Alex       | Chicago | Google  |
| Alex       | NY      | Twitter |
+------------+---------+---------+

But if is null, should look like this:

+------------+----------+------------+
|   Employee |   City   |   Client   |
+------------+----------+------------+
| Alex       | All City | All Client |
+------------+----------+------------+

score 1 · Answer 1 · answered Apr 05 '19 at 15:53

1

Have you tried something like this:

(?param? is null AND [City] = 'All City' AND [Client] = 'All Client')
OR
(?param? is not null AND [City] <> 'All City' AND [Client] <> 'All Client')

answered Apr 05 '19 at 15:53

Johnsonium

2,005
1
13
15

score 0 · Answer 2 · answered May 09 '19 at 21:03

I think you are using DMR or a cube?

Make 2 hierarchies

For the second hierarchy, set Multiple root members to True - the hierarchy contains multiple root members. Selecting this option deletes the All level that is automatically created at the top of the hierarchy.

see https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ug_cog_rlp.doc/c_cog_rlp_dsgn_hier.html

Make a variable to determine if the parameter is null
Make two lists
List1 will include all cities -- have a hierarchy that includes ALL
List2 will exclude all cities -- have a hierarchy without ALL
Render List1 when the parameter is null
Render List2 when the parameter is NOT null

I'm using a TM1 Cube, It's possible to implement your solution in any way? — O. Escobedo, May 11 '19 at 18:57

Filter data when parameter is not null

2 Answers2