Conditional Prompting

[rfrink]

I've found a TON of articles on conditional prompting in regards to bringing up a prompt page based on the selection of another prompt. This is not exactly what I'm trying to accomplish though. We have sales reports for our Sales Reps. Usually, they are the ones who run these reports but not always.

What I want is, if this person is a Sales Rep (which can be looked up in the database), then display the report for their records. If they are not a sales rep, then prompt them for the sales rep they want to run the report for. In other words....a single report, with a single prompt page....but that prompt page would only come up based on a condition found in the DB.

I'm very new to Cognos, so I'd appreciate any help I can get. Thank you in advance!

Ryan

[BigChris]

Hi Ryan,

There's a macro that you should be able to use which should bring back the user name

#sq($account.personalInfo.userName)#

I haven't used it much myself, but you should be able to match that against the data that you've got which shows whether a person is a sales rep or not.

[rfrink]

I appreciate the reply, but actually, that's not quite the issue I'm having. In fact, I'm using that method to get the user name. That's all working fine and dandy. The part I can't figure out is how to only bring up that prompt page if that person is not a sales rep. In other words, using the following logic:

If (user = SalesRep)
    do not bring up prompt page
    run the report for that SalesRep
Else
    bring up the prompt page, asking which SalesRep they want to run the report for
End

Does that make sense?

[navissar]

Let's try the following direction:
I assume the following: in your database you have an employee table/view that contains the name of the user and position. If position is "Sales" then this person is a sales person.
Let's take as an example two users: Gregory is a sales rep, Lianne is management.
1. You create a query "EmployeePosition" which brings [Employee Name] and [Position]. It should return something like this:
Name||Position
Gregory||Sales
Lianne||Management

2. You filter the query with [Employee Name]=#sq($account.personalInfo.userName)#
So the query in fact should bring one row - the one pertinent to the user running the report.

3. Now, you filter your main query like this:

[Employee name]=#prompt('Employee','string',sq($account.personalInfo.userName))#

This will default the filter to the username of the sales person.

4. You create a prompt page with a prompt (Make the prompt required), and then you select the entire page, and assign the query you created in step 1 (EmployeePosition) to it.
5. You create a boolean variable that looks like this
[EmployeePosition].[Position]<>'Sales'
(So you drag the position data item from the query created in step 1)
6. You make this variable the render variable for the prompt page (Again, you select the entire page, and in properties under "render variable" - you assign that variable.

Expected outcome: When Gregory runs the report, the report runs EmployeePosition Query, gets the position (="Sales"). The render variable gets a false and the prompt page isn't displayed. Since it isn't displayed the query defaults to the default value of the prompt, which is the user name, which is Gregory, so dear old Greg can only see his data.
When Lianne runs the report, the report runs EmployeePosition Query, gets the position (="Management"). The render variable gets a true and the prompt page is displayed. Since the prompt is defined as required Lianne has to fill it, and then the report is filtered to show her selection.
I can't quite test this (It would require a lot of set up), but it should work.

[rfrink]

I will give this a try as soon as I have a chance, hopefully tomorrow. I'll let you know how it goes. I appreciate your help. Cheers!