Report Builder allows you to manipulate database information before it is generated to a report page - this is very handy for the next "trick".
Our report so far has three columns that break apart the city, state and zip code. While this is acceptable, we can make it look much better by formatting the database information before it is printed. Instead of printing information inside three FRAMES we will instead print a formatted city/state/zip inside of one FRAME. Not only will it look better, but it may also take up less space on the report!
Here is an example of how the report used to print this information:
Los Angeles CA 90003
and here is how we want it to look:
Los Angeles, CA 90003
Go ahead and open up the report again; the Report Formatter screen should be displayed. Delete the two frames for City and State, and also delete the header information for those two columns (to delete a FRAME you right click it, then select DELETE from the pop up menu).
Left click the Zip Code FRAME inside the DETAIL band; the properties box for that frame should look like this:
Contents of Zip Code frame
If you examine the old report you will notice that there is a lot of blank space after the city. We need a way to clip the empty spaces out of the database field. This is achieved by digging deeper into the Report Builder's bag of tricks, and using a built-in function that CLIPs off blank space at the end of a database field. The function called CLIP() does exactly this for us, and here is how to use it.
Delete the Contents information inside the "Properties" box for the frame, making it completely blank. Click the button with the flashlight to the right (this is the Dictionary access button). This will display the DICTIONARY. Not only does the DICTIONARY maintain the database field information for ComQuest, but is also a repository for built-in functions that the Report Builder has to offer. Here is a screen capture showing where the CLIP() function is located inside the DICTIONARY:
The Clip() function in the dictionary
Double click the CLIP(string) entry, which inserts it into the FRAME properties:
Clip function in contents field
Obviously we aren't manipulating a database field called (string); this is simply telling you that the CLIP() function is used against text strings - you can't use this function on numeric fields. Go ahead and remove the word "string" from inside the '()' symbols, thus leaving you with = CLIP() inside the contents entry field. Left click once inside the '()' characters, then click the DICTIONARY button again to select the City field from the dictionary. Double click the City field, which changes the above screen to look like this:
Clip function with res:City field
Now our frame is going to print the contents of the City field from the database, but it will remove all the blank spaces from the end of the field!
We've solved one piece of the puzzle; now we need to combine a comma with the State field, and a blank space after the comma. To combine two or more strings together we use the Ampersand (&) character; any text that we want to combine with a database field has to be enclosed in single quotes. So, here is what the Contents entry field looks like now:
Clipped city with comma and space
So far so good. We have the City (with all blank spaces removed), and the comma character added directly after it, with a blank space after the comma. Now it's time to add the State field; however, remember that the State field can also have blank spaces at the end, so we need to CLIP() that field too. We are combining the State field to the existing information, so we also need the Ampersand character to join them together. Go ahead and type in this information:
then click inside the '()' symbols where the State field will be inserted, then click the DICTIONARY button to display the database fields. Select ST (Res:State) from the respondent fields list, and double-click on it. Here is our revised screen:
Frame contents with City, State
Now for the last part - adding the Zip Code. First, we want to add two spaces after the State. Click at the end of the State field, and add: & ' ' (an ampersand, an apostrophe, two spaces, then another apostrophe). Next add the Zip Code. This database field is only nine positions in length, so we don't need to CLIP() that field - we just need to append it to the rest of the information. We do need a blank space after the State field though. So, go ahead and type in the ampersand (&) symbol at the end of the Contents string, then add a blank space between two quotes, then click on the DICTIONARY button; select the Zip field to make the Contents look like this:
Final frame contents with City, State and Zip Code fields
Now we need to change the PICTURE for this FRAME; originally it was as long as the Zip Code field allowed, which was 9 places. We want to extend this to be as long as all the fields and blank spaces require for the 3 fields. To see how long the database fields are you can use the DICTIONARY:
Field lengths in dictionary (to the far right)
You can use the right arrow to scroll the list to the right in case the PICTURE column doesn't display on your screen. The number after the @S is the length of the field; based on the above information we need to extend the frame to 18 + 2 + 9 = 29 spaces. But, don't forget to add in the comma and the spaces after the city and state! So we should make this picture 32 spaces.
Click on the tab titled 'Picture'; right now the picture is @s9; go ahead and increase the size to 32 characters and click OK to save.
Resize the Zip Code FRAME to accommodate the increased length. Don't forget to change the column header, since it now is the City/State/Zip not just the Zip Code! Once you have the FRAMES properly aligned save the report, then run it to see how it looks. Here is what the sample report looks like now:
Sample report with new City/State/Zip field
To recap; in this lesson you learned:
|•||How to remove blank spaces from the end of database field information|
|•||How to format separate pieces of information into one frame|
|•||Where to find the different functions in the dictionary to manipulate database information|
|•||How and why you need to change the Picture for a frame when combining data|
|•||How and why you need to resize a frame when combining separate pieces of information|