Steps to pass multiple values to Global Variable in BODS during run time

While working on the BODS tool, the developer often comes across a requirement to pass multiple values to a specific field using the global variable at a run time. But the global variable can’t store multiple values in it.
So, there is a workaround for this to make it work.
Let’s consider below example:

Here we have a table with column DEPT_CODE which has values 3051,3046,3047 and 3055.
Now we want to retrieve one or two DEPT_CODE from the table depending upon the requirement.

Source Data

To achieve this let’s create one job and a global variable to accept the multiple DEPT_CODE.

To pass multiple values in global variable you have to comma as a separator like done in above screenshot. I have mentioned default values already as I want to retrieve DEPT CODE 3051,3046 and 3047.
Now you need to create a workaround in data flow where it will take multiple values as a string from global variable, will split it into multiple rows and load it in one target table.

Step 1.

Take two row generation transform and connect it will one query transform. Don’t apply any joins on two transforms.

Step 2.

Now open any one row generation transform and set row count to maximum multiple values you can pass at a time. Here I have selected MAX 20.

Step 3.Open query transform and create DEPT_CODE as output field and apply word_ext function as mentioned in screenshot below:

In word_ext function you have to pass the three parameters .

1. Input string is the Global Variable
2. Wordnumber is the DI_ROW_ID of the row generation transform whose rowcount value you have set it to maximum value.
3. Last parameter is the comma separator to identify the string.

Step 4.It will create the cartesian product since we have not applied any join. So now we apply one filter condition in where clause of query transform to filter out the null records.

Step 5

Create one target table to store the values and run the job .You will get only those Dept code that you have passed during run time of job.

Once you get the selected records in DEPT_CODE table , you can use this table further in different ways to restrict the data on other table.

Also Read:Work From Home 2020:The best IT companies in India that will allow you to work from home

Here DEPT_CODE table is used in where clause of DEPT_INFO table in SQL transforms to retrieve the data.

So if you require to retrieve data from the DEPT_INFO table based on passing multiple values to the global variable at run time, first you need to create one intermediate table like DEPT_CODE in our case which will store the multiple values you have passed through a variable in multiple rows.

If you cannot use SQL Transform to filter the data using the DEPT_CODE table then there are other ways to achieve it. Like

1. You can apply lookup in query transform on the DEPT_INFO table for example based on DEPT_CODE. If you get values then insert 1 else 0. Then in the next query transform, you filter all 0 records means unmatched records.
2. You can apply to join with the DEPT_INFO table, in that case, you need to have a common join column which you can use as per your requirement.