Querying SQL Schemas

[Anthony]

I'm just getting started with Report Server and have an overall strategy/approach question. The database containing our report data is a SQL Server 2017 database that uses a schema-per-tenant design. My question is, is there anybody else out there querying their report database in such a manner from Telerik reports? What I want to avoid, is creating a data source for every schema that exists in our database. Schemas are added weekly, every time we setup a new customer. I don't want to have to create a new data source for every report every time we add a new customer.

Any input or advice is welcome!

[Todor]

Hi Anthony,

If I understood correctly, you would like to change dynamically the schema in the query of an  SqlDataSource. This is a new requirement that is easily achievable with Telerik Reporting, although not directly supported. Here is the workaround :

1. Declare the SqlDataSource that should use 'schema-per-tenant'. Set irs 'ConnectionString'. Note that this can be done Dynamically through a report parameter. Assign the DataSource to the corresponding Data item.
2. Declare a Report Parameter (i.e. 'Schema') to provide the name of the user schema.
3. Use the Bindings property of the Data item to provide the SelectCommand property :

Property path               |   Expression
DataSource.SelectCommand    |   = Format("SELECT {0}.[Production].[Product].[Name],
                    |       {0}.[Production].[Product].[ProductNumber],
                    |       {0}.[Production].[Product].[ListPrice],
                    |       {0}.[Production].[Product].[StandardCost]
                    |   FROM {0}.[Production].[Product]", Parameters.Schema.Value)

The DataSource.SelectCommand property is *not* listed in the dropdown and must be typed manually.
Note that the example uses the Format Text function to integrate the schema in the query string.

You may provide also the entire SelectCommand as a value of a Report parameter :

Property path               |   Expression
DataSource.SelectCommand    |   = Parameters.Query.Value

I have attached a sample report definition demonstrating the approach. The screenshot denotes the major steps of the approach.

Regards,
Todor
Progress Telerik