Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Questions about conditions depending values etc..
icon5.gif  Questions about conditions depending values etc.. [message #916805] Wed, 19 September 2012 13:42 Go to next message
Blaine Mising name is currently offline Blaine Mising name
Messages: 25
Registered: December 2010
Junior Member
I want to do a report that shows all the changes of a procut. The product will be asked in a parameter window.

I've a database in Oracle 11. I will simplify the tables.

The table "PRODUCT" has this columns: Product_id (key column), description, price, quantity

This table contains all the products

Example of data:

---------- ------------- -------- ----------
PRODUCT_ID DESCRIPTION PRICE quantity
---------- ------------- -------- ----------
1 product 1 10 1
2 product 2 500 10
---------- ------------- -------------------

All the changes made in this table are stored in a table called H_PRODCUT
H_PRODUCT table has this columns: Operation, date, product_id, event_id, field, value.

Operation = I (Insertion), U (is an Update), D ( the product is Deleted)
Date = Date and time of the operation.
procuct_id = the id of the product.
field = contains the field changed in an operation. If field is not
updated, it is not registered.
value = New value of the field.
event_id = if more than one field is modified (description, price, quantity) there will be more than one register with the same product_id and event_id, because they are updated in the same operation, if not each operation has his own event_id.

Data example:
---------- ---------------- -------------- ---------- ----------- -----------
Operation date product_id event_id field value
---------- ---------------- -------------- ---------- ------------ ----------
I 2012-03-10 01:00 1 1 description product1
I 2012-03-10 01:00 1 1 price 10
I 2012-03-10 01:00 1 1 quantity 0

M 2012-03-10 01:05 1 2 description produ1

M 2012-03-10 12:05 1 3 description prod1
M 2012-03-10 12:05 1 3 price 15
M 2012-03-10 12:05 1 3 quantity 1

D 2012-03-10 01:05 1 4 product_id 1


Here is an output example of the report that I want to do:

---------- ---------------- -------------- ------------- --------- ----------
Operation date product_id description price Quantity
---------- ---------------- -------------- ------------- --------- ----------
I 2012-03-10 01:00 1 product1 10 0
M 2012-03-10 01:05 1 produ1 10 0
M 2012-03-10 12:05 1 prod1 15 1
D 2012-03-10 13:25 1 prod1 15 1

When a product is new, H_PRODUCT will have a register for each field. Then I've all the information.

When a product is updated, H_PRODUCT will have only an operation field for the fields modified. Not all fields and values are stored.

When a product is deleted H_PRODUCT will have only an operation register with one field.

The problem is when a product is updated because only the new fields/values are stored.

I know that it's possible obtain the info with SQL but, I think that this is not very clean. it's possible do it doing something like this?

For example:

1. Do a DataSet, getting the insertion data and store the values of all the current fields in global variables
2. Do a DataShet getting the updated data and check with birt if there is a register with the field, if not put in the report the current value of the global variable for the field. If the field is in datashet store the new value in the global variable and put in the report the new value.
3. Do a DataShet getting the deleted data of the product. Get the fields values as in second step.

It's possible doing this with conditions? How Can I do them?



[Updated on: Wed, 19 September 2012 14:31]

Report message to a moderator

Re: Questions about conditions depending values etc.. [message #916972 is a reply to message #916805] Wed, 19 September 2012 17:43 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

You could do this with some script, but I believe it will be simpler in SQL. If you wish to pursue the script option, can you put the data for the two tables in a csv file and we can help you with an example.

Jason
Re: Questions about conditions depending values etc.. [message #917580 is a reply to message #916972] Thu, 20 September 2012 08:07 Go to previous message
Blaine Mising name is currently offline Blaine Mising name
Messages: 25
Registered: December 2010
Junior Member
Doing it with some script is a very interesting option, but I don't know how can I do it.
I attached the csv file. If you need something else, please ask me.

Thank you very much for your help!
  • Attachment: products.csv
    (Size: 0.53KB, Downloaded 46 times)
Previous Topic:Consolidating table rows on some conditions
Next Topic:Exporting to PDF with 'https' images
Goto Forum:
  


Current Time: Fri Oct 31 13:08:28 GMT 2014

Powered by FUDForum. Page generated in 0.02874 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software