Print the selected database from datasource on reporting services report

  • Van Heghe Eddy

    SSCarpal Tunnel

    Points: 4449

    Hello,

    We have 3 environments over here (Development, Test, Production),

    When we create reports we always use the same name (eg. datasource1)

    On the reporting services database we have created 3 different datasources (eg dsDev, dsTest,dsProd) witch point to their databases.

    Now at the moment that we publish a report we use the sqlsrvmgt to link 1 of the above 3 datasources to the published report.

    So far so good.

    Now would we love to print the name of the used datasource on our report self.

    That way we can see directly when a report is printed from witch database the data comes from.

    but i can't find of a way to accomplisch that.

    Is there someone over here that has the same issue or even better, a solution for it.

    Any help would be verry appreciated.

    Wkr,

    Eddy

  • Steve Newton

    SSCommitted

    Points: 1864

    We simply call the report something sensible when we upload it. If you incorporate the env in the report name and then you can use include the report name in the footer using a global from expression editor.

    You could also pass through the DB name in a dataset and use it somewhere.

    Steve.

  • Van Heghe Eddy

    SSCarpal Tunnel

    Points: 4449

    Hello Steve,

    No, thats just the whole point of it.

    We upload 1 report to the reportserver,

    but on the reportserver we have 3 datasources defined,

    Now, newly created reports will be uploaded and linked on the development datasource

    when satisfied we can link it to the testdatasource (eg. pulling data from the testdatabase)

    What we want to do is: displaying the used datasource on the report so when data seems not correct we can direct see from witch database the data was coming.

    But in the globals i can't find some way to show the datasource that was used for the report.

    Is it even possible.?

    Wkr,

    Eddy

  • Steve Newton

    SSCommitted

    Points: 1864

    Steve's post is better solution.

  • stevefromOZ

    SSC-Forever

    Points: 43646

    Eddy,

    You could try the following as an expression in a textbox - you simply have to name your DS, you had mentioned 'datasource1' earlier, so i used that -->

    DataSources("datasource1").DataSourceReference

    This will return a text string, along the lines of /DataSources/devDataSource (or prod or test).

    HTH,

    Steve.

  • Van Heghe Eddy

    SSCarpal Tunnel

    Points: 4449

    Hello steveFromOz,

    That indeed seems like a good solution,

    Will try it tomorrow @ work and will give feedback on this subject,

    Tnx already for the support in this,

    Wkr,

    Eddy

  • Van Heghe Eddy

    SSCarpal Tunnel

    Points: 4449

    Hi SteveFromOz,

    I have tried your solution, but fortunately, it didn't work.

    It seems that the report while creating it in the BI Studio, does not recognize the "datasources" object.

    Ive tried as expression: =DataSources("DataSource1").DataSourceReference

    But the first word is immediatly underlined in red as the BI does not understand the object.

    Ive tried other way of writing the same but none did work 🙁

    Wkr,

    Eddy

  • stevefromOZ

    SSC-Forever

    Points: 43646

    Just out of interest, did you try publishing the report and viewing the result there? MSDN indicates that this property is "not available in local preview" for both 05 and 08 releases.

    Steve.

  • Van Heghe Eddy

    SSCarpal Tunnel

    Points: 4449

    Hi Steve,

    No that i did not.

    Wonder whether it would even be possible to publisch it,

    Cause while trying to preview the report i get an error that there is an error in the textbox that i use to display the expression result saying this can not be placed in the Header of Footer of this report.

    If i place it in the body i can "compile" it, but it displays an error while previewing it.

    Will try to publish the report tomorrow and keep you updated about the result.

    Wkr,

    Eddy

  • gavindux

    Mr or Mrs. 500

    Points: 566

    Hi

    Success, I was able to preview the report as well as publish it to a server, using SSRS 2005. I was unable to put the textbox on the Page Header, but its quite happy in the Report Body.

    =DataSources("DataSourceName").DataSourceReference

  • Van Heghe Eddy

    SSCarpal Tunnel

    Points: 4449

    Hi steve,

    Idd it seems to work when you publisch it to a server,

    Can't put it neither in header nor footer doh.,

    But just right above my tablelayout is quite ok as well,

    Tnx again for your support in this,

    Wkr,

    Eddy

  • Marianne L Collins

    SSCommitted

    Points: 1895

    If you really want it in the header or footer, you can make the textbox in the body hidden, and then create a textbox in the header/footer that refers to the hidden box in the body. For example, if the name of your hidden textbox is txtDataSrc, create a textbox in the header/footer with this:

    =ReportItems!txtDataSrc.Value

    BTW - mine rendered fine in Visual Studio 2005. (Version 8.0.50727.762 (SP.050727-7600))

    -Marianne

  • stevefromOZ

    SSC-Forever

    Points: 43646

    @marianne - i like that approach, neat

    @eddy - glad it worked for you

    Steve.

  • Van Heghe Eddy

    SSCarpal Tunnel

    Points: 4449

    Marianne,

    Super neat,

    Did try that already but wasn't working cause he could not find textbox17

    i tried the expression =textbox17.value

    Did not know you had to put ReportItems! before the actual textbox

    Tnx a lot you both,

    Wkr,

    Eddy

  • sgmunson

    SSC Guru

    Points: 110459

    Just in case there's a value to the concept, I thought I'd describe how things were set up at one place I did some SSRS work:

    They had multiple databases, but all were on the same instance, so the net result was that I was able to code a parameter within SSRS that would just represent the name of the database, and then it's a dropdown box for the report. Then the report contained the parameter value in the header, and the datasource query used the parameter as part of a USE statement. Turned out to be a darned handy method.

    Steve

    (aka smunson)

    :-):-):-)

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

Viewing 15 posts - 1 through 15 (of 21 total)

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