Jump to content

Pivot Table conditional formatting


Recommended Posts

Hello all,

I have been putting together a simple pivot table view to display outcomes and percentages of an audit process that we perform which looks something like below;

image.thumb.png.8c2ba1db70e4fa3f60ecbb9f7d87657d.png

I would like to conditionally format the "% Correct" columns so that they are Green if = 100% or Red if < 100% I have found this a bit difficult as there do not appear to be any Element ID's associated with the cells, I got the result below by using Class Name and column No;

image.thumb.png.3f30e7d3e1a45eb01e854a3badccf865.png

Using this code deployed in the footer;

<script>
function f_color(){
if('[@calcfield:11]' == '100 %') {
document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.backgroundColor="green";
document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.color="white";
}else{document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.backgroundColor="red";
document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.color="white";
}
}
f_color();
</script>

However it doesn't switch to green when the field is = 100% (see below I assume this is because the calculated field I am referencing is either the incorrect syntax or can't be used in this way ) and as you can see doesn't apply to all cells in the respective column;

image.thumb.png.3473f0a25073d7b2a70831b2f1fd2732.png

Any suggestions or guidance would be gratefully received as I am probably going in completely the wrong direction and I have searched the forums and can find a number of suggestions/solutions for Tabular reports but nothing for Pivot Table reports.

Kind regards,

 

Roosta

 

Link to comment
Share on other sites

Have a solution sort of...

This works for the way I have the datapage deployed but probably won't work with direct URL's or deployment via Weebly or similar as it utilises the CSS code of the website page its deployed on and therefore also doesn't work if you try to download the data as a PDF but works sufficiently for my current needs;

<script>
$("table.cbResultSetTable tbody tr.cbResultSetOddRow, table.cbResultSetTable tbody tr.cbResultSetEvenRow").each(
    function (x) {
        $(this).children().each(
            function(){
                if ($(this).text().indexOf("%") != -1){
                    if ($(this).text() == "100 %"){
                        $(this).css("color", "white").css("background","green")
                    } else {
                        $(this).css("color", "white").css("background","red")
                    }
                }
            }
        )
    }
)
</script>

Probably not the most elegant solution in the world but gives the following results in the frontend webpage when deployed in the footer of the pivot report;

image.thumb.png.0be481a7779eaccfe9fbec8299dc8420.png

It would obviously be far easier if Caspio had a conditional formatting function in the "formatting" section for Values but in the absence of that this may be useful to others if all you need is to format the display of certain columns.

Thanks for looking,

Roosta

 

 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...