Creating a new data source for reporting against the Operational Database

I am going to be publishing several reports over the next few months, that query the Operational Database instead of the Data warehouse.  We will use these reports for getting all sorts of administrative information... which will allow us to get data without having to launch a SQL query analyzer window all the time.


First thing... we will need to create a new Data Source for the OpsDB.

Start, by opening a web page, and browsing to http://SRSServer/Reports  where "SRSServer" is the name of your reporting server.


Click "New Data source"



1.  For the Name - type "OpsDB"  This will be the data source that I will be using for all my reports.  If you want a different name, thats fine and dandy... you will just have to modify each report to link to your data source name.  Not a big deal.


2.  In the "Connections String" paste in the following:     data source=DBSERVER;initial catalog=OperationsManager;Integrated Security=SSPI

DBSERVER is the name of the SQL server hosting your operational database.  Initial Catalog is the name of your Operational Database.


3.  Under "Connect Using" choose "Credentials are not required"  Click OK.


Here is my example:





All done with the data source!


Now... we need a custom folder to place our custom reports.  Click New Folder:




Give this folder a name.  This name will appear to all reporting users in the OpsMgr console.  I am calling mine "Custom - Reports"




Click OK.


Now - in the OpsMgr Console - we can see our new report folder:




When we publish or import new reports in the future, we can use this folder.

Comments (12)

  1. Anonymous says:

      This is a continuation of my previous post on determining which agents are missing a hot-fix:

  2. Anonymous says:

    Nice report. Works well,and I learnt a thing or two about SRS along the way. I did have to refer to Marnix’s blog about importing rdl files, but then it all came together.

    Thx Kevin

    John Bradshaw

  3. Anonymous says:

    In OpsMgr 2007, when a agent experiences a heartbeat failure, several things happen.  There are

  4. Kevin Holman says:

    Yeah – shame on me – I dorked up the data source in that report.  Simply go to the reporting server url http://servername/reports, find the Agents Missing hotfix report – click it – get the error.  Then – click properties, and on the left – click Data Sources – on shared data source – browse to the correct "OpsDB" we just made here… and then click ok, then apply, then your report will work.

    Sorry… I guess I had a different data source when I wrote that one.  But this is a good learning opportunity on how to change a data source in a report!  🙂

  5. Kevin Holman says:

    paste your connect string….

  6. Anonymous says:

    Certain types of agents need the agent proxy setting enabled.  These are documented in various guides…

  7. Hi Kevin,

    I have followed the steps in creating the data source and imported the rdl file after updating Ops to OpsDB. Im getting an error  – could not open a connection to sql server.

  8. LayneR says:

    Kevin, great blog.  I setup the datasource as described here, but after importing your Agents_Missing_Hotfix.rdl and trying to run it, I get an error "the data source connection information has been deleted (rsInvalidDataSourceReference)".  If I try to open it with Report Builder an error says the report cannot be opened because no data source is associated with it and to associate a model data source with the report and try again.  This happens from the OpsMgr console and from SRS.

  9. LayneR says:

    Uh, this is embarrassing, I just figured out I had to edit the report data source in SRS and choose a shared data source ‘OpsDB’.  So that problem is solved.  Now the report lets me put in a hotfix KB number, but when I run the report, I get an error "An error has occurred during report processing.  Cannot create a connection to data source ‘OperationsManager’."  That is the name of my operational database.

  10. LayneR says:

    data source=iolopsmgr01;initial catalog=OperationsManager;Integrated Security=SSPI

    I also noticed that in the .rdl of the report, it says <DataSource Name="OperationsManager">.  I tried changing the name of my datasource from OpsDB to OperationsManager and I get the same error.

  11. LayneR says:

    Looks like a permissions issue.  When I change my datasource to use "Credentials supplied by the user running the report" and also click "use as Windows credentials when connecting to the data source", I can run the report.

  12. Boris says:

    Thanks, great blog. Jimmy created something like that for us in the past actually

Skip to main content