on 04-26-2012 7:09 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.