August 14, 2009 at 5:28 am
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
August 14, 2009 at 1:20 pm
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.
August 15, 2009 at 4:50 am
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
August 15, 2009 at 8:46 am
Steve's post is better solution.
August 15, 2009 at 9:41 pm
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.
August 16, 2009 at 3:55 am
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
August 17, 2009 at 10:02 am
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
August 17, 2009 at 10:20 am
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.
August 17, 2009 at 12:25 pm
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
August 18, 2009 at 5:57 am
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
August 18, 2009 at 1:27 pm
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
August 18, 2009 at 2:58 pm
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
August 19, 2009 at 4:28 am
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
August 19, 2009 at 12:02 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply