Capture a "snapshot" of a data provider

[system]

Author: Dwayne Hoffpauir, EDS Corporation

Further discussion on this code sample should take place in this topic.

Author Notes:

The code below uses eFashion as an example of how to capture a snapshot of data before it is refreshed. This technique would be useful if you wanted to do a “change since last refresh” type of reporting within the document itself.

The technique creates and populates a VBA data provider with the contents of a universe-based data provider. The tricky part is to get the data providers to run in the correct order. Data providers are run in the order they are built, which means the VBA data provider needs to be built BEFORE the universe-based data provider from which the copy is taken . Follow these steps:

1. In a blank document, open the VB Editor (Alt-F11) and copy the code below into the ThisDocument module. Notice the Exit Sub line of code. This will enable the VBA data provider to be built BEFORE the universe-based data provider.
2. Create the VBA data provider … Data, New Data provider, Begin, choose Visual Basic procedure from the Others list, and click Finish.
3. Be sure ThisDocument.CapturePreviousData is selected, and click Run… You will receive a “no data to fetch” message, but the data provider will be created.
4. Now create the universe-based data provider. The sample assumes a data provider named “Current” with Year, Month, Store Name, and Sales Revenue from eFashion.
5. Return to the VB Editor, and “comment out” the Exit Sub line, since now the remaining code is valid.

When the document is refreshed, the VBA data provider will be updated with the contents of “Current” before it is refreshed.

Public Sub CapturePreviousData(dpInterface As busobj.DpVBAInterface)

    Dim dpcube As busobj.DpVBACube
    Dim dpCol As busobj.DpVBAColumn
    Dim Vars As busobj.DocumentVariables

    'create the VBA data cube
    Set dpcube = dpInterface.DpVBACubes(1)
    Call dpcube.DpVBAColumns.SetNbColumns(4)

    Set dpCol = dpcube.DpVBAColumns(1)
    dpCol.Name = "Year"

    Set dpCol = dpcube.DpVBAColumns(2)
    dpCol.Name = "Month"
    dpCol.Type = boNumericObject

    Set dpCol = dpcube.DpVBAColumns(3)
    dpCol.Name = "Store name"

    Set dpCol = dpcube.DpVBAColumns(4)
    dpCol.Name = "Sales Revenue"
    dpCol.Type = boNumericObject
    dpCol.Qualification = boMeasure
    dpCol.Aggregation = boAggregateBySumObject

Exit Sub 'use this line before the "Current" data provider is built

    'populate the VBA data cube
    Set Vars = ThisDocument.DocumentVariables
    Call dpcube.DpVBAColumns(1).Add(Vars("Year(Current)").Values(BoAllValues))
    Call dpcube.DpVBAColumns(2).Add(Vars("Month(Current)").Values(BoAllValues))
    Call dpcube.DpVBAColumns(3).Add(Vars("Store Name(Current)").Values(BoAllValues))
    Call dpcube.DpVBAColumns(4).Add(Vars("Sales Revenue(Current)").Values(BoAllValues))

End Sub

---

BOB Downloads (BOB member since 2003-05-05)