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.

[mhmohammed]

Hi Jeewan,

Use the ID column as is in the table, but for the Name column, create a variable as v_Name with the below formula, and use that variable in the table.

=If([Status]="Y") Then [Name]
Elseif([Status]="N") Then ""

Thanks,
Mahboob Mohammed

[former_member672670]

I tried the solution, but it only return rows where Status = "Y". There are other rows that have Status of "N" or "", but those are not displayed in the report. There are other block level filters applied on the report, so I'm not sure if that might be an issue.