Row colour based on previous result

hatra · 2017-09-20T05:09:03+00:00

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) …

hatra

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

Find more posts tagged with

Comments

Clement Wong

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 @ <a data-ipb='nomediaparse' href='http://sqlfiddle.com/#!4/c2e2c/1'>http://sqlfiddle.com/#!4/c2e2c/1</a>
<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>
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.
<pre class="_prettyXprint _lang-">
previousValue = null;
</pre>
Then in the Computed Column, you would have an expression like:
<pre class="_prettyXprint _lang-">
savedPreviousValue = previousValue;
previousValue = row["YOUR COLUMN"];
savedPreviousValue;
</pre>

hatra

HI thanks for the respond, I am using MSS 2014
Could this be done in BIRT designer rather? highlights
if myclumn =myclomn to red

Clement Wong

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.

hatra

understood thanks Clement

hatra

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

Matthew L.

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:
<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>

Clement Wong

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 @ <a data-ipb='nomediaparse' href='http://sqlfiddle.com/#!4/32258/1'>http://sqlfiddle.com/#!4/32258/1</a>
 
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?

hatra

@ 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).

hatra

@ 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

hatra

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

Matthew L.

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:
<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>
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.

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.

hatra

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

Matthew L.

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?

hatra

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

Matthew L.

Sorry, I meant open a support ticket with OpenText support to investigate.
<div>However, what version of BIRT are you using? I will test against that version to verify on my side.</div>
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.

hatra

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

Matthew L.

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>
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?
<div> </div>

hatra

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

)

Matthew L.

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.

hatra

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

Matthew L.

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):
<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>
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.
<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>

padytom

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

hatra

solved