Question Re: Display column values based on object values in Technology Q&A

Display column values based on object values

former_member672670 — Fri, 01 May 2020 05:55:18 GMT

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?

Re: Display column values based on object values

mhmohammed — Fri, 01 May 2020 08:14:40 GMT

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

Re: Display column values based on object values

ayman_salem — Fri, 01 May 2020 08:16:06 GMT

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

Re: Display column values based on object values

former_member672670 — Sat, 02 May 2020 16:36:50 GMT

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.

Re: Display column values based on object values

former_member672670 — Sat, 02 May 2020 16:41:16 GMT

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

Re: Display column values based on object values

ayman_salem — Mon, 04 May 2020 13:31:43 GMT

Which block level filters are also used?

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

Re: Display column values based on object values

former_member672670 — Tue, 05 May 2020 05:33:26 GMT

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?

Re: Display column values based on object values

ayman_salem — Tue, 05 May 2020 07:28:20 GMT

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

Re: Display column values based on object values

former_member672670 — Fri, 08 May 2020 02:38:32 GMT

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.

Re: Display column values based on object values

ayman_salem — Fri, 08 May 2020 09:35:52 GMT

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.

Re: Display column values based on object values

former_member672670 — Sun, 10 May 2020 06:47:21 GMT

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.

Re: Display column values based on object values

ayman_salem — Sun, 10 May 2020 07:48:33 GMT

see NoFilter help:

Description

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