SQL Server Performance dashboard reports

Question

SQL Server Performance dashboard reports

Viewing 15 posts - 16 through 29 (of 29 total)

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

w.koster SSC Journeyman Points: 94 More actions · Answer 1

BL0B_EATER (9/3/2015)
For each connecting report ( from the main page) you will need to pass in the @servername as an “action”..
See screen shot.
This is how I passed in the servername into the datasource within the specific reports so they run within your selected server and not cms :
="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=master"

Thank you for helping me out. This is my first experience with SQL Reporting Services.

I managed to get most subreports working except for Active Traces and Active Xevent Sessions. Both give me this error:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)

Format of the initialization string does not conform to specification starting at index 0.

I cant figure out what is different from the other subreports. I added the parameter to the action and the data source like you gave me.

Besides this error i still get the CMS server when i open the main entrance page. I must be missing something.

Do you have any clue what i did wrong?

Rhox Hall of Fame Points: 3462 More actions · Answer 2

BL0B_EATER (9/3/2015)
For each connecting report ( from the main page) you will need to pass in the @servername as an “action”..
See screen shot.
This is how I passed in the servername into the datasource within the specific reports so they run within your selected server and not cms :
="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=master"

Thanks for this great report!

But I'm missing the 'action' part too 🙁

What screenshot do you refer?

I have the dropdownmenu with the serverlist from CMS, but my subreport (performance_dashboard_main) fails:

Data retrieval failed for the subreport, 'PerformanceDashboardMain', located at: /SPDR/performance_dashboard_main. Please check the log files for more information.

I'm also new to create reports...

BLOB EATER SSChampion Points: 14609 More actions · Answer 3

Rhox (9/8/2015)
BL0B_EATER (9/3/2015)
For each connecting report ( from the main page) you will need to pass in the @servername as an “action”..
See screen shot.
This is how I passed in the servername into the datasource within the specific reports so they run within your selected server and not cms :
="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=master"
Thanks for this great report!
But I'm missing the 'action' part too 🙁
What screenshot do you refer?
I have the dropdownmenu with the serverlist from CMS, but my subreport (performance_dashboard_main) fails:
Data retrieval failed for the subreport, 'PerformanceDashboardMain', located at: /SPDR/performance_dashboard_main. Please check the log files for more information.
I'm also new to create reports...

The action part refers to my last post - attachment called SSC1.jpg.

For the data retrieval issue please confirm all your security / access is ok.

BLOB EATER SSChampion Points: 14609 More actions · Answer 4

w.koster (9/7/2015)
BL0B_EATER (9/3/2015)
For each connecting report ( from the main page) you will need to pass in the @servername as an “action”..
See screen shot.
This is how I passed in the servername into the datasource within the specific reports so they run within your selected server and not cms :
="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=master"
Thank you for helping me out. This is my first experience with SQL Reporting Services.
I managed to get most subreports working except for Active Traces and Active Xevent Sessions. Both give me this error:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
Format of the initialization string does not conform to specification starting at index 0.
I cant figure out what is different from the other subreports. I added the parameter to the action and the data source like you gave me.
Besides this error i still get the CMS server when i open the main entrance page. I must be missing something.
Do you have any clue what i did wrong?

you will probably need to email me the steps you took to get to your issue(s), hard for me to troubleshoot without looking at your screen.

Rhox Hall of Fame Points: 3462 More actions · Answer 5

OK, it works now.

I had a shared data source caused the problem! 🙂

Eric Hanson-367641 Grasshopper Points: 17 More actions · Answer 6

I've read through this several times and I'm not sure what this "action" step is. I have the shared dataset to get the list of the servers - that's straight forward. What is needed to 1) prompt for that list and 2) pass that selection into the connection string?

w.koster SSC Journeyman Points: 94 More actions · Answer 7

Edit each (sub)report and add a parameter to it.

Name: pServername

Prompt: It's hidden so it does not really matter:)

Data type: Text

Visibility: Hidden

Add an embedded data source to the (sub)report

Value: ="Data Source=" & Parameters!pServername.Value & ";Initial Catalog=master"

Credentials: Use current Windows user

If you choose another name for your server parameter change it in your data source too.

Edit the main page in Report Builder.

Add the same parameter you used in the (sub)reports.

Name: pServername

Prompt: Something like "select your server here"

Data type: Text

Visibility: Visible

Add an embedded data source to the main page

Value: ="Data Source=" & Parameters!pServername.Value & ";Initial Catalog=master"

Credentials: Use current Windows user

In the main page right click each link and choose Text Box Properties...

From there select Action in the left pane and add a parameter.

Mine has the name pServername and a value [@pServername]

BLOB EATER SSChampion Points: 14609 More actions · Answer 8

I know its quite a bump in time but I thought this update would be useful:

I have moved the project to codeplex so people can download it, that's if you are struggling to see how the inter-connecting of reports takes place.

http://sqldashboards.codeplex.com/"> http://sqldashboards.codeplex.com/

Rhox Hall of Fame Points: 3462 More actions · Answer 9

Hi, thx for the reports.

But I've a strange error.

My parameter is working, I receive all items from our CMS.

But when I select an instance at the parameter drop down list: it works fine, but when I select another instance I receive following errormessage:

"Data retrieval failed for the subreport, 'PerformanceDashboardMain', located at: /SPDR/performance_dashboard_main. Please check the log files for more information. "

But I don't know what's wrong, parameters are working, SQL script is deployed on all SQL instances etc....

BLOB EATER SSChampion Points: 14609 More actions · Answer 10

Rhox (4/29/2016)
Hi, thx for the reports.
But I've a strange error.
My parameter is working, I receive all items from our CMS.
But when I select an instance at the parameter drop down list: it works fine, but when I select another instance I receive following errormessage:
"Data retrieval failed for the subreport, 'PerformanceDashboardMain', located at: /SPDR/performance_dashboard_main. Please check the log files for more information. "
But I don't know what's wrong, parameters are working, SQL script is deployed on all SQL instances etc....

I cant replicate this issue. the only true way to switch between servers that you need to go back to the main home page and re-select the server you want.....

what does the log file say?

MattF SSCrazy Points: 2285 More actions · Answer 11

Thanks for posting this - it is a great database monitoring solution. We have it in production on all versions of SQL up to SQL 2016. Note that in SQL 2016 there is a known issue with the SQL Data Collections that some of the reports depend upon - please vote up the post on Connect: https://connect.microsoft.com/SQLServer/feedback/details/2887824/data-collector-does-not-collect-performance-counter

Matt

MattF

hiramfleitas Valued Member Points: 64 More actions · Answer 12

Nice job, I'd actually wonder if we can transfer the data out of the target into the SSRS svr perhaps daily or every xhrs to poll it locally for a given time range instead of the querying the target directly every time.

Thanks,
Hiram
http://fleitasarts.com

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 13

akljfhnlaflkj

SSC Guru

Points: 76202

December 12, 2016 at 8:06 am

#1917381

Oldie but goodie.

tstrole Valued Member Points: 50 More actions · Answer 14

Thank for the good article ; I can currently execute the report from SSMS. I'm not able to locate where the source files are though ; I have done numerous searches for .rdl and various names, such as performance_dashboard_main. I looked in the user folder who installed SSMS and in the C:\ SQL Server folders to no avail. Therefore, do I need to download the reports , even though I can access them via SSMS?

MattF SSCrazy Points: 2285 More actions · Answer 15

Some of the MDW reports can be downloaded from here: https://blogs.msdn.microsoft.com/billramo/2010/11/18/may-the-source-be-with-you-mdw-report-series-part-6the-final-edition/

MattF