Our server team wanted a report of a bunch of config items on their hardware. In 2007 this many joins would have made my head spin, let alone adding in Report Builder which I'm kinda new at. So here was my process for developing my first SRSS Report.
First, open SQL Server Buisness Intelligence Development Studio (BIDS). If you don't know what that is, well I just use it for the query builder that used to be in older version of SQL Management Studio. Create a new project or overwrite an old one.
- In the Solution Explorer pane (cntr+alt+L) if it isn't on the upper right side of the screen, Right Click 'Shared Data Sources' --> Add Data Source, click Edit and fill in the information needed to access your database (I can't help you here). Test the connection till it works, and click OK until you're out of all the pop-ups
- Right Click Shared Datasets --> Add New DataSet in the Solution Explorer. Give it a name and use the drop down to select the dataset you just created above if it's not already there.
- This is where I use the Query Designer. In my case the design looked something like this (query below).
- Once you have the data you want in the result pane, highlight everything in the SQL pane (the query), and copy it. Now it's time to go to report builder.
- Assuming you've got your reporting point all set up correctly, open your SCCM console and access Monitoring --> Reporting, Right Click Reports --> Create Report. The Create Report wizard opens.
- Select SQL-based Report, give it a name and description, server 'should' be filled in, and give it a path (folder under reports). I called this Server Hardware Information and put it in Hardware - General.
- Next, next, close. Report Builder will open (I'm using 3.0)
- Click "Table or Matrix"
- Click Next to create a new Dataset (think Dataset = Query)
- You 'should' already have a Data Source and can click next. If not your Reporting Point has problems and that's outside the scope of this article. If you get a Data-Source credentials pop-up use credentials that will let you read all views in your database and click OK.
- On the Design a query window, click Edit as Text. Now you can paste in the query you developed in BIDS. Go ahead and run it just to make sure you get the dataset you're looking for then click Next.
- Here's where the artform comes in. Since I'm doing a very basic report, I just drag the server name in the 'Row Group' field and everything else in Values. Maybe later I'll get tricky and put in some column groups. Anyway put all your fields out there and click Next.
- Since this is a basic report, I uncheck show subtotals/grand totals, and Expand/collaps groups. Aaaaand, click next.
- What is your favorite color? Click Finish.
- Now your report is build, give it a test run. Here's what mine looks like (truncated)
- Next I like the groups shaded. Thanks to Paul at LINK for the following code.
- Click empty space under your report when in design view. In the properties pane you should see a Code section. Clcik the ... and paste this in:
Private bOddRow As Boolean
'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
Function AlternateColor(ByVal OddColor As String, _
ByVal EvenColor As String, ByVal Toggle As Boolean) As String
If Toggle Then bOddRow = Not bOddRow
If bOddRow Then
Return OddColor
Else
Return EvenColor
End If
End Function
- Now select the cell that your Row Grouping is set for ([Name] in my example). In the Properties pane --> Fill --> Background color, hit the drop down and select 'Expression'. Put this in the box (click OK after):
- Now select the rest of the data cells and paste the following into the background expression.
- Run it:
And wala, report.
--Dave
Oh yeah. The query:
SELECT DISTINCT
v_R_System.Netbios_Name0 AS Name,
v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 AS SITE,
v_GS_WORKSTATION_STATUS.LastHWScan AS HWScan,
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer,
v_GS_COMPUTER_SYSTEM.Model0 AS Model,
v_GS_PC_BIOS.SerialNumber0 AS Serial,
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 AS IP,
v_R_System.Operating_System_Name_and0 AS OS,
v_GS_PROCESSOR.Name0 AS Processor,
v_GS_PROCESSOR.NumberOfCores0 AS Cores,
COUNT(v_GS_PROCESSOR.DeviceID0) AS NumProcs,
v_GS_OPERATING_SYSTEM.CSDVersion0 AS SP,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS TotMemMB,
v_GS_LOGICAL_DISK.DeviceID0 AS Drive,
v_GS_LOGICAL_DISK.Size0 AS TotalGB,
v_GS_LOGICAL_DISK.FreeSpace0 AS FreeGB
FROM
v_R_System INNER JOIN
v_RA_System_SMSAssignedSites ON v_R_System.ResourceID =
v_RA_System_SMSAssignedSites.ResourceID INNER JOIN
v_GS_LOGICAL_DISK ON v_R_System.ResourceID =
v_GS_LOGICAL_DISK.ResourceID INNER JOIN
v_GS_PC_BIOS ON v_R_System.ResourceID =
v_GS_PC_BIOS.ResourceID INNER JOIN
v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
v_GS_X86_PC_MEMORY ON v_R_System.ResourceID =
v_GS_X86_PC_MEMORY.ResourceID INNER JOIN
v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
v_GS_PROCESSOR ON v_R_System.ResourceID =
v_GS_PROCESSOR.ResourceID INNER JOIN
v_GS_NETWORK_ADAPTER_CONFIGURATION ON v_R_System.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID
WHERE
(NOT (v_GS_LOGICAL_DISK.DriveType0 = '2')) AND
(NOT (v_GS_LOGICAL_DISK.DriveType0 = '5'))
GROUP BY v_R_System.Netbios_Name0,
v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0,
v_GS_WORKSTATION_STATUS.LastHWScan,
v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0,
v_GS_PC_BIOS.SerialNumber0,
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0,
v_R_System.Operating_System_Name_and0,
v_GS_PROCESSOR.Name0,
v_GS_PROCESSOR.NumberOfCores0,
v_GS_OPERATING_SYSTEM.CSDVersion0,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,
v_GS_LOGICAL_DISK.DeviceID0,
v_GS_LOGICAL_DISK.Size0,
v_GS_LOGICAL_DISK.FreeSpace0
HAVING
(v_R_System.Operating_System_Name_and0 LIKE N'%server%') AND
(v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 IS NOT NULL)
