Many execution of a query with different selections in the same work book

[Former Member]

Hi all,

I have a workbook with one query that show sales of a plant. User can select many plants, but he doesn't like it, too easy. In this way query shows all the plant in the same sheet and he want a sheet for each plant.

The most simply way to do this is making a query for each plant with a filter with the plant as a constant. Then I put all that queries in the workbook. But this is not too smart, right?

Any suggestion?

[Former Member · Accepted solution]

Hi,

What you can do is use your current query, insert in several times in each sheet, and, on each sheet, put a filter directly in the workbook (and not in the query).

The filter will apply on each sheet and you won't have to maintain manually the queries per plant (especially create new queries if you have new plants in the system).

[Former Member · Accepted solution]

Hi Frédéric,

I try this, but when I refresh the work book (all queries) I have to input a plant, only one, and all the queries in all the sheets have the same plant. How can I fix one selection at each query of each sheet of the work book?

thanks

[Former Member · Accepted solution]

I would advise to remove the plant variable from the query.

If the query is used elsewhere, create a specific query for your workbook, without any plant selection.

[Former Member · Accepted solution]

Hi,

if I understand properly, if I do that i'm executing the same query for all de N plant N times, right? In my case is not a good solution because is a great query that would take a lot of time to execute.

thanks,

[Former Member · Accepted solution]

try selecting the option to repeat the variable selection for each query in the workbook and not combine the common variables together.

[Former Member · Accepted solution]

Hi Raj,

Sorry but I don't know where is this option, I'm new in reporting ;-). Can you tell me where it is?

Thanks

[Former Member · Accepted solution]

I'm working with BW 3.5, is this option available in this version?

Thanks,

[Former Member · Accepted solution]

Yes, the solution I talked about is running the query each time you've a plant ... But the choice has to be done also by the user, HE is asking for a sheet per plant, you can do it, but at a price !

The other solution could be to have only 1 query in one hidden sheet, and to spread the results in different sheets, by using visual basic.

The solution preconised in the previous answer is to have only 1 screen for all the variables of all the queries, it's by default in BW 3.5, but it's not really answering your need.

Edited by: Frédéric GAUTHIER on Sep 19, 2008 1:21 PM

[Former Member · Accepted solution]

Thank you very much Frédéric,

Yesterday I started doing the second option, one hidden query and a lot of excel works ;-). I put plants in the columns and is relatively easy to do. The problem will be when they reach 255 columns.....(and now we have 200...)

[Former Member · Accepted solution]

Hi,

If you are using 3.5 then what you can do is use some API and get this working.

1) Create a Query with No Variable on Plant.

2) Create a Query that will return you all the Plants.

3) Embed the 2 Queries into a Hidden Sheet.

4) When the user Runs the report, Refresh the Sheet that gets all the plants.

5) Using BEX API, Refresh the Plant Query.

This will involve writing VBA Code but can be done. I have an example if you need.

Hope this gets you started.

[Former Member · Accepted solution]

Hi,

Thanks, an example would be very helpful....

[Former Member · Accepted solution]

Hi,

I will try and explain here, if you get stuck and do get back.

1) On Sheet1 Rename it to BEX01 and Add the Query that will return all Plants. Hide this Sheet

2) On Sheet2 Rename it to BEX02 and Add the Main Query. Hide this Sheet.

3) On Sheet3 Rename it to Main and Add a Command Button. Goto Properties and Change the Name to cmdRefresh and then do View Code and add following code.

 
Private Sub cmdRefresh_Click()
           Call RefreshReports
End Sub

4) Add a Module to your VBA Project

5) Open Module1 and Add following code.

 
Option Explicit

Public Sub RefreshReports()
    Dim wksBEX01 As Worksheet, wksBEX02 As Worksheet, wks As Worksheet
    Dim lRows As Long, lRow As Long
    Dim sPlant As String, sName As String
    Dim fRng As Range
    
    sName = "Main"
    
    With ThisWorkbook
        Set wksBEX01 = .Sheets("BEX01")
        Set wksBEX02 = .Sheets("BEX02")
    End With

    wksBEX01.Activate
    wksBEX01.Range("A1").Select
    
    'This will refresh both the Queries All Plants and the Main one.
    If Run("SAPBEX.XLA!SAPBEXrefresh", True) <> 0 Then
        MsgBox "Refresh of All Queries Failed."
        Exit Sub
    End If
    
    'Lets say your All Plants Query, Lists all the Plants Starting in Cell A15
    'Then Do the following.
    
    lRows = wksBEX01.Cells(Rows.Count, 1).End(xlUp).Row
        
    For lRow = 15 To lRows
        sPlant = Trim$(wksBEX01.Cells(lRow, 1).Value)
        
        'Now Use this Plant to Set the Filter Value for the Main Query.
        'I am assuming that your Plant Charateristic will show up in A14 on the Worksheet.
        With wksBEX02
            .Activate
            'Adjust this according to where you insert the Query and which cell has correct info.
            .Range("A14").Select
            Set fRng = Selection
        End With
        
        'Call the API to Do a Select Filter Value.
        If Run("SAPBEX.XLA!SAPBEXSetFilterValue", sPlant, "", fRng) <> 0 Then
            MsgBox "Unble to Set Filter for Plant : " & sPlant
            Exit Sub
        Else
            'Here you can Now Copy the the Sheet to new Sheet. If filter value worked.
             Set wks = ThisWorkbook.Sheets.Add(After:=sName)
             wksBEX02.Cells.Select
             wksBEX02.Copy
             wks.Range("A1").Paste
             wks.Name = sPlant
             sName = sPlant
        End If
    Next

     'Save the workbook as new name else you will have to first delete all the old sheets.     
     Thisworkbook.SaveAs "Some New Name"

End Sub

Here I have just created a new Sheet for Each Plant. You can work on the EXCEL Part to take and do what you want with new Sheets.

Hope this gets you started.

Just a Suggestion. Don't hide the BEX01 and BEX02 sheets untill the code works correctly.

Datta

[Former Member · Accepted solution]

hi, this seems very useful.....;-)

thanks