• 1. Creating a report, using only "Table" datasets.
• 2. Searching for data using SQL based search reports.
• 3. Speeding up an SQL based report
• 4. Some notes
• 5. Downloadable examples.

1. Creating a report, using only "Table" datasets.

• Startup PictureBook and open the User Report Designer (URD)(by clicking the menu commands "Print" - "User Report Designer").
  
  
• Create a new report (by clicking menu commands "File" - "New"). The "New Report Definition" dialog box comes up.
  
  
• For this example, select the second report type in the dialog, "List type" and click the "Next" button.
  
  
• The "New Report Definition" dialog now requires you to indicate if the main dataset for this report is a "Table" or a "Query". For this example, select "Table".
  In the section below, select "PictureBook" as the database set to be used and select table "PBDATA" as the "Table name".
  These settings now declare table PBDATA (the PictureBook database) as the main database for this report. Now click on the "Next" button.
  
  
• The "New Report Definition" dialog now requires you to indicate which fields from the "PBDATA" table you wish to use in the report. For this example, select the field "Title". Now click on the "Next" button.
  
  
• The "New Report Definition" dialog now requires you to select any additional bands that should be added to the report. (For this report, the "Detail band" will appear as a minimum and is therefor selected and grayed out). For this example, we will not add any additional bands. Now click on the "Next" button.
  
  
• The "New Report Definition" dialog now presents you the opportunity to change some of the General Report Options. For this sample, we will change the Report Title to "PictureBook image listing" and colums to "1". Set the page orientation to "Portrait". Now click on the "Next" button.
  
  
• In the next phase of the "New Report Definition" dialog, some more options can be set. For now, leave them as they are and click on the "Next" button.
  
  
• You've now reached the finish of the "New Report Definition". Press the "Preview" button to see the result so far...
  
  
• In order to save this example, use the URD's menu commands "File" - "Save as...".

2. Searching for data using SQL based search reports

• Startup PictureBook and open the User Report Designer (URD)(by clicking the menu commands "Print" - "User Report Designer").
  
  
• Create a new report (by clicking menu commands "File" - "New"). The "New Report Definition" dialog box comes up.
  
  
• In the now open "New Report Definition" dialog, select the report type "List style" and click the "Next" button.
  
  
• In the top section of the current dialog, you may select the type of "Main dataset for report" required: select "Query".
  
  
• In the lower section of the current dialog, you may select the database or directory required. Select "PictureBook" as the database alias to be used. In the larger input field, with the "SQL" button, you may manually enter your SQL query.
  Enter following query in this input field:
  
  SELECT * FROM 'PBDATA' Q1
  WHERE (UPPER(Q1.'Title') LIKE UPPER(:Title_name))
  ORDER BY Q1.'Title'
  
  Then click the "Next" button. (For a description of the commands mentioned here, see the URD's online helpfile)
  
  
• You will now come to see a dialogbox, named "Parameters". This is caused by the inclusion of the ":" character - any word following the ":" character is regarded as being an SQL parameter. The "Parameters" dialog allows you to set the preferences as required for this particular parameter.
  
  For our example parameter "Title_name", we will select Datatype = string and we select radio button "Ask value before printing", since we want to put in our search phrase just before the query executes.
  
  
• At this stage, we are presented with a dialog that allows us to select the fields which we want to use in the report the URD is currently generating for us. For now, we only need the field "Title".
  Therefor, point your mouse to the "Available fields" list and click on the fieldname "Title". Next, click on the top button - with the arrowhead pointing toward the right.
  As you will see, this transports the fieldname "Title" from the "Available fields" list to the "Fields used in report" list. Now, press this dialog's "Next" button.
  
  
• We now get to the final stages of the initial layout. The currently open dialog invites us to select any additionally required bands (summary and footer bands), but for our example, we will leave those for what they are. Proceed by selecting this dialog's "Next" button.
  
  
• We arrive at the "General report options" dialog. For this example, we will change the report title "Report" to something more elaborate as "PictureBook image search results".
  Now click this dialog's "Next" button to proceed.
  
  
• We now are shown some options to change the fonts used and add some lines in the layout as generated so far. For the example, it is not required to change these. Click the "Next" button.
  
  
• The initial layout is now ready. You may either "Finish" or "Preview" or whatever you like.
  Should you decide to "Preview", you'll get to see the search phrase input dialog. Enter a title name which you expect to be present in your database en click the "OK" button... You may want to repeat this preview using only the SQL wildcard character "%" as a search phrase... All present categories are now listed - they all match, since we only entered the single wildcard character. Not bad for a first SQL query attempt, right?
  
  
• In order to save this example, use the URD's menu commands "File" - "Save as...".

3. Speeding up an SQL based report.

• An example of such an SQL assignment:
  
  SELECT Title, DBName.'Date' FROM 'PBDATA' DBName
  WHERE (UPPER(DBName.'Title') LIKE UPPER(:Title_name))
  ORDER BY DBName.'Title'
  
  This example shows how to select only the fields "Title" and "Date" from the database set PBData.
  
  By the way, also shown here are various field naming possibilities: you can access the field "Title" in PBData by calling it directly OR by calling it in relation to the database it's in.
  You will have seen the name "DBName" in the first line: in this manner, we declare the name "DBName" as an variable that contains the reference to the "PBData" database. Referring to database fields in this manner (DBName.'Field') is especially useful when trying to access fields like the "Date" field, since the term "Date" is also a reserved word in the SQL language or fields with identical names in two (different) databases.

4. Some notes.

• If you would like to create or change a User Report Designer report, then you can find all required information in the User Report Designer's online help facility. Nevertheless, should you have problems creating the report you need, feel free to contact us.
  
  
• The URD's database image display function is currently not able to dynamically resize an image for optimal display (as is the case with PictureBook's standard printjob's). We will investigate the possibility of adding this functionality to the URD as well.
  
  
• If you would like to access a tutorial on the SQL language, please see the last links beginning with "LocalSQL..." on our "Using the SQLBox User Report Designer (URD)"-page. There, you will find helpful links to both the Paradox community and to Xploiter.com.
  
  
• Should you seek a method to export (part of) your data to a plain text file, then this is - of course - always possible, by using the standard facilities built into the Windows operating system(s), to "print" data directly to a text file.
  Create your own data layout in the User Report Designer - or - select a suitable data layout from the standard printjobs available and then select system printer driver "Print to textfile". Then follow the directions on screen (or see your Windows (online) manual for further info).