Row colour based on previous result

hatra
edited February 11, 2022 in Analytics #1
<p>Hi All,</p>
<p>is there way to compare a result of same filed and change the colour of repeated result ?</p>
<p> </p>
<p>ie</p>
<p> </p>
<p>column name</p>
<p>   1       ( font color black)</p>
<p>   1       ( font color red)</p>
<p>    1      (font color red)</p>
<p>    1      (font color red)</p>
<p>   2      </p>
<p>    3     ( font color black)</p>
<p>    3     ( font color red)</p>
<p> </p>
<p>thanks</p>

Comments

  • <p>What is your data source?  For example, Oracle 11g2 or SQL Server 2016?  I ask because they support LAG and LEAD functions.</p>
    <p> </p>
    <p>Take a look at this SQL Fiddle based on Oracle 11g2 @ <a data-ipb='nomediaparse' href='http://sqlfiddle.com/#!4/c2e2c/1'>http://sqlfiddle.com/#!4/c2e2c/1</a></p&gt;
    <pre class="_prettyXprint _lang-">
    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
    </pre>
    <pre class="_prettyXprint _lang-">
    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
    </pre>
    <p>Notice that there is a column called ID_PREV for the previous value using LAG, and ID_NEXT for the next value using LEAD.</p>
    <p> </p>
    <p>With these values, you can use them to change the Highlight rules of your report item to change color accordingly.</p>
    <p> </p>
    <p> </p>
    <p> </p>
    <p>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 <em>beforeOpen </em>event to keep the previous value.</p>
    <pre class="_prettyXprint _lang-">
    previousValue = null;
    </pre>
    <p>Then in the Computed Column, you would have an expression like:</p>
    <pre class="_prettyXprint _lang-">
    savedPreviousValue = previousValue;
    previousValue = row["YOUR COLUMN"];
    savedPreviousValue;
    </pre>
    Warning No formatter is installed for the format ipb
  • <p>HI thanks for the respond, I am using MSS 2014</p>
    <p>Could this be done in BIRT designer rather? highlights</p>
    <p>if myclumn =myclomn to  red</p>
  • <p>Microsoft SQL Server 2014 has LAG and LEAD.</p>
    <p> </p>
    <p>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.</p>
    Warning No formatter is installed for the format ipb
  • <p>understood thanks Clement</p>
  • <p>hi,</p>
    <p>I have applied the computed column logic</p>
    <p>there is an issue where the new computed column return blank on first row  therefor it never matches the previous column ie</p>
    <p>column 1       computed column</p>
    <p>a1                  </p>
    <p>a2                 a1</p>
    <p>a2                 a2</p>
    <p>a2                a2</p>
    <p>a4                a2</p>
  • <p>If you are only looking at the previous row value and not all previous rows before the current one.</p>
    <p>Then I was able to accomplish this using a computed column in the data set.</p>
    <p>See attached example.</p>
    <p> </p>
    <p>Code used in my Data Set's Computed Column:</p>
    <pre class="_prettyXprint _lang-js">
    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
    }
    </pre>
    Warning No formatter is installed for the format ipb
  • <p>That's expected.  The first row should be blank (or null) because we initialized the variable <span>previousValue to null, and it's the first row where there is no previous value.</span></p>
    <p> </p>
    <p>If you use a database with LAG, the previous value for the first row is "0" as seen in the SQL Fiddle @  <a data-ipb='nomediaparse' href='http://sqlfiddle.com/#!4/32258/1'>http://sqlfiddle.com/#!4/32258/1</a></p&gt;
    <p> </p>
    <p>You can initialize it to 0 like what a database would return.</p>
    <p> </p>
    <p>What value are you expecting for the first row that does not have a previous value?</p>
    Warning No formatter is installed for the format ipb
  • <p>@ Matthew,</p>
    <p>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).</p>
  • <p>@ Clement,</p>
    <p>thanks for reply we always want current row = previous row if that make any sense,</p>
    <p>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</p>
  • <p>@Matthew sorry to mention</p>
    <p>it is not whole row we want to change color, it is only on first column of report</p>
  • <p>Lets go over a few things,</p>
    <p>First, when you created the computed column, did you change "DATAROW" to the name of your own data row name.</p>
    <p>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:</p>
    <pre class="_prettyXprint _lang-js">
    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
    }
    </pre>
    <p>As a result, you should see the column compute correctly via the data preview (see attached image).</p>
    <p>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.</p>
    <p> </p>
    <p>If the computed column is calculating correctly, please make sure that your highlight rule has been applied correctly to the table (see attached image).</p>
    <p> </p>
    Warning No formatter is installed for the format ipb
  • <p>Hello hatraatrin,</p>
    <p> </p>
    <p>I just saw that you made post #11 while I was writing post #12.</p>
    <p>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.</p>
    <p>This should generate the results you are looking for.</p>
    Warning No formatter is installed for the format ipb
  • <p>thanks for the respond Matthew,</p>
    <p>I have changed the DATAROW to my datarow ( po number )</p>
    <p>computed column is created successfully and data preview seem to be working fine</p>
    <p>highlight has been applied as per your example</p>
    <p> </p>
    <p>but in the report out put it picks up random repeated rows to silver and not all and main column not working,</p>
    <p>just to remind you again, it is not whole row we want to change it is only one column,</p>
    <p>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</p>
    <p>thanks</p>
  • <p>Sounds like you have a report theme, style, or another highlight rule that is conflicting with the results.</p>
    <p> </p>
    <p>As for uploading the design file, can you open a ticket with support?</p>
    Warning No formatter is installed for the format ipb
  • <p>I have checked the report and there is no style or highlight elsewhere,</p>
    <p>I have a suspicion that  second column may causing this issue as one part of the bug  ie</p>
    <p>column 1            column2      </p>
    <p>a1      black            si          not working</p>
    <p>a1     black             bi          not working</p>
    <p>a1     black              bi        not working</p>
    <p>a2     black             ti       </p>
    <p>a3    black              yi</p>
    <p>a4    black             mi       working</p>
    <p>a4    red                ni         working</p>
    <p>a4    red                ri         working</p>
    <p> </p>
    <p>do you mean open a ticket in here or opentext support?</p>
    <p>I don't believe this version of design is open text product  </p>
    <p> </p>
    <p>thanks</p>
  • <p>Sorry, I meant open a support ticket with OpenText support to investigate.</p>
    <div>However, what version of BIRT are you using?  I will test against that version to verify on my side.</div>
    <p>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.</p>
    Warning No formatter is installed for the format ipb
  • <p>BIRT Report Designer 2.6.1 Report Version 3.2.22 (RCP)</p>
    <p>unfortunately I don't believe we can open a ticket with opentext, it is provided by Excel ltd</p>
    <p>Will try different ways , if this could be done in older version of Crystal Report I have no doubt birt can handle it somehow :)</p>
    <p>I am open to any suggestion</p>
    <p> </p>
    <p>thanks   </p>
  • <p>I'm not sure what else to check as l tested in BIRT RCP 2.6.1: <a data-ipb='nomediaparse' href='http://archive.eclipse.org/birt/downloads/build.php?build=R-R1-2_6_1-201009171723'>http://archive.eclipse.org/birt/downloads/build.php?build=R-R1-2_6_1-201009171723</a></p&gt;
    <p>and I do not have any issues generating the output (See attached image and example report).</p>
    <p> </p>
    <p>If you cant post your design file, can you post an example that replicates the issue?</p>
    <p>Or change my example to replicate the issue you are seeing?</p>
    <div> </div>
    Warning No formatter is installed for the format ipb
  • <p>Matthew I really appreciate you spare your time to figure this issue out,</p>
    <p>I have seen your sample and I agree it is just strange that I cant get it working.</p>
    <p>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 :( )</p>
  • <p>To attach a design file, first select the "More Reply Options" button in the Reply window.</p>
    <p>This should bring you to a more advanced input editor.</p>
    <p>Click the "Choose File" button to select your design file.</p>
    <p>Once selected, click the "Attach This File" button to attach the selected file.</p>
    <p>If you need to add additional files (such as images), repeat the process of "Choose File", then "Attach This File" buttons.</p>
    <p>Select the "Add Reply" button to complete the post.</p>
    Warning No formatter is installed for the format ipb
  • <p>Thanks for your respond</p>
    <p>testhighlight is the one I used classicmodle data to test your logic</p>
    <p>highlight is my actual report ( I need the first column to have the highlight we discussed)</p>
    <p>output is the result I am getting from highlight report</p>
    <p> </p>
    <p>thanks</p>
  • <p>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.</p>
    <p>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?).</p>
    <p> </p>
    <p>Here are two additional code methods to try to see if they work for you:</p>
    <p>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):</p>
    <pre class="_prettyXprint _lang-js">
    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
    }
    </pre>
    <p>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.</p>
    <pre class="_prettyXprint _lang-js">
    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
    }
    </pre>
    Warning No formatter is installed for the format ipb
  • padytom
    edited November 1, 2017 #24

    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