My First Little Report – Part II: Let’s add some brains…

----------------------------------------------------------------------------------
Postings in the same series:
Part I – Let’s make a ‘stupid’ Report
----------------------------------------------------------------------------------

In the second and last posting of this series we’ll add some functionality to the Report, enabling the user to select a Start- and End date and what EventID number to report upon. So we’re adding some ‘brains’ to the Report.

Before we start there are two things to reckon with:

1. It goes without saying that the Report can only contain information present in the Data Warehouse. So when you want to collect certain Events from your servers, make sure the required Event Collection Rules are in place AND running.
   
   
2. You really need to have run through the steps described in the first Posting in this series before you can start with this article. So when you’re not on that level yet, go back see you later :).

Let’s start
In order to get some input from the user of the Report AND to have the Report take that input into account, we need to add a Filter to the Report. But before we do that, it’s better to create some new Fields.

Why? Suppose you run a Report and are requested for some input like a start- and end date and find fields like these:

I know, it does the job but it isn’t very user friendly either. From the start the user will notice that the Report isn’t very professional.

So when we create new Fields we’re allowed to make NEW names as well, like ‘Start Date’ and ‘End Date’. Looks better already, doesn’t it?

1. Start the SCOM Console > Reporting pane > Select a Report (don’t double click it) and click on the link Design a new report in the Actions Pane;
   
   
2. Ad Hoc Report Builder is loaded and started. Load the Report you made based on the first posting;
   
   
3. When the Report is loaded go to Report > New Field. We’re going to create three new Fields: ‘Start Date’, ‘End Date’ and ‘EventID Number’.
   Let’s start with Start Date: enter as Field name Start Date, expand in the Fields column (lower left side of the screen) the Date Time node and double click on First Date Time. This Field is now added to the Formula for each Event: part of the screen. Click OK.
   
   
   
4. Repeat Step 3 for a new Field with the name End Date (select as Field Last Date Time) and for a Field with the name EventID Number (select as Field Event Display Number);
   
   
5. In the Explorer pane under the header Fields the three new Fields are shown. Now we can start to create our Filter;
   
   
   
6. Click on the button Filter in the toolbar. Add the three new Fields in this order: Start Date, End Date and EventID Number. The Filter screen looks like this now:
    
   Still the user of the Report isn’t prompted for any input. Let’s change it.
   
   
7. Right click on the Field Start Date and select the option Prompt. Now you’ll see a question mark in front of the field.
   
   Repeat this step for the other two remaining Fields. Now the screen looks like this:
   
   
   
8. Lets make it the user of the Report a bit easier by defining a default Start Date (one week back on the day the Report is run) and a default End Date (the day the Report is run). Click on the arrow button next to Start Date. A calendar is shown. Click on Relative Date.
   
   Select the option (n) days ago.
   
   Replace the whole string (n) with the number 8. (I know. But SQL starts a date at 0:00 AM which is basically a new day. So when you want to see all data from the past 7 days, you must add an additional day. So it’s 8 instead of 7). Now the screen looks like this:
   
   
   Repeat the same for the Field End Date. Instead of (n) days you select today. The screen looks like this now:
   
   
   
9. Still we need to do some more work. The conditions have to be changed for both dates. For the Field Start Date the condition must be set to after. For the Field End Date the condition must be set to before. Just click on equal and adjust it as required. The screen looks like this now:
   
   
   
10. There is one more thing to look at. As long we don’t fill out the Field EventID Number, a NULL value will be accepted as well. Which will grey out this Field in the Report. One can enable it by placing a checkmark in a small checkbox, but perhaps it’s better to add a basic value, like EventID 6022. This EventID is collected by default, so one will always get a filled Report and the user is allowed to change that value any time. Now the screen looks like this:
    
    
    
11. Click on OK to close the Filter screen. Now it’s time to add a title, like Custom EventID Report, counted. Run the Report and see what it does:
    
    Nice! We have the Fields which prompt for input from the user, the Report itself counts the EventIDs per server AND per Event the details are shown!
    
    
12. We can go back now to the Designer and add a picture (Insert > Picture) as a header. Like this:
    
    Try it yourself. Colors can be added as well. Just play with it and be surprised!
    
    
13. Now it’s time to save the Report. First it’s better to create a folder like ‘_Customized Reports’ in SSRS. In order to do this open the url to your Report Server (like http://localhost/reports) and create a new folder as you like. And yes, you can nest folders as well (don’t nest it too much though);
    
    
    
14. Now go back to Ad Hoc Report Builder and save the Report. The folder you created earlier is present as well;
    
    Click Save. The Report is available now in the SCOM R2 Console;
    
    
15. A refresh of the Reporting tree in the SCOM R2 Console is required in order to show the new folder (_Customized Reports).
     
    Run the Report by double clicking on it:
    
    
    
16. Click on the parameter area button and now you’ll see the filter you defined earlier on:
    
    Nice!

Conclusion
Even though Ad Hoc Report Builder is limited in its capabilities, it still can do some nice things for you. Just start experimenting and be surprised! This small series of blog postings showed some possibilities. I am sure there are many more. Explore and have fun.

BUT never forget that a Report is only good when it serves a good purpose. So before you start with asking questions in your organization what they want, be sure you can deliver AND that Ad Hoc Report Builder is up to the job. Otherwise you’ll soon find the boundaries of it and then it’s time to start SQL Server Business Intelligence Development Studio, which is a total different kind of ballgame all together...