MS Sql Server Report Builder 3 and NeSS data

[Steven]

Does anyone have any experience with utilising the data exchange and
pulling it into MS Report Builder? I am keen to utilise this data
alongside my own local information to generate community profiles but
have yet to get the SOAP queries working properly to pull back actual
data. I currently have next to no experience with SOAP but just need
an example to get things rolling if anyone has any thing to share.

Thanks,

Steven

[Frank]

Steven,

 

one of our technical team responded with:

 

Download Report Builder 3 (free) http://www.microsoft.com/en-gb/download/details.aspx?id=6116

Create an XML Data Source http://msdn.microsoft.com/en-us/library/ms159741(v=SQL.100).aspx

Check "Use a connection embedded in my computer"

Select "XML" from the connection type

No credentials required

Paste a discovery or delivery NDE URL into the connection string box

e.g. http://neighbourhood.statistics.gov.uk/NDE2/Disco/GetHierarchies

or http://neighbourhood.statistics.gov.uk/NDE2/Deli/getChildAreaTables?Datasets=1359&ParentAreaId=279217

Create a Dataset from the Data Source - specify the XML nodes to include or empty query for all nodes

Use the table wizard to make a table from the Dataset.

Hope this helps

 

[Steven]

Frank,

Many thanks - I had got the disco side of things working but not the
deli, and while both the address methods above produce a return and
table I can not get any data through the delivery method - I end up
with a table of meta data which is probably what I need to specify
nodes but I think I have missed something in still returning the
actual data values.

Sorry to ask for further advice on this, however I hope it is just
another single step to then give me enough of a push to understand...

Thanks,

Steven

On Dec 18, 12:39 pm, Frank <better.i...@ons.gov.uk> wrote:
> Steven,
>
> one of our technical team responded with:
>

> Download Report Builder 3 (free)http://www.microsoft.com/en-gb/download/details.aspx?id=6116
>
> Create an XML Data Sourcehttp://msdn.microsoft.com/en-us/library/ms159741(v=SQL.100).aspx

>
> Check "Use a connection embedded in my computer"
>
> Select "XML" from the connection type
>
> No credentials required
>
> Paste a discovery or delivery NDE URL into the connection string box
>
> e.g.http://neighbourhood.statistics.gov.uk/NDE2/Disco/GetHierarchies
>

> orhttp://neighbourhood.statistics.gov.uk/NDE2/Deli/getChildAreaTables?D...

[Frank]

The Microsoft documentation says "When the element path is empty, the query uses the default element path: the first path to a leaf node collection" which in the case of an LGDX delivery query is the topic definitions.

Adding an XML Query to the Dataset properties allows you to retrieve a different section of the response. I tried this one

<Query xmlns:ns2="http://neighbourhood.statistics.gov.uk/nde/v1-0/deliveryservice" xmlns:ns3="http://schema.esd.org.uk/LGDX">

<ElementPath>

ns2:getDataCubeResponseElement/ns3:Datasets/ns3:Dataset/ns3:DatasetItems/ns3:DatasetItem

</ElementPath>

</Query>

and it does give the  cells, but this still leaves the issue of how to do the lookups to convert the ids to names (the NDE AJAX example zip file http://www.neighbourhood.statistics.gov.uk/dissemination/Info.do?page=NDE-2-0-Ajax.htm contains an XSL example of this). However, not having any SQL Server Report Tool experience in the office we are unable to make any recomendations at this point.