Compartilhar via

Rendering SQL Reporting Reports with PowerShell

  • Artigo

A couple of weeks ago I was playing around with the new PowerShell v2.0 cmdlet New-WebServiceProxy cmdlet and I found that you can also use this cmdlet for Rendering SQL Reports. Let’s start with some info on the New-WebServiceProxy cmdlet.

NAME     New-WebServiceProxy

SYNOPSIS     Creates a Web service proxy object that lets you use and manage the Web service in Windows PowerShell.

SYNTAX     New-WebServiceProxy [-URI] <Uri> [[-Class] <string>] [[-Namespace] <string>] [<CommonParameters>]      New-WebServiceProxy [-URI] <Uri> [[-Class] <string>] [[-Namespace] <string>] [-Credential <PSCredential>] [<CommonParameters>]      New-WebServiceProxy [-URI] <Uri> [[-Class] <string>] [[-Namespace] <string>] [-UseDefaultCredential] [<CommonParameters>]

DESCRIPTION     The New-WebServiceProxy cmdlet lets you use a Web service in Windows PowerShell. The cmdlet connects to a Web service and creates a Web service proxy object in Windows PowerShell. You can use the proxy object to manage the Web service.

    A Web service is an XML-based program that exchanges data over a network, particularly over the Internet. The Microsoft .NET Framework provides Web service proxy objects that     represent the Web service as a .NET Framework object.

Jeremy Pavleck has also blogged about using this Cmdlet together with OpsMgr in his blogarticle How to monitor Everything. Check it out for more options of using this Cmdlet.

So how can we use this Cmdlet with our OpsMgr Reports?

Let me give an example. I’ve tried in the past to find the number of OpsMgr licenses using PowerShell. Till now I failed badly. But let’s try to get the OpsMgr License Report info in PowerShell using the New-WebServiceProxy Cmdlet.

What info do we need to Render the OpsMgr Licenses Report?

  • SQL Report Server Name
  • Path to the Report
  • The Report Parameters

 

SQL Report Server Name

This is an easy one. This is just the name of your SQL Reporting Server.
image

Path to the Report

In our case this is the OpsMgr Licenses Report. You can find the Path to the OpsMgr Licenses Report by opening your Favorite Web browser and going to https://reportservername/reports
image

So the path to the License Report is "/Microsoft.SystemCenter.DataWarehouse.Report.Library/Microsoft.SystemCenter.DataWarehouse.Report.License"

 

The Report Parameters

The easiest way to find the correct parameters is by running the Report from the OpsMgr Console and Publish the Report. Now you can find the Report under My Reports.

image

Open the Report from your browser

image

Here you already see the parameters being used  by the License Report, but you can even see the parameters and their Data Type if you go to Properties and then select Parameters.

image

 

Ok, now we know everything needed for Rendering the SQL License Report from PowerShell. Because I want to show the result of the Report in the PowerShell shell I want the data returned to be as clear as it can be, so that’s why I render the Report in a CSV format.

image

Our goal is to create a script that renders the OpsMgr Licenses Report from a PowerShell script and shows the result like this:

image

This shows that there are 2 standard OpsMgr Licenses needed in my demo environment. Keep in mind that the OpsMgr Licenses Report differentiates between Physical and Virtual Machines.

Let’s get scripting!

I started with this version of the RenderSQLReportFromPosh script

