Current Position:Home > Retrieving BEx Query Results Area for use in Excel formulas

Retrieving BEx Query Results Area for use in Excel formulas

Update:11-30Source: network consolidation
Advertisement
Hello,
I run BEx queries in workbooks and then use Excel formulas ( mostly VLOOKUP(LookupValue, ResultsArea, ColumnReturned,) ) to retrieve certain information from the query ResultsArea.
****Problem:
Depending on the query parameters, the query "Results Area" is larger or smaller.
****Non-optimal solution:
One way to define the  ever changing Excel range occupied by the Results Area is to create an Excel name as:
QueryRange = OFFSET(QueryFirstCell,,,COUNTA(OFFSET(Sheet1!$A:$A,,COLUMN(QueryFirstCell)-1,,)),COUNTA(OFFSET(Sheet1!$1:$1,ROW(QueryFirstCell)-1,,,)))
Then, the only input required is the top-left cell of the Results Area, which stays the same (unless the query definition changes).
****Question
What is the best way to refer to an ever-changing query Results Area in an Excel formula?
Thank you!
Louis

The Best Answer

Advertisement
It is actually much easier than that.
Use a VBA statement like the following:
Set RptRng = ActiveCell.CurrentRegion
This will retrieve the data in the Results Area, no matter what the size is.
Hope this helps...
Bob
  • Retrieving BEx Query Results Area for use in Excel formulas Update:11-30

    Hello, I run BEx queries in workbooks and then use Excel formulas ( mostly VLOOKUP(LookupValue, ResultsArea, ColumnReturned,) ) to retrieve certain information from the query ResultsArea. ****Problem: Depending on the query parameters, the query "Res

  • Exporting BEx Query results to CSV or other files. Update:11-30

    Hi All, We have a report designed in WAD, which uses a BEx query to display department's information. Because of amount of data in the results it crashes when u get all the Free Characters into ROWS(Error is : Result set too large(552244 cells); data

  • BEx Query results not getting updated Update:11-30

    Hi, I am using a BEx Query based on a multiprovider which is fetching data from a multiple remote cubes and multiple regular cubes. Now when data in the source system is getting changed the corresponding remote cubes are getting update but query resu

  • Output of BOBJ (Web Intelligence Reports) are twice BEx Query Result Update:11-30

    Hi Experts, Incase of first level of hierarchy, correct values (i.e same as BEx Query) are displayed in BOBJ reports . However on selecting two and more levels of Hierarchy, values,twice the corresponding BEx query values, are displayed in BOBJ repor

  • BEx-Query results in error in SAPLRRI2 and KFB_FUELLEN_CONST Update:11-30

    Hello, I hope anyone has a solution for the following problem. When applying the "Calculate with Attributes"-how-to, I got the following error message in a BEx-Query. It is reproducable and only occurs if I have a formula A minus B (key figure A

  • ABAP class or function for retrieving BEx query properties at runtime Update:11-30

    Hello Experts, I'm working on BADI RSR_OLAP_BADI and for a special calculation I need to know which characteristics are in the drill-down of the BEX query, sol later on I can aggregate some key figures using only these characteristics. Problem is tha

  • To retrieve a query result in to OAF page Update:11-30

    I am newbee........... i have an urgent requirement *"+TO GET THE RESULT OF QUERY+"* FROM MY DATABASE IN TO MSG TEXT INPUT item in my page any sort of help is appreciated thanx in advance...... DEV Edited by: Dev586 on Jan 9, 2013 12:29 PMHi, tr

  • BEx query : results and exceptions Update:11-30

    Hi people, I'm using BI 7. I've two questions. In a first part i want to know if i can use an exception in order to color all the line (characteristics + key figures) ? Because just the key figures are colored with many options I tried. In a second p

  • Use BEx query results from a FM in a Transformation to Infocube Update:11-30

    I am trying to use the Function Module RRW3_GET_QUERY_VIEW_DATA to execute a query and read the results from E_CELL_DATA. The issue i am having is that i get the keyfigure values but not the characteristics values. Is their a new FM that i could use

  • BEX Query not dispaying results Update:11-30

    Hi All, I think this week am hit with number of issues....as mentioned in my earlier posts am new to SAP BW and having issues. Issue: We have a BEx query and it is not displaying results(in Web report). Error is "could not find any records for the gi