SCCM 2012 Reporting for dummies: Creating your own SSRS reports

So we’ve talked about reviewing reports out of the box using the SCCM console as well as using the web browser. We’ll now get to the nitty gritty and create our own reports using SSRS. I’m sure you’re all eager to learn as you’ve come this far in my “Reporting for Dummies” series.

We’ll first need to go into the console and open up the “Reporting” node under the “Monitoring” tab. Right click the “Reports” node and click on “Create Report”.

Remember, if you are getting the error (Report Builder 2.0 is not installed…) then you will need to edit the registry or close the console and attempt to reopen as administrator as I’ve advised in my previous blog post:

http://sccmgeekdiary.wordpress.com/2012/09/11/sccm-2012-reporting-for-dummies-report-builder-2-0-is-not-installed/

You should now get a “Create Report Wizard” which will lead you through the steps required to create your own custom report.

So we’re going to create a SQL-based Report and we’ll need to fill out the fields. We’re going to name this report “All Desktops Machines” so type that into the “Name” field. We’re also going to need to store the report somewhere so for the Path field, click “Browse”.

You can choose the most relevant category you feel the report should go under. For now, we’re going to put this under “Site – General”.

Click OK and then click Next. You’ll be on the summary page. Click Next and then close after the report is created.

When you finish the report and click close, you should notice that SQL Server Report Builder should now open.

We’ll start this report by creating a “Table or Matrix” so click on that icon. We now need to enter in a dataset for the report to use so leave the “Create a dataset” selected and click Next. You’ll now be prompted by a “Choose a connection to data source” page. Click next whilst the highlight is on the server that hosts your reporting point.

It should prompt you to enter the credentials that will be used to access the dataset. Enter in the relevant credentials that have access to the SCCM database and click Next.

You’ll now hit the “Design a query” page. This is practically the same as the query builder we saw when we were dealing with Queries! Click on “Edit as Text” in the top left corner.

Now you are in the text editor mode, you can write your SQL query here. Remember SQL queries we did in an earlier tutorial? Well this is where it comes in handy!

http://sccmgeekdiary.wordpress.com/2012/09/06/sccm-2012-reporting-for-dummies-how-to-use-sql-management-studio-to-build-your-sccm-queries/

We’ll be using the same SQL query as we did in that tutorial as we’re creating a report to bring up all the desktop clients! Bet you didn’t see that one coming did you!

The query is this:

SELECT dbo.v_R_System.ResourceID,dbo.v_R_System.ResourceType,dbo.v_R_System.Name0,dbo.v_R_System.Resource_Domain_OR_Workgr0,dbo.v_R_System.SMS_Unique_Identifier0,dbo.v_R_System.Client0,dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
FROM dbo.v_R_System
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = N’3′)

Copy this query and paste it into the query field. Now that you’ve pasted this in, click on the exclaimation mark (!) to run the query to see if it is correct.

On the next screen, you’ll get the ability to lay out how you want your report to look. I’ve selected all the resourceID field to be my row field so all the data will be stacked against this key field. I’ve placed all the other fields in the values field so they’ll populate with values in the report. You can be selective here if you wish but I will be modifying the report in a moment so it doesn’t matter if you leave them out now or delete them after the fact.

Since this is a simple report that doesn’t have aggregated values, we can uncheck the two boxes to create a simple report.

Click Next and then Finish. You should be back at the report builder screen. You can start modifying the report to look exactly how you’d like as well as add new dynamic fields or text as you wish. Go crazy with it as you learn more by playing with the reports. For now, we’ll just add in some simple text for a description and two dynamic fields from the “Built-in Fields” node.

After adding these in, renaming the fields and deleting unwanted ones, you should have a report that looks more to what you want. When this is done, click on “Run” to view the report to see what it would look like with actual data populated.

You can always go back to the design by clicking “Design” if you need to make modifications or tweaks to your custom report.

After you are happy with your report, click the save button and there we are. You’ve created a new report!

If we look in the reports under “Site – General” you should now see your new report. This will appear now in the console as well as the web reports.

~P~

email

Written by , Posted .
  • http://myITforum.com/myitforumwp/community/members/mr-mule/ mr mule

    I get the following error when trying to create this report, unfortunately there seems to be no helkp from MS on this.

    ===================================

    Incorrect syntax near ‘’’. (.Net SqlClient Data Provider)

    ——————————
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476

    ——————————
    Server Name: XX-XX-XX.domain.local
    Error Number: 102
    Severity: 15
    State: 1
    Line Number: 4
    ——————————
    Program Location:

    Server stack trace:
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
    at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.AsyncExecuteReader(IDbCommand command, CommandBehavior behaviour)
    at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
    at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
    at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

    Exception rethrown at [0]:
    at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
    at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
    at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.ExecuteReaderHandler.EndInvoke(IAsyncResult result)
    at Microsoft.ReportingServices.QueryDesigners.MultiThreadedMultiConnectionQueryResultsHelper.c__DisplayClass4.b__3()
    at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.EndExecuteReaderInUiThread(GetDataReaderHandler getDataReaderCallback)

  • http://myITforum.com/myitforumwp/community/members/mr-mule/ mr mule

    syntax was incorrect on the last line.. should have read:

    SELECT SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
    FROM SMS_R_System
    INNER JOIN SMS_G_System_SYSTEM_ENCLOSURE ON SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId
    WHERE SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes = “3″