Workbook : Problem of Overlapping two query

[former_member1320038]

Hi All,

                 I want to add two query in one Workbook sheet. so that i took two grid analysis when i display report the coloums in two queries are overlapping.

Please help me out.

[Former Member]

Hello Suhas,

There is a VBA solution:

First make sure the second query is far enough never to be overlapped by the first query.

After the refresh, call the following sub, of course, changing the code to point to your specific locations.

This code leaves at most 2 columns between your requests

( Tested in Excel 2010 on Windows 7)

Hope this solves your problem

Sub hideSpaceBetweenQueries()

    Sheets("ResultsSheet").Activate

    Sheets("ResultsSheet").Select

    '                                                  First ColumnTitle cell from Query 1

    '                                                                                         First empty cell before Query 2

    Call hideUnusedColumnsBetween(ActiveSheet.Range("A1"), ActiveSheet.Range("BZ1"))

End Sub

Private Sub hideUnusedColumnsBetween(req1ColumnTitle1 As Range, firstEmptyCellBeforeReq2 As Range)

   Dim j As Integer

   Dim titleRow As Integer

   titleRow = req1ColumnTitle1.Row

   ' UnHide columns if they were

   Range(req1ColumnTitle1, firstEmptyCellBeforeReq2).EntireColumn.Hidden = False

   j = req1ColumnTitle1.Column

   ' I rely on the cell interior color to know if the title cell contains data

   Do While Cells(titleRow, j).Interior.Color <> firstEmptyCellBeforeReq2.Interior.Color _

            And j < firstEmptyCellBeforeReq2.Column - 1

      j = j + 1

   Loop

   If j < firstEmptyCellBeforeReq2.Column - 1 Then

      Range(Cells(1, j + 1), Cells(1, firstEmptyCellBeforeReq2.Column - 1)).EntireColumn.Hidden = True  ' This leaves 2 blank columns

   End If

End Sub

[umashankar_poojar]

Hi Suhas,

It is the expected behavior, if you place two queries side by side using analysis grid. Then it will overlap when you drilldown with more than one characteristics.

Suggest you to place in two different sheets of same workbook to avoid the overlap. In case if you have fixed columns then calculate accordingly and place the Bex Queries.

Thanks,

Umashankar

[Former Member]

Dear Suhas,

Go to Design Mode, and change the range of the analysis grid so that it doesn't overlap, Means if your one Analysis grid is taking range suppose from G14:I60, then change second analysis grid range starting from J14:L60.

Thanks

Vijayendra

[former_member1320038]

Dear Vijayendra,

                      But column are not fix values are dynamic . will it work after putting range?

[Former Member]

If Columns are not fixed then you can put it after rows like say G61:I120, something like that,

or better you can keep it in another sheet.

or if you wanted to keep in next columns only then place it at far column suppose you know that the max columns can be around 50 then keep the next grip in say BA14:BD60.

[former_member1320038]

Dear Vijayendra,

                     Thanks for reply . I increases the range but when there is less value in table it

shows gap between two report. It does not adjust dynamically .

[Former Member]

Yes Suhas, it is static so it wont adjust automatically, if you want you can write the macro to hide the empty columns.

[Loed]

Hi,

ANALYSIS will really not auto adjust since their location is fixed..So the only way is to put the other query in another worksheet or place the two (2) queries side by side..

Regards,

Loed

[Former Member]

Dear Suhas,

is your problem solved?

[Former Member]

Hi Suhas,

Below steps may help to add multiple queries in one workbook sheet

1. Create queries you would like to join.

2. Open one of the queries in BEx Analyzer and save it as a workbook.

3. Create two additional tabs in the workbook, and give them names (e.g., query2, results)

4. Edit the query2 tab by adding design items: click BEx Analyzer > Design Toolbar > Insert Analysis Grid

5. Click on the Properties dialog box, change Data Provider's name and click create button.

6. Choose the second query and confirm your choice.

7. Create a table, on the result tab, that merge data form both queries. Save the workbook.

Thanks,

Shiva