cancel
Showing results for 
Search instead for 
Did you mean: 

Formula in transformation rule to transfer only open items

Former Member
0 Kudos
738

Hi gurus,

I created a transformation to transfer only open items from 0FI_GL_4 datasource to ODS 0FIGL_O02. In transformation rule for datasource field "Item status" I want to specify that only those registers with 'O' value should be transfer to data target.

When I indicate "STATUSPS = 'O'" on formula at field/infobject transformation rule, I obtain "formula element does not match type ....." error message.

Which is the correct syntax for the formule?

Thanks in advance.

Ana

Accepted Solutions (1)

Accepted Solutions (1)

esjewett
Active Contributor
0 Kudos

The correct thing to do here is to use a filter in your DTP. Does that not work for you for some reason?

Former Member
0 Kudos

Hi Ethan, could you please specify how?

Thanks!

Former Member
0 Kudos

Hi Ana,

The easiest way would be to filter your DTP. In the DTP settings extraction tab, you can filter out the records for STATUSPS = 'O'.

In this way you can filter Item Status.

There is no need to write a routine.

Br,

H

esjewett
Active Contributor
0 Kudos

Hi Ana,

Harish has given a bit more detail. Hopefully you can work from there. If you have more questions, please let us know.

Please keep in mind that in BW, ABAP should only be used if other BW functionality (like DTP filters, in this case) does not do the job. Depending on the customer, 50%-80% of ABAP routines I see written in BW are unnecessary, just costing the customer extra money to maintain, and usually working less efficiently than the standard BW functionality.

Cheers,

Ethan

Former Member
0 Kudos

Hi Ethan and Harish,

You're right! So much easy in DTP but no data is transfered. I'm attaching an image. Process ends successfully in DTP monitor but all data packages indicate "No More Data Available" (in green status). I'm sure that there is information to transfer from source system.

Could you please help!?

Thanks!

Regards,

Ana

esjewett
Active Contributor
0 Kudos

Are you restricting to status "0" (the number) or "O" (the letter)? In your forum post you say the letter, but in your filter it looks like a number to me.

Cheers,

Ethan

Former Member
0 Kudos

Hi Ethan, its the letter. The number looks different (it has a point inside).

I made a full load without filters and it transfered data. The problem is that it just transfered data for the same selection as previous loaded data into ODS.

I mean, ODS data target had information for fiscal periods 001.2011 to 006.2011 (before running DTP). When I run DTP without filters it loaded the same information (001.2011 to 006.2011) already loaded in data target.

I want to load open items from prev ones. I was expecting that a full load for fiscal periods 001.2007 to 012.2010 and item status = 'O' would load it, but not.

Any suggestion?

Thanks again.

Ana

esjewett
Active Contributor
0 Kudos

Hi Ana,

You're loading from a PSA, so whatever data is in the PSA will be the data loaded. You should take a look in your PSA (go to the DataSource, right-click, "Manage" - I think) and see if you have any data for the date range you've selected. I'm guessing that older data has already been deleted from the PSA and will need to be re-loaded from the source system.

Cheers,

Ethan

Former Member
0 Kudos

Hi Ethan,

Older data was never load into data target. When I manage datasource PSA table it only has data for the mentioned fiscal periods (001.2011 to 006.2011). I don't want the DTP to transfer data from PSA to ODS but from source system to ODS. Where can I specify it?

I guess that it is related to data source delta initialization filters. Datasource has a delta initialization with these filters. I also run a new delta initialization without transfer data from fiscal periods 001.2007 to 012.2010 and then run DTP for 2008 open items but again no data was transfered.

It seems to be a way to ensure data consistency. What do you think?

I know that it's not the correct way but modifying data source selection fields seems to be the unique way to load historical open items.

What's your opinion?

Thanks!

Ana

esjewett
Active Contributor
0 Kudos

Hi Ana,

You need to use an InfoPackage to load more data from the source system into the DataSource PSA.

It seems like you are struggling with some basic BW concepts here. You might want to consider purchasing a BW reference book (like http://www.sap-press.com/products/A-Practical-Guide-to-SAP-NetWeaver-Business-Warehouse-7.0.html), which could help you become more familiar with all of the basic features and capabilities of BW.

Good luck!

Ethan

Former Member
0 Kudos

I cannot use infopackage because item status is not a selection field in this datasource.

Thanks for all your helpful answers.

esjewett
Active Contributor
0 Kudos

Hi Ana,

Right - you'll have to use an InfoPackage to load everything in the time periods you want to the PSA (regardless of item status). Then use a DTP with filter so that you only load the records with the item status you want into the DSO. Then you can delete the requests from the PSA.

No problem with answering. Always happy to help!

Cheers,

Ethan

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Ana,

   This can be achieved in routine with a single line code.

If you are using BW 3.5 version:

Transformation Start Routine:

DELETE DATA_PACKAGE WHERE STATUSPS NE 'O'.

(or)

DELETE DATA_PACKAGE WHERE STATUSPS <> 'O'.


If you are using BW 7.0 version:

Transformation END Routine:

DELETE RESULT_PACKAGE WHERE STATUSPS NE 'O'.

(or)

DELETE RESULT_PACKAGE WHERE STATUSPS <> 'O'.

This will work. Else you can use a filter on STATUSPS in DTP level.

Thanks,

Umashankar