Display column values based on object values

[former_member672670]

Hi All,

I want to display values of certain columns based on the value of an object in my Webi report.

My columns are - ID and Name. My object name is Status that has values of 'Y' & 'N'. If Status = 'Y', I want to display values for both the ID and Name columns. If Status = 'N', I only want to display values for the ID column, but no values for the Name column i.e. blank. I am not sure how I would incorporate the logic in a report variable with an IF statement to display values for multiple columns. Any ideas?

[ayman_salem · Accepted solution]

From your description it appears that no variable has to be defined for the ID column, since it is displayed in both "Status" cases. Just define a variable for "[Name]" and use it in the "Name" column.

vName: if ([Status] = "Y") then [Name] else ""

....

hope this help you

[former_member672670 · Accepted solution]

The report is only returning rows where Status = "Y" after applying the formula as mentioned in my comment to Mahboob's solution.

[ayman_salem · Accepted solution]

Which block level filters are also used?

A screenshot of the table is helpful so that we can give you the correct answer

[former_member672670 · Accepted solution]

The block level filters are applied on the date fields in the report using variables. For e.g. V_Latest Update Date = If (Update Date = Max(Update Date) In (ID)) Then 1 Else 0. When I remove the block level filters, the suggested formula seems to work fine. Any ideas on how to resolve the issue without removing the filters?

[ayman_salem · Accepted solution]

A screenshot of the table (with and without filters) is helpful so that we can give you the correct answer

[former_member672670 · Accepted solution]

The block filters are applied on Max(ID), Max(Update Date), and Min(Program Date). The formula uses the same logic for all three variable i.e. If(ID) = Max(ID) In (ID)) Then 1 Else 0. Here's how the table looks like without the new vName variable.

After using the new variable, only the first row is returned where status = "Y". It does not return rows where status = "N" or null. If status = 'N' or Null, I want to display blank values for all the columns except the ID column.

[ayman_salem · Accepted solution]

Your original question was about the "Name" column, not for all columns except ID, as you are mentioning now. (This makes a big difference, especially if you use the formula in the other column).

The displayed result is therefore normal. If you set the "update date" and / or the "program date" to "empty", you will not get the expected result for "max" and "min".

To solve it, use NoFilter() in the formula Max(Update Date) and Min(Program Date).

...

I hope this gives you an idea how to solve it.

[former_member672670 · Accepted solution]

Thanks. I was able to apply the formula to the columns after using NoFilter() on the Max(Update Date) and Min(Program Date). However, if I add another block filter based on a dimension object (Valid ID = 'Y'), the issue seems to come back. Looks like you can only apply NoFilter() to measure objects.

[ayman_salem · Accepted solution]

see NoFilter help:

Description

Ignores filters when calculating a value. NoFilter is used with measure objects. It does not apply to dimensions.