#requires -version 2.0            ###############################################################################            # Render SQL Reports using PowerShell            # This script is using the new Posh v 2.0 cmdlet New-WebServiceProxy            # FileName: RenderSQLReportFromPosh.v1.000.ps1            # Authors: Stefan Stranger (Microsoft)            # Help from: Jin Chen (Microsoft)            # Example of Rendering the OpsMgr Licenses Report from the Generic Report Library            #            # v1.000 – 15/05/2010 - stefstr - initial sstranger's release            ###############################################################################                        #Define Variables            #Enter URI to asmx file on Report Server            $URI  = "https://opsmgrrms.stranger.local//ReportServer//ReportExecution2005.asmx?wsdl"            #Enter Report Path            $ReportPath = "/Microsoft.SystemCenter.DataWarehouse.Report.Library/Microsoft.SystemCenter.DataWarehouse.Report.License"            $format = "csv"            $deviceinfo = ""            $extention = ""            $mimeType = ""            $encoding = "UTF-8"            $warnings = $null            $streamIDs = $null            $Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential -namespace "ReportExecution2005"                                    $rsExec = new-object ReportExecution2005.ReportExecutionService            $rsExec.Credentials = [System.Net.CredentialCache]::DefaultCredentials                         #Set ExecutionParameters            $execInfo = @($ReportPath, $null)                         #Load the selected report.            $rsExec.GetType().GetMethod("LoadReport").Invoke($rsExec, $execInfo) | out-null                           #Report Parameters            #Depending on the number of Parameters being used in the Report you need to add more Parameters.            #Search the rdl file for the correct parameter names.            $param1 = new-object ReportExecution2005.ParameterValue            $param1.Name = "ManagementGroupId"            $param1.Value = "MGT01"                         $param2 = new-object ReportExecution2005.ParameterValue            $param2.Name = "StartDate_BaseType"            $param2.Value = "Today"                         $param3 = new-object ReportExecution2005.ParameterValue            $param3.Name = "StartDate_BaseValue"            $param3.Value = "04-21-2010" #Use US Date time!                        $param4 = new-object ReportExecution2005.ParameterValue            $param4.Name = "StartDate_OffsetType"            $param4.Value = "Day"                         $param5 = new-object ReportExecution2005.ParameterValue            $param5.Name = "StartDate_OffsetValue"            $param5.Value = "-1"                         $param6 = new-object ReportExecution2005.ParameterValue            $param6.Name = "EndDate_BaseType"            $param6.Value = "Today"                         $param7 = new-object ReportExecution2005.ParameterValue            $param7.Name = "EndDate_BaseValue"            $param7.Value = "04-21-2010"                         $param8 = new-object ReportExecution2005.ParameterValue            $param8.Name = "EndDate_OffsetType"            $param8.Value = "None"                         $param9 = new-object ReportExecution2005.ParameterValue            $param9.Name = "EndDate_OffsetValue"            $param9.Value = "0"                         $param10 = new-object ReportExecution2005.ParameterValue            $param10.Name = "TimeZone"            $param10.Value = "C4FFFFFF00000000C4FFFFFF00000A0000000500030000000000000000000300000005000200000000000000|W. Europe Standard Time"                         $param11 = new-object ReportExecution2005.ParameterValue            $param11.Name = "TimeZoneName"            $param11.Value = "(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna"                         $param12 = new-object ReportExecution2005.ParameterValue            $param12.Name = "Interactive"            $param12.Value = "False"                         $parameters = [ReportExecution2005.ParameterValue[]] ($param1,$param2,$param3,$param4,$param5,$param6,$param7,$param8,$param9,$param10)                         #Set ExecutionParameters            $ExecParams = $rsExec.SetExecutionParameters($parameters, "en-us");                         $render = $rsExec.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs)                         $Result = [text.encoding]::ascii.getString($render)                         #Write the Result to the PowerShell shell            $Result

Result in PowerShell:

image 

This is looking good! But as you see there is still some info we are not really interested in, like when the report is generated, which I want to remove from the result. And we also want to be able to do some sorting off course.

