Using VB code in BEx Analyzer

[former_member194898]

VB coding in BEx Analyzer.

In some situations MS Visual Basic coding may be used to transform data displayed in BEx Analyzer.
Let’s suppose we have to create a report that presents service costs with respect to the country of vendor and the code of service.  

The problem is that two queries should be used in this case. First query where we can see country associated with vendor (0vendor__0country) and second query using 0pcompany__0counry (as we have no data in 0vendor in some records ). 

Now we have two tables that we have to combine into one to see the data in a proper way.
First: based on 0vendor_0country:

SERVICE (code and text); 0VENDOR__COUNTRY (code); AMOUNT (rounded); AMOUNT
232 service group 1  DE 100 100,01 
232 service group 1  GB 200 200,01
236 service group 1  DE 150 150,02

Second (based on 0pcompany_0country)

SERVICE (code and text); 0PCOMPANY__COUNTRY (code); AMOUNT (rounded); AMOUNT
232 service group 1  DE 300 300,01 
232 service group 1  GB 100 100,01
236 service group 1  DE 50 50,02

We need combine data from both tables into:

SERVICE (code and text); COUNTRY (code); AMOUNT (rounded); AMOUNT
232 service group 1  DE 400 400,02 
232 service group 1  GB 300 300,02
236 service group 1  DE 200 200,02

Here is the moment when we may use VB coding. 

What should be done in this case is:
1. data from the first table should be copied to another location.
2. data from the second table should be copied to the same location just below the last row from first table
(here we have one table containing the data from both tables)
3. data in the new table should be sorted by service and country
4. data should be aggregated by service and country

Copying the data from first table may be realized by code:

   Sheets("0VENDOR SHEET").Select   
    r = 2
    t = Cells(r, 1).Value
    While t <> ""
        r = r + 1
        t = Cells(r, 1).Value
    Wend
    RwMax = r
    Range(Cells(3, 1), Cells(RwMax - 2, 7)).Select
    Selection.Copy
    Sheets("RESULT").Select
    Cells(2, 1).Select
    ActiveSheet.Paste
 
Copying the data from the second table is slightly more difficult because the first row of that table have to be just under the last row of the first table.

   Sheets("0PCOMPANY SHEET").Select   
    r = 2
    t = Cells(r, 1).Value
    While t <> ""
        r = r + 1
        t = Cells(r, 1).Value
    Wend
    RwMax2 = r
    Range(Cells(3, 1), Cells(RwMax2 - 2, 7)).Select
    Selection.Copy
   
    Sheets("RESULT").Select
    last_row = Selection.SpecialCells(xlCellTypeLastCell).Row
    Cells(last_row + 1, 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

To sort the data we use code like this 

    ActiveWorkbook.Worksheets("OdbRazem").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("OdbRazem").Sort.SortFields.Add Key:=Range( _
        "A2:A300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("OdbRazem").Sort.SortFields.Add Key:=Range( _
        "C2:C300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("OdbRazem").Sort
        .SetRange Range("A1:F100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Aggregation of records is not very complicated 

    serviceCode = ""
    serviceText = ""
    vCountry = ""
    roundedValue = 0
    myValue = 0
   
    sumRow = last_row + 7
    cRow = 2
    serviceCode = Cells(cRow, 1).Value
    serviceText = Cells(cRow, 2).Value
    vCountry = Cells(cRow, 3).Value
    roundedValue = Cells(cRow, 4).Value
    myValue = Cells(cRow, 5).Value
   
    For row = 3 To last_row + 1
        If Cells(cRow, 1).Value <> serviceCode Or Cells(cRow, 3).Value <> vCountry Then
            sumRow = sumRow + 1
           
            Cells(sumRow, 1).Value = serviceCode
            Cells(sumRow, 2).Value = serviceText
            Cells(sumRow, 3).Value = vCountry
            Cells(sumRow, 4).Value = roundedValue
            Cells(sumRow, 5).Value = value
           
            serviceCode = Cells(cRow, 1).Value
            serviceText = Cells(cRow, 2).Value
            vCountry = Cells(cRow, 3).Value
            roundedValue = Cells(cRow, 4).Value
            myValue = Cells(cRow, 5).Value
          
        Else
            roundedValue = roundedValue + Cells(cRow, 4).Value
            myValue = myValue + Cells(cRow, 5).Value
        End If
    Next

Now we have aggregated data that may be used in a beautiful report.

The program of course may be associated with a button that we create in one of the sheets.

This is only an example of a method. The program may be much more elaborated. Using VB we can do everything in what we need.

Hope it encouraged you to use VB coding in analyzer.

Regards, Leszek