• 1. Creating a report, using only "Table" datasets.
• 1a. Extending the report: include sub details and insert data (through relations).
• 1b. Extending the report: add an expression.
  
  
• 2. Creating a report using both SQL queries and "Table" datasets.
• 2a. Extending the report: insert data (through relations).
  
  
• 3. Searching for data using SQL based search reports.
• 4. Speeding up an SQL based report.
  
  
• 5. Some notes.
• 6. Downloadable examples.

In the now following example, we will build a standard report that gives us a summary of the artists found in the artist database MCBW1.DB:


• Startup MCBase and open the User Report Designer (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 "MCBase" as the database set to be used and select table "MCBW1" as the "Table name".
  These settings now declare table MCBW1 (the Artist 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 "MCBW1" table you wish to use in the report. For the sample, select the fields "Artist" and "Period". 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). 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 "My Report" 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...".

In this section, we will add a sub detail band to the report. For this example, we will add the data in the album database MCBW2.DB to the report and relate it's data to the artist database, which has been set as a Master database in the previous section:


• Add a new dataset to the report, using menu command "Report" - "Datasets..."
  Click Button "New table". In the now opening dialog, in the lower right corner, set the "Alias" to "MCBase". In the listbox on the left of the dialog, now appears the database files for the MCBase databaset set: select "MCBW2.DB" (the Album database) from this list and click on the "Open" button.
  
  
• You have now returned to the "Datasets" dialog. The focusbar is on the MCBW2 dataset (if not, click on the MCBW2 entry, to get the focusbar there). Now click the "Properties" button.
  In the now opening Properties dialog for the "MCBW2_Table", set the field "Mastertable" to "MCBW1_Table". Set the index to "AlbumArtistID". Click on the "..." labelled button to the right of the field "Linked Fields
  
  
• In the now open dialog "MCBW2_Table -> MCBW1_Table", you will make the relation between the album (from the album database) to the album's artist (from the artist database).
  By selecting the index "AlbumArtistID", the field "DetailField" has been set to the field "AlbumArtist" (holds the relational ID code for the album's artist).
  Set the "MasterField" combobox to the field "Artist_ID" and click the "Add" button.
  In the "Joined Fields" field, you will now see the firm connection represented between these two fields: "AlbumArtist -> Artist_ID".
  Now click the "OK" button.
  
  
• You're now back in the "MCBW2_Table's" Properties dialog. Click on the "Ok" button.
  
  
• You're now back in the "Datasets" dialog. Click on the "Ok" button.
  
  
• You're now back on the design interface of "My Report".
  
  
• Add a new sub detail band, by clicking on the appropriate button on the left side of the User Report Designer window, after which you click on the unused portion of the "My Report" report. The band's configuration dialog opens up ("Band1").
  
  
• In the dialog's top, set the "Dataset" combobox to "MCBW2_Table".
  The "Master" field should read "MCBW1_Table".
  
  
• Click the "Ok" button.
  
  
• Click on the "Add a new datafield" button on the left side of the User Report Designer window and then click on the position in the "Band1" sub detail band where you would like to position the field. For this example, place it on the left side of the sub detail band.
  When you click on that position, a dialog will pop up, requiring you to set some fields:
  
  • Please select a table: select the "MCBW2_Table" and click the "Ok" button.
    
  • Properties for the new "Field MCBW2_Table":
    Table is: MCBW2_Table.
    Field is: Album
    For this example, leave the other settings as they are and click the "Ok" button.
  
  
  
• You have now added a subdetail band to the basic report: the report now summarizes all albums for each artist in the database.
  
  
• If column names are required for the sub detail band, you may want to expand the detail band down and add the labels required at the bottom of the detail band. For cosmetic purposes, you may want to give the fields and labels on the detail band a background color, so that the detail band and sub detail band are easily recognized. You may also want to use font settings.
  These items can be easily set, by clicking on the object you wish to modify after which you click on the right mouse button - this will open up a menu box: select "Edit", which opens up a dialog in which you will be able to find the various settings: play around with these to get a feeling about their use.
  

• When you have the "My Report" open in the User Report Designer, click on the detail band, so that it gets the focus. Then click your right mouse button, to invoke it's local menu and select "Edit".
  
  
• The field "Only print if expression is true" is where we will enter our expression. To do this, click the "..." labelled button to the right of the expression field: this will open the built in Expression Builder.
  
  
• In the Expression Builder, in the dialog's "Data Field" group, set the dataset to "MCBW2_Table". Then, in the "Field" listbox, select field "AlbumArtist" and click the "Add" button (in the "Data Field" group).
  The first part of our expression is now inserted in the Expression box at the bottom of the "Expression Builder" dialog.
  
  
• Go to the "Operators" group, on the right side of the Expression Builder, and click the "=" button (which is immediately inserted in the Expression box.
  
  
• Go to the "Data Field" group again and now set the dataset to "MCBW1_Table". Then, in the "Field" listbox, select field "Artist_ID" and click the "Add" button (in the "Data Field" group).
  
  
• As you will now see, we have built following expression: "MCBW2_Table.AlbumArtist = MCBW1_Table.Artist_ID".
  
  
• Click the Expression Builder dialog's "Ok" button.
  
  
• Click the Detail band dialog's "Ok" button.
  
  
• You are now returned to the User Report Designer. You may press the preview button (icon = paper with magnifying glass) to view the result. You should find, that now only those artists that are actually related to an album, are being printed.

• Startup MCBase and open the User Report Designer (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 "MCBase" as the database alias to be used. In the larger input field, with the "SQL" button, you may manually enter your SQL query. When not familiar with SQL, you will want click the "SQL" button, to invoke the "SQL Wizard".
  For the sake of this example, we will assume, you are NOT familiar with SQL and we will use the "SQL Wizard". Click on the "SQL" button.
  
  
• We have now arrived in the "SQL Wizard", on the tab "Table".
  The name of the query is "Query1" - when required, you can change that to a more recognizable name. For the example, we will leave it as it is: "Query1".
  The name of the database or directory has been copied from the previous dialog and is already set at "MCBase". We will leave that as it is also.
  The field containing the "Table name" is currently still empty. Access that combobox and set it to "MCBW6.DB" (the table holding the songs - info on which table holds which data can be found in MCBase's online helpfile).
  
  
• We now access the next tab page in the "SQL Wizard" by clicking on the "Sort order" tab.
  For our example, we will want the songs sorted by title. This requires only one setting in the "1st index field": access this combobox and select the field "Song" in order to sort the data on song title. If required, you may alter the setting "Ascending" to "Descending": for the example, we will leave it at "Ascending".
  
  
• We now access the next tab page in the "SQL Wizard" by clicking on the "Filtering" tab.
  For our example, we wanted those songs that were not related to a category: so we will want those songs, for which the field "category" is left blank.
  Four filters can be applied on this tab: access the first row in the dialog and set it's "Fieldname" combobox to "SongCategory".
  Then set the "Comparison" combobox to "is blank".
  
  
• If you like, you can preview the data that is a result of this simple SQL query, by accessing the "Preview data" tab.
  Otherwise you can click the "Ok" button to finish the "SQL Wizard"
  
  
• We are now returned to the "New Report Definition" dialog. The text of our "SQL Wizard" activities is inserted in the SQL field.
  Click on the "Next" button to proceed.
  
  
• We now arrive at the section where we can select the fields to be included on the report's page layout.
  For our example, we select the field "Song" (for the songtitle) only. (The actually related artist and/or albumtitle are not accessible from here and need to be added seperately, using the appropriate tables and relations).
  To select the field "Song", click on "Song" and then click on the ">" button.
  Now you selected the field "Song" into the "Fields used in report" list. Click the "Next" button.
  
  
• We now arrive at the section "Please select which additional bands should be added to the report". For now, we will leave this as it is. Click the "Next" button.
  
  
• We now see the "General report options". Change the "Report title" to "Songs without category relations" and set the page orientation to "Landscape" (we need the extra space for the artist name and album title, which we will add lateron). Now click the "Next" button.
  
  
• We now are shown some options to change to 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.
  
  
• Our first setup of the report is now finished. You may preview the layout by clicking the "Preview" button or close the "New Report Definition" dialog by clicking the "Finish" button. When you press the "Finish" button, you are returned to the User Report Designer's design interface. (Don't forget to use the "File" - "Save as" command to store your new report!!).
  

• First, we will setup the relational link between the songtitles (gathered through the SQL query) and their related artists.
  To do this, click on the menu commands "Report" - "Datasets", which will open the "Datasets" dialog. You will see that the "Main Report Table" is currently set to "Query1" - the SQL query with which we gathered the songtitles, outlined in the above section.
  
  
• Click on the "New Table" button at the bottom of this dialog. In the now open dialog, set the database alias combobox (located in the lower right corner of this dialog) to "MCBase".
  You will now see the MCBase database files listed in the listbox in the upper left corner of this dialog. Let's set the relational link for the artist database: select the file "MCBW1.DB" and click the "Open" button.
  
  
• We are now returned to the "Datasets" dialog, where the MCBW1.DB table is focused and has received the name "MCBW1_Table". Now click on the "Properties" button to make the required settings for the artist relation.
  In the now open "MCBW1_Table" properties dialog, we see that the database's name is "MCBW1_Table" (if required, you could change that name here - for this example, we will leave it as it is).
  
  
• Then we see the field "Index". The relational link from the song database (MCBW6.DB) to the artist database (MCBW1.DB) is based on the artists relational ID code. (See the MCBase's online help file for background info). Because of this basis, it is required that we take the Artist ID index as a the active index: set the index combobox to "ArtistIdNdx".
  
  
• Then we see the third group, with first the field "Master table": the master table for this report is "Query1", so we will set this "Master table" combobox accordingly - select "Query1" in this combobox.
  
  
• Then we arrive at the "Linked fields" field. This is the field where the actual relation is set: click on the "..." button, to the right of this field.
  We now see a dialog labelled "MCBW1_Table -> Query1". The "Detail field" is set to "Artist_ID" (because we had selected it's index - this setting is correct as it is). Next is the "Master field": we need to set this combobox to a field that we can match with the contents on the "Detail field" ("Artist_ID") - therefor, set the "Master field" combobox to the field "SongArtist" (since it contains the relational ID of this song's performing artist).
  Now click the "Add" button. As a result, you will see the relation "Artist_ID -> SongArtist" inserted into the "Joined Fields" area. Now click on the "Ok" button, to close the "MCBW1_Table -> Query1" dialog.
  
  
• We are now returned tot he "MCBW1_Table" properties dialog. Click the "Ok" button to close this dialog also.
  
  
• We are now returned to the "Datasets" dialog. Click the "Ok" button to close this dialog too.
  
  
• We are now returned to the User Report Designer's design interface. You now need to select the menu commands "Edit" - "New" - "Data field". Next, click on the position next to the "Song" field that is positioned in the report's "Detail band".
  The "Selection" dialog will now pop up, in which the two data sources we have used so far (a query and a table) are available. Select the "MCBW1_Table" and click the "OK" button.
  
  
• The "Datafield MCBW1_Table" dialog comes up now. By default, these settings are mostly correct and sufficient for our purpose - for our example, you should only make sure that the "Autosize width" option is checked. Now click the dialog's "Ok" button. The "Artist" field is now inserted into the "Detail band".
  
  
• Please do not forget to save your new report? Then you can execute the menu commands "File" - "Preview" to see what we have achieved so far.
  
  
• If you wish, you can add the related album title (or any other related field for that matter) in the same manner, by following again the steps as outline here in section 2a. Extending the report: insert data (through relations).

• Startup MCBase and open the User Report Designer (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 "MCBase" 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 'Mcbw5.DB' Q1
  WHERE (UPPER(Q1.'Category') LIKE UPPER(:Category_name))
  ORDER BY Q1.'Category'
  
  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 "Category_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 "Category".
  Therefor, point your mouse to the "Available fields" list and click on the fieldname "Category". Next, click on the top button - with the arrowhead pointing toward the right.
  As you will see, this transports the fieldname "Category" 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 "Songs by category".
  Also - since we plan to add a few more fields - set the page orientation to "Landscape" by selecting the appropriate radio button. Then 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 - but since there wouldn't be much to see yet, you might as well select the "Finish" button.
  Should you decide to "Preview", you'll get to see the search phrase input dialog. Enter a category 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?
  Now go back to the design interface of the "Songs by category" report.
  
  
• When you look at the design interface, now in front of you, you will notice the "Category" field in the Detail band (currently the last band on the design interface). In order to report the songtitels related to any and all categories matching our search phrase, we will now need to add the song database "table" and a new "sub detail" band.
  
  
• Add a new dataset to the report, using menu command "Report" - "Datasets..." Click Button "New table". In the now opening dialog, in the lower right corner, set the "Alias" to "MCBase". In the listbox on the left of the dialog, now appears the database files for the MCBase databaset set: select "MCBW6.DB" (the Song database) from this list and click on the "Open" button.
  
  
• You have now returned to the "Datasets" dialog. The focusbar is on the "MCBW6_Table" entry (if not, click on the "MCBW6_Table" entry, to get the focusbar there). Now click the "Properties" button.
  In the now opening Properties dialog for the "MCBW6_Table", set the field "Mastertable" to "Query1" (we had not renamed our generated query yet). Set the index to "SongCategoryID" and click on the "..." labelled button to go to the "linked fields" dialog.
  
  
• In the now open dialog "MCBW6_Table -> Query1", you will make the relation between the song (from the song database) to the category (from the category query).
  By selecting the index "SongCategoryID", the field "DetailField" has been set to the field "SongCategory" (holds the relational ID code for the song's category).
  Set the "MasterField" combobox to the field "Category_ID" and click the "Add" button.
  In the "Joined Fields" field, you will now see the firm connection represented between these two fields: "SongCategory -> Category_ID". Now click the "OK" button.
  
  
• You're now back in the "MCBW6_Table's" Properties dialog. Click on the "Ok" button.
  
  
• You're now back in the "Datasets" dialog. Click on the "Ok" button.
  
  
• You're now back on the design interface of "Songs_by_category".
  
  
• Add a new sub detail band, by clicking on the appropriate button on the left side of the User Report Designer window, after which you click on the unused portion of the "Songs_by_category" report. The band's configuration dialog opens up ("Band1").
  In the dialog's top, set the "Dataset" combobox to "MCBW6_Table".
  The "Master" field should read "Query1". Then click this dialog's "OK" button
  
  
• Click on the "Add a new datafield" button on the left side of the User Report Designer window and then click on the position in the "Band1" sub detail band where you would like to position the field. For this example, place it on the left side of the sub detail band.
  When you click on that position, a dialog will pop up, requiring you to set some fields:
  
  • Please select a table: select the "MCBW6_Table" and click the "Ok" button.
  • Properties for the new "Data field MCBW6_Table":
    Table is: MCBW6_Table.
    Field is: Song
    For this example, leave the other settings as they are and click the "Ok" button.
  
  
  
• You have now added a subdetail band to the initial report: the report now summarizes all songtitles for each category in the database that matches an input phrase.
  
  
• Adding additional data to this report, as for instance the related artists for the reported songs is done in the same manner as described above, under "Extending the report: include sub details and insert data (through relations)". These additional fields may be added to the same sub detail band that contains the songtitle data.
  
  
• If column names are required for the sub detail band, you may want to expand the detail band down and add the labels required at the bottom of the detail band. For cosmetic purposes, you may want to give the fields and labels on the detail band a background color, so that the detail band and sub detail band are easily recognized. You may also want to use font settings.
  Alternatively, you add the labels to the column header band. This will allow you the column labels to appear correctly on each new page.
  All these items can be easily set, by clicking on the object you wish to modify after which you click on the right mouse button - this will open up a menu box: select "Edit" and or "Options", which opens up a dialog in which you will be able to find the various settings: please feel free to play around with these to get a feeling about their use.

3. Speeding up an SQL based report.

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

• 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.
  
  
• 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).