CODEWITHSUNDEEP
reportfilteringcognoscognos-10
Tyler Dovall
Tyler Dovall

Reputation: 140

Cognos report studio filter on available options from value prompt

I have a few value prompts that don't quite cascade, but they are related. As an example:

So if you chose Jim in prompt 1, prompt 2 would be populated by only shift supervisors that Jim is in charge, and so on down the line of value prompts. For the actual report the only prompt that truly matters is the final prompt for employees, the rest just filter down the list of relevant employees.

The problem I'm running into is that all the prompts are optional depending on narrow you want the scope to be, and the query it's looking at is really only looking at the final prompt value. If the final one isn't filled in, but let's say the shift manager is, how can I make the report only show employees under the shift supervisor I've chosen?

To say it another way, if the hierarchy tree looks like this:

where each level represents a different value prompt, if I've chosen Jim as the Manager, and Teri as the shift supervisor, my report is currently showing results for Dave, Bill, Amanda, AND Joel, but I only want to see Dave and Bill.

I have the value prompts filtering appropriately, so how can I make my report see the available options in the final prompt if none are explicitly chosen, instead of not filtering on employees at all?

Upvotes: 0

Views: 661

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

Generally this is achieved by adding a static value to all your prompts called "All". If you choose "All" in your final prompt, then you add some logic to your where clause to recognise this. For example:

([ManagerID] = ?pManagerID? OR ?pManagerID?='All')
AND
([SupervisorID] = ?SupervisorID? OR ?SupervisorID?='All')
AND
([EmployeeID] = ?EmployeeID? OR ?EmployeeID?='All')

Then all that remains is all the fiddling about with prompt macros etc. to make your prompts optional as required.

For example if you pick a specific Manager and a specific Supervisor then pick "All" in the employee prompt, this is the result:

([ManagerID] = '050' OR ?pManagerID?='All')
AND
([SupervisorID] = '012' OR ?SupervisorID?='All')
AND
([EmployeeID] = 'All' OR ?EmployeeID?='All')

Simplified this means:

([ManagerID] = '050')
AND
([SupervisorID] = '012')

So it won't pick all employees - it will only pick records with your selected ManagerID and SupervisorID

Upvotes: 1

Johnsonium
Johnsonium

Reputation: 2005

You can take advantage of the way ORs work to construct a solution. Since only one clause is required for an OR logic construct to return true, you can use this behavior to test from specific to general and stop when at the appropriate level.

([EmployeeID] is not missing AND [EmployeeID] = ?EmployeeID?)
OR
([SupervisorID] is not missing AND [SupervisorID] = ?SupervisorID?)
OR
([ManagerID] is not missing AND [ManagerID] = ?ManagerID?)

If an employee is selected, the first clause in the OR is used and the specific employee is filtered.

If no employee is selected but a supervisor is, then the first clause will return false and the second clause will be used.

If no employee and no supervisor are selected but the manager is, then the first and second clauses will return false and last clause will be used.

Upvotes: 0

Related Questions