Row colour based on previous result

Hi All,


is there way to compare a result of same filed and change the colour of repeated result ?


 


ie


 


column name


   1       ( font color black)


   1       ( font color red)


    1      (font color red)


    1      (font color red)


   2      


    3     ( font color black)


    3     ( font color red)


 


thanks


Comments

  • What is your data source?  For example, Oracle 11g2 or SQL Server 2016?  I ask because they support LAG and LEAD functions.


     


    Take a look at this SQL Fiddle based on Oracle 11g2 @ http://sqlfiddle.com/#!4/c2e2c/1



    SELECT THE_ID,
    THE_VALUE,
    LAG(THE_ID, 1, 0) OVER (ORDER BY THE_ID) AS THE_ID_PREV,
    LEAD(THE_ID, 1, 0) OVER (ORDER BY THE_ID) AS THE_ID_NEXT
    FROM RAW_DATA


    THE_ID THE_VALUE THE_ID_PREV THE_ID_NEXT
    1 Value 1 0 1
    1 Value 11 1 1
    1 Value 111 1 1
    1 Value 1111 1 1
    1 Value 11111 1 2
    2 Value 2 1 3
    3 Value 3 2 3
    3 Value 33 3 0

    Notice that there is a column called ID_PREV for the previous value using LAG, and ID_NEXT for the next value using LEAD.


     


    With these values, you can use them to change the Highlight rules of your report item to change color accordingly.


     


     


     


    Now, if your data source does not support these types of functions.  You can do this a Computed Column of your data set.  You would initialize a variable in the beforeOpen event to keep the previous value.



    previousValue = null;

    Then in the Computed Column, you would have an expression like:



    savedPreviousValue = previousValue;
    previousValue = row["YOUR COLUMN"];
    savedPreviousValue;

    Clement Wong
    Principal Analytics Architect

    OpenText Corporation
    http://www.actuate.com | http://www.opentext.com

  • HI thanks for the respond, I am using MSS 2014


    Could this be done in BIRT designer rather? highlights


    if myclumn =myclomn to  red

  • Microsoft SQL Server 2014 has LAG and LEAD.


     


    You won't be able to create a Highlight expression without the extra field whether it's from the database, as a computed column, or save in a variable.


    Clement Wong
    Principal Analytics Architect

    OpenText Corporation
    http://www.actuate.com | http://www.opentext.com

  • understood thanks Clement


  • hi,


    I have applied the computed column logic


    there is an issue where the new computed column return blank on first row  therefor it never matches the previous column ie


    column 1       computed column


    a1                  


    a2                 a1


    a2                 a2


    a2                a2


    a4                a2

  • If you are only looking at the previous row value and not all previous rows before the current one.


    Then I was able to accomplish this using a computed column in the data set.


    See attached example.


     


    Code used in my Data Set's Computed Column:



    var currentRow = row["DATAROW"]; //Get current data row
    var previousRow = reportContext.getPersistentGlobalVariable("previousRow"); //Get global variable value

    if(currentRow==null){reportContext.setPersistentGlobalVariable("previousRow",null);} //Check for null and set null
    else{reportContext.setPersistentGlobalVariable("previousRow",currentRow.toString());} //Or assign global variable to current row value

    if(currentRow == previousRow){ //Compare currentRow with previousRow value
    "Row Color: Red"; //If currentRow is the same as previousRow value
    }else{
    "Row Color: Black"; //If currentRow is not the same as previousRow value
    }

    Matthew L.
    Actuate is now OpenText
    http://www.actuate.com

  • That's expected.  The first row should be blank (or null) because we initialized the variable previousValue to null, and it's the first row where there is no previous value.


     


    If you use a database with LAG, the previous value for the first row is "0" as seen in the SQL Fiddle ; http://sqlfiddle.com/#!4/32258/1


     


    You can initialize it to 0 like what a database would return.


     


    What value are you expecting for the first row that does not have a previous value?


    Clement Wong
    Principal Analytics Architect

    OpenText Corporation
    http://www.actuate.com | http://www.opentext.com

  • @ Matthew,


    your example is exactly what I needed, I have created a new computed column and replaced my row["myrow"] with row["DATAROW"] in the script but it doesn't seem it is working , not able to attach my rpt design here to show what I have done ( is there a trick to do that).

  • @ Clement,


    thanks for reply we always want current row = previous row if that make any sense,


    I would have rather to apply these into database as you mention which I think it is better but I don't have access to database in that regard and it is a long process to get anyone to change a basic thing in database

  • @Matthew sorry to mention


    it is not whole row we want to change color, it is only on first column of report


  • Lets go over a few things,


    First, when you created the computed column, did you change "DATAROW" to the name of your own data row name.


    Per this code, change "CHANGE_THIS_TO_THE_NAME_OF_YOUR_DATA_ROW" to the name of your data row.  Such as "myrow" from your previous message:



    var currentRow = row["CHANGE_THIS_TO_THE_NAME_OF_YOUR_DATA_ROW"]; //Get current data row
    var previousRow = reportContext.getPersistentGlobalVariable("previousRow"); //Get global variable value

    if(currentRow==null){reportContext.setPersistentGlobalVariable("previousRow",null);} //Check for null and set null
    else{reportContext.setPersistentGlobalVariable("previousRow",currentRow.toString());} //Or assign global variable to current row value

    if(currentRow == previousRow){ //Compare currentRow with previousRow value
    "Row Color: Red"; //If currentRow is the same as previousRow value
    }else{
    "Row Color: Black"; //If currentRow is not the same as previousRow value
    }

    As a result, you should see the column compute correctly via the data preview (see attached image).


    If you are not seeing the correct results in the data preview, could you please let me know what data type your "myrow" is?  Or provide a sample screenshot of a few data rows that we are checking against to build an example against.


     


    If the computed column is calculating correctly, please make sure that your highlight rule has been applied correctly to the table (see attached image).


     


    Matthew L.
    Actuate is now OpenText
    http://www.actuate.com

  • Hello hatraatrin,


     


    I just saw that you made post #11 while I was writing post #12.


    Per the highlight rule suggestion, instead of selecting the table row, select the data cell element you wish to have the color change and apply the same highlight rules.


    This should generate the results you are looking for.


    Matthew L.
    Actuate is now OpenText
    http://www.actuate.com

  • thanks for the respond Matthew,


    I have changed the DATAROW to my datarow ( po number )


    computed column is created successfully and data preview seem to be working fine


    highlight has been applied as per your example


     


    but in the report out put it picks up random repeated rows to silver and not all and main column not working,


    just to remind you again, it is not whole row we want to change it is only one column,


    is there a way I can send you the out put and rpt to see maybe it make more sense. I am not able to attach anything here


    thanks


  • Sounds like you have a report theme, style, or another highlight rule that is conflicting with the results.


     


    As for uploading the design file, can you open a ticket with support?


    Matthew L.
    Actuate is now OpenText
    http://www.actuate.com

  • I have checked the report and there is no style or highlight elsewhere,


    I have a suspicion that  second column may causing this issue as one part of the bug  ie


    column 1            column2      


    a1      black            si          not working


    a1     black             bi          not working


    a1     black              bi        not working


    a2     black             ti       


    a3    black              yi


    a4    black             mi       working


    a4    red                ni         working


    a4    red                ri         working


     


    do you mean open a ticket in here or opentext support?


    I don't believe this version of design is open text product  


     


    thanks


  • Sorry, I meant open a support ticket with OpenText support to investigate.


    However, what version of BIRT are you using?  I will test against that version to verify on my side.

    Also if you think it's a bug, please test against a newer (prefer latest) version of BIRT to verify the issue doesn't still occur.

    Matthew L.
    Actuate is now OpenText
    http://www.actuate.com

  • BIRT Report Designer 2.6.1 Report Version 3.2.22 (RCP)


    unfortunately I don't believe we can open a ticket with opentext, it is provided by Excel ltd


    Will try different ways , if this could be done in older version of Crystal Report I have no doubt birt can handle it somehow :)


    I am open to any suggestion


     


    thanks   


  • I'm not sure what else to check as l tested in BIRT RCP 2.6.1: http://archive.eclipse.org/birt/downloads/build.php?build=R-R1-2_6_1-201009171723


    and I do not have any issues generating the output (See attached image and example report).


     


    If you cant post your design file, can you post an example that replicates the issue?


    Or change my example to replicate the issue you are seeing?


     

    Matthew L.
    Actuate is now OpenText
    http://www.actuate.com

  • Matthew I really appreciate you spare your time to figure this issue out,


    I have seen your sample and I agree it is just strange that I cant get it working.


    I have used  customer table from Classicmodle to test it and it doesn't work there either ,  ( I want to attach the design document, but I just cant find how to attach a file here :( )


  • To attach a design file, first select the "More Reply Options" button in the Reply window.


    This should bring you to a more advanced input editor.


    Click the "Choose File" button to select your design file.


    Once selected, click the "Attach This File" button to attach the selected file.


    If you need to add additional files (such as images), repeat the process of "Choose File", then "Attach This File" buttons.


    Select the "Add Reply" button to complete the post.


    Matthew L.
    Actuate is now OpenText
    http://www.actuate.com

  • Thanks for your respond


    testhighlight is the one I used classicmodle data to test your logic


    highlight is my actual report ( I need the first column to have the highlight we discussed)


    output is the result I am getting from highlight report


     


    thanks


  • Looking at the sample you provided, I extracted the PO_Number column from the PDF and copied the computed column code in your design and they work perfectly together.


    My only thought is that perhaps the PO_Number column is coming back differently for each row from the database (perhaps the data has spaces after it?).


     


    Here are two additional code methods to try to see if they work for you:


    This first one verifies the PO_Number is set to a String data type and will use RegEx to trim the spaces from the string (FYI: due to such an old version of BIRT, the JavaScript ".trim()" command is not recognized):



    var currentRow = ("" + row["PO_Number"]).replace(/^\s+|\s+$/gm,''); //Get current data row as string & trim
    var previousRow = reportContext.getPersistentGlobalVariable("previousRow"); //Get global variable value

    if(currentRow==null){reportContext.setPersistentGlobalVariable("previousRow",null);} //Check for null and set null
    else{reportContext.setPersistentGlobalVariable("previousRow",currentRow.toString());} //Or assign global variable to current row value

    if(currentRow == previousRow){ //Compare currentRow with previousRow value
    "Row Color: Silver"; //If currentRow is the same as previousRow value
    }else{
    "Row Color: Black"; //If currentRow is not the same as previousRow value
    }

    This second suggestion uses a different comparison method.  In theory this method "could" generate invalid results, however it is a good test to try if the suggestion above fails to work.



    var currentRow = ("" + row["PO_Number"]); //Get current data row as string
    var previousRow = reportContext.getPersistentGlobalVariable("previousRow"); //Get global variable value

    if(currentRow==null){reportContext.setPersistentGlobalVariable("previousRow",null);} //Check for null and set null
    else{reportContext.setPersistentGlobalVariable("previousRow",currentRow.toString());} //Or assign global variable to current row value

    if(currentRow.indexOf(previousRow) != -1){ //Compare currentRow with previousRow value
    "Row Color: Silver"; //If currentRow is the same as previousRow value
    }else{
    "Row Color: Black"; //If currentRow is not the same as previousRow value
    }

    Matthew L.
    Actuate is now OpenText
    http://www.actuate.com

  • edited November 1, 2017

    I will do this on the table column.
    1. Create a group on that column and hide it since you don't need it
    2. create aggregation of running count based on the group.
    3. apply two highlight on column. first if aggregation = 1 color red
    4. second highlight. if aggregation greater than 1 color blue.
    I hope this helps

  • solved

Sign In or Register to comment.