Using a Custom Database for reporting queries

Very often you need to create custom reports, and sometimes the queries are non-trivial. At this point you need to create a custom database because this keeps you supported (MS) and simplifies maintenance - particularly in multi-tenant scenarios. Note: This article assumes that all relevant databases are on the same server.

But what about security and other settings - what do you need on your custom database?

If you are basing your custom reports on the stock ones (or otherwise), you may also find it very useful to keep using the common shared datasource. You are then easily able to return tenant-specific formats, culture info etc.

This all seems a bit of a nightmare to wade through. How can you do this? Well, it is possible, and this article shows you how, based on my own experience...

First of all, consider the collation of your custom database and make it the same as your tenant databases (CI_AI). If you don't you will later have collation compatibility issues that you don't want or need.

Next, users, roles and security. You need to make users and give roles to both network service and CRM reporting group. You can use the following script to do this:

USE [CUSTOMDB]
GO
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] WITH DEFAULT_SCHEMA=[dbo]
EXEC sp_addrolemember 'db_owner',[NT AUTHORITY\NETWORK SERVICE]
GO
DECLARE @SQLAccessGroup VARCHAR(255)SELECT TOP 1 @SQLAccessGroup=name FROM sys.server_principals WHERE CHARINDEX('SQLAccessGroup',name)<>0
EXEC ( 'CREATE USER [' + @SQLAccessGroup + '] FOR LOGIN [' + @SQLAccessGroup + ']' )EXEC ( 'sp_addrolemember ''db_owner'', [' + @SQLAccessGroup + ']' )
GO

So now you have your custom database setup and ready to populate with views, stored procedures, functions etc. If you are working in a multi-tenant environment (or even otherwise), and want to keep the standard shared datasource, you should aim to pass in the organization name from your report, apart from any other parameters. Consider using a query similar the following in each report.

<Query>
<DataSourceName>CRM</DataSourceName>
<CommandText>
DECLARE @orgname Varchar(100)
SELECT @orgname = Name FROM OrganizationBase
EXEC [CUSTOMDB].dbo.usp_customsp @orgname, @filter
</CommandText>
<QueryParameters>
<QueryParameter Name="@filter">
<Value>=Parameters!CRM_xxxxxxxxxxxxxxxxx.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>

The final piece of the puzzle is creating a function in your custom database to return the specific tenant's database name. You can use this in your dynamic SQL to retrieve your query information from the right database.

CREATE FUNCTION [dbo].[GetDBName] ( @orgname varchar (100))RETURNS varchar (100)AS BEGIN
DECLARE @dbname varchar (100)
SELECT @dbname = DatabaseName FROM MSCRM_CONFIG.dbo.Organization WHERE (UniqueName = @orgname) OR (FriendlyName = @orgname)
RETURN @dbname
END
GO

For anyone who is starting to write custom reports, this information would have saved me a lot of time. I hope it helps someone?