How do I get this from as SSRS type object?

richard-schaefer · July 18, 2016, 6:23am

I’m trying to query the SSRS Web Service to get information about a report’s data sources. I use this code:

$wsUrl="http://msbi-reports-dev.thehartford.com/ReportServer/ReportService2010.asmx"
Write-Host "Creating Proxy, connecting to : $wsUrl"
	$ssrsProxy = New-WebServiceProxy -Uri $wsUrl -UseDefaultCredential
$ds=$ssrsProxy.GetItemDataSources('/msbi/ads/ahpusers/ahpusers')

and I get back this object:

$ds|gm

TypeName: Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSource

Name        MemberType Definition
----        ---------- ----------                                                                                       
Equals      Method     bool Equals(System.Object obj)
GetHashCode Method     int GetHashCode()
GetType     Method     type GetType()
ToString    Method     string ToString()
Item        Property   Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceDe..
Name        Property   string Name {get;set;}

$ds|fl
Name : AHPUsersData
Item : Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceReference

The string that defines the Item property is the value I need. How do I get the actual value of the definition of the Item property? Relative newbie to PS and this is a little obtuse for me…

dan-potter · July 18, 2016, 6:54am

$ds.item ?

richard-schaefer · July 18, 2016, 7:00am

I tried $x=$ds.Item and I get a System.Management.Automation.PSParameterizedProperty object. I can’t figure out how to get the value. The Value property is System.Object Value {get;set;} and $x.Value gives me:

IsSettable          : True
IsGettable          : True
OverloadDefinitions : {System.Object IList.Item(int index) {get;set;}}
TypeNameOfValue     : System.Object
MemberType          : ParameterizedProperty
Value               : System.Object IList.Item(int index) {get;set;}
Name                : Item
IsInstance          : True

I feel like I’m running down a rabbit hole.

system · July 18, 2016, 7:25am

$ds |
Get-Member -Name Item |
Select-Object -ExpandProperty Definition

richard-schaefer · July 18, 2016, 7:27am

Dang! As soon as I saw your code I said “-ExpandProperty Definition” !! Of course. Duh.

Thanks much.

system · July 18, 2016, 7:28am

No problem!

dan-potter · July 18, 2016, 7:37am

Ha, totally misread that. I thought you wanted the object.

(gci |gm DirectoryName).definition

richard-schaefer · July 18, 2016, 8:22am

Almost, but not quite. Dave Potter’s solution returns:

Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceDefinitionOrReference

The response is a DataSourceDefinitionOrReference object that contains either a DataSourceReference or a DataSourceDefinition. If I query the data it looks like this:

PS C:\users\rs02130\Desktop\Projects\AHP\xml> $ds|fl

Name : AHPUsersData
Item : Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceReference

Which is a DataSourceReference object. If I query another report object I get:

Name : MSBI_Common
Item : Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceDefinition

Which is a DataSourceDefinition object. I’m guessing that what Item contains is a DataSourceOrDefinition object (gotta love MS) with one of the above two values. When I do the gm - Name Definition I get the DataSourceOrDefinition object, not the underlying object.

I think I have to get the object inside Item and get it’s definition?

Here goes the rabbit hole again…

system · July 18, 2016, 8:30am

Probably this:

$ds.Item.GetType().FullName

richard-schaefer · July 18, 2016, 8:33am

PS C:\users\rs02130\Desktop\Projects\AHP\xml> $ds.Item.GetType().FullName
System.Management.Automation.PSParameterizedProperty

This is the rabbit hole. It seems like once you try to dive into the object returned in $ds you lose the connection to the SSRS namespace and things get all bolloxed. This is impossible for you to replicate unless you have an SSRS server you can query…

dan-potter · July 18, 2016, 8:34am

I thought you asked for the definition? Not sure why you would do this. What are your intentions in using the string?

richard-schaefer · July 18, 2016, 8:40am

I need the last part of the value of this:
Item : Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceReference

vs. this:

Item : Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceDefinition

To determine what to do with the report datasource. When I run the code you gave me:

$ds |
Get-Member -Name Item |
Select-Object -ExpandProperty Definition

It returns:

Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceDefinitionOrReference

Which is the object the MS docs say is returned from a GetItemDatasources call against a report. I’m not sure why $ds shows up as a DataSourceReference or a DataSourceDefinition depending on which report I query when clearly the object type is actually a DataSourceDefinitionOrReference object based on what is returned from your code…

dan-potter · July 18, 2016, 8:52am

Still not following, what will you do with the definition after you retrieve it?

richard-schaefer · July 18, 2016, 9:03am

I’m writing a script to deploy reports to an SSRS server. After you copy the files you need to establish links between the datasources defined in your report files and the data source files you deployed. This is only done for report datasources that are DataSourceReference objects, not DataSourceDefinition objects. The process is (in simplified terms):

$ds=$proxy.GetItemDatasources(“path to report”)
if ($ds -eq “DataSourceReference”) {$proxy calls to link report to datasource}

But $ds appears to be a DataSourceReferenceOrDefinition object (despite what is displayed by the $ds|fl) which is what the docs say is returned by GetItemDatasources. The DataSourceReferenceOrDefinition object, according to the docs, contains either a DataSourceDefinition object or a DataSourceReference object.

It looks like the Format-List command is actually showing the type of object contained in $ds.Item as opposed to the definition of $ds.Item. So when I run the code you posted I get back a string that essentially says “DataSourceDefinitionOrReference”. When I try to dive into $ds.Item, assuming it contains an SSRS object, I end up with Powershell ParameterizedProperty objects, not SSRS objects. So I can’t extend the simple code above to match the SSRS docs.

dan-potter · July 18, 2016, 9:05am

Is there a method called getitemdefinition under $ssrsProxy?

richard-schaefer · July 18, 2016, 9:38am

Yes, but I can only run it against a file. If I run it against the report file it tells me it’s a report. I have to dig into the report to understand the datasources defined in the report to determine which (if any) datasources in the report need to be linked to datasource files that were deployed. I can’t go backwards from the datasource files to the datasources in the report. They’re only linked in the forward direction (report->datasource).

The prescribed method is to call GetItemDatasources against the report file. You get back one or more DataSourceDefinitionOrReference objects. Each one points to either a DataSourceReference or DataSourceDefinition object, or one of its properties says “DataSourceReference” or “DataSourceDefinition”. I haven’t gotten to that level yet.

I’ve also asked our MS rep for help. If we can’t figure this out maybe someone at MS can. Worst case I’ll be in Redmond in August and one of the session is Powershell so I’ll bug them.

richard-schaefer · July 18, 2016, 9:56am

Here’s the problem in a nutshell (I think). I run this:

$ds=$ssrsProxy.GetItemDataSources('/msbi/ads/ahpusers/ahpusers')
$ds|fl
$ds|Get-Member -Name Item|fl

I get back this from $ds|fl

Name : AHPUsersData
Item : Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceReference

I get back this from $ds|Get-Member -Name Item|fl:

TypeName   : Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSource
Name       : Item
MemberType : Property
Definition : Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceDefinitionOrReference Item {get;set;}

The second (from the code from @Dave Wyatt) actually shows that the object inside $ds is a DataSourceDefinitionOrReference object (I think). I’m really starting to believe that the $ds|fl is displaying the content of that object somehow which is why it’s showing a DataSourceDefinition. I just can’t figure out how to get to that “Definition vs. Reference” that appears to be buried in there.

richard-schaefer · July 18, 2016, 10:26am

Well, here’s the answer. I had also posted on StackOverflow and I got this (extended a bit to parse out the part I need):

$ds | select -expand Item|select @{n="SourceType";e={$_.GetType()}}

This returns:
Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3tServer_ReportService2010_asmx.DataSourceReference

All I have to do is test the name to see if it ends in “DataSourceReference” and then I can execute the appropriate code!!!

richard-schaefer · July 25, 2016, 7:48am

Turns out: $ds.Item|Select -ExpandProperty Definition