BusinessObjects Board

Purge data provider from VBA

sdk development
#1

Is there a way to purge a data provider through VBA? I want to have the code check the date when the document is open and based on a condition (such as beginning of the week), purge the data from the report and have the report refresh using the data for the new week.

Thanks in advance.

Joe Wiorek

jwiorek (BOB member since 2002-08-15)

Purging Utility
Automate - Purging of Dataprovider
BCA and Condition
to purge DP using VBA
Purging Deski data
Macro
VBA to purge DP
VB to purge multiple DP's
how to make data provider empty
BO Report Export
purging report before open or saving
Script to Purge Data
VBA to delete DeskI report data
DocumentVariables updated asynchronously after DP refresh
#2

I could possibly be missing something in your requirement, but there would be no need to purge data first. Refreshing the report completely replaces any previously existing data.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#3

Dwayne,

The requirement from our user is to have a report to view inventory quantities per month, but to only refresh the current month while keeping the data for previous months in the report. If we were to refresh for all months each time, by the end of the year the report would take an inordinate amount of time to run. So I have created a dataprovider for each month and a script to run only a given DP at a time. Leaving the other month’s data static on the report. What I want to do is at the beginning of a new year, purge all data providers and begin with a fresh report.

Thanks and sorry for the long answer… :smiley:

Joe

jwiorek (BOB member since 2002-08-15)

#4

That helps a lot actually. I can think of three solutions:
[list]1. Just start with an empty report from the corporate repository … no muss, no fuss.
2. I assume you are setting the IsRefreshable property to control which are refreshed, right? What would happen if all are refreshed at the beginning of the year? Wouldn’t Feb - Dec run quickly and retrieve no data?
3. I can’t find a purge method either. Here is a total kludge, but given the proper user privileges, this code will effectively purge data providers. It simulates doing a “save and close” on each data provider and choosing yes to the purge question.[/list]

Sub PurgeDataProviders()
    Dim DataProv as busobj.DataProvider
    For Each DataProv in ThisDocument.DataProviders
        If DataProv.GetType = "DPQTC" Then
        SendKeys "%s%y"
        DataProv.Edit
    Next DataProv
End Sub

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Me too...
How Do I delete datacube inside the .rep using text editor
#5

Dwayne,

I finally got a chance to implement your code and it worked like a charm!
Thanks for the help.

Joe

jwiorek (BOB member since 2002-08-15)

#6

You’re quite welcome :+1:

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#7

Hi,
Does this code work in 6.1a and on the BCA? Has anybody tried it?

Akshay

akshay :india: (BOB member since 2004-02-05)

#8

It is posible? I think it’s work :?:


Sub PurgeDataProviders()
Application.Interactive = False
    Dim DataProv As busobj.DataProvider
    Dim MaxLines As Long
    For Each DataProv In ActiveDocument.DataProviders
        If DataProv.GetType = "DPQTC" Then
            MaxLines = DataProv.MaxNbLines
            DataProv.MaxNbLines = 0
            DataProv.MaxDuration = 0
            DataProv.Refresh
            DataProv.MaxNbLines = MaxLines
            DataProv.MaxDuration = MaxLines
        End If
    Next DataProv
Application.Interactive = True
End Sub

Thanks for your time to study this

Juan_Lu :es: (BOB member since 2005-05-11)

#9

Looks like a neat solution. I don’t have time to test it, but I wonder if it actually runs the query anyway, and just doesn’t return any records. That would be effective, but a lot of unnecessary queries.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#10

Sorry, but we have just tested this solution and it doesn’t work.

The MaxNbLines and MaxDuration are only “stats” for DP and can’t be used as “limit” for query and won’t permit you to achieve the purge operation…

Keep searching :slight_smile:

bernard timbal :fr: (BOB member since 2003-05-26)

#11

Hi,

I tested your solution Dwayne.

The “Do you want to purge ?” box appeared right, and it did choose “yes”, but looking in the cube the data was still here…

So i used your sendkeys idea to do it by the cube directly :


' The letters called in the SendKeys are for the french version
' Find the right letters for your version

Sub Purge()

    SendKeys "%d"  'Data menu
    SendKeys "l"  'Open the cube
    SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}"  'Navigate to the dataproviders list
    
    For i = 0 To ActiveDocument.DataProviders.Count
        SendKeys "%p%o"  'Purge + Yes
        SendKeys "{DOWN}"  'Next Dataprovider :o)
    Next
    
End Sub

Thats too damn ugly, but it works.

Fabien Duprey (BOB member since 2006-04-05)

#12

That is often the case! Innovative solution though … congrats.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#13

Note the 5x{tab} won’t work if the first dataprovider is already empty - the Purge button will be greyed out so doesn’t get tabbed to (in 6.5.1, anyway). You can use 7x+{tab} (i.e. Shift+Tab) instead though, if you’re expecting any empty dataproviders.

SendKeys "+{TAB}+{TAB}+{TAB}+{TAB}+{TAB}+{TAB}+{TAB}"

Good solution, though :slight_smile:

swl543 :new_zealand: (BOB member since 2005-08-16)

#14

Hi all,

My client requirement is to purge all reports in the repository.

how can i achieve this…

any idea will be appreciated.

Thanks,
Reddy

raveesh (BOB member since 2007-11-24)