Skip to Content
0

Adding data to the same report periodically

Jan 26, 2017 at 07:58 PM

41

avatar image

Hi:

I'm working on a report that needs to be updated monthly, only for the current month (new data) but the old data needs to be static.

The current process in place is to make a simple one month report, then export it as .csv and then the data is copied and pasted into an excel spreadsheet. The data on the spreadsheet is the data from last month and then we paste the data for this month. Next month we use the same spreadsheet. We feed a bar chart with the resulting data.

I tried doing the entire process in Business Objects, but one field is giving me issues."as of date"

We use the field 'as of date' to establish a period of time for the data, like a snapshot of the database.

For example if I receive a payment today and the claims are as of yesterday, then that payment should not be shown.

In the same way if I had a payment last month and is voided this month, that payment will not show next month if I use 'as of date' = next month date. If I use 'as of date' = last month date then the payment should be shown.

I tried using combined queries where I select the fields that I need and the desired 'as of date' value for each month (as of 01/31, as of 02/29, etc), to test the idea I started with January and February. When I try to run the report the prompt ask me to input the 'as of date', then I don't receive any data regardless of the 'as of date' that I select. Probably I'm using the wrong approach but if I was writing pure SQL or SQL\PL this is the approach that I would take.

select a,b,c from tableName where asofDate = January

union

select a,b,c from tableName where asofDate = Feabruary

(I'm more than happy to try a different approach)

I wonder why If I have the 'as of date' field as a parameter in each combined query, I don't understand why the prompt shows again?

My report has to look something like this:

number of payments amount of payment(s)

as of January 5 10.55

as of February 3 3.1416

as of March 7 1010.11

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Jyothirmayee A Feb 15, 2017 at 09:14 PM
0

Hi,

1. Select Query properties to merge the prompts within the Query results editor

2. As of Date is prompt and if the user selects "January", report will compare if it is equal or not to the date. Correct?.

Can you please post a sample data?. little confused though.

Thanks,

Jothi

Share
10 |10000 characters needed characters left characters exceeded