Add a Smiley Face Rating Icon to a Report
Add a Smiley Face Rating Icon to a Report
You can easily add a rating icon, such as smiley faces to communicate at a glance in a variety of ways. Building on the checkbox article posted earlier, we’ll look at how to use Wingdings to quickly add a rating icon based on a character:
- Create a new formula, with an appropriate name (for example, RatingIcon) that defines what character code to show, depending on the value of the field. In our example, we want a smiley face if the value is above 90%, a neutral face if the value is above 80% but less than 90%, and a frowning face if the value is 80% or lower.
if {@Percentage}>90 then chr(74) else if {@Percentage}>80 then chr(75) else chr(76) Continue reading ‘Add a Smiley Face Rating Icon to a Report’ »
Add a Watermark to a Report
A Watermark is a background image or text block that appears on a page. In our example we’ll be adding a faint DRAFT statement that appears on all pages. (To make our example show up on the screenshots, I’ve made the DRAFT much darker than I would really use.)
- Open the Report and select the Design Tab.
- Right-click the Page Header section and select Insert Section Below from the pop up menu.
There will now be two Page Headers, PHa and PHb.
- Insert the graphic or add the text block to Page Header b (PHb).
- Position the graphic how you like:
- To position it vertically, you will need to resize the header area and move the item down.
- To make it repeat, insert or copy and paste the item repeatedly.
- Right-click the Page Header b section name and select Section Expert from the pop up menu.
- In the Section Expert, make sure Page Header b is selected, then check Underlay Following Sections.
- Close the Section Expert.
If you want the watermark to appear behind the page header (Page Header a), drag the position of Page Header b so that it appears before Page Header a.
Here’s the final report:
Shade Alternate Lines in a Report
Tabular data can be tough to read; I frequently want my reports to have alternate lines shaded to make the data easier to follow.
Shading Alternate Details Lines
- Right-click the Details section name.
- Select Section Expert.
- Click the color tab and click the Conditional Formatting button.
- To shade every other line, enter the formula
If RecordNumber Mod 2 = 0 Then crSilver else crNoColor
To shade every two lines, enter the formula
If RecordNumber Mod 4 in 1 to 2 Then crSilver else crNoColor
- Click OK twice, and preview your report.
How to Format a Percentage
I was a little confused when I created my first report–I wanted to show a calculation as a percentage, which doesn’t appear to be an option. However, here is a work around that works for me.
- Create the formula. In my example, I want to show the Actual Sales as a percentage of the Target Sales so my formula is
({Sheet1_.Sales}/{Sheet1_.Target})*100
- Place the field on your report.
- Right-click the field, select Format Field, and select the Number tab.
- Check the Display Currency Symbol option.
- Click Customize. You see the Custom Style Dialog.
- Set your number options, then click the Currency Symbol Tab.
- Change the Currency Symbol to %, and change the position to display the symbol after the number.
- Click OK, then OK and preview your field.
Using a Parameter to Select What to Group On
Making reports more flexible means having less to update/revise/manage. For example, lets say you need to create a report showing all incidents by subject, by who the ticket was assigned to, and by client. You could create three different reports. But then if the company logo changes, or something else changes, you will need to update three reports. Instead, you can create a single report that uses a parameter which allows the user to select how they want to see the data grouped.
Create The Basic Report
Start by creating the basic report, linking the tables, and selecting the data you want to view. (In this example, we’re searching the Incidents table, selecting all tickets that are closed between two date parameters, FDate and FTDate). Don’t set up the groups, yet, but you can start arranging the data you want to see.
Create Your Parameter
First you will create a parameter that will let users select how they want the report organized.
- On the field explorer pane, right click Parameter and select New from the pop-up menu.
- You see the Create Parameter Field dialog.
- Enter the Name (for example, Grouper) and provide some Prompting text.
- Set the value type to String.
- Do not check Allow multiple values.
- Click Default Values…
- You see the Set Default Values dialog.
- Enter the options you want to use to organize the report. (If you want you can select each option and click Set Description to provide more information on the option.)
- Click OK twice to return to your report.
Create a Grouping Variable
You now have a way for the user to select what to use when grouping the report; you next need to create a variable that contains different fields depending on the user’s choice. This will be used to create the grouping.
- On the Explorer Pane, right-click on Formula Fields and select New from the pop-up menu.
- Name your variable GH Select.
- Enter the following formula into GH Select:
stringvar array group := ({?Grouper});
select group[1]
case 'Assigned To' : if isnull({Incident.AssignedTo}) then 'Not Assigned' else {Incident.AssignedTo}
case 'Client' : if isnull({Incident.Client}) then 'No Client' else {Incident.Client}
case 'Subject' : if isnull({Incident.Subject}) then 'No Subject' else {Incident.Subject} - Replace the case options with your default values (in blue), and the field names with the correct field to group on (in green).
Group Your Report
Now, you just need to add the grouping to your report.
- Select Report|Group Expert.
- Select the variable you created above, GH Select, to group on.
- Click OK.
- Select an option to group by, and preview your report.
Add a Conditional Graphic
You can use this approach to make a different graphic appear, depending on a value or condition. In our example, we’re looking at a report of presidents and will show a party graphic if they are democrats or republicans.
- Open the report and add all the graphics you will be using.
- Right-click on the first graphic and select Format Graphic from the pop up menu.
- On the Format Editor dialog, make sure you are on the Common tab.
- Click the Conditional Formula button next to the Suppress check box.
- Enter the formula to determine when to hide the graphic. In our case, since this graphic is the Republican Elephant, we want to suppress the image anytime the Party<>”Republican”:
{Sheet1_.Party}<>"Republican" - Click Save and Close to save the formula and close the Format Editor.
- Repeat steps 2-6 for each graphic. In our case that means applying the following formula to the Democratic Donkey:
{Sheet1_.Party}<>"Democrat" - Now, select both the graphics and use the align command to stack the graphics in the location on the report where you want them to appear.
- Here’s the result:
Use Array Parameters in Titles
I like to include details in my report titles that explain just what is shown in the report; this usually means that I create a formula for the title that includes the Parameters. Here are some tips on including array parameters attractively.
Create a Title Formula
The first step is to create a new formula, I usually call mine ReportTitle. I then define it as a text string incorporating any parameters I want to show. For example:
'Linked Work Orders, Child Changes, and Incidents for Change number(s):' & {@parameter}
Format a Number Array Parameter in Titles
Adding a parameter containing an array of number variables to a title is simple. By default the parameter will show as a list of numbers separated by commas. So to show the list of record numbers selected by the user you would enter:
'Linked Work Orders, Child Changes, and Incidents for Change number(s):' & {@NumbersSelected}
The result would show as: Linked Work Orders, Child Changes, and Incidents for Change number(s) : 900243, 900245
Format a String Array Parameter in Titles
To make an array of string variables (for example, a list of states) to appear in a title as a list separated by commas, you need to use the Join function. The Join function takes uses the following syntax:
Join (list[, delimiter])
List
The String array you want joined.
Delimiter
Optional. The String you want to use to separate the individual elements in your array. If you don’t specify a Delimiter, a space is used.
So, to concatenate your parameter of the desired states, separated by a comma and a space, you would use
Join ({?States}, ', ')
If {?States} includes Alabama, Michigan, and Texas, you would see:
Alabama, Michigan, Texas
To concatenate your parameter of the desired states, with no separator, you would use
Join ({?States}, '')
Comments
Post a Comment