Intellicus Enterprise Reporting and Business Insights 19.1

Conditional Formatting

0 views June 28, 2020 0

You can achieve conditional formatting through Scripting too.

You can format a displayed row value if the values of that row satisfy a given condition. For example, if you need to compare the database field (say ‘dbfield’) with a field in the previous row, and encircle it in red if it is different, add a text box (say ‘text box’) in the report header and set its visible property (from the Properties list) as ‘False’.

Set its text property (from the Properties list) to any arbitrary value (say ‘-999’) that can never be attained by the field to be compared with.

Now, add a shape (say “shape1”) around the ‘dbfield’ and set its color and shape to a red ellipse. The same can be achieved using the Script Editor as follows:

  1. From the Tools menu, click Scripting.
  2. Select object as Detail.
  3. Select event as OnFormat.
  4. Type the following JavaScript:

Object: Detail            Event: OnFormat

Code:

function OnFormat()
   {
     if ( ( rpt.Section("ReportHeader").Controls("txtBox").text != "_99999" ) &&
          ( rpt.Fields("dbValue").value != rpt.Sections("PageHeader").Controls("txtBox").text ) )
	  {
	    rpt.Sections("Detail").Controls("Shape1").visible = true ;
	  }
	  else
	  {
	    rpt.Sections("Detail").Controls("Shape1").visible = false ;
	  }

         rpt.Sections("PageHeader").Controls("txtBox").text = rpt.Fields("dbField").value;
   }

 

Conditional Suppressing Of Rows

You can suppress the display of certain rows as per your requirement, like some column containing NULL values can be suppressed (hidden) from getting displayed on the report.

There are two methods to do this:

  • Select the control and set the visible property (Property window) value as false, and assign a 0 (Zero) value to the height property (Property window) of the control.
  • Go to Tools > Scripting; select the object as Detail, and the event as OnFormat, and write the following code:

Object: Detail            Event: OnFormat

Code:

function OnFormat()
   {
     if ( rpt.Fields("Name").value == null )
     { 
      rpt.Sections("Details").visible = false;
      
     }
     else
     {
       rpt.Sections("Details").visible = true;
     }
     
     if ( rpt.Fields("Name").value == null )
     { 
      rpt.Sections("Details").height = 0;
      
     }
     else
     {
       rpt.sections("Details").height = 285;
     }

   }

 

Important: To dynamically change the height of a section through a program, the ‘CanGrow’ property (Properties list) of Detail Section should be set to ‘False’.

If it is set to ‘True’, then the report section will override your (height) value to adjust the height of the section.

Conditional Calculation

You can calculate values in the report by giving conditions for calculation. For example, there are two fields in a report Account_type and Amount. There can be two account types say ‘A’ and ‘B’. If you want to sum ‘A’ and ‘B’ separately, write a JavaScript in the Script Editor as:

function OnDataInitialize() 
{ 
   rpt.Fields.add("valueA"); 
   rpt.Fields.add("valueB"); 
   rpt.Fields.("valueA").value = 0; 
   rpt.Fields.("valueB").value = 0; 
} 

function OnFetchData(eof) 
{ 
   if ( rpt.Fields("accType").value == "A" ) 
   { 
       rpt.Fields.("valueA").value =       parseFloat(rpt.Fields.("valueA").value)) + rpt.Fields("Amt").value;      } 
   else 
   { 
       rpt.Fields.("valueB").value = parseFloat(rpt.Fields.("valueB").value)) + rpt.Fields("Amt").value; 
   } 

   rpt.Sections("gfDEPT").Controls("txtACC_A").dataValue = rpt.Fields.("valueA").value; 
   rpt.Sections("gfDEPT").Controls("txtACC_B").dataValue = rpt.Fields.("valueB").value; 
}

This script will add two new fields in the report containing summated values for ‘A’ and ‘B’ account types.