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 through some tutorials to learn more about the Report Builder software and then use guides like this to help you connect to your ChilliDB database. Report builder can be downloaded from the following page: https://www.microsoft.com/en-us/download/details.aspx?id=53613
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” or “ChilliDB for Divisions Installation Complete”. Some older installations may have referred to “ChilliDB for Divisions” as “Division Information System”. 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 Microsoft SQL Server Report Builder.
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
1. Open Report Builder and then select File menu > New > Blank Report.
2. You need to choose a data source. On the left side of the screen there is a Report Data section containing a list of folders. Right click the ‘Data Sources’ folder to add one. Give your data source an appropriate name, select the embedded connection option then ‘Microsoft SQL Server from the dropdown. Click Build 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 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 OK to complete this process, and the popup will close itself.
4. Now you need to build your query by creating a dataset. Right click the Dataset folder on the left side of the screen to begin. Give your dataset an appropriate name, select the data source created earlier from the 'Data source' dropdown and click Query Designer to continue
5. The Query designer window lets you choose the views you want to include in your report, specify the relationships, and add any filters if necessary. The views are located to the left in the ‘Database view’ section, and as you add views the fields available appear in the ‘Selected fields’ section. Relationships are specified below this, all of your views must be related before you can save your query. Turn ‘Auto Detect’ off and add the links manually as it is usually not correct and can lead to missing or incorrect data.
6. The Dataset folder located to the left of your screen will contain your new dataset once it has been created. Expand it to show the list of fields.
7. Data can be displayed inside a Table, Matrix or a List. These are found in the Insert menu. Data can also be displayed in the body of the report however only the first instance of the field can be shown if placed inside the body.
8. Parameters can be added by right clicking the ‘Parameters’ folder in the Report data section.
9. More information about designing reports can be found here https://docs.microsoft.com/enus/sql/reporting-services/report-design
Uploading the Report into ChilliDB
To share this report with other ChilliDB users, you need to make some changes to it then upload the report into ChilliDB.
1. Go back and edit your data source and set the connection string to:
="Data Source=" & Parameters!ServerName.Value & ";initial catalog=" & Parameters!DatabaseName.Value2. Create 2 new hidden parameters. Please ensure that these 2 parameters are the top 2 items in the parameter list. They must have the following names:
ServerName
DatabaseName
ChilliDB will pass the server name and database name into these parameters when loading the report.
3. 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. After selecting a report, click the Upload button to upload the report into ChilliDB.
4. 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 Categorisation table
5. Give the report a title and description then click Save to complete this process, and you then will see your report within ChilliDB.
Uploading Integrated Reports into ChilliDB
In addition to uploading reports to the report screen, reports can be integrated into a selection of other screens instead. The reports are accessed from the screen’s action tab.
To integrate your report into a screen, a parameter with the appropriate parameter name needs to be created in the report. When the report is accessed, the screen will supply the ID to the parameter so the report can display data relevant to the page being viewed.
Report Writing TipsThe following tips will answer frequently asked questions and provide some expert tips to assist you with your report writing.
Database Connections – Creating: Reports should be written using Microsoft SQL Server to connect to your SQL Server
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 Report builder 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 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 IIF(IsNothing(Fields!rpt_Contact_Expired.Value),True,False) for Contacts,
o IIF(IsNothing (Fields!rpt_Organisations_Expired.Value),True,False) 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 Report Builder
Comentários