< Blog

SCCM Guru Webcast Follow Up

During my recent SCCM Guru webcast there were a number of questions that came in during the webcast. I will answer most of them within this blog post, however, there are two that I will give short answers to and the long answers will show up on my blog site (listed below) in the coming weeks.

 

Note: that some questions may have been edited for clarity and length.

 

Q: Can the input variable that is query-based be searchable like how it is in the native SCCM Web Reporting interface? I.e. the Collection ID can be searched using a wild card rather than having to select one from a drop down only?

A: Yes you can do this. Watch my blog for more details on how to do this, but here’s the short answer if you want to try this yourself:

·      Use a free form text box

·      Use Multiple prompts and have the 1st prompt provide data to the 2nd prompt. (Order is important)

·      Use a Multi-value

 

 

Q: Can reporting services reports use OU and not CollectionID or Collection name?­

A: Sure can, for example the query below uses PC OU names as a filter:

Select 
 R.Name0,
 R.User_Name0,
 ROU.System_OU_Name0
from 
 dbo.v_R_System R
 join dbo.v_RA_System_SystemOUName ROU on R.ResourceID = ROU.ResourceID
Where
 ROU.System_OU_Name0 = 'GARTEK.TST/SERVER'
Order by 
 R.Name0

Q: Question for Garth: The T-SQL and SSRS courses, do you have recommendations?­

A: The courses are:

 

T-SQL

50027A: Querying and Processing SQL Server Data Using Microsoft SQL Server http://www.microsoft.com/learning/en/us/course.aspx?ID=50027A&locale=en-us#tab2

2778A: Writing Queries Using Microsoft SQL Server 2008 Transact-SQL http://www.microsoft.com/learning/en/us/course.aspx?ID=2778A&locale=en-us

10774AB: Beta course – Querying Microsoft SQL Server 2012 http://www.microsoft.com/learning/en/us/Course.aspx?ID=10774AB&Locale=en-us

50439B: Basics of Transact SQL with SQL Server 2008 R2 http://www.microsoft.com/learning/en/us/course.aspx?ID=50439B&Locale=en-us

 

SSRS

10778AB: Beta course – Implementing Data Models and Reports with Microsoft SQL Server 2012 http://www.microsoft.com/learning/en/us/Course.aspx?ID=10778AB&Locale=en-us

50555A: Visualizing Data with SQL 2008 R2 and Report Builder 3.0 http://www.microsoft.com/learning/en/us/Course.aspx?ID=50555A&Locale=en-us

 

 

Q: How can I use multiple values from drop down?

A: Good question, and yes this can be done. Watch my blog for more details on how to do this, but here’s the short answer if you want to try this yourself.  SelectMulti-value option within the Report Parameters (within BIDS)

 

 

Q: Is it possible to use SSRS to join data from multiple data sources?

A: Yes this can be done, but it can be a bit tricky too. The “simple” way is to use linked servers within SQL and then directly query the data from your SQL query. See this blog post on how to query AD from SQL. http://smsug.ca/blogs/garth_jones/archive/2008/12/03/how-to-add-ad-data-to-configmgr-reporting.aspx

 

Q: Will those labs be made available for those who can’t attend MMS?

A: I assume that we are talking about the MVP labs, so to be honest, I’m not sure if they are going to be made available outside of MMS, however, if they are they will likely fall into the Extended Lab Subscription http://www.mms-2012.com/extended_lab

 

Q: Can you show the URL to your blog where you have those SQL queries?

A: Sure. I have a few blogs depending on what I’m blogging about. My main blogs are :

·      http://smsug.ca/blogs/garth_jones/default.aspx

·      https://www.enhansoft.com/blog

 

Both of which are replicated to the myITforum blog site: http://myitforum.com/myitforumwp/community/activity/feed/

 

Q: Who provides the SSRS courses?

A: The two SSRS courses that I know about are listed below. However, at MMS there should be (at least) two MVP created self-paced labs on SSRS and understanding CM07/CM12 data.

10778AB: Beta course – Implementing Data Models and Reports with Microsoft SQL Server 2012 http://www.microsoft.com/learning/en/us/Course.aspx?ID=10778AB&Locale=en-us

50555A: Visualizing Data with SQL 2008 R2 and Report Builder 3.0 http://www.microsoft.com/learning/en/us/Course.aspx?ID=50555A&Locale=en-us

 

Q: The 2 courses you mentioned, Garth, any info on these, been looking for something like this, although based in the UK.

A: all the courses that I have talked about (with the exception on MVP self-paced labs at MMS) are regular Microsoft courses and you should be able to find them at your local training center.

 

 

Q: Is it true that CM 2012 will only use SRS (legacy reporting interface goes bye bye)?

A:Yes it is true, and to be honest, it is about time, although it is extremely easy to create a report using the old ASP interface.  They would crash, were slow, looked ugly, etc… SSRS isso much nicer and there is just so much more cool stuff you can do with it. In my opinion, once you understand how to create reports in SSRS, you will love it for what it can do and how it looks.

 

 

Q: ­Is there a quick and easy way to replace the template for all of the built-in reports to use your custom template with a company logo, etc.?­

A: Yes there is an easy way to do this in CM12 and fellow MVP Kent Agerlund has created a blog post on the subject. http://blog.coretech.dk/kea/easy-way-to-change-logos-in-configuration-manaer-2012-reports/

 

 

Q: ­What is the easiest process to move from the old ConfigMgr ASP reports to SSRS? What is the best way to organize the new reports? We have thousands of reports in the old ASP so I am trying to figure out what is the best way to move to the new reports­.

A: The easiest way to convert the ASP reports to SSRS reports is using the built-in conversion wizard, see TechNet article for step-by-step instructions:http://technet.microsoft.com/en-ca/library/cc161979.aspx. This will convert them in most cases without issue. It will keep the existing categories and create SSRS folders for those categories. Additionally, it will place the RDL (SSRS report files) files of all converted reports into the temp directory and you can manually edit these reports if there is any issues with a report. As for organizing reports, group like reports within the SSRS folder. Also remember that you can nest folders and control access via SSRS folders too.

 

 

Q: The Asset Hardware inventory report which I configured on SRS does not come in Mail. The reason I found is the size of the report is large (18000+ workstations). But how to control or solve the same to come in email report?

A: There could be many reasons for this, without looking at the report itself there is no way of telling what the issue is. My gut reaction without looking at the report or understanding what you are trying to do is this is one of those reports that you need to say “No, this report does not make sense.” If this report does make sense to do then I recommend starting with the SQL query and confirming that it is written correctly. Then check the performance of your SQL server, finally the report itself.

 

 

Q: How about joining data from an external BI data warehouse?

A: Yes this can be done but it can be a bit tricky too. The “simple” way is to use linked servers within SQL and then directly query the data from your SQL query. See this blog post on how to query AD from SQL. http://smsug.ca/blogs/garth_jones/archive/2008/12/03/how-to-add-ad-data-to-configmgr-reporting.aspx