So in the next example script I added formatting to have a cleaner result after rendering the OpsMgr Licenses Report.

 #requires -version 2.0            ###############################################################################            # Render SQL Reports using PowerShell            # This script is using the new Posh v 2.0 cmdlet New-WebServiceProxy            # FileName: RenderSQLReportFromPosh.v1.001.ps1            # Authors: Stefan Stranger (Microsoft)            # Help from: Jin Chen (Microsoft)            # Example of Rendering the OpsMgr Licenses Report from the Generic Report Library            #             # v1.000 – 15/05/2010 - stefstr - initial sstranger's release            # v1.001 - 16/5/2010  - stefstr - added formatting            ###############################################################################                         #Define Variables            #Enter URI to asmx file on Report Server            $URI  = "https://opsmgrrms.stranger.local//ReportServer//ReportExecution2005.asmx?wsdl"            #Enter Report Path            $ReportPath = "/Microsoft.SystemCenter.DataWarehouse.Report.Library/Microsoft.SystemCenter.DataWarehouse.Report.License"            $format = "csv"            $deviceinfo = ""            $extention = ""            $mimeType = ""            $encoding = "UTF-8"            $warnings = $null            $streamIDs = $null            $Reports = New-WebServiceProxy -Uri $URI -UseDefaultCredential -namespace "ReportExecution2005"            #Temporay File to store the Result of the CSV rendering. It turned out that formatting the Result in memory was not easy.           $TempFile = "d:\temp\render.txt"                               $rsExec = new-object ReportExecution2005.ReportExecutionService            $rsExec.Credentials = [System.Net.CredentialCache]::DefaultCredentials                         #Set ExecutionParameters            $execInfo = @($ReportPath, $null)                         #Load the selected report.            $rsExec.GetType().GetMethod("LoadReport").Invoke($rsExec, $execInfo) | out-null                                     #Report Parameters            #Depending on the number of Parameters being used in the Report you need to add more Parameters.            #Search the rdl file for the correct parameter names.            $param1 = new-object ReportExecution2005.ParameterValue            $param1.Name = "ManagementGroupId"            $param1.Value = "MGT01"                         $param2 = new-object ReportExecution2005.ParameterValue            $param2.Name = "StartDate_BaseType"            $param2.Value = "Today"                         $param3 = new-object ReportExecution2005.ParameterValue            $param3.Name = "StartDate_BaseValue"            $param3.Value = "04-21-2010" #Use US Date time!                         $param4 = new-object ReportExecution2005.ParameterValue            $param4.Name = "StartDate_OffsetType"            $param4.Value = "Day"                         $param5 = new-object ReportExecution2005.ParameterValue            $param5.Name = "StartDate_OffsetValue"            $param5.Value = "-1"                         $param6 = new-object ReportExecution2005.ParameterValue            $param6.Name = "EndDate_BaseType"            $param6.Value = "Today"                         $param7 = new-object ReportExecution2005.ParameterValue            $param7.Name = "EndDate_BaseValue"            $param7.Value = "04-21-2010"                         $param8 = new-object ReportExecution2005.ParameterValue            $param8.Name = "EndDate_OffsetType"            $param8.Value = "None"                         $param9 = new-object ReportExecution2005.ParameterValue            $param9.Name = "EndDate_OffsetValue"            $param9.Value = "0"                         $param10 = new-object ReportExecution2005.ParameterValue            $param10.Name = "TimeZone"            $param10.Value = "C4FFFFFF00000000C4FFFFFF00000A0000000500030000000000000000000300000005000200000000000000|W. Europe Standard Time"                         $param11 = new-object ReportExecution2005.ParameterValue            $param11.Name = "TimeZoneName"            $param11.Value = "(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna"                         $param12 = new-object ReportExecution2005.ParameterValue            $param12.Name = "Interactive"            $param12.Value = "False"                         $parameters = [ReportExecution2005.ParameterValue[]] ($param1,$param2,$param3,$param4,$param5,$param6,$param7,$param8,$param9,$param10)                         #Set ExecutionParameters            $ExecParams = $rsExec.SetExecutionParameters($parameters, "en-us");                         $render = $rsExec.Render($format, $deviceInfo,[ref] $extention, [ref] $mimeType,[ref] $encoding, [ref] $warnings, [ref] $streamIDs)                         $Result = [text.encoding]::ascii.getString($render)                         #Write $Result to temp file.            #Was not able to remove first 7 lines in memory.            $Result > $TempFile                        #Get Content Temp File            $FinalResult = Get-Content -path $TempFile                        #Remove Temp File            Remove-Item $TempFile                        #Remove first 6 lines from $result. Depends on Report being run.            $Result = @($FinalResult[6..$FinalResult.Length])                                    #Format the results            $header, $data = $Result # we use multiple assignments to separate each of the sections into it's own variable                        #Find out how wide the fields are by splitting the string on comma's            $first, $second,$third = $header.split(",") |                 foreach { $_.length }                $second+=$first                        #Reformat the headers.            $h1,$h2,$h3 = $header.split(",") #Remark: Having the same column names is an issue so change the Name to License                       #Change the first Column Name to License            $h1 = "License"                        $data | foreach {                $v1, $v2, $v3 = $_.split(",")                                New-Object psobject |                    add-member -pass -mem Noteproperty $h1 $v1 |                    add-member -pass -mem Noteproperty $h2 $v2 |                    add-member -pass -mem Noteproperty $h3 $v3            }

Let’s check again if we get the Result we hoped for.

image

Yabbadabbadoo! Now we can add more info if we want, but that’s up to you. Have fun rendering SQL Reports from PowerShell! Let me know about cool new PowerShell scripts and results rendering OpsMgr Reports.

Here are some sources I used when trying to get PowerShell rendering SQL Reports.

Comments

  • Anonymous
    November 15, 2014
    The comment has been removed
  • Anonymous
    November 15, 2014
    By the way I see Jin Chen in the credits. I assume that this is the same person who answered some recent posts for me on the MS Reporting Services Forum, which I much appreciate.
  • Anonymous
    November 10, 2016
    Hi Stefan,Its very handy blog. Thanks for sharing. Can you pls let me know, what if i want to apply this Report Builder SQL query for only for specific collections instead of All Systems. Do I need to add some others commands in the ends of this .I will be thankful to your help.ThanksSyed
    • Anonymous
      November 20, 2016
      Hi Syed,It has been quite a long time ago when I wrote this blog post, so it's not completely clear to me anymore how it all worked.What I can remember is that the query is determined by the parameters of the Report.Hope this helps a bit./Stefan
  • Anonymous
    March 06, 2017
    I am getting the following error:Add-Type : Cannot add type. The assembly 'Microsoft.ReportViewer.WinForms, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' could not be found.Any idea what might be?Thanks