CODEWITHSUNDEEP
cognoscognos-11
O. Escobedo
O. Escobedo

Reputation: 3

Filter data when parameter is not null

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 |
+------------+----------+------------+

Upvotes: 0

Views: 1980

Answers (2)

VAI Jason
VAI Jason

Reputation: 544

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

Upvotes: 0

Johnsonium
Johnsonium

Reputation: 2005

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')

Upvotes: 1

Related Questions