I am using cascasing prompts for 2 objects
Delegated ID (field from table which stores users login and delgated ids) and Role Name(field from table which stores role names for delgated ids) . When users logs in user will prompt first for Role name and when he clicks on values it will prompt for Delegated ID once user select delegated id it will prompt Role name with role names values for that selected delegated id.
This work fines. But in query on Fact table how can put condition both on delegated id and role name. Currently in condition i use role name = @prompt (role name). so that when users run report it will prompt for role name and as cascading prompt it will prompt for delgated id.
But how can i get value selected in Delegated id prompt for delegated object used in Condition on fact table.
Thanks for Reply. But can you please clarify little bit.
If i user prompt for Delegated id in condition it will ask me 2 prompts
(other is role prompt) simultaneously which will not give me correct results which i am getting using cascading prompt.
For e.g currently i am using in condition
Select BGID from Table where Id = “value for Delegated id” and
Role =@prompt (‘role name’)
where for object Role name i have created cascading prompt on delegated id. But how that prompt value i can pass in Delegated id in above condition?
The way you can get a single prompt for Delegated ID is to use the IDENTICAL prompt that is used in the cascading prompt. Try it.
In fact, you might be able to use the “Show list of Prompts” when you’re building the condition – and select the one used in the cascading prompt. If not, then copy the prompt text from the cascading prompt exactly, and use it in your added condition.
Thanks for reply. But i don’t want to include prompt in report condition.
I want to put condition on Universe level on fact table (self join on universe level as you put row level restrictions on supervisor level on table. but i want to put condition role name as well as delegated id, i can’t put row level restriction on supervisor level)
I think what you are asking is to have a cascading prompt but have both levels of the cascade in the SQL condtion. I do not believe this is possible, nor do I think this is necessary. If it was necessary, then a cascading prompt likely would not be appropriate. But, I’ll let you decide that.
You certainly could should be able to assign all the “conditions” to a complex join that does the prompting you want.
The real problem is that cascading prompts are really an LOV thing. Other than the first condition, they don’t have an effect on the SQL.
I could implement this somewau using Nested prompt.
But i have new issue. I use cascading prompt for Role Name and
Delegated Id. When user creates report he gets prompt on role name first and when click on value he gets prompt for delgated id. When user select delegated id, role prompt will show corresponding role names to select.
This works fine for user who got record in delegated id table.
But how can i bypass delegated id prompt and keep only Role prompt for users who donot have delegated id in Delegation table?
Also is it possible to get Prompt for Delegation id first. But values in Role prompt are depend on delegation id selected.
(BOB member since 2002-06-17)