Dynamically Generate a Target Flat File Format from Dataflow in SAP BODS Designer

Scenario: To generate a flat file format from inside a data-flow rather than typing each column, datatype and size.

Solution: Being new to SAP BODS designer, I found it really hard to type out each column in a flat file format, along with the datatype and it's corresponding size. It can become a tiresome process. 

The easiest solution is to go to one of your query transforms and on the schema out pane(right pane), right click on the parent of your 'Schema out' as in screenshot below,

SAP BODS - Create File Format Generation Option ( Enlarge )

Now from the list that opened up, select 'Create File Format'. This will open up a flat file format window for you with same column names, datatypes and lengths as in the schema out part of your query. 

You can then save the same, edit the properties and use in your BODS dataflow for data loading.  

Generate a number sequence grouped on a column/key in SAP BODS

Scenario: Create a sequence number grouped or based on another key/column value from the source result set. 

eg: consider the following data-set

Now in the target dataset/table, we require a new column which would sequentially assign a number starting from 1 for each student under a particular class. The result set then would look something like below, 

Now we have this new column (StudentSeqNbr) which sequentially numbers each student under a particular ClassId. The aim is to create such a sequential column using SAP BODS.

Solution: SAP BODS has an inbuilt function which will let you create such a sequence and can be mapped inside a Query transform.

Gen Rownum function in SAP BODS( Click to enlarge )

Using this function and the below syntax, a sequence can be created for the scenario above.

gen_row_num_by_group(ClassId)

Syntax:

gen_row_num_by_group(ColumnName)

UPDATE: The input should be sorted for this function to work properly to create a sequence.