... ... How to create a Microsoft Reporting Services Report using your ChilliDB data | | chillidb

Contact Us

Polymorphic Solutions

Level 13, 97 Creek Street
Brisbane, QLD, 4000

Australia


info@chillidb.com

Tel: 1300 65 72 43

About

Newsletter

Video

Partners

Pricing

Terms of Use

Privacy

Security

ChilliDB is a Registered Trademark of Polymorphic Solutions © 2019

Dec 20, 2018

How to create a Microsoft Reporting Services Report using your ChilliDB data

0 comments

Edited: Jul 1

 

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 Report1. 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.Value

2. 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

https://docs.microsoft.com/en-us/sql/reporting-services/report-design

Was this article helpful?

New Posts
  • Sometimes you want to secure certain Reports to only certain Users or Roles of Users. ChilliDB allows you to do this through Report Security, which you find under the Reports menu, the menu item is called Report Security. Report Security allows you to configure Report Types to certain User Roles. By default in ChilliDB, all Report Types are visible to all User Roles. When you start securing reports, you need to then review and likely set permissions for every Report Type. You do this by selecting each Type from the Drop Down List, then you can uncheck the "All Roles" checkbox and choose zero, one or more User Roles who can see reports of that Type. Work through all Types in your System setting permissions. Was this article helpful?
  • This article describes how to quickly send reports to contacts in ChilliDB. This is functionality is for SSRS (.rdl) type reports only. It is not available for Crystal Reports. With SSRS reports we offer the functionality to quickly send an email with the currently loaded report attached. 1. Load your report and click the email report link located at the top right of the screen 2. This will open the send message window with the report attached 3. Complete the form like normal, all recipients will receive the report as an attachment Was this article helpful?
  • Did you know that you are able to integrate reports into many screens throughout ChilliDB and then access those reports from screens within ChilliDB. This is in addition to the reports you run from the standard report listing available under Reports > Display. Some examples of where this will be most useful include: Print reports related to a specific Event such as certificates or signatory sheets from the Events display screen Print Summaries of Notes related to a Contact or Organisation from the Contacts or Organisation display screens Print a Contact sheet for all Contacts belonging to an Organisation from the Organisation display screen Print Membership renewal reminders for a Membership Package from the Membership Package display screen This Fact Sheet will help you integrate your reports into specific screens within ChilliDB On the screens that you selected to link your report with, ChilliDB will pass the record identifier for the record being displayed on the screen to the report. All you need to do to take advantage of this is: Create your report with a parameter to accept that record identifier. Your parameter should be specially named based on the available parameter named found in the next section of this article. Upload your report into ChilliDB, flagging the report as an integrated report and select which screen it should be linked to. Your reports may have additional parameters and ChilliDB will prompt you for only those additional parameters and pass the record identifier automatically. For example, if you were printing a Contact sheet for all Contacts for an Organisation, you may like to have a second parameter to prompt the user to filter down the Contacts based on their Type. Available Parameter Names for Integrated Reports ChilliDB needs to know which screen is running the report so that it can find the record identifier for that screen to pass to your report and finally, the name of the parameter in your report to place the record identifier into. Therefore, when you create your report you need to create parameters named in a special way to accept that record identifier if you would like your report to be an integrated report. ChilliDB can only pass the record identifier which identifies a Contact, Organisation etc. that is being displayed in ChilliDB. It is not able to pass identifiers from records within a grid, or identifiers associated with the record being displayed in ChilliDB. The following table outlines the parameter names which you are able to use in your integrated reports. All parameters are of data type “Number” ("Integer" if you are using report builder) Linking to ChilliDB screens You can associate the report with a ChilliDB screen in a similar manner to uploading any standard report, with some simple additional steps. In ChilliDB, navigate to the Reports > Upload. Fill out the form as you normally would, then select “Integrated into the following screen” for the “Access Method” field. You will then see a second drop down appear below that field and you can then select the screen in ChilliDB to link the report with. Select the screen you would like to link your report to and save your changes. Viewing Integrated Reports If you navigate to one of the screen into which you linked your report (e.g. The Contacts display screen), you will see a Quick Actions menu near the help icon in the top right corner of that screen. Within that Quick Actions menu, you will see a Reports sub menu and you will see your report listed within that menu. Selecting your report will pop up the report viewer to display your report. Managing Integrated Reports By default, integrated reports are not included in the standard report listing screen (Reports menu > Display). The report listing screen includes a filter called “Show Integrated Reports”. Checking this box will reveal your integrated reports in the listing. You can then perform the usual maintenance options with that report. If you change a report’s “Access Method” from “Integrated into the following screen” to “Run from Report Listing”, your report will then only appear in the default report listing screen. When you run an integrated report from the report listing, you will see a prompt for the parameter which is normally set by ChilliDB when running the report. Troubleshooting If you see your parameter when running your integrated report, check the name of the parameter is correct and check that the data type for your parameter is set to “Number”. Also ensure that you have linked your report to the correct screen. Was this article helpful?