Creating a Custom Report from a SQL Query

This tutorial shows how to create a report from scratch using a SQL query. It is for more sophisticated users who are both familiar with the Crystal Reports designer and experienced with SQL. The tutorial explains how one of the standard Report Server reports, the Inactive Users report, was created. You can use the steps from this tutorial to create your own reports.

To create a custom report from scratch using a SQL query:

  1. In Crystal Reports, choose File > New > Blank Report. The Database Expert dialog box appears.

    Figure 1. Database Expert dialog box

  2. In the Available Data Sources box, open the Create New Connection folder, and then open the ODBC (RDO) folder.

    The Data Source Selection dialog box appears.

    Figure 2. Data Source Selection dialog box

  3. Select VoceraReports, and then click Next.

    The Connection Information dialog box appears.

    Figure 3. Connection Info dialog box

  4. For both the User ID and Password fields, enter "vocera". Click Finish.

    The Connection Information dialog box closes, leaving the Database Expert.

  5. In the VoceraReports folder, double-click Add Command. The Add Command To Report dialog box appears.

    Figure 4. Add Command To Report dialog box

  6. Enter the following SQL command in the box. This is the SQL command for the Inactive Users report:

    select * from users, sites where users.site=sites.siteID 
    and users.deletedflag = 0 and userid not in (select 
    Distinct UserID from Inventory force index(idx_TxDate) 
    where  {?DateRange_Clause}) and `UserID` <> '__NLI__' 
    and `UserID` <> '__PHONE__'  and  
     {?Site_Clause}  and  
     {?Dept_Clause}

    In the Parameter List box, click Create. The Command Parameter dialog box appears.

    Figure 5. Command Parameter dialog box

  7. In the Parameter Name field, enter DateRange_Clause. In the Value Type field, select String. For Default Value, enter 1=1.

    Click OK.

  8. Create parameters for Dept_Clause and Site_Clause. They are also String parameters whose default value is 1=1.

  9. Click OK in the Add Command to Report dialog box.

    The Enter Values page appears.

    Figure 6. Enter Values page

  10. Select 1=1 for each value, and then click OK.

    The Enter Values page closes, returning to the Database Expert. In the Selected Tables box, a new Command table is listed under VoceraReports. This is the SQL command you just created.

  11. Select the Command table, and press F2. Change the name of the table to Inactive.

    Figure 7. Inactive command

    Click OK.

  12. Now you need to specify how the fields should be grouped in the report. Choose Report > Group Expert.

    The Group Expert dialog box appears.

  13. In the Available Fields box, select the Inactive.Site field, and then click the button to move it to the Group By box.

    Select the Inactive.Departments field, and click the button again.

    Figure 8. Group Expert dialog box

    Click OK.

  14. In the Field Explorer, expand Inactive table and insert the UserID, LastName, and FirstName fields into the report.

    Afterward, the designer should look like this:

    Figure 9. Fields inserted onto the report

  15. Now let's also apply a Vocera template to the report. Choose Report > Template Expert. The Template Expert dialog box appears.

    Figure 10. Template Expert dialog box

  16. Click Browse. In the Open dialog box, navigate to the folder where Vocera standard reports are located. On the Report Server, the reports are installed in \vocera\reports\Reports.

  17. Select the report named VoceraReportTemplatePortrait.rpt, and then click OK.

    Note:

    Vocera also provides a template for landscape format named VoceraReportTemplateLandscape.rpt.

  18. Insert text objects in the Group Footer #2, Group Footer #1, and Report Footer to provide a count of inactive users for groups and for the report. Afterward, the designer should look like this:

    Figure 11. Inserted text objects to summarize groups

  19. In the Field Explorer, right-click Parameter Fields and choose New from the popup menu.

    The Create New Parameter dialog box appears.

    Figure 12. Create New Parameter dialog box

  20. In the Name field, type DateRange. For the Type field, select Date. In the Options list, scroll down until you see the Allow Range Values option. Change its setting to True. Click OK.

    Note: If you fail to change the setting of Allow Range Values to True, you will be unable to run the report. See Troubleshooting Problems with Custom Reports

  21. Add another parameter called CompanyName whose type is String.

  22. Choose File > Summary Info. The Document Properties dialog box appears.

  23. In the Title field, enter Inactive Users Report.

    Enter values in other fields as you choose, such as Author and Subject, and then click OK.

  24. Format the report to improve its look. Afterward, it should look like this:

    Figure 13. Finished report

  25. Choose File > Save to save the report. Save the report as CustomInactiveUsers.rpt.

  26. Copy the CustomInactiveUsers.rpt file to the \vocera\reports\Reports\custom folder on the Report Server computer.

    Now you are ready to add the report to the customconfig.xml file.

  27. On the Report Server computer, navigate to the \vocera\reports\Reports\custom folder and open the customconfig.xml file in a text editor.

  28. Add the following new report configuration to customconfig.xml. You do not need to type this text. Instead, you can copy the Inactive Users report configuration from reportconfig.xml, which is located in the \vocera\reports folder. You should make changes only to to the following elements in the configuration and save.

    There are many different parameters in this report configuration. For information about each type of parameter, see Configuring Report Parameters.

    Figure 14. New report configurations to customconfig.xml
    <REPORT ID="Custom Inactive Users">
      <NAME>Custom Report - Inactive Users</NAME>
      <DESCRIPTION>This report shows the users who have not 
        used the Vocera System during the specified date 
        range.</DESCRIPTION>
      <FILENAME>custom/CustomInactiveUsers.rpt</FILENAME>
      <TYPE>CUSTOM</TYPE>
      
    	
  29. From the Windows Start menu, select Settings > Control Panel > Administrative Tools > Services. The Services window appears.

  30. Stop the Tomcat service and then start it again.

  31. Close the Services window.

  32. Log into the Report Console.

  33. Click Custom Reports.

  34. Select Custom Report - Inactive Users and then click Generate.

    The Report Parameters page appears.

  35. Specify the report date range, site, and department(s), and then click Generate.

    The report should look like the following screen.

    Figure 15. Inactive Users report