Skip to Content

BEX Workbook: Set analysis grid range dynamically

Hi Experts,

I have been working on this workbook for days (FYI: I am not an expert in excel macros :-) ).

There are 2 queries in serted in my workbook.

1st query range : Rows 15 - 88

2nd query range: Rows 90 - 122

Now the queries are basically structures which are expandable and collapsable. Now, when I collapse an element in query1, the range for query 1 becomes 15 - 75. Means rows 76 - 88 will be shown as blank lines. My aim is to avoid this. Is there any means by which we can set the range for the 2nd query dynamically so that I can manipulate the same and set as 76 - 109???

Regards,

Arun.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Apr 26, 2012 at 11:29 AM

    Dear Arun and Marco,

    I was able to resolve this problem through a significant effort. Here is roughly what I did:

    1- I added a large number of blank rows between the analysis grids (25% more than the maximum number of rows each dataprovider would return).

    2- I then "Hid" the rows using Excel

    3- I then wrote a Visual Basic Macro that finds all Bex Analysis Grid objects and unhides all the rows where those objects reside.

    Sub BexUnhideAnalysisGridRows(ParamArray varname())

    ' This program is used as an automatic executing program on a Bex 7 workbook sheet

    ' CBB, Cleveland, Ohio 2010

    ' Free to use license, must include these comments and not take credit, blah blah, don't sue me

    ' Requires "C:\Program Files\SAP\Business Explorer\BI\BexAPI.tlb" to be added as as reference

    ' under Tools->References...

    '

    ' Dimension Statement for variables used

    Dim oBexItem As BExItem

    Dim oBexItems As BExItems

    Dim range1, range2, unhiderange As Range

    Dim oBex As Object

    ' Connect to Bex Analyzer

    Set oBex = Application.Run("BEXAnalyzer.xla!GetBex")

    ' Obtain all BEx Items in the worksheet

    Set oBexItems = oBex.Items

    ' Start by turning off view updates

    Application.EnableEvents = False

    Application.ScreenUpdating = False

    ' Find the GRID item by name and unhide it plus a range that is offset to the row below it

    For Each oBexItem In oBexItems

    If InStr(oBexItem.Name, "GRID") Then

    Set range1 = oBexItem.Range

    ' for this workbook, hide title bar and include one extra rows below results

    Set range1 = range1.Offset(2, 0)

    ' Set range2 = oBexItem.Range.Offset(1, 0)

    Set unhiderange = range1

    unhiderange.Rows.Select

    Selection.EntireRow.Hidden = False

    End If

    Next

    ' Turn back on screen updating, and set cursor to A1

    Range("a1").Select

    Application.EnableEvents = True

    Application.ScreenUpdating = True

    End Sub

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 26, 2012 at 11:35 AM

    One additional point... we completely removed the CALLBACK macros from our standard template when we converted from 3.x to Bex 7. Our users were not familiar with button controls, were trained to use the toolbox (Change variable values). The Macro listed above is attached to the workbook and does run after query refresh.

    it will *not* re-hide previously hidden rows if variables are changed and fewer rows result on the next refresh.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Charlie Belt

      Hi Charlie,

      I know you have said that you got this code from some other user. I am trying my luck here for finding anything about the BEXItemButton. I am trying to code a VBA to give me a list of all the BEX items with their settings. Here you will find the link to my discussion. I appreciate any help on this.

      Thanks.

      Sangharsh

  • Posted on Apr 26, 2012 at 08:41 AM

    Hi Arun,

    I had the same problem (or slightly different: If you expand the first query, it overlaps with the 2nd - I'd like to push the 2nd further down dynamicaly to avoid overlapping)

    I've to confess that in the end I did not find a real soution.

    You could move the excel-ranges in the CallBack-Macro (the range of the output-area is one of the input-parameters of this function), but that's it. You might loose context-menu-handling etc.

    Maybe you'd like to search for some of my posts in this board...

    Please let us know if you should find a solution.

    Kind regards,

    Marco

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      HI Simon,

      I am facing the same issue.Recently we migrated workbooks to bex7. Now when i am trying to drill down values in the first query (two queries on single sheet in workbook), it is restricting to few line and It is not overlapping with second query which below.

      When i checked in design mode i can see that query one is restricted to range in between 55 to 59.thats the reason i believe dynamically query is not radding new rows to show values.

      It would be brilliant if you help me out from the issue.

      Thank you inadvance!

      Hari

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.