Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Reporting Services 3-Trick Pony


The Reporting Services 3-Trick Pony

Author
Message
Rodney Landrum
Rodney Landrum
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 712
Comments posted to this topic are about the item The Reporting Services 3-Trick Pony
Bart Hayes-427724
Bart Hayes-427724
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 197
I'm keen to try this out but cannot create the data source. An error message stating that "expressions cannot be used in a shared data source" appears. How do I create a data source that is not a shared data source? The Solution only has Shared Data Sources.

Even the Report Wizard stalled at this, there was a warning messages saying "that a connection can not be made to the database" and that "connection string expressions cannot be tested at design time" which is fine but the "next" button would not respond and I could only go back or cancel.

How did you get around this?
Jon Spink
Jon Spink
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 945
This is neat, but couldn;t you have the report call itself? Then there's be no need for an SR1 AND an SR2. I do this for a couple of reports where a hyperlink initiates a data update and the report then runs again automatically to show the changed state of the data.
Bart Hayes-427724
Bart Hayes-427724
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 197
I found the answer using the magic of Google.

A report specific datasource can be created by selecting the Data tab and creating a new dataset. Should have known but funnily enough I've just always used shared datasources.

Cheers for now.
Bart van de Griendt
Bart van de Griendt
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 11
It keeps failing on error "Cannot create a connection to data source 'test'.
I checked the datasets and one of them is referring to 'test'.
That data source is not available of course.
I tried to repoint the dataset to the 'DBA_REP.rsd' datasource, but that does not help.
Rodney Landrum
Rodney Landrum
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 712
Thanks for all of your comments so far.
The "test" data source is not a shared data source. When developing the reports, I used a static datasource for the "test" connection. Yes, I realize, I should have named "test" something else, like "dynamic_server_source". Once the sp_who2 returned results, I then added the dynamic connection string that is in the article.

Two notes:

When I downloaded and tested loading in the RDL in a new project this morning everthing went fine except that the data set that execute "sp_who2" reverted to a type of Text. You will need to change dataset2 to be Stored Procedure, not Text.

And yes...I sheepishly note that SR1 can call itself in a hyperlink. I was just waiting for someone to point that out. Smile Actually, that is a great idea, instead of having two reports. Big red palm print on my forehead. I recall starting down that path and hit an error with a invalid nested parameter. Works great, though...thanks for additional the tip Jon.

Rodney Landrum
Author (Pro SQL Server 2005 Rpeorting Services - Apress)
Rodney Landrum
Rodney Landrum
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 712
I have asked to place new RDLs in the code download.
The issue everyone is having with the "TEST" datasource is the default parameter value for the ServerName parameter.
To address without new code, change the default value to be NULL not Non-Queried.
Sorry for the oversight.
Rodney Landrum
Tony Davis
Tony Davis
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: Administrators
Points: 591 Visits: 1148
The new RDLs are now in the code download.

Cheers,

Tony.

jereme.guenther@gmail.com
jereme.guenther@gmail.com
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 194
Thanks Tony. It is really nice having a larger group of people who can makes these small changes for us. I think poor Steve was getting overwhelmed with the amount of work and time this site was taking.
bbychkov
bbychkov
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 576
This is really cool!

I'm thinking of using these techniques and combining them with standard SSMS reports and/or performance dashboard reports. That would be a nice muli-server monitoring solution.

Thanks Rodney!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search