Breaking row by value and column

hatra
edited February 11, 2022 in Analytics #1

Hi All,
I have two requirement that I am trying to figer out what would be the best way to do in BIRT
1. I have two column Order and Level
this two column has two rows but I want to break the rows based on values.
I have attached two images, image1 is what I have and image two in what I want to have my output be like.
2. I have two column Order and Level
and I want to break the Level value into separate columns,
Image 1 is what data looks like and image3 what I want to out put to look like.
I could have done that by creating computed columns but it wont be dynamic.

Any Ideas?
Appreciate any help, sample and advice
thanks

Comments

  • Additionally
    image 4 for the third requirement ,
    thanks for help

  • jfranken
    edited May 22, 2019 #3

    Requirements #2 & #3 should be simple to achieve. For #2, you want to create the columns Order, Level, Level, and Level as shown in image3. The Order column already exists. For the three Level columns, create three computed columns. They can't all have the same name, so name them something like Level1, Level2, and Level3. Write JavaScript expressions to parse the desired value from the original Level column data. There are many ways to parse comma-separated values using JavaScript, for example: split().

    The same method can be used for #3. You will need to create computed columns for Level, Size, and Room.

    For #1, the only option I can think of is to use a Scripted Data Source and Data Set. Get the data in the Data Set Open event and write JavaScript to convert the comma-separated values in each row into individual rows of data.

    Warning No formatter is installed for the format ipb
  • Thanks for your respond Jeff,
    For#1 is there sample you have that can share with me please?
    For#2, For#3 in my example I only have only 3 values to split but is there a way to have it dynamically regardless how many values to split or what the data is called?
    if I create computed columns I have to base the javascript on what the value of first column are and if later on data changes or updated the computed columns would no longer work.
    thanks

  • Attached is an example of a Scripted Data Set. It is a very basic example. There are many online resources that show how to parse strings and place the parsed values into arrays similar to the arrays in the example. The fetch event will repeatedly trigger inserting data into the table on the report until there is no more data in the arrays.

    Regarding your requirement to dynamically change the columns based on unspecified future changes to the data, I recommend that you do not go down that path. The suggestions I made in my previous post were workarounds to accommodate very poorly structured data. It will be difficult to normalize the data and store it properly, but that approach will be much easier than trying to create a report where all of the elements are dynamically generated and all of the elements can be dynamically morphed to accommodate data that is essentially restructured every time a user enters a different value.

    Warning No formatter is installed for the format ipb
  • Thanks Jeff,
    thanks for your example, it helped me to understand how scripted data source work, but I am failing to find anything online to create scripted data source with real data ( for example if the data is stored in where the report will run called "d1234", what is the syntax in open, and If my columns are "Order", "Level", what would be the fetch syntax code to call the columns, I may search wrong or missing something,
    thanks for your help

  • If the data is in a file, add code in beforeOpen of the Data Set to create a file reader. Something like:

    importPackage(Packages.java.io); 
    br = java.io.BufferedReader(java.io.FileReader(<insert_path_to_file>));
    

    In the fetch event, get the next line:

    line = br.readLine();

    Do whatever string manipulation is needed to get the values and insert them into the table as shown in the example I posted. When the line is null, return false to stop fetching.

    If the data is in a database, look up the code to query your database on the internet. For example:

    https://javatpoint.com/example-to-connect-to-the-oracle-database

    I have not tested this code. It is just an example of the type of code used to query a database.

    Warning No formatter is installed for the format ipb
  • Hey Jeff,
    thanks for the reply after experimenting above approach it turns out it wont work for the requirement we have so I have been looking at the variable in BIRT an done some research and it seems like you can do plenty by writing some code,
    so can variable not be apply to create a split to new row based on value, ie from image1 to image2? but regardless the number of value to split? this way I can use the variable any where else that requires,
    I have been looking online to see if there is a similar java code for it but it seems more complicated then it sounds,
    any advice to go through this path?
    thanks

  • I don't understand the question completely. In general, it sounds like you have JavaScript code that splits the data and you want to do the same thing in Java. There is one trick I know that might help. If you are using Java to read the data like the code I posted above, you can convert the string variable to a JavaScript string by adding the following:

    line = br.readLine();
    line = "" + line;

    Then you should be able to do:

    line.split()

    Warning No formatter is installed for the format ipb
  • Hi,
    Sorry for the poor explanation,
    What I meant was that there is limitation on data source and I cant use scripted Data source,
    so I was looking for another way/solution of splitting value to multiple rows like image 2.
    Regards

  • jfranken
    edited May 30, 2019 #11

    Thank you for the clarification. I attached an example that might work for you. It avoids using a Scripted Data Set. The Data Set in the example applies formatting to turn one row of data into multiple rows.

    There are 4 computed columns in the Data Set. Ignore the first 3 columns (rowcnt, Order, and Level). I created those columns to generate the data from your screenshot (Image1.png). I could have created a file with the data, but this was a little easier. You will be getting the Order and Level data from your data source.

    The last column, "Order Level", contains the code to convert the data so that it displays as separate rows. The code gets the comma-separated values (CSV) from the Level column and puts them in an array. The rows are created by looping through the array and adding a new line for each of the CSV values.

    The top table on the report shows the original data. The bottom table shows the data turned into separate rows. Note that the second table has one column and two rows of data formatted to look like two columns and six rows. Filtering, sorting, and aggregating will not work as normal. It is a limitation related to the manipulation of data that you require.

    Warning No formatter is installed for the format ipb
  • Hey Jeff,
    This is a smart solution,
    just one other thing, not sure how you manage to get them all in one filed , please see my result file attached, I get a line after third row ???

  • There are three elements that could be adding the line. It is easiest to select them using the Outline tab. Check the border property of the Row, Cell, and Data element as shown in this screenshot.

    Warning No formatter is installed for the format ipb
  • Thank you