Merge Prompts

[former_member402770]

Hi Experts,

I have two query in one report and user wants to enter prompt value only once for both queries.

First Query Prompt:

1) Enter Start Date

2) Enter End Date

3) Order

Second Query Prompt:

1) Enter Start Date

2) Enter End Date

In this user wants to  enter value only once in the  " Enter Start Date" and "Enter End  Date".This you can achieve by merge the syntax and spelling needs to be exactly the same (including upper and lower case) in the both queries prompts.

Below is the prompt user sees, now will this be an start and end date have list of date values  from both the prompts? or it will display LOV's from first query prompt only? or LOV's from second query prompt only?

Enter Start Date

Enter End Date

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

LOV's are not going to be merge. LOV's are going to display from the object where you have selected highest options.

let say if you want to display the lovs from query one prompt then uncheck the "Prompt with values" option for query 2 prompt.

Amit

[former_member402770 · Accepted solution]

Amit,

So if have the LOV's in both query set as Emp Id Prompt with similar text.

Say now i was prompted and selected in Emp Id with LOV's as 100, 200, 700, 800, 900 will it display data only from query 1 in the webi block ouput.

or

will it create two webi blocks one output as 100, 200, 700, 800, 900 and another block with output as 100, 200 only.?

Appreciate your thoughts on this,

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

If you have selected 100, 200, 700, 800, 900 in the prompt condition then if you drag query 1 and query 2 objects in separate tables then  in first you will get 100, 200, 700, 800, 900 and in second table you will get 100,200.

If you merge both the object and drag merged object then you will get the 100, 200, 700, 800, 900 values in single column.

data is going to be based on this  one.

will it create two webi blocks one output as 100, 200, 700, 800, 900 and another block with output as 100, 200 only.?

Amit

[former_member402770 · Accepted solution]

Hi Amit,

Nice, right 1) If you merge both the object and drag merged object then you will get the 100, 200, 700, 800, 900 values in single column

Merged Obj       KF.query1   KF.query2    KF.query1 - KF.query2

100                       234           236                   -5

200                       353            344                   9

700                 234          345             -109

800                       324             567                 -143

900                       535              733                 -198

Can you verify my Above understandings are right/

2)  In my above i need to display only matching records of both query 1 and query 2. In the prompts  i have checked the "Prompt with values" option for both query 1 and query 2 prompt along with select only from list+optional prompts.

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

according to your first post you are passing 100, 200, 700, 800, 900 values in the prompt.With this if you see in the second query you will get the records only 100,200.

In the merged object you will get the 100, 200, 700, 800, 900 values. But KF values are wrong in your table.

Right output will be

Merged Obj       KF.query1   KF.query2    KF.query1 - KF.query2

100                       234           236                   -5

200                       353            344                   9

700                        234           0                      234

800                       324            0                       324

900                       535            0                      0

reason is 700,800,900 values are not in the  coming in the query 2 so KF values are also not come.

First confirm if values are coming in both the queries for proj code like 100,200 only (without any text).

If yes then it will also work.

=If([Query 1].[Code]=([[Query 2].[Code]))  Then "Show" Else "Hide"

Amit

[former_member402770 · Accepted solution]

Hi Amit,

Thanks yes you are right iam wrong there in KF2. I agree your output is right.

Coming to the below code:

=If([Query 1].[Code]=([[Query 2].[Code]))  Then "Show" Else "Hide"

This will bring only first two records 100, 200 yes it is coming from both queris. Matching records what i expected.

Since I was saying it is an multsource report i have  multiple instance of sql db having different IDT connection .

So for this can i rewrite to restrict the matching records:

=If([Query 1].[Code]=([Query 2].[Code]+ [Query 3].[Code]+[Query 4].[Code]))  Then "Show" Else "Hide"

where as query2, query3, query4 are the query's having individual connectivity to the instances specific to one DB .

My query 1 will be the oracle source to reconcile with query2, query3, query4 instances specific to mssql. in one report block.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

Write your formula like this.

=If([Query 1].[Code]=([Query 2].[Code])) And ([Query 1].[Code]=[Query 3].[Code])  Then "Show" Else "Hide"

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  iam not understanding the below you mentioned:

let say if you want to display the lovs from query one prompt then uncheck the "Prompt with values" option for query 2 prompt.

  1)  Whatif i donot uncheck the "Prompt with values" option for query 2 prompt?

   2) Whatif i check "Prompt with values", "Select only list" option for both the prompts?

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

By unchecking the "Prompt with List of values" option means you are forcing to display the LOV's from the other query prompt.

  1)  Whatif i donot uncheck the "Prompt with values" option for query 2 prompt?

  2) Whatif i check "Prompt with values", "Select only list" option for both the prompts?

Even if all options are selected same in both the prompts still you will get the LOV's from one object only.Making a difference means you are controlling the LOV's display.

"Prompt with List of values" is one option i have suggested.even you can uncheck the "Select only list"  in one query.

9.1.1 Merged prompts

When a document contains multiple data providers, any prompts that include objects with the same data type, operators of the same operator type, and that use the same prompt text are merged. When all the data providers are refreshed, a single prompt message appears for such prompts. The List of Values displayed by the merged prompt is the list associated with the object in the prompt that has the most display property constraints.

Source-Webi user guide. page 130.

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_ia_en.pdf

Amit