Walkthrough: How to list models from SQL Server Master Data Services in a SQL Server Reporting Services (SSRS) report

  • Article

 

SQL Server Master Data Services (MDS) is a new feature in SQL Server 2008 R2. It is the source of master data for an organization. To learn more information about it, you can read “Master Data Services”. This article walks through the steps of how to access the models of SQL MDS in a SSRS report. The points are exposing the MDS web services and using XML data source type to access the data from the web services.

 

Expose the MDS WCF services to using BasicHttpBinding

  1. In order to consume MDS web services, you need to enable web services for MDS first. For detailed steps, please refer to How to: Enable Web Services (Master Data Services).
  2. Open the web.config configuration file for the MDS web site with text editor. By default, the file is host in C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication
  3. Add the BasicHttpBinding configurations to the bindings section of the web.config file as follows:

                        <bindings>

                                    <wsHttpBinding>

                                                <binding name="mdsWsHttpBinding" maxBufferPoolSize="2147483647" maxReceivedMessageSize="2147483647">

                                                            <readerQuotas maxArrayLength="2147483647" maxBytesPerRead="2147483647" maxDepth="2147483647" maxNameTableCharCount="2147483647" maxStringContentLength="2147483647"/>

                                                            <!-- Non-SSL implementations.-->

                                                            <security mode="Message">

                                                                        <message clientCredentialType="Windows"/>

                                                            </security>

                                                            <!-- SSL implementations -->

                                                            <!--<security mode="Transport">-->

                                                            <!--<message clientCredentialType="Windows" />-->

                                                            <!--</security>-->

                                                </binding>

                                    </wsHttpBinding>

                                    <basicHttpBinding>

                                                <binding name="mdsBasicHttpBinding" maxBufferPoolSize="2147483647" maxReceivedMessageSize="2147483647">

                                                            <readerQuotas maxArrayLength="2147483647" maxBytesPerRead="2147483647" maxDepth="2147483647" maxNameTableCharCount="2147483647" maxStringContentLength="2147483647"/>

                                                            <!-- Non-SSL implementations.-->

                                                            <!--<security mode="Message">

                                                                        <message clientCredentialType="Windows"/>

                                                            </security>-->

                                                            <!-- SSL implementations -->

                                                            <!--<security mode="Transport">-->

                                                            <!--<message clientCredentialType="Windows" />-->

                                                            <!--</security>-->

                                                </binding>

                                    </basicHttpBinding>

                        </bindings>

4. Configure the service to use BasicHttpBinding

 

   <service behaviorConfiguration="mdsWsHttpBehavior" name="Microsoft.MasterDataServices.Services.Service">

   <!--<endpoint binding="wsHttpBinding" bindingConfiguration="mdsWsHttpBinding"

     bindingNamespace="https://schemas.microsoft.com/sqlserver/masterdataservices/2009/09"

     contract="Microsoft.MasterDataServices.Services.ServiceContracts.IService" /> -->

   <endpoint binding="basicHttpBinding" bindingConfiguration="mdsBasicHttpBinding"

     bindingNamespace="https://schemas.microsoft.com/sqlserver/masterdataservices/2009/09"

     contract="Microsoft.MasterDataServices.Services.ServiceContracts.IService" />

   </service>

 

Create a Shared Data Source or embedded Data Source that is using XML data source type

Create a Shared Data Source

  1. In the Business Intelligence Development Studio (BIDS), in the Solution Explorer, right-click Shared Data Sources.
  2. Then click Add New Data Source.
  3. In the Shared Data Source Properties dialog, type in a name for the data source, select type XML
  4. Set the connecting string to be the URL address of the MDS WCF service. E.g.  https://servername:10001/Service/Service.svc
  5. Go to Credentials tab, check Use Windows Authentication(Integrated Security)

