The University of St. Thomas

Conditional Formatting


Highlight Data
Add a "No Rows Returned" message to a report
Highlighting Alternate Rows

Highlight Data

 

Highlight data in your report to better identify exceptional results. For example, if you want to identify students with large account balances, you might create a condition that checks whether each students account balance is greater than a given amount.

 Steps

  1. Open the report that you want.

  2. From the File menu, click Variables.

  3. Create a variable by clicking the add button below the Variables box.

  4. In the Name box, type a name for the variable.

  5. In the Type box, select the type of variable you want to create:                           
        To create a variable that has only two possible values, yes and no, click Boolean.  
        To create a variable whose values are string-based, click String.

  6. Click OK.

  7. In the Expression Definition box, define the condition.                                        
          (e.g.  [BALANCE]>10,000)

  8. Click OK twice.

  9. In the work area, click the column that you want to highlight based on the condition you just created.

  10. Click the Condition Explorer button .                                                                
         The Condition Explorer dialog box appears.

  11. Click the variable you just created.

  12. In the Properties pane, click the Conditional Style property.

  13. In the Variable dialog box, click the Variable box.

  14. Click the variable you want to assign to the object.

  15. Click OK.

  16. Open the Condition Explorer dialog box again, and click one of the possible values for the variable other than the default value.                                          
                      For example, if you created a boolean variable, click the Yes value.

  17. In the Properties pane, specify the formatting that you want to highlight the column with when the condition is satisfied.                                                         
                      (e.g. Change the Background color to Red)

  18. Repeat steps 17 to 18 for other possible values defined for the variable.

  19. Repeat steps 10 to 19 for other objects that you want to highlight. 

Tip: In the Condition Explorer dialog box, click (No variable) to view how the report looks when no variable is applied.

When you run the report, the report objects to which you applied the variable are highlighted when the condition is satisfied. For example, if you created a boolean variable, the objects are highlighted when the condition is met.

 But……

 What if you want to highlight the data on multiple levels?  For example students with  balances greater than $5,000.00 you want flagged as “Yellow” and  balances greater than $10,000.00 flagged as “Red”.

 Steps 

  1. Open the report that you want.

  2. From the File menu, click Variables.

  3. Create a variable by clicking the add button below the Variables box.

  4. In the Name box, type a name for the variable.

  5. In the Type box, select the type of variable you want to create. Click String.

  6. Click OK.

  7. In the Expression Definition box, define the condition.
  8. Click OK.


  9. Create the values for the string by clicking the add  button below the values box. (Create a “Red” value and a “Yellow” value.)

  10. Click OK.

  11. In the work area, click the column that you want to highlight based on the condition you just created.

  12. Click the Condition Explorer button .
    The Condition Explorer dialog box appears.

  13. Click the variable you just created.

  14. In the Properties pane, click the Conditional Style property.

  15. In the Variable dialog box, click the Variable box.

  16. Click the variable you want to assign to the object.

  17. Click OK.

  18. Open the Condition Explorer dialog box again, and click one of the possible values for the variable other than the default value.

  19. In the Properties pane, specify the formatting that you want to highlight the column with when the condition is satisfied. (e.g. Change the Background color to Red or yellow)

  20. Repeat steps 17 to 18 for other possible values defined for the variable.

  21. Repeat steps 10 to 19 for other objects that you want to highlight.

 back to top...

How to Print a “No Rows Returned” Message on a Report

 Steps

  1. Add a List footer from the toolbar  or a Table cell from the toolbox to your report.

  2. Change the text to “No Rows Returned”.  Tip:  Enlarge the Font size to call the message out.

  3. Insert a List item from the Toolbox into the Table cell.

  4. Select Existing Query from the Create – List dialog box. 

  5. Un-check Auto populate.  

  6.  Click OK.

  7. The table fields will appear in the Table cell. 

  8. From Query Items select a field and drag it into the table (you will only need one field).

  9. Select the entire Table cell.

  10. In the properties pane click the ellipsis next to Conditional Style.

  11. Click the Variables button to create a new variable.

  12. Click the New icon.

  13. Name the variable and select the type.  For this example select Boolean.

  14. Click OK.

  15. In the Layout Expression, create the expression definition:  RowNumber()>0

  16. Click OK 3 times to return to the report page.

  17. Select the Condition Explorer from your report page.  (Note that when a condition is active in a report the toolbar will be green.  To inactivate a condition simply select No variable.)

  18. Define the condition for Yes and No by selecting either Yes or No for the variable in the Condition Explorer.

  19. Select the entire table cell.

  20. In the Properties Pane set the Box Type to reflect the response to Yes or No. 
    (Leaving the Box Type to Default causes the text message to appear on the report.  Setting the Box Type to None removes the message from the report.  In this example the Box Type is set to None for a Yes response to the Variable created and Default for a No response to the Variable.  If the report comes back with no results the message “No Rows Returned” will appear on the report page.  If the report returns results no message will appear.)

  21. Save and Run the report.
back to top...


Highlighting Alternate Rows


Have you ever wanted to create a report with alternating grey and white rows that would make it easier to read?  Well you can!  The way to apply an alternating color scheme is to create two calculations and then add a conditional format.  Here is an example:

Step 1

Create a running count calculation.  This calculation uses the running-count summary function and a detailed column in a report.

Example:  Drag a calculation on to the report from the Toolbox and name it RunningCalc.
In the Expression Definition box, define the calculation:                                                                       running-count([IX_STUDENT_NUMBER]) 

Step 2

Create a calculation that will display the remainder from a division.  This calculation uses the mod function and the calculation created in step 1

Example:  Drag a calculation on to the report from the Toolbox and name it MOD.  In the Expression Definition box, define the calculation:  mod([RunningCalc],2)

This column will now display alternating numbers of 1 and 0. 
You can now cut the two calculations so they are not displayed on the screen. 
(Note:  Cutting these calculations keeps them in the query.  If you delete the calculations they are removed from the query as well.)

Step 3

Create a conditional format that will alternate the color schemes on the rows.

Example:From the File menu select Variable and create a Boolean variable that contains the following expression:  [MOD]=1

Follow steps 9 – 19 from the Highlight Data section on page 1 of this document to complete the formatting.

back to top...