Querying SQL Schemas

2 posts, 0 answers
  1. Anthony
    Anthony avatar
    5 posts
    Member since:
    Oct 2017

    Posted 01 Nov 2018 Link to this post

    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!

  2. Todor
    Admin
    Todor avatar
    321 posts

    Posted 06 Nov 2018 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top