SQL Server Performance dashboard reports

  • w.koster

    SSC Journeyman

    Points: 94

    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

    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: 14614

    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: 14614

    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

    OK, it works now.

    I had a shared data source caused the problem! 🙂

  • Eric Hanson-367641

    Grasshopper

    Points: 17

    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

    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: 14614

    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

    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: 14614

    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

    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

    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

    Oldie but goodie.

  • tstrole

    Valued Member

    Points: 50

    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

    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

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

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