Prompts & Join Expressions

[DTV12345]

It is possible to create a prompt in report studio that allows me to select a join expression between tables? For example 

tableA (One) --> tableB (Many)
AND
tableA (One) --> tableC (One)


I was able to do this using a CASE() expression in my query. Problem is that it is VERY slow.

A year back, I worked on a project using BusinessObjects. They use a concept called "context" whereby the user can pick at runtime which join path to use in a report.

Can we do this in Report Studio? Framework Manager? Help!! ???

TIA for any help you can provide.

Al.

[larsonr]

Simple solution:

Build your two queries that represent the two joins.  so one query is method A and one query is method b.  (You might need to some modeling in Framework to build support for both queries)

In your report build both solutions.  You can do this either one method per page, use a conditional block, create a render variable, etc....

When done, use your prompt to have Cognos render one of the methods.  Congos then will only execute the SQL needed to build your report therefore not running method b if method a is selected and vice versa.

Hope this helps.

Other solutions may work (i've actually used the report variable to flip which item to use in a data item before), but this one is probably the easiest to get off the ground.