Print the selected database from datasource on reporting services report

  • ian payne

    SSC Veteran

    Points: 246

    Yes that is an interesting concept ... are you able to post any details

    Thanks

    Ian

  • ian payne

    SSC Veteran

    Points: 246

    Have just found some articles on MSDN and another site

    see 1 : http://www.sqlservercentral.com/articles/Development/2945/

    see 2:

    Expression-based Connection Strings

    Expression-based connection strings are evaluated at run time. For example, you can specify the data source as a parameter, include the parameter reference in the connection string, and allow the user to choose a data source for the report. For example, suppose a multinational firm has data servers in several countries. With an expression-based connection string, a user who is running a sales report can select a data source for a particular country before running the report.

    The following example illustrates the use of a data source expression in a SQL Server connection string. The example assumes you have created a report parameter named ServerName:

    Copy Code

    ="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks

    Data source expressions are processed at run time or when a report is previewed. The expression must be written in Visual Basic. Use the following guidelines when defining a data source expression:

    Design the report using a static connection string. A static connection string refers to a connection string that is not set through an expression (for example, when you follow the steps for creating a report-specific or shared data source, you are defining a static connection string). Using a static connection string allows you to connect to the data source in Report Designer so that you can get the query results you need to create the report.

    When defining the data source connection, do not use a shared data source. You cannot use a data source expression in a shared data source. You must define an embedded data source for the report.

    Specify credentials separately from the connection string. You can use stored credentials, prompted credentials, or integrated security.

    Add a report parameter to specify a data source. For parameter values, you can either provide a static list of available values (in this case, the available values should be data sources you can use with the report) or define a query that retrieves a list of data sources at run time.

    Be sure that the list of data sources shares the same database schema. All report design begins with schema information. If there is a mismatch between the schema used to define the report and the actual schema used by the report at run time, the report might not run.

    Before publishing the report, replace the static connection string with an expression. Wait until you are finished designing the report before you replace the static connection string with an expression. Once you use an expression, you cannot execute the query in Report Designer. Furthermore, the field list in the Report Data pane and the Parameters list will not update automatically.

  • gavindux

    Mr or Mrs. 500

    Points: 566

    Taking that one step further:

    The companies I write reports for usually have anything between 2 and 8 ERP databases with exactly the same physical structures. Most of their reporting requirements are the same between the different financial companies. What I do is put a table in the Report Server database (too lazy to create a new db specially for this), and store the Company Name and database connection string (no username/password). I then use one query in the report to load and fill a "Database" parameter that allows the user to pick the database. That parametrized datasource connection still uses the security that is set against the datasource as normal - so there are no extra security issues, just flexibility, and no linked reports that are referencing different but similar databases. Even using drill through/sub reports is easy - just pass the same database parameter through to the sub.

  • sgmunson

    SSC Guru

    Points: 110459

    Nothing beyond the fact that each database's schema was identical, and that all the databases were on the same instance of SQL 2005. The data source query's first statement was USE @DATABASE, where @DATABASE was a parameter with a drop-down selection. That's all there was to it. It was nothing compared to the parameterized connection string, which is even more powerful, and potentially a lot more useful.

    Steve

    (aka smunson)

    :-):-):-)

    ian payne (8/20/2009)


    Yes that is an interesting concept ... are you able to post any details

    Thanks

    Ian

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • WILLIAM MITCHELL

    SSChampion

    Points: 13697

    Here's another idea.

    We have production, QA, test, and DEV databases on different servers & the front-end can be connected using either Windows or SQL authentication. On the opening screen of the app we use this to display the connection info:

    SELECT

    SUSER_NAME() + ' connected to ' +

    DB_NAME() + ' on ' +

    @@SERVERNAME AS MyConnection

  • ramkumarsambandam

    Newbie

    Points: 1

    ;WITH XMLNAMESPACES -- XML namespace def must be the first in with clause.

    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'

    ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'

    AS rd)

    ,SDS AS

    (SELECT SDS.ItemID, SDS.[Path], SDS.[Name] AS SharedDsName, SDS.ParentID, SDS.[Type], SDS.Content,

    CONVERT(xml, CONVERT(varbinary(max), SDS.content)) AS DEF, SDS.LinkSourceID

    FROM dbo.[Catalog] AS SDS WHERE SDS.Type = 5)

    ,sdsxml as

    (

    SELECT CON.ItemID, CON.[Path], CON.SharedDsName, CON.ParentID, CON.[Type], Con.ConnString

    FROM

    (

    SELECT SDS.ItemID, SDS.[Path], SDS.SharedDsName, SDS.ParentID, SDS.[Type],DSN.value('ConnectString[1]', 'varchar(max)') AS ConnString

    FROM SDS

    CROSS APPLY

    SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)

    ) AS CON

    )

    -- SELECT * FROM sdsxml

    SELECT t.ConnString ,t.SharedDsName

    ,IIF(PATINDEX('%Initial Catalog=%',ConnString)>0,SUBSTRING(ConnString,PATINDEX('%Initial Catalog=%',ConnString)+16,LEN(ConnString)),'') AS DBName

    ,c.* FROM [ReportServer].[dbo].[Catalog] c

    INNER JOIN [ReportServer].[dbo].[DataSource] d ON d.ItemID = c.ItemID

    LEFT JOIN sdsxml t ON t.itemid = d.Link

    WHERE c.Type = 2

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply