If you would like to create your own reports in ChilliDB, you can use this article to step through creating a simple report against your ChilliDB database.
Polymorphic Solutions recommend that you would benefit from going on a Crystal Reports course to learn more about the Crystal Reports software product and then use guides like this to help you connect to your ChilliDB database.
If you decide that you do not want to learn to write your own reports, Polymorphic Solutions can develop report templates for you using helpdesk support hours.
When ChilliDB was installed, a member of your organisation would have received an email with a subject of either “ChilliDB Installation Complete”. This email is important as it includes information for your Report Writer (for the ChilliDB website and reporting help), your Systems Administrator (for backups), and your key user (for the ChilliDB website, and lodging support requests). The following are examples of part of the content from that email which deals with the Report Writer information – some parts of your email of course will be different; however the email will have your information in it
Getting Started, identifying the ChilliDB data
There is a special document which will identify all of the database views which make the data available for report writers in an easy to use and understand format. It will identify how to link the views together to join the related information so for example, you may select basic information about a Contact such as their Name, Gender, and then also include information about their Organisations, their Address and Communication details. This document is called the Reporting Interface Database Layer, or Reporting Interface document for short - obtain a copy from the article listing located on the ChilliDB Helpdesk.
In the next sections of this document, you will step through the process of connecting to a ChilliDB database, selecting some Contact data for reporting on, and then the creation of a sample report in either Crystal Reports XI or Crystal Reports 2008 report designer. Once you have created your report, this document will also step you through uploading this report into ChilliDB to share with other users. Finally, you should also review the tips in the Report Writing Tips section of this document which will help you to produce great reports.
Creating a Report - version XI of the Report Designer
The following steps are for clients using Crystal Reports XI
1. Open Crystal Reports and then select File menu > New > Standard Report.
2. You need to choose a data source. While doing this, we will add the database to the Favourites section of the tree view, so next time you will be able to expand that and see your database there to use for future reports. Expand the Create New Connection tree view item. Expand the OLE DB (ADO) tree view item, and the dialog box shown below will appear. Scroll down until you find Microsoft OLE DB Provider for SQL Server, and click Next to continue.
3. Using the information described at the top of this document (i.e. from the email it discusses), fill out the connection details for the Server (the SQL Server Name), User Id (the SQL Server Login), Password (the SQL Server Password), and Database (the SQL Server Database). In the following image (Figure 6). Click Finish to complete this process, and the popup will close itself.
4. You should see your server listed under the tree view. Right click on the name of your server, and select Add to Favorites as shown below in Figure 6. mYou will not have to do steps 2 and 3 again next time you open Crystal Reports.
5. Under your server shown in Figure 7, you will see the database name, in this case DGP_DIS. Expand that item in your tree view, and under dbo you will see Tables and Views. ChilliDB comes with a clearly defined reporting interface (available from the ChilliDB website) based on database views which allow you easy access to ChilliDB data in a logical way, saving you the need to gain an understanding of the tables within your database. Expand the Views item and select rpt_Contact, and click on the arrow button to add it to the Selected Tables part of the window. This view represents your contact data, and will be used in this walk through. Select Next to continue.
6. We now will select what fields should be displayed on the report. Select as many fields as you like, or all of them, and click on the arrow button to add them to the Fields to Display part of the window. Click Next to continue.
7. You can now choose to Group your data. In the left part of the window, you will see all the fields currently on your report, as well as any from the rpt_Contact view which we selected above. Select Type from either of the two sections of fields on the left hand side, and click the arrow button to add it to the Group By part of the screen. Click Next to continue.
8. The next step is to add some summary data to your report. Leave the standard ones and click Next to continue.
9. The next step will allow you to add a chart to your report. Leave this as is, and click Next to continue.
10. The next step will allow you to filter your report data (perhaps to Contacts who’s surname begins with ‘S’). Leave this as is, and click Next to continue.
11. The next step will allow you to apply a template to your report. Select No Template and click Finish to complete your report.
12. Optionally, you may set some File Properties for this report, a name and description which ChilliDB will automatically then extract when you upload the report into ChilliDB. In Crystal Reports, select the File menu > Summary Info and provide a Title and Description. 13. You have now created your first report.
14. You should now save this report to the file system somewhere, call this your “report repository” or “report source”, where you keep all of your report templates.
15. You may close Crystal Reports now.
Creating a Report – version 2008 of the Report Designer
The following steps are for people using the 2008 version of Crystal Reports.
1. Open Crystal Reports and then select File menu > New > Standard Report.
2. You need to choose a data source. While doing this, we will add the database to the Favourites section of the tree view, so next time you will be able to expand that and see your database there to use for future reports. Expand the Create New Connection tree view item. Expand the OLE DB (ADO) tree view item, and the dialog box shown below will appear. Scroll down until you find Microsoft OLE DB Provider for SQL Server, and click Next to continue.
3. Using the information described at the top of this document (i.e. from the email it discusses), fill out the connection details for the Server (the SQL Server Name), User Id (the SQL Server Login), Password (the SQL Server Password), and Database (the SQL Server Database). In the following image (Figure 6)
Note, in my case, my server is called MyServer, and my server is a named database server instance called ChilliDB. My database is called ChilliDB
Click Finish to complete this process, and the popup will close itself and you will see the following screen.
Under your server name, you will see the database name (ChilliDB). Expand that item in your tree view, and under dbo you will see Tables and Views. It is strongly recommended that you use the database Views rather than the tables.
4. Expand the Views item and select rpt_Contact, and click on the arrow button to add it to the Selected Tables part of the window. This view represents your contact data, and will be used in this walk through. Select Next to continue.
5. We now will select what fields should be displayed on the report. Select as many fields as you like, or all of them, and click on the arrow button to add them to the Fields to Display part of the window. Click Next to continue.
6. You can now choose to Group your data. In the left part of the window, you will see all the fields currently on your report, as well as any from the rpt_Contact view which we selected above. Select Type from either of the two sections of fields on the left hand side, and click the arrow button to add it to the Group By part of the screen. Click Next to continue.
7. The next step is to add some summary data to your report. Leave the standard ones and click Next to continue.
8. The next step will allow you to add a chart to your report. Leave this as is, and click Next to continue.
9. The next step will allow you to filter your report data (perhaps to Contacts who’s surname begins with ‘S’). Leave this as is, and click Next to continue.
10. The next step will allow you to apply a template to your report. Select No Template and click Finish to complete your report. Your report will execute and you will see your data displayed on the report.
11. Optionally, you may set some File Properties for this report, a name and description are the most obvious ones. When you upload the report into ChilliDB, ChilliDB will give you options to extract this information to automatically fill in the Upload screen with the same Title and Description you enter here. In Crystal Reports, select the File menu > Summary Info and provide a Title and Description.
12. You have now created your first report!
13. You should now save this report to the file system somewhere, call this your “report repository” or “report source”, where you keep all of your report templates.
14. You may close Crystal Reports now and proceed to login to ChilliDB to upload the report for other users to view
Uploading the Report to into ChilliDB
To share this report with other ChilliDB users, you should upload the report into ChilliDB.
1. To share this report with other users of ChilliDB, login to ChilliDB and navigate to the Reports menu > select Upload > select the Find link produce a window which will let browse for your report in the “report repository” or “report source” (where you chose above to save your reports into).
2. After selecting a report, click the Upload button to upload the report into ChilliDB. Click the Read Title and Description From Report File button to populate the Report Title and Report Description information straight from the report file (if you performed Step 12 above in Creating a Report) or enter information for those fields manually.
3. Select a Report Type to classify this report. You may add additional Report Type values through the Reference Table Manager within ChilliDB, where you would add items to the Report Type table
4. Click Save to complete this process, and you then will see your report within ChilliDB.
Report Writing Tips
The following tips will answer frequently asked questions and provide some expert tips to assist you with your report writing. Tweaking your Crystal Reports Options
Crystal Reports – Automatic Smart Linking: We recommend that you avoid using the Auto Link feature within the Crystal Reports Database Expert. What it will do is try to link tables/views based on their field name or data types, however it often this is not correct and can lead to missing data or incorrect data shown in your report. You can set this as your preference when writing all reports in Crystal by going to File menu > Options > Database tab of the popup which appears > set Automatic Smart Linking to off.
Crystal Reports - Save Data with Report: This will feature allows the report to cache data shown in the report from the database when writing the report. When ChilliDB runs the report, it shows the cached data, not live data. It is best if you disable this preference through the File menu > Options > Reporting tab of the popup which appears > set Save Data with Report to off. If you decide to use Saved Data while authoring your report, you can check the status or enable/disable this feature from the File menu > Save Data with Report. If you receive a report from someone, you should check this option is off before uploading into ChilliDB.
Crystal Reports - No Printer: We recommend turning this option off to increase the speed of displaying reports in ChilliDB. You need to do this for each report you write (i.e. it’s not a configuration option). This option is found on the File menu > Page Setup > Printer Options sections of the popup window which appears > set No Printer (optimized for screen display) to being checked.
Report Writing Tips for ChilliDB
Database Connections – Creating: Crystal Reports should be written using OLE DB (ADO) to connect to your SQL Server. Do not use ODBC to connect to ChilliDB.
Polymorphic Solutions Reporting Views - Polymorphic Solutions provide a reporting layer to our database using Database Views. Our reporting views are prefixed with rpt_.
Creating your own Customised Views: When the Polymorphic Solutions Reporting Views aren’t sufficient for your needs, you may choose to create custom database views, tables, or stored procedures (collectively referred to as Database Objects) within your database. To ensure that updates to the system do not overwrite your customised database objects, Polymorphic Solutions recommends that you prefix your custom Database Objects with a prefix. We recommend using your organisation prefix. For example a view showing Contacts in Brisbane for a company called ACME Inc would use create a view named acme_Contacts_In_Brisbane. The rpt prefix is reserved and should not be used for your custom reporting views.
Automatic View Generation – Custom Fields automatically generates Reporting Views for each Section of Custom Fields.
Renaming Custom Field Sections and Items – if you notice that a report no longer works and you then load it into Crystal Reports and it informs you that a field cannot be found, it is because the field may have been renamed within ChilliDB by one of your users. You should correct the report and then upload it back into ChilliDB.
Report Branding – Using information from the Crystal Report branding view to display your company details will allow you to easily share your reports with other ChilliDB users in the community. You can configure the information displayed in this view by selecting Reports > Report Branding from the ChilliDB menu. From that screen you configure your display name, address details, privacy or confidentiality statement and even upload your organisation logo image to be included in your reports.
Modifying or Copying Polymorphic Solutions Reports: You are free to modify the Polymorphic Solutions reports and customise them to suit your needs. To avoid them being overwritten, you should make a copy and give them a different name.
Filtering out Expired records: ChilliDB records a date in the Expired field of Contacts and Organisations reporting views as a datetime value, meaning if a datetime is present, that record is expired. If it is not expired, it be NULL. Example formulas for Contacts and Organisations would be:
o IsNull ({rpt_Contact.Expired}) for Contacts,
o IsNull ({rpt_Organisations.Expired}) for Organisations
Date Time Values are stored in Greenwich Mean Time (GMT) time: ChilliDB stores database date time data in Greenwich Mean Time (GMT) and displays the date and time in localised time through the web application. All rpt_ reporting views automatically convert the GMT date time values to localised date time values for you.
Training Options
You may want to take a look at some of these resources to learn more about Crystal Reports. While they are using a later version, it will be very similar if not the same for most activities.