How do I get this from as SSRS type object?

This topic contains 18 replies, has 3 voices, and was last updated by Profile photo of Richard Schaefer Richard Schaefer 4 months, 2 weeks ago.

  • Author
    Posts
  • #47295
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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...

  • #47305
    Profile photo of Dan Potter
    Dan Potter
    Participant

    $ds.item ?

  • #47308
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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.

  • #47310
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator
    $ds |
    Get-Member -Name Item |
    Select-Object -ExpandProperty Definition
    
  • #47314
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

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

    Thanks much.

  • #47316
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    No problem! 🙂

  • #47318
    Profile photo of Dan Potter
    Dan Potter
    Participant

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

    (gci |gm DirectoryName).definition

  • #47328
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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...

  • #47330
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Probably this:

    $ds.Item.GetType().FullName
    
  • #47332
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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...

  • #47334
    Profile photo of Dan Potter
    Dan Potter
    Participant

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

  • #47336
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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...

  • #47339
    Profile photo of Dan Potter
    Dan Potter
    Participant

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

  • #47341
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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.

  • #47343
    Profile photo of Dan Potter
    Dan Potter
    Participant

    Is there a method called getitemdefinition under $ssrsProxy?

  • #47350
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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.

  • #47353
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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.

  • #47368
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

    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!!!

  • #48117
    Profile photo of Richard Schaefer
    Richard Schaefer
    Participant

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

You must be logged in to reply to this topic.