BusinessObjects Board

A report containing data of previous and current refresh

building reports desktopintelligence
#1

Hi,

I need to create a report which will have two tables, one containing the up to date data (no problem) and the other to contain the data from the last time the report was run. Is there a way to do this on Reporter?

Thanks in advance,
Marcy.

elise (BOB member since 2004-02-27)

Freeze previous results
Static Charts
Capture a "snapshot" of a data provider
#2

Not in a normal way, no. By design, when you refresh a document all of the data providers in the document are refreshed.

What you could consider (just some ideas off of the top of my head)

  1. Mark the “previous” data provider with the Refreshable checkbox as off. This is in the “cube” button (View Data) on the main toolbar.
  2. Export the “current” data to a text file, then use that text file as the source for the “previous” data provider. In this way, it (the previous data provider) will always show the previous data even if you refresh. The entire process could be automated with VBA.

Dave Rathbun :us: (BOB member since 2002-06-06)

#3

Sounds like a VBA solution is needed using the Event BeforeRefresh to save the existing data into a new data provider…

Andreas :de: (BOB member since 2002-06-20)

#4

I actually have a VBA routine (somewhere) that will snapshot a universe-based data provider into a VBA data provider. marcy, I can look it up for you if this is an option.

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

#5

Hi,

Thanks for all the replies. I have been using just one data provider for this but can create a separate one.
I haven’t used the VBA with Business Objects before but would certainly be interested in it as this requirement is across a couple of reports. The requirement is also to do comparisons between the different data sets i.e. % difference between this week and last week.
Would this still be achievable using VBA.

The reporting here is on a transactional database, I suppose the solution would be easier were the reports from a datawarehouse/datamart.

Thanks,
Marcy.

elise (BOB member since 2004-02-27)

#6

The sample code has been posted here.

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

#7

I really like the VBA code (thx Dwayne). Did everything right (so I think) in the instructions.

My VBA data provider goes ‘funky’. The Year/Month/Store Name columns are empty (#EMPTY) and the Sales Revenue column is just one long list of the data that should be in the 1st 3 columns (eg the year, month AND the sales revenue).

Anyone else have this happen or any suggestions as to why it didn’t work correctly for me?

zkreg :us: (BOB member since 2002-08-15)

#8

That’s what I get for trying to do that from memory {sigh}. I’ve fixed it now (hopefully) … give it another try.

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

#9

Dwayne,

Worked like a charm. This is a great one to play with and customize for my own uses. Thanks alot. Quick reponse. I’m impressed you wrote it from memory.

zkreg :us: (BOB member since 2002-08-15)

#10

Glad you find it helpful. VBA data providers can serve as a sort of “ETL-lite” at times.

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

#11 

1. Mark the "previous" data provider with the Refreshable checkbox as off. This is in the "cube" button (View Data) on the main toolbar.
2. Export the "current" data to a text file, then use that text file as the source for the "previous" data provider. In this way, it (the previous data provider) will always show the previous data even if you refresh. The entire process could be automated with VBA.

I have same requirement with scheduled report.
so, what if i have to schedule this report? is the previous data provider concept will work ?

Vills :india: (BOB member since 2007-10-24)