When it comes to custom reporting in Blackbaud CRM™, there are several options to be considered. Customers often think that SSRS (Microsoft SQL Server Reporting Services™) is the only option for custom reports. The good news is there are many other options.
SSRS custom reports often take the longest to develop and are usually the most complex to develop. So we recommend evaluating the other options for creating outputs in Blackbaud CRM™.
If your report does not need page headers, titles or any specific formatting, SSRS might be more than you need. Evaluate what kind of aggregations and calculations are necessary on the report. What kind and how many parameters are needed for a specific report. Understanding these requirements can help determine the best output. If the user who needs to see the report does not have access to CRM, think about how they will be using the data. An export to Excel from an Ad-hoc Query might be all that is necessary for an external user or a data list or list builder might be sufficient for a user within the system.
Options for outputs in Blackbaud CRM™ are listed below. User defined smart queries and data lists can be useful for producing outputs. If the out of the box tools don't meet your needs, most can be extended through customizations using the Blackbaud CRM SDK™ much more easily than an SSRS can be developed.
Ad-hoc Query or Selections
Data List/ List Builder
There are also other reporting tools on the market such as Tableau and Power BI that many organizations are using to create reports and dashboard.
If you're using the Blackbaud CRM Data Warehouse™ (BBDW) for reporting. there are a few things to keep in mind:
If you want to use smart fields and attributes in the BBDW™, make sure you have chosen to include them in the data warehouse so that these items are usable in the BBDW™ after the ETL Refresh completes.
Best practice is to not schedule the ETL Refresh until after the smart fields and any other necessary Blackbaud CRM™ processing occurs first.
Setup a backup schedule for the database.
Setup a maintenance plan to keep the indices updated and rebuild them as necessary.
If you add extensions to the BBDW™ tables so that you do not lose the extensions during hot-fixes and upgrades., keep a naming convention separate from the out of the box BBDW™
Remember that a change made in the CRM on a given day will not show up in the BBDW™ until the ETL Refresh occurs. Any reporting using the BBDW will reflect this.
Indices can be added to the BBDW™ to help with reporting and performance. If you have a lot of records in the table you added an index to you might want to drop the index before the ETL Refresh. Dropping any new indices before the ETL Refresh will help the runtime of the ETL Refresh. One way of handling this is to setup a job that runs a few minutes before the ETL Refresh to drop the extra indices. Then to recreate the indices a SSIS package could be setup to check if the index exists and create the index if it does not exist.
If you have any questions about Blackbaud CRM™ or the Blackbaud CRM Data Warehouse™, please let us know!