Same Query, different value per worksheet

[Former Member]

Hi Experts,

I tried searching for discussions here that could be related to my issue, but I couldn't find one.  Hopefully, you could provide me some insight.

Basically, we have a workbook with several worksheet using the same BW query for a datasource.

However, our target is for the worksheets is to have different values based from one of the prompt variables

Ex.

Worksheet 1

-datasource: DS_1

-prompts: Prompt1 = A, Prompt2 = B

Worksheet 2

-datasource: DS_1

-prompts: Prompt1 = A, Prompt 2 = C

I know it's possible in Unmerged variables, but is it possible in Merged Variable as well?

We have a like 10+ prompts and several worksheets and if variables are remained unmerged,

the prompt screen would look pretty messy.

We are using SAP BO AO 1.4.5.2837

Regards,

Mark

[pavel_afanasiev]

Hi Mark,

what if you use the another option, where you have both data sets shown on the same worksheet, and show it twice?

so, you have 1 DS_1

your prompts are:

- prompts are: Prompt1 = A, Prompts 2 = B, C

Drill down by Prompt 2 in either rows or columns (whichever way you have it laid out)

Display full crosstab on both worksheets, and use the Excel Group function to "hide" the rows/columns that don't belong (hide C on Worksheet 1, B on Worksheet 2)

this way, you are only pulling the data from the back-end once. 

Regards

Pasha

[Former Member]

Hi Mark,

I don't get your problem?!

Each and every included query is one DS_x ... there cannot be two DS_1 on 2 different sheets?

Like in AO help, you can set any variable specificly per DS_x:

Dim lResult As Long

lResult=Application.Run("SAPSetVariable", "0BWVC_COUNTRY", "DE", "INPUT_STRING", "DS_1")

So why don't you create one common sheet with "Input" cells for variables you want to fill in all your datasources, and then per sheet you create other "Input" cells for independent selections?

Then you create a macro with refreshing and "variable setting" logic and you are able to refresh any report independently?

If a user wants to Change the prompts on one query then he can open only that one, otherwise he'll work via the once central sheet?!

Just my 2 Cents...

Martin

[TammyPowlas]

Hi Mark - have you considered using BEx Query views for each workbook?

[Former Member]

Hi Tammy,

Thanks for the response!

I tried using Query Views but the result is still the same when I Merged the Variable.

I have a solutions in mind, but I don't think it's the most optimum one:

Create several copies of the BEx query (equal to the number of worksheets), then create

different variables of the same object (eg. CompanyPrompt1, CompanyPrompt2...)

Sample:

Worksheet 1

-datasource: DS_1

-prompts: Prompt1 = A, CompanyPrompt1 = B

Worksheet 2

-datasource: DS_1.2

-prompts: Prompt1 = A, CompanyPrompt 2 = C

Any suggestion would be greatly appreciated.

Kind Regards,

Mark

[TammyPowlas]

Hi Mark - I think the merge variables are always going to share prompts

What I was thinking was to use query views to help filter / use less prompts and then you can use unmerge variables to lessen the number of prompts

[Former Member]

Unfortunately with two different Queries OR unmerged are the only options....

one (slower) option would be to do it with filter instead of variables

regards

Thorsten