Thursday, 5 July 2012

Prompt with Combined Subject Area

Values produced from a single subject area in the prompt can be used to filter the data in the report. But if we need to combine the results of both the columns in two different subject area then we need to combine both the columns using a UNION request in the SQL results.

Lets start with the creation of the prompt. We first pull the column from the first subject area and in Options-->Choice List Values-->SQL Results. We copy the Logical SQL query from the first prompt.
For Example-SELECT "Time"."Per Name Year" FROM "Sample Sales Lite"

Next, we create a second prompt and as the steps mentioned above try to copy the Logical SQL results from the second prompt with a different subject area.
For Example- SELECT "Time"."Per Name Half" FROM "Sample Targets Lite"

We combine the first Logical SQL query with the first logical SQL query with a union between the two SQL clause.
Now the query looks similar to the below-

SELECT "Time"."Per Name Half" FROM "Sample Targets Lite"
SELECT "Time"."Per Name Year" FROM "Sample Sales Lite"

Save and check the results...

Comments are always welcome...

1 comment:

  1. Hi,
    Quick question, will this approach works if we select multiple values from the prompt and try to pass them to report? For some reason its not working for me because of formatting issue, for example multiple values missing the space after comma like (A,B) .. correct format is (A, B)


Thanks to Comment