Tutorial - Lesson 8

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! Mail us feedback on this topic!  

 

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:

 

RBDetail8

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:

 

RBDetail9

The Clip() function in the dictionary

 

Double click the CLIP(string) entry, which inserts it into the FRAME properties:

 

RBDetail10

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:

 

RBDetail11

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:

 

RBDetail12

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:

 

& clip()

 

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:

 

RBDetail13

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:

 

 

RBDetail14

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:

 

RBDetail15

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:

 

RBReport4

Sample report with new City/State/Zip field

 

Much better!

 

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