WinVask -  Faktura

Basics: Step 8 (Functions & Pictures)

Basics: Step 8 (Functions & Pictures)

Previous topic Next topic  

Basics: Step 8 (Functions & Pictures)

Previous topic Next topic  

 

The Report Builder allows you to manipulate database information before it is generated to a report page - this is very handy for the next "trick" that is next.

 

Our tutorial report so far has three columns that break apart the name (last name, first name, middle initial). 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 3 FRAMES we will instead print a formatted name inside of 1 FRAME. Not only will it look better, but may also take up less space on the report! Here is an example of how the report used to print name information:

 

PRESLEY                ELVIS              A

 

and here is how we want it to look:

 

PRESLEY, ELVIS A

 

Go ahead and open up the report again; the Report Formatter screen should be displayed. Delete the two frames for First Name and Middle Initial, and also delete the header information for those two columns (to delete a FRAME you right click it, then select DELETE from the popup menu).

 

Left click the Last Name FRAME inside the DETAIL band; the properties box for that frame should look like this:

 

frb_bm77

 

If you examine the old tutorial report you will notice that there is a LOT of blank space after the last name. 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 use 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.

 

Go ahead and delete the Contents information inside the "Properties" box for the frame, making it completely blank. Click the button with the flashlight (this is the Dictionary access button). This will display the DICTIONARY onscreen. Not only does the DICTIONARY maintain the database field information for your program, but is also a centralized place for accessing/using the different built in functions that the Report Builder has to offer. Here is screen capture showing where the CLIP() function is located inside the DICTIONARY:

 

frb_bm78

 

Go ahead and double click the CLIP(string) entry, which inserts it into the FRAME properties window:

 

frb_bm79

 

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 Last Name database field from the dictionary. Double click the Last Name database field, which changes the above screen to look like this:

 

frb_bm80

 

Now our frame is going to print the contents of the Last Name 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 the last name database field with a comma character, 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 Frame Properties screen looks like now:

 

frb_bm81

 

So far so good. We have the last name (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 First Name database field; however, remember that the first name field can also have blank spaces at the end, so we need to CLIP() that field to. We are combining the First Name field to the existing information, so we also need the Ampersand character to join them together. Go ahead and type in this information:

 

& clip()

 

and then click inside the '()' symbols where the First Name field will be inserted, then click the DICTIONARY button to display the database fields. Here is our revised screen:

 

frb_bm82

 

Now for the last part - adding the Middle Initial. This database field is only one position 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 last name 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 Middle Initial database field to make the Contents look like this:

 

frb_bm83

 

Now we need to change the PICTURE for this FRAME; originally it was as long as the Last Name database field allowed, which was 20 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:

 

frb_bm84

 

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 20 + 2 + 20+ 1 + 1 = 44 spaces. It is doubtful that both the last name and first name would fully occupy 20 spaces apiece, so it is up to you if you want to make the picture shorter.

Click on the tab titled 'Picture'; right now the picture is @S20; go ahead and type in @S34  If you find that some names are being truncated you can increase the length of the picture to accomodate the longer length.

 

Resize the last name FRAME to accomodate the increased length; you may need to move some FRAMES to the right to accomodate the extra length. Don't forget to change the column header, since it now is the full name -vs- just the last name! 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:

 

frb_bm85

 

 

Much better!  To recap: on this page 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

 

Next Tutorial Page