Wednesday, April 21, 2010

Want to ‘Design a new report’ in SCOM 2007?

Most have you will have clicked on the ‘Design a new report’ button and found that there were ‘No Data Sources available’.
Design a new report
Here is how you can setup this feature for SCOM 2007.

CREATE A REPORT MODEL
1. Open SQL Server Business Intelligence Development Studio
2. Click File -> New -> Project
3. Select Report Model Project
4. Fill in a Name and Location
5. Click OK
New Project

CREATE A DATA SOURCE
1. In Solution Explorer, right-click Data Sources, Click Add New Data Source
Solution Explorer
2. Click Next on the Welcome Screen
3. Click New
4. Fill in the connection to the SQL Server and Instance (ie: SERVER\INSTANCE) to your SCOM Database
5. Select the Database you want to interact with. (ie: OperationsManagerDW)
DNR Connection Manager
6. Click OK
7. Click Finish
You will now see the Data Source under Solution Explorer
CREATE A DATA SOURCE VIEW
1. In Solution Explorer, right-click Data Sources, Click Add New Data Source View
2. Click Next
3. Select the data source from the Relational data souces: panel
4. Click Next
5. This is the part where you need to think, You will need to select the tables and views you want to include in the model. If you not sure and just want to play around you can however I would not suggest it… but you can sellect all the tables, however leave the views out as they can cause issues.
6. Click Next
7. Click Finish
You will now see the Data Source View under Solution Explorer
Solution Explorer 2

CREATE A REPORT MODEL
1. In Solution Explorer, right-click Data Sources, Click Add New Report
2. Click Next
3. Select the Data Source and Click Next
4. Select the Model Rules and Language (If unsure, keep the defaults) Click Next
5. Select Update model statistics before generating and Click Next
6. Type in a name Name and Click Run
Note: This may take some time depending on the number of Tables and Views selected when creating the Data Source View.
7. Click Finish
8. Under Solution Explorer Right-Click the new Report Model and selct Deploy
Solution Explorer 3
9. Exit SQL Server Business Intelligence Development Studio and Save.
RUN DESIGN A NEW REPORT
1. Open System Center Operations Manager Console
2. In Report Click Design new report
3. Report Builder will launch and you will see a Data Source
4. Highlight it and Select a Report Layout Click OK
Microsoft Report Builder

HAVE FUN!!!