Filter A List By a Calculated Data Item

[DonRitchie]

Hi!

I posted a similar question last week, but it was within the context of building a dashboard and I wasn't able to get a working solution.  I apologize if you would consider this a repost and if that would be a violation of community standards, but I believe this issue is sufficiently different that it warrants its own post.

I have a report consisting of a list of active employees.  Based upon data I query, I assign a label to each employee using a series of If/Then/Else statements which I call [Business Line].

Code Select Expand

if([Business Layer].[Employee].[Org Level 1 Code]='30')then('Property Management')

else(if([Business Layer].[Employee].[Org Level 1 Code]='45')then('Relocation')

else(if([Business Layer].[Employee].[Org Level 1 Code]in('28','47','87','90','91','95'))then('Shared Services')

else(if([Business Layer].[Employee].[Org Level 2 Code] in('8660','8670'))then('Shared Services')

else(if([Business Layer].[Employee].[Org Level 2 Code] ='8680')then('Mortgage')

else(if([Business Layer].[Employee].[Org Level 1 Code] in('86','88')) then ('Mortgage')

else(if([Business Layer].[Employee].[Org Level 1 Code]in('42','56','58','78','72','73'))then('Settlement Services')

else(if([Business Layer].[Employee].[Org Level 1 Code]in('81','82','62'))then('Insurance')

else(if([Business Layer].[Employee].[Org Level 2 Code]='4108')then('Relocation')

else('General Brokerage')))))))))

How can I filter my report based upon this calculation's outputs?  For example, how could I create a filter that only shows records where the [Business Line]='Insurance'

Thanks!

[HalfBloodPrince]

Create this calculation in your report query ( query of List) then you can add the filter

[Lynn]

Your last post did not specify whether you are using a relational or a dimensional package and I don't see that you've answered that question anywhere. It is an important question. It was also unclear what tool you are using. Although you posted on the Report Studio board your posts seemed like you could be using Report Studio to build a report that looks like a dashboard, or you could be attempting the same thing using Cognos Workspace Advanced, or you could have been using portal pages in Cognos Connection, or perhaps you were working with Cognos Workspace. There are separate boards for each of the three tools.

So....is your source relational or dimensional? And are you using Report Studio, Cognos Workspace Advanced, Cognos Workspace, Cognos Connection portal pages, or something else?

Assuming it is a relational source and assuming you are writing a report in Report Studio, use the query explorer to navigate to your query and drag the [Business Line] data item into the detail filter area and write the expression exactly as you've indicated.

As a side note, I think the if/then/else logic is very cumbersome when there is an excessive amount of nesting involved. I'd suggest you rewrite it as a case statement. Better yet, have this created as a data item in the model so a simple drag and drop is the only thing needed.

Code Select Expand


case
  when [Business Layer].[Employee].[Org Level 1 Code]='30' then 'Property Management'
  when [Business Layer].[Employee].[Org Level 1 Code]='45' then 'Relocation'
  when [Business Layer].[Employee].[Org Level 1 Code] in ('28','47','87','90','91','95') then 'Shared Services'
...etc...
  else 'General Brokerage'
end