Workbook - Query problem

[Former Member]

Scenario -

Workbook attached with 2 queries as below.

Query 1 –

Fiscal year – variable (user input)

Posting period – variable (user input)

Query -2

Fiscal year/Period – Replacement path Variable from Query -1

Replacement path variable should fill the values of Fiscal year and Posting period from Query-1 to Query -2 Fiscal year/period , so user dont have to key in input .

Workbook - Query - <b>Properties</b> (Refresh query when opening Workbook) has been set for all 3 queries.

currently while opening workbook it directly goes to query-2 and fails instead of going to query 1.

How to achieve it??

thanks for help

[udayabhanupattabhiram_cha]

Hi Bill:

You can rename the sheets how ever you want after inserting the queries in right order so that you don't loose your Mapping.

Chamarthy

[udayabhanupattabhiram_cha]

Hi Bill:

I am not sure why there is a problem.

But, if you want to change the Order, its worth a try becasue its easy to test.

Create a new Workbook and insert the Queries in the reverse order.

Means, your Replacement Query will be inserted first and so on.

Try this and see. If I find any thing else, I will let you know.

Chamarthy

[Former Member]

Hi,

As per My Bex Knowlegde...

BEx Call Queries in workbook in LIFO(Last in First OuT)...means if you have insert your query-2 after query-1..then Query-2 will be called first.

So the workaround is...

First Remove both Query from Workbbok...then..First Insert Query2 in Sheet2..

Then Insert Query1 in Sheet1.

Now Save the Existing Workbook..

Then close the Workbook..then again open and refresh..in NON-ACTIVE Area of query..it will pop-up..Refresh all queries. OK..

Hope i m clr...Correct me if i wrong..

Regards,

San!

[Former Member]

Thanks Sandeep,

can you please confirm ,as guess what

i have already mapped the query structures (253) from sheet -1 which has 2 queries in to the workbook (addition,subtraction,division )lot of complex calculations...

If i remove this query 1 & 2 from sheet 1 ,then i amight lose all those mappings which i have done before ..

please confirm the above...

thanks

[Former Member]

Hi,

As UP have said...create another dummy Workbbok and first Insert Query 2 and then Query1,,see if this works...i am sure...it should work...

IF yes then...do this..in orginial..Workbbok..

First remove both queries...

Make sure that you remember...the excel..(where you placed your cursor when you inserted the Queries for both)...Then Click on tthta "EXACT CELL" in excel andinsert Q2..where your second Query supposed to start as per your MApping..

Then do ths same for query1.

Lets us know this works...

Regards,

San!

if helpful, Assign points...

Message was edited by:

Sandeep Khatri

[Former Member]

Thanks Guys

Sandeep the LIFO principle works in WB ,i tried and assigned points..

Now the question is that the replacement path doesnt work.

Let me give you clear picture ..

Query 1 - has

fiscal year

posting period

Query 2- has

fiscal year/period

i want to take the values user entered from query-1 from 2 infoobjects

thats fiscal year ,posting period both individual values and map to infoobject fiscalyear/period which is single.hence i used replacement path variable which is taking query-1 as input for query-2

currently query-2 disconnects after refreshing workbook

thanks guys for your help again

i know we can do it thru RSBBS but here we do not want user having GOTO..

[Former Member]

Hi,

Do this...(Assuming you have posting period and year in Q2)

1)Create a User-entry Variable for posting period and Fiscal Year..in Query2..

2)Now Create a Customer-exit varibale for fiscal/year period in Query2.

replace the earlier replacemeth variable in query with this...

It shld work..

Hoep i m clr.

Regards,

San!

[Former Member]

Thanks Sandeep

I didnt get it

1)query -1

user input variable for fiscal year,posting period i created.

2)query -2

customer exit variable for fiscalyear/period

what shoudl be the properties??ready for input ,single value ,optional??

Also if its customer exit variable than i cannot use replacement path variable..

correct me if wrong??

Also how to display ZERO value in query which has structure cell elements

Query property has "Display zeroes"

any property setting for structure element of cell to be worked to get 0.

or work around if any...

Currently if the structure cell is empty or null value it just displays blank without displaying 0,how to mandatory display 0 for all such values.

thanks

[Former Member]

Hi,

Let's ask you have Created "YEAR" and "POSTPER" variable for Query...use same variable in Query2 and

Now create a Customer Exit variable which take value from this VARIABLES...

Properties of

Remove check on ready for input

single value,

Mandatory.

And you can replace your replacementh path variable with this new variable..

OR

Some more BEx Exp..normally..there will be a Single pop-up screen when you refresh..when all the queries in workbook have a same variables used as user-entry.For example->Query1 have year (yearvar) as user-entry and query2 uses same variable (yearvar) then..there will only single pop-up screen ..while you refresh..The pop-up will be o based of insertion of query in WORKBOOK..that LIFO..

The other idea is to have same variable in place of your replacementh path...a thought process...

Say in Query1 user have enter 2006 and 012 as user-entry...now you have to use this...Query2..

Make a restricted KF as -> Place "Year" and "POSTING" as it is same as "FISCAL/YR PERIOD" in terms Bex.

you want "FISCAL/YR PERIOD" as 012.2006..which you gonna restrict..

Hope i m clr...correct me if i m wrong..

Regards,

San!

[udayabhanupattabhiram_cha]

Hi:

Try reversign the Order

SAPBEXq0001 - 3rd query which has replacement path

SAPBEXq0002 - second

SAPBEXq0003 - first query.

Chamarthy

[Former Member]

How to change query -ID??

i dont think we can do it ,its assigned automatically by SAP.

Is the reason that in replacement path of query -2 ,i am attaching query-1 giving the problems??

thanks

[udayabhanupattabhiram_cha]

Hi Bill:

You may already tried this, but I want to make sure.

When you go to the first query,

Right Click on the Query,

Go to Properties

Go To Tab "Information"

There is a box called Query ID

What is its value?

Is it something like - SAPBEXq0001

As far as I know, the queries gets refreshed in the order stated above.

If its not SAPBEXq0001, then there may be a way to change the order OR

create a new workbook where you include the First Query first.

If you already did this and checked, then, you have to wait if someone else already looked into it.

Chamarthy

[Former Member]

Thanks UP..

you are rite..and the properties are set correctly..

SAPBEXq0001 - first query

SAPBEXq0002 - second

SAPBEXq0003 - 3rd query which has replacement path.

if not this way is there another way around??out of the box??

thanks

[Former Member]

Hi

If each Query is in a different Excel tab I suggest you do this:

Go to the Workbook and go to the tab where you want it to start.

Then save the Workbook!

Then retest.

Reg's

Edan

[Former Member]

Thanks Edan for ur reply..i awarded points..

But i have same sheet 3 sheets attached ...in my case

Neverther less ,do u think that passing of 2 different variable values

FISCAL YEAR

POSTING PERIOD

from query -1

can be passed and clubbed to Query-2 FISCALYEAR/PERIOD ??

through replacement path variables..

thanks again

[Former Member]

Now i removed query from same sheet and attached to other sheet.

Also i tried ur option of going to that sheet ,saving it ,coming out and opening that workbook ;however its always running the sheet which has query -2

Nutshell - Replacement path variable query always runs first no matter what i do..and it fails as the first query from where it should have filled in has not run..

thanks

[Former Member]

Hello ..

any updates??