Tutorial 11-1. Creating a Report History Table

As a report designer, a useful tool to have at your disposal is a list of each report that gets printed and when it was printed. You can review this list on a periodic basis to see how the reports are being used. The reports that are printed the most can be reviewed to make sure they are completely optimized and use as few resources as possible. The reports that are rarely printed can be reviewed to see why people aren’t using them and if there are improvements that should be made to make them more useful.

In this tutorial, we are going to make it easy for you to track every report that gets printed and save this information to a database table. We are going to create a report which logs the report activity in a ReportHistory table in the database. You can then include this as a subreport in any report and you will be able to log every time someone prints that report. By including this subreport in all your reports, you will have a complete report history that tracks every report that gets printed within the company.

Before we create the report, you first need to create the ReportHistory table in your database. Since I don’t know what type of database you are using, I can’t exactly tell you the steps for doing this. But I can tell you the table structure so that you can create the table yourself.

The ReportHistory table has two fields: ReportName and ReportDate. The report name gets stored in the first field and the date that the report was printed is stored in the second field. The structure is shown below.

Table 11-1. ReportHistory table structure

Once you finish creating the ReportHistory table in your database, you are ready to start the tutorial to create the reports.

1. Let’s start by creating the report that uses a command object for logging the report activity to the database. Create a new report using the report wizard and after connecting to the data source, double-click the Add Command node to open the Add Command dialog box.
2. Enter the following SQL into the Add Command dialog box.

INSERT INTO ReportHistory
(ReportName, ReportDate)
VALUES
('Report History Subreport', GetDate());
SELECT 1 AS Dummy

1. This command consists of two separate SQL statements. The first is an INSERT INTO statement that creates a new row in the ReportHistory table. It lists the two fields to be modified and the data to populate them with. The data is listed inside the pair of parentheses. The first data is a string that represents the report name and the second is the GetDate() function which returns the current date and time.
2. Even though this SQL statement uses the report name ‘ReportHistorySubreport’, this is only a temporary name. Once you add this subreport to the main report, you will change this report name string to match the name of the main report.
3. The second SQL statement selects the value 1 and puts it in the field Dummy. This is to comply with the rule that an updateable command must return a value or else Crystal Reports won’t call it.
3. Click the OK button to save the command and return to the report wizard.
4. If you have an administrator tool to look at database tables (e.g. SQL Enterprise Manager), you can check if you entered everything correctly. Look at the ReportHistory table and see that it already has data in it. If you recall from Chapter 10, when you save the command object Crystal Reports actually sends it to the database to check its validity. Thus, if the command executed successfully, it will have recorded its first entry in the table.
4. Click the Next button on the report wizard to select the fields that should appear on the report.
5. In this case, the only field to choose from is the Dummy field. Double-click on it so that it is put in the Fields to Display window.
6. Click the Finish button to close the wizard and preview the report. All you should see is a simple report that shows the report date and the number 1.
7. Since the only purpose of this report is to execute the SQL statement in the command object, we really don’t want to see anything printed on the report. Delete the column header and the report date from the Page Header section, and delete the page number from the Page Footer section.
8. Now that the unnecessary fields have been deleted, suppress all the sections except the Details section. We want this report to take up as little space as possible.
9. All that is left on the report is the Dummy field. Right-click on it and select Format Field. Click on the Font tab and change the color to White. The Dummy field will print, but it won’t be visible.
10. Save this report as Report History Subreport.rpt. You can re-use it in all your reports to track their activity.

Now that the subreport has been created, we need to add it into an existing report to see how well it works. The next set of steps walks you through adding it to any report you have. These steps can be repeated for your entire report library if you wish.

1. Open up one of your reports and go into design mode.
2. Select the menu options Insert > Subreport.
3. On the Insert Subreport dialog box, select the option Choose an Existing Report and click the Browse button.
4. Find the file ‘Report History Subreport.rpt’ that you created in the last set of steps and select it.
5. When you return to the Insert Subreport dialog box, click the OK button to return to the main report.
6. Place the subreport object in the Report Header of the main report.
7. The report is almost finished. The only problem is that if we preview the report right now, it is going to update the ReportHistory table with the report name ‘Report History Subreport’. We need to modify it so that is uses the main report’s name instead.
7. Double-click on the subreport object to open it in design mode.
8. Select the menu options Database > Database Expert. This opens the Database Expert dialog box.
9. In the Selected Tables list on the right, you’ll see the command object you created. Right-click on it and select Edit Command.
10. Change the report name string from Report History Subreport to the name of the main report that you are working with. This is the report name string that will be saved to the ReportHistory table.
11. Click the OK button twice to save all your changes and return to the subreport in design mode.
12. Click on the main report’s design tab to bring it to the foreground. Then click the preview button to view the report. Since we hid everything on the subreport, the main report should look exactly the same as it did before you added the subreport. What you can’t see is that in the background it was updating the ReportHistory table to log the current date and time that you viewed the report.
13. Remember, if you don’t see the ReportHistory table being updated, it might be because you have the option to save the data with the report. Click the Refresh button to requery the database and force the command object to be executed.

As an additional challenge to this tutorial, let’s add one twist. In the SQL statement, we returned the value 1 in a report field that was hidden on the subreport. But rather than returning a piece of dummy data, why don’t we return something interesting that can be used on the report? It could be useful to print at the top of the report the number of times the report was printed during the month. We can use SQL to count how many times the report was printed and filter on the records for the current month. The following code block does just that:

INSERT INTO ReportHistory
(ReportName, UserName, ReportDate)
VALUES
(‘Report History Subreport’, User, GetDate());
SELECT Count(*) AS TimesPrinted
FROM ReportHistory
WHERE ReportName=’Report History Subreport’
AND Month(GetDate())=Month(ReportDate)
AND Year(GetDate())=Year(ReportDate)

The first half is the same as before. It simply inserts a new record into the table. The second half uses the Count(*) function to count the number of records that would be returned using the filter. The filter selects the records matching the report name and only if they were added during the current month of the current year.

I leave it to you to replace the subreport’s current command object with this new SQL statement. You will also have to modify the subreport to change the Dummy field’s font color back to Black and put some descriptive text beside it so that the user knows that the value represents how many times the report was printed that month.