Create an Embedded Data Source

  1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Data Sources.
  2. Then click Add Data Source.
  3. In the Shared Data Source Properties dialog, type a name for the data source, select type XML
  4. Set the connecting string to be the URL address of the MDS WCF service. E.g.  https://servername:10001/Service/Service.svc
  5. Go to Credentials tab, check Use Windows Authentication(Integrated Security)

 List all models in a SSRS report

  1. In the Business Intelligence Development Studio (BIDS), in the Report Data, right-click Datasets.
  2. Then click Add Dataset… .
  3. In the Dataset Properties dialog, in the Query tab, type a name for the dataset
  4. Choose the data source created before for this dataset.
  5. In the Query textbox, type the following query:

 

<Query>

          <Method Namespace="https://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="MetadataGetRequest">

         

 

          <Parameters>

            <Parameter Name="ResultOptions" Type="XML">

                        <DefaultValue>

                                    <Models>Details</Models>

                        </DefaultValue>

            </Parameter>

 

            <Parameter Name="SearchCriteria" Type="XML">

                        <DefaultValue>

                        </DefaultValue>

             </Parameter>

          </Parameters>

          </Method>

<SoapAction>https://schemas.microsoft.com/sqlserver/masterdataservices/2009/09/IService/MetadataGet</SoapAction>

          <ElementPath IgnoreNamespaces="True">MetadataGetResponse{}/Metadata{}/Models{}/Model{}/Identifier{Id, Name, InternalId}</ElementPath>

 </Query>

 

Pass user defined search criteria to the web service call

  1. In the Business Intelligence Development Studio (BIDS), in the Report Data, double-click the dataset for the web service call.
  2. In the Dataset Properties dialog, go to Parameters tab
  3. Click Add to add the first parameter.
    Note:
    1. The number of the parameters must be same with the number of the parameters in the web service call.
    2. The sequence of the parameters must be same with the sequence in the web service call.
    3.  The name of each parameter must be same with the parameter in the web service call.
  4. Set the parameter name to be ResultOptions, and the value to be :

                                    <Models>Details</Models>

 

5. Click Add again to and another parameter SearchCriteria.

6. Set the value of the parameter to be:

="<Models><Identifier><Name>" & Parameters!modelName.Value & "</Name></Identifier></Models>"

modelName is a report parameter.

 

More Information

a.      The ResultOptions parameter is a MetadataResultOptions object.

b.      The SearchCriteria parameter is a MetadataSearchCriteria object.

c.       In order to construct the SOAP message, you can follow these steps:

1.      Create a .NET application to invoke the MDS WCF services.

2.      Call the MetaDataGet method in the application.

3.      Debug the application.

4.      Use a tool named Fiddler to get the constructed SOAP message.

 d.      Another samples for ResultOptions:

          <Parameter Name="ResultOptions" Type="XML">

            <DefaultValue>

                        <AttributeGroups>Details</AttributeGroups>

                        <Attributes>Details</Attributes>

                        <DerivedHierarchies>Details</DerivedHierarchies>

                        <DerivedHierarchyLevels>Details</DerivedHierarchyLevels>

                        <Entities>Details</Entities>

                        <ExplicitHierarchies>Details</ExplicitHierarchies>

                        <MemberTypes>Details</MemberTypes>

                        <Models>Details</Models>

                        <VersionFlags>Details</VersionFlags>

                        <Versions>Details</Versions>

            </DefaultValue>

          </Parameter>

 

e.       Another sample for SearchCriteria:

          <Parameter Name="SearchCriteria" Type="XML">

            <DefaultValue>

            <Models><Identifier><Name>modelName</Name></Identifier></Models>><Entities><Identifier><Name>entityName</Name></Identifier></Entities>

            </DefaultValue>

          </Parameter>

 

Reference

XML Connection Type (SSRS)

https://msdn.microsoft.com/en-us/library/dd220468.aspx

Element Path Syntax for Specifying XML Report Data (SSRS)

https://msdn.microsoft.com/en-us/library/ms365158.aspx

ServiceClient.MetadataGet Method

https://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.serviceclient.metadataget.aspx

MetadataResultOptions Class

https://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.metadataresultoptions.aspx

MetadataSearchCriteria Class

https://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.services.datacontracts.metadatasearchcriteria.aspx

 

[Download sample]