Filter/Slicer based upon a Calculated Data Item

[DonRitchie]

Hi,

First time poster, long time lurker.

I have a report in BI that provides an roster of active employees in the company I work for.  I have a data item that assigns each employee into a workgroup based upon data pulled from my tables using a series of if/then/else expressions:

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','87','90',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')))))))))

I have a few question and I hope a few of the expert Cognos users here might be able to help me.

1.  How can I filter based upon this calculated field?  I can easily create a filter to only show employees in a certain or set of [Org Level 1 Code]'s.  How could I filter based upon this calculated expression (which I call [Business Line])?

2.  I am trying to create a dashboard with four common reports that my business partners use.  I am able to create a slicer that will find a specific [Org Level Code] and I can make my dashboard filter by that, but I would like to make a slicer that allows me to filter by the result of the calculated expression above [Business Line].  I try to create a slicer where the expression is

Code Select Expand

[Business Line]->?par_BusinessLine?, but the expression builder gives me an error.  How can I make my dashboard filter by this?

Thanks!
Don

[Lynn]

Are you certain that you're working with a dimensional model? Slicers are not applicable in a relational model.

Your expression for the work group references data items with three parts [Namespace].[Query Subject].[Query Item] which looks relational. Also, in a dimensional world you generally wouldn't use "if" or "case" logic.

A dimensional model item has three, four or five parts, depending upon what exactly it is:

A Hierarchy would be [Namespace].[Dimension].[Hierarchy]

A Level would be [Namespace].[Dimension].[Hierarchy].[Level]

A Member or Attribute would be [Namespace].[Dimension].[Hierarchy].[Level].[Member/Attribute]

[DonRitchie]

Then how do I set a parameter to pass between my filter page and the rest of the dashboard with a relational model?

I watched a YouTube on how to filter a dashboard and the YouTube used a slicer.

[BigChris]

Ah ok...it sounds like you might need to ask someone to find out what sort of data source you're using. Once you've done that you could perhaps have another look on YouTube. You need to understand whether you're using a dashboard, or a workspace. Once you know that you'll need perhaps need to look at how to link two portals together using channels...but that will depend on the answers you get from the other questions.

[DonRitchie]

Thanks!

I made a page using the "New Page" with two columns.  I am calling this a "dashboard" for the lack of better terminology.  I have two Cognos Viewers in the left column and three Cognos Viewers in the right column.  I have my Filter report with a dropdown in the upper-left and a report with chart on the lower left.  I have three reports on the right that are all lists of employees.  Each of these three list reports have a calculated field called "Business Line" that I showed the code for in my OP.

All of my pages have communication enabled via channel "PromptFilter".

I want to be able to select a "Business Line" from my dropdown and each of the reports to filter by "Business Line".

[BigChris]

Ok - so when you run each of those reports does it present you with a prompt for Business Line?

[DonRitchie]

No, it does not, because I can't figure out how to create a filter based on a calculation like that.

[Business Line] includes ?BLine? doesn't work.