.\Matthew Long

{An unsorted collection of thoughts}

SCCM – Modifying SQL Reporting Services Update Reports so that Update ‘Vendor’ and ‘Category’ are optional

Posted by Matthew on November 15, 2010

I’ve recently been scheduling various different reports for different users in SCCM 2007 R3 using the SQL Services reporting role.  However, due to a limitation with SQL Reporting services many of the SCCM reports have fields that are optional in the traditional reports but become mandatory in SRS reports.  As an example, the Software Updates A Compliance 6 – Specific Computer report now requires you to specify both the update vendor and category.  This means that you often have to run the report several times to view the status of all catalogued updates.

You can however easily modify the report so that it will display updates from all vendors and/or update categories.  First we need to add a static entry to the calculated dataset to represent no filtering (or in this case, all entries).  Then we need to modify the final displayed dataset to match the new entry to all items instead of only those with the matching attribute.

  1. Open the SCCM Console, browse to the Reporting node, and under the Reporting Services entry select your report (in this case, Compliance 6 – Specific Computer).
  2. Right click the report and select “Properties”, then click on the Dataset tab.
  3. Click on the “Report Parameters” button.
  4. Note down the Dataset that is used by the parameter you wish to modify.  Click on OK once you are done.
  5. In the “Dataset Name” dropdown, select the dataset that is linked to your parameter.
  6. Click the “Edit Command Text” button to modify the dataset.
  7. What we need to do now is specify a UNION between the data queried from the database, and any additional values we want to add.  In this case, i’m going to add “All” as an option for the Update Vendor.  Below is the dataset before modification :
  8. begin
    if (@filterwildcard = '')
    select distinct CategoryInstanceName as Vendor0 from v_CategoryInfo where CategoryTypeName='Company' order by CategoryInstanceName
    else
    select distinct CategoryInstanceName as Vendor0 from v_CategoryInfo where CategoryTypeName='Company' and CategoryInstanceName like @filterwildcard
    order by CategoryInstanceName
    end
    

    And now after..

    begin
    if (@filterwildcard = '')
    select distinct CategoryInstanceName as Vendor0 from v_CategoryInfo where CategoryTypeName='Company' union select 'All' order by CategoryInstanceName
    else
    select distinct CategoryInstanceName as Vendor0 from v_CategoryInfo where CategoryTypeName='Company' and CategoryInstanceName like @filterwildcard
    order by CategoryInstanceName
    end

    As you can see i’ve inserted a Union after the first Select – Where clause. This will add “All” to the list of values returned by the first select statement. I don’t need to add this to the query in the Else block because this is the query used when you use a wildcard to select a value before running the report.

  9. Click on Ok to finish editing that dataset, and then select the dataset that is going to be displayed as the report. This will be the dataset which has the “Display Dataset on Report” tickbox ticked.  Click on “Edit Command Text”.
  10. Scroll Down to the WHERE clause in the report, and find the line that checks the value of your parameter against each entry in the database.  In this example, this is the @Vendor parameter, which is checked in the following line :
  11. and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor)

    And then modify it so that every item will match if the @Vendor parameter equals our new value, ‘All’

    and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor or @Vendor = 'All')
  12. Make the same changes for any other parameters you wish to modify (@UpdateClass, for example) and then click on Ok.
  13. Finally, click OK again to save the changes to your report.

You should now find when you run the report, that the “All” entry will appear in your Vendor list, and when selected, updates from every vendor are shown in the report!  You can easily perform this same operation on other reports if you wish to, just make sure that the parameters you are modifying are not actually required for the report.  Generally, any parameter that’s used simply to reduce the results returned can be modified in this way.

Advertisements

2 Responses to “SCCM – Modifying SQL Reporting Services Update Reports so that Update ‘Vendor’ and ‘Category’ are optional”

  1. Mykel said

    and i am suppose to do junk like this for every report?

    • Matthew said

      Unfortunately this will be the case for many of the SRS reports, as more than a small number were not translated very well. This is one of the reasons that many people leave the original SCCM reporting point installed, and use whichever is more convenient for the report in question.

      There are quite a few 3rd party products that attempt to assist with SCCM Reporting (especially around Asset intelligence, OSD and Software updates) which complement or replace the built in reports, but nearly all of them are licensed solutions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s