Programmatically Modifying and Verifying Data Sources for Paginated Reports

This blog post is a follow up to a previous blog post (Programmatically Setting and Verifying Data Source Credentials for Power BI Reports). While that blog post was focused on Power BI Reports, in this blog post, I want to explain how you can set data source credentials for Paginated Reports (via /Reports(…)/DataSources) using REST API. The intent here is to help users fully transition to REST API for managing and verifying data source credentials instead of using REST API for Power BI Reports but SOAP API for Paginated Reports.

Prerequisites

The primary assumption made in this blog post is you are using either Power BI Report Server (October 2017) or SQL Server Reporting Services 2017. The reason behind this is the APIs described here were first shipped in this release. If you haven’t had a chance to explore https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0 (for Power BI Report Server users) or https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0 (for SQL Server Reporting Services users) so far, I highly recommend you to have a look at this page.

DataSources API

Background

If you look through the SwaggerHub Doc, you will notice the word “DataSources” appears several times:

  • /DataSources
  • /DataSets(…)/DataSources
  • /PowerBIReports(…)/DataSources
  • /Reports(…)/DataSources

Without going into too much detail, each API is for managing either Shared Data Sources or embedded Data Sources for Shared Data Sets, Power BI Reports, and Paginated Reports. In a previous blog post, I explained how to use /PowerBIReports(…)/DataSources API.

In this blog post, I am going to focus on /Reports(…)/DataSources API.

Retrieving Paginated Report Data Sources

Retrieving data sources for Paginated Reports is not any different than retrieving data sources for Power BI Reports. You need to perform a GET request to one of following APIs:

  • Retrieve Data Sources using Paginated Report ID
    http://localhost/Reports/api/v2.0/Reports(<Id>)/DataSources

    Please substitute <ID> with the actual ID of your Paginated Report. (For example http://localhost/Reports/api/v2.0/Reports(00000-00000-00000-00000-00000)/DataSources)

  • Retrieve Data Sources using Paginated Report Path
    http://localhost/Reports/api/v2.0/Reports(Path=‘<path>’)/DataSources

    Please substitute <path> with the path of your Power BI Report. (For example http://localhost/Reports/api/v2.0/Reports(Path=‘/My PBI Report’)/DataSources)

When you make a request to this API, you will get a response like the following.

Click here to see the response
{
  "@odata.context": "string",
  "@odata.count": 0,
  "value": [
    {
      "Id": "01234567-89ab-cdef-0123-456789abcdef",
      "Name": "string",
      "Description": "string",
      "Path": "string",
      "Type": "Unknown",
      "Hidden": true,
      "Size": 0,
      "ModifiedBy": "string",
      "ModifiedDate": "2017-04-13T15:51:04Z",
      "CreatedBy": "string",
      "CreatedDate": "2017-04-13T15:51:04Z",
      "ParentFolderId": "01234567-89ab-cdef-0123-456789abcdef",
      "ContentType": "string",
      "Content": "string",
      "IsFavorite": true,
      "IsEnabled": true,
      "ConnectionString": "string",
      "DataModelDataSource": {
        "AuthType": "Windows",
        "SupportedAuthTypes": [
          "Windows",
          "UsernamePassword"
        ],
        "Kind": "SQL",
        "ModelConnectionName": "string",
        "Secret": "string",
        "Type": "Import",
        "Username": "string"
      },
      "DataSourceSubType": "string",
      "DataSourceType": "string",
      "IsOriginalConnectionStringExpressionBased": true,
      "IsConnectionStringOverridden": true,
      "CredentialsByUser": {
        "DisplayText": "string",
        "UseAsWindowsCredentials": true
      },
      "CredentialsInServer": {
        "UserName": "string",
        "Password": "string",
        "UseAsWindowsCredentials": true,
        "ImpersonateAuthenticatedUser": true
      },
      "IsReference": true,
      "Subscriptions": {
        "Id": "01234567-89ab-cdef-0123-456789abcdef",
        "Owner": "string",
        "IsDataDriven": true,
        "Description": "string",
        "Report": "string",
        "IsActive": true,
        "EventType": "string",
        "ScheduleDescription": "string",
        "LastRunTime": "2017-04-13T15:51:04Z",
        "LastStatus": "string",
        "ExtensionSettings": {
          "Extension": "string",
          "ParameterValues": {
            "Name": "string",
            "Value": "string",
            "IsValueFieldReference": true
          }
        },
        "DeliveryExtension": "string",
        "LocalizedDeliveryExtensionName": "string",
        "ModifiedBy": "string",
        "ModifiedDate": "2017-04-13T15:51:04Z",
        "ParameterValues": [
          {
            "Name": "string",
            "Value": "string",
            "IsValueFieldReference": true
          }
        ]
      }
    }
  ]
}

Updating Data Sources

Unlike Power BI Reports, you can modify a wider range of properties for Paginated Reports. In most cases, I have found that changing just the following list of properties (which are bolded and underlined) is all you need to do:

  • DataSourceType: You can change the type of data source by changing this property. The list of values you can specify here is dependent on the values found under <Extensions>\<Data> in <installDirectory>\ReportServer\RSReportServer.config file. By default, you can use any of the following data extensions: SQL, SQLAzure, Essbase, ODBC, OLEDB, OLEDB-MD, Oracle,  SAPBW, SharePointList, and XML.
  • ConnectionString: You can change the connection string of a data source by changing this property. If you do change your connection string, you will also need to change value of IsConnectionStringOverridden property to true. (See example below.)
  • CredentialRetrieval: You can change the type of credentials to use when Report Server tries to fetch data from your data source. The valid values for this property are: Integrated, Store, Prompt and None.
    • Integrated: When this property is specified, the credentials of the user who is consuming this data source will be used when Report Server is fetching data.
    • Store: When this property is specified, you can specify the credentials to use when connecting to the data source in another property called CredentialsInServer. Report Server will uses these credentials when connection to the data source and fetching the data. (See example below.)
    • Prompt: When this property is specified, Report Server will prompt the user, who is consuming this data source, for credentials. You can optionally specify additional information such as the prompt message to display and whether the credentials entered are Windows credentials or SQL credentials in another property called CredentialsByUser. (See example below.)

After you have finished updating your data sources, you need to make a PUT request to the same URL which you had made a GET request to. In the body of your request, you will need to provide an array of ALL the Data Sources, which you received when you performed GET request regardless of whether you modified 1 or all of them. The following is an example of request body.

Example: Modifying Data Source Connection String

Let’s assume you have just uploaded your Paginated Report and you want to change the connection string of your data source. (In order to focus on the properties you need to set, I will assume you have fetched the data source from Report Server and deserialized into a variable called datasource.)

datasource.ConnectionString = "My new connection string";
datasource.IsConnectionStringOverridden = true;

Example: Modifying Data Source to use Stored Credentials

Let’s assume you have just uploaded your Paginated Report and you want Report Server to connect to the data source using a specific credentials. You can optionally specify (via ImpersonateAuthenticatedUser property) whether Report Server should fetch data using the specific credentials or using the credentials of the current user.  (In order to focus on the properties you need to set, I will assume you have fetched the data source from Report Server and deserialized into a variable called datasource.)

datasource.CredentialRetrieval = "Store";
datasource.CredentialsInServer = 
    new {
        UserName = "<username>";
        Password = "<sql user password>";
        UseAsWindowsCredentials = false; // set this to true if these are Windows credentials
        ImpersonateAuthenticatedUser = false; // set this to true if you want impersonation
    };

Example: Modifying Data Source to Prompt user for their credentials

Let’s assume you have just uploaded your Paginated Report and you want Report Server to ask user for credentials to use for retrieving data.  (In order to focus on the properties you need to set, I will assume you have fetched the data source from Report Server and deserialized into a variable called datasource.)

datasource.CredentialRetrieval = "Prompt";
datasource.CredentialsByUser = 
    new {
        DisplayText = "Please enter your credentials."; // change this to the message you want to display to user
        UseAsWindowsCredentials = false;  // set this to true if user will specify Windows credentials
    };

Verifying credentials are valid (aka Test Connection)

Once you have set the credentials, you would also want to verify that Report Server is able to connect to your data source using the specified AuthType and credentials. In order to do this, you have to make a POST request to one of the following APIs:

  • Perform Test Connection using Paginated Report ID
    http://localhost/Reports/api/v2.0/Reports(<Id>)/Model.CheckDataSourceConnection

    Please substitute <ID> with the ID of your Paginated Report. (e.g. http://localhost/Reports/api/v2.0/Reports(00000-00000-00000-00000-00000)/Model.CheckDataSourceConnection)

  • Perform Test Connection using Paginated Report Path
    http://localhost/Reports/api/v2.0/Reports(Path=‘<path>’)/Model.CheckDataSourceConnection

    Please substitute <path> with the path of your Paginated Report. (e.g. http://localhost/Reports/api/v2.0/Reports(Path=‘/My Paginated Report’)/Model.CheckDataSourceConnection)

In the body of your request, you need to specify the Data Source Name, which you want to perform Test Connection on. Unfortunately, you can only perform Test Connection at 1 data source at a time. If you are not sure what the Name of your data source is, just look at the response you got when you fetched all data sources for your Paginated Report. Here is how your request body should look like.

{ 
    "DataSourceName":"<enter Data Source Name>" 
}

Once you make that request, in the response you receive, you will see a JSON object with 2 properties: IsSuccessful and ErrrorMessage. If Report Server is able to connect to your data source using your selected AuthType and credentials, IsSuccessful will be true, otherwise IsSuccessful will be false and ErrorMessage will contain some information on what went wrong.

Add a Comment

Your email address will not be published. Required fields are marked *