BEX Workbook: Set analysis grid range dynamically

[arunkumar_vr]

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.

[former_member211907 · Accepted solution]

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

[former_member211907]

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.

[marco_simon2]

Hi Charlie,

first of all: Thank you a lot for this code-snippet.

The idea of preparing hidden fields is certainly not the most beautiful solution, but maybe it is enough if it works.

I've got 2 additional questions:

1) I did not read about internal BEx-objects and their attributes like BExItems. Is there some documentation about the API out there? Or did you explore the objects by analysing the default-workbook and its VBA?

2) If you do not use the CALLBACK-macro, how do you catch the "after-query-refresh" event?

Did you replace the default-callback macro by the one you described (and referenced to it using the  

workbook-properties)?

Best regards and thanks a lot!

  Marco

[former_member211907]

Macro-

1) I actually got part of that code from another Bex user in the area. SAP is particularly useless about providing documentation for anything VBA. The default workbook and it's VBA did not contain anything of the sort, and I had to add that library based on another person's recommendation.

2) I replaced the CALLBACK macro with my macro and in the workbook properties as you noted. 95% of our standard workbooks have no embedded VBA in them, and we prefer it that way. We want our users to be analyzing data, not fussing around with buttons.

[arunkumar_vr]

Hi Charlie & Marco,

Thanks for the efforts taken. I am on travel today and tomorow. So I will come out with a point on saturday probably.

Thanks a lott....

Arun.

[arunkumar_vr]

Hi Charlie & Marco,

Sorry for the delayed reply as my travel was extended a bit that I expected.

Thanks a lot for the code snippet and your advices. I was able to solve the issue using the logic provided and finally got finished my huge BEx report which ran over a month of development 🙂 .

Regards,

Arun.

[Former Member]

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

[marco_simon2]

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

[former_member782071]

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