BEx query - change results via program before rendering?

[Former Member]

I have a BEx query based on a Left Outer Join InfoSet. I need to evaluate the results prior to handing them over to BEx to render. I am working in 7.0

My InfoSet is based on a cube and a DSO.

Cube is sales, the DSO is Bill of Material. The DSO lists parent materials and the component materials the make up the parent material. The Component material can also be sold separately and be listed in the Sales cube as a parent material.

My results look like this:

Material Sales Qty Component Material Component Qty Total Qty

1000 10 50A 5 50

1000 10 70C 4 40

50A 500 N/A N/A 500

If you notice, 50A is a parent and component material. When it is a parent material, it does not have a component material in the DSO. That means it returns nothing.

I would like to evaluate the parent material, if it doesn't have a component material in the DSO, then dynamically place the parent material into the component material field. Then add a component Qty = 1.

This is what I'd like to see:

Material Sales Qty Component Material Component Qty Total Qty

1000 10 50A 5 50

1000 10 70C 4 40

50A 500 50A 1 500

Does anyone know how I can do this with code?

[Former Member · Accepted solution]

Michael,

Was thinking why to do this with infoset - why not look upon DSO and upload data in cube itself.

This way you can also write routine to populate component material if its parent material.

Any reasons you want to do this with infoset?

--S@A

[Former Member · Accepted solution]

This was considered and then vetoed. My DSO is a specialty package created by utilizing different materials.

For example, my client is in the health and beauty industry. They sell hair brushes, clippers, etc. Their products are sold separately. But they may also take those products and put them in a beauty kit (kit consists of a brush, different types of clippers, mirror, etc.)

My DSO only contains the specialty kits and what products are used in the bundling. Yes, at the time of the invoice, I could take the component products and load them with the invoice into the cube. The problem is that they sometimes market the product before IT is informed the product was created. That means the upload to the cube will not have the component products when the DSO was never updated. It may be a month or more before they are told to update it.

They do not want to perform selective deletes and reload the old records just because they weren't told to update the DSO by marketing. Therefore, uploading the data to the cube from the DSO is not an option. I have to work with an InfoSet to combine the cube and DSO.

[Former Member]

Decided to go in another direction.