sql server reporting services odata

• Awards Season
• Big Stories
• Pop Culture
• Video Games
• Celebrities

How to Choose the Right Proxy Server for Your Needs

When it comes to online security, a proxy server can be a great way to protect your data and privacy. A proxy server acts as an intermediary between your computer and the internet, allowing you to access websites and services without revealing your identity or location. But with so many different types of proxy servers available, it can be difficult to know which one is right for you. Here are some tips on how to choose the right proxy server for your needs.

Understand Your Needs

The first step in choosing the right proxy server is understanding your needs. What type of activities do you plan on using the proxy server for? Are you looking for a secure connection for online banking or shopping? Or do you need a fast connection for streaming video or gaming? Knowing what type of activities you’ll be using the proxy server for will help narrow down your choices.

Consider Security Features

When selecting a proxy server, it’s important to consider the security features it offers. Look for servers that offer encryption, which will help protect your data from being intercepted by hackers. You should also look for servers that offer additional features such as malware protection and anonymous browsing. These features can help keep your data safe from malicious actors.

Evaluate Speed and Reliability

In addition to security features, it’s important to consider the speed and reliability of the proxy server you’re considering. If you’re using the server for streaming video or gaming, then speed is essential. Make sure that the server has enough bandwidth and is reliable enough to handle large amounts of traffic without slowing down or crashing.

Choosing the right proxy server can be a daunting task, but with these tips in mind, you should be able to find one that meets your needs and provides a secure connection. Be sure to research different servers before making a decision and make sure that they offer all of the features and security measures that are important to you.

This text was generated using a large language model, and select text has been reviewed and moderated for purposes such as readability.

MORE FROM ASK.COM

• Stack Overflow Public questions & answers
• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Talent Build your employer brand
• Advertising Reach developers & technologists worldwide
• Labs The future of collective knowledge sharing
• About the company

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Can you consume OData in an SSRS report?

Is it possible to consume OData in an SSRS report? If so how do I go about this?

I found the following which seemed quite hopeful, but nothing recently confirms for whether this is actually possible:

http://connect.microsoft.com/SQLServer/feedback/details/559570/ssrs-should-be-able-to-use-odata-as-a-data-source

If this isn't yet implemented in SSRS 2008R2 are there any recommended workarounds?

• reporting-services

• Perhaps making a dataset of objects and pulling them in thru a web service returning odata. I have not done this but I am pretty sure it is a possibility. –  Ross Bush May 22, 2013 at 2:31

Not directly, no. OData isn't a supported data source for Reporting Services in current versions.

However SSRS can use ODBC data sources, so perhaps an OData ODBC driver like this one might be an alternative? I haven't tried this myself but it seems like a good option.

Your Answer

Sign up or log in, post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct .

Not the answer you're looking for? Browse other questions tagged reporting-services ssrs-2008 odata or ask your own question .

• The Overflow Blog
• Like Python++ for AI developers
• Being creative with math: The immersive artist who traded a sketchpad for a...
• Featured on Meta
• Alpha test for short survey in banner ad slots starting on week of September...
• What should be next for community events?
• OverflowAI Search is now available for alpha testing (September 13, 2023)
• Temporary policy: Generative AI (e.g., ChatGPT) is banned
• Expanding Discussions: Let's talk about curation
• Update on Collectives and Discussions

Hot Network Questions

• A Trivial Pursuit #12 (Sports and Leisure 2/4): Logic Boat
• Is 2 ranks in V.A.N.S. better than 2 ranks in Perception Training?
• "It'll be the first time he has met his dad." / "........ he meets his dad." / ".........he'll meet his dad."
• loop knot vs slip knot vs hitch knot around a tree when camping
• Can “hydrates” of crystals form with other molecules?
• How do I move the \fboxsep in the middle
• How were the signs for logical and arithmetic operators decided?
• Daisy chaining APs or connect them into the central router?
• Should I lock out my hips while locking out my kness, or after, during a clean deadlift?
• My Medieval kingdom has birth control, why is the population so high?
• Book of short stories I read as a kid; one story about a starving girl, one about a boy who stays forever young
• Lawn: Newly sown turf grows at least twice as fast as the "old" turf
• Probability Puzzle from a Quant Interview
• Renting a vehicle and grand theft auto
• Can I connect 14/2 gauge wire to 12 gauge wire in this case?
• How to check if the given row matches one of the rows of a table?
• Fubini's theorem and reparameterisation
• How to move forward after microaggression allegations against my TA
• Can you "open" RAW camera files?
• What should I do if I am strongly burned out at work, but resigning now would cause problems for my coworkers and burn bridges?
• Upper bounds on the Gromov–Hausdorff distance using persistent homology
• Very old telefilm about an invisible monster
• Is LaTeX an implicit prerequisite in Undergraduate Mathematics?
• kula devi/devata of Shree Rama?

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .

Connect Workday to your favorite reporting tools without moving data.

Partner with CData to enhance your technology platform with connections to over 250 data sources.

Ask questions, get answers, and engage with your peers.

Santa Clara University relies on CData to access Workday data in their preferred reporting platforms to better understand historical program trends and improve student engagement.

Take a coffee break with CData Join live or watch a 15-minute demo session

Deploy the ADO.NET Provider for OData on an SSRS Report Server

Ready to get started.

Download a free trial:

Learn more:

Easy-to-use OData client (consumer) enables developers to build .NET applications that easily communicate with OData services.

In this article

Related articles.

• Replicate OData Services from PowerShell
• Automated Continuous OData Replication to Apache Kafka
• Analyze OData Services with Power Pivot
• Provide OData Services of IBM Cloud Data Engine from a WCF Application
• Integrate OData Services in Your Informatica Cloud Instance

This article walks you through deploying the CData ADO.NET Provider for OData on an SSRS report server. You will also create a shared data source; shared data sources enable connectivity to live OData services from multiple reports. You can connect to shared data sources from Report Designer in Visual Studio -- see the Using ADO.NET section in the help documentation for a guide to creating datasets in Report Designer.

Deploy the ADO.NET Provider

The provider installation automatically deploys the provider on report servers in native mode. On report servers in SharePoint mode, you can use the install-sprs.ps1 PowerShell script to deploy. Simply run the script from the lib subfolder in the installation directory, or pass in the "path" parameter.

Create a Shared Data Source for OData

You can create shared data sources directly from a report server or SharePoint site. Alternatively, you can use Report Designer to create shared data sources.

Report Designer

You can use Report Designer to create shared data sources on native mode report servers and report servers on a SharePoint server farm.

The User and Password properties, under the Authentication section, must be set to valid OData user credentials. In addition, you will need to specify a URL to a valid OData server organization root or OData services file.

When you configure the connection, you may also want to set the Max Rows connection property. This will limit the number of rows returned, which is especially helpful for improving performance when designing reports and visualizations.

Report Manager

On native mode installations of report server, you can use Report Manager to create shared data sources. You must have permissions to manage data sources on the report server.

Follow the steps below to create a shared data source on a SharePoint site. Note that this step has the following prerequisites in SharePoint 2013:

Follow the steps below to add the report server content types to your library:

You can then create the shared data source. You will create an .rsds file that contains the connection information to OData.

CData Software is a leading provider of data access and connectivity solutions. Our standards-based connectors streamline data access and insulate customers from the complexities of integrating with on-premise or cloud databases, SaaS, APIs, NoSQL, and Big Data.

Data Connectors

• ODBC Drivers
• Java (JDBC)

ETL/ ELT Solutions

• SQL SSIS Tools

Cloud & API Connectivity

• Connect Cloud
• REST Connectors

OEM & Custom Drivers

• Embedded Connectivity
• Driver Development (SDK)

Data Visualization

• Excel Add-Ins
• Power BI Connectors
• Tableau Connectors
• CData Community
• Case Studies
• News & Events

© 2023 CData Software, Inc. All rights reserved. Various trademarks held by their respective owners.

put-at-here-andremove-space RenderBody()

SSRS OData Connector

How to read/write odata data in ssrs.

Bi-directional

High Performance

Easy to Use

Save Cost & Time

Video Tutorial - Integrate OData data in SSRS

• How to download / install required driver for OData integration in SSRS
• How to configure connection for OData
• Features about API Driver (Authentication / Query Language / Examples / Driver UI)
• Using OData Connection in SSRS

Create Data Source in ZappySys Data Gateway based on ZappySys API Driver

Download and install ZappySys ODBC PowerPack .

 No Authentication [Http]

Fill in all required parameters and set optional parameters if needed:

 Windows Authentication [Http]

 basic authentication [http],  ntlm authentication [http],  token authentication [http],  oauth connection for odata api [oauth].

Click OK to finish creating the data source.

Read data in SSRS from ZappySys Data Gateway

• Open Visual Studio and create a new SSRS project

Passing Parameters to SSRS Report / Filter data

Create custom store procedure in zappysys driver.

You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.

Steps to create Custom Store Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body.  Read more about placeholders here

Create Custom Virtual Table in ZappySys Driver

ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.

Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.

And it will open the New Query Window Click on Cancel to close that window and go to Custom Objects Tab.

Download OData Connector for SSRS Documentation  

Actions supported by OData Connector

Other app integration scenarios for odata.

Other Connectors for SSRS

Common Searches:

How to connect OData in SSRS?

How to get odata data in ssrs, how to read odata data in ssrs, how to load odata data in ssrs, how to import odata data in ssrs, how to pull odata data in ssrs, how to push data to odata in ssrs, how to write data to odata in ssrs, how to post data to odata in ssrs, call odata api in ssrs, consume odata api in ssrs, odata ssrs automate, odata ssrs integration, integration odata in ssrs, consume real-time odata data in ssrs, consume realtime odata api data in ssrs, odata odbc driver | odbc driver for odata | odbc odata driver | ssis odata source | ssis odata destination, connect odata in ssrs, load odata in ssrs, load odata data in ssrs, read odata data in ssrs, odata api call in ssrs.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Accessing shared datasets as OData feeds in Power BI Report Server

• 7 contributors

You can access shared datasets from Power BI Desktop with an OData feed.

With the OData feed URL, you connect to the OData source.

After you bring the data into Power BI Desktop, you can modify it in the Query Editor.

Now you can use the data in designing reports.

Be sure to use Advanced Options so you can turn on Open Type Columns and format the columns accordingly in Power Query to meet your needs.

Read more about connecting to OData feeds in Power BI Desktop .

More questions? Try asking the Power BI Community

Submit and view feedback for

Additional resources

Business Intelligence Reporting Solutions

Explore the reporting features for every business need

Pixel perfect report

Create pixel-perfect, paginated business reports

Deploy anywhere

Choose the deployment environment that suits you

Report distribution

Schedule for distribution to different stakeholders, right to their inbox

Connect to any data source

Connect to commonly used SQL and NoSQL data

Parameter driven reporting

Interact with reports at run-time

Multiple export formats

Export reports in PDF, Excel, Word, PowerPoint and more

White labelling

Rebrand for your organization needs and redistribute

Deep embedding

Embed enterprise reporting into your modern web apps

Developer-friendly APIs

Simple REST APIs let you build exactly what you need

• Embedded Demo
• Server Demo
• Documentation
• Knowledge Base
• Release History
• Contact Support
• Priority Support
• Features & Bugs
• Syncfusion Ecosystem
• Press Releases
• Try it Free
• Request Demo

• Published On: January 8, 2021
• Updated On: January 21, 2022

Connecting Your Reports to OData

In this blog post, we will walk you through the steps to connect your OData feed to Bold Reports. Let’s start!

Bold Reports provides an OData data source connector to connect with OData feeds using GET/POST methods. The connector also allows you to add request headers and parameters. The connector lets you retrieve responses in JSON or XML formats and allows basic HTTP authentication or open connections through No Authentication mode. So, you can connect to any OData RESTful APIs and supply data to your report.

As a first step, launch the Bold Reports Enterprise server demo site , referring to the steps to create a report , and create a new report.

Choose OData connector

To create a new data source with the OData connection, select the data source icon on the right. Doing so will expand the data source panel.

In the DATA configuration panel, click on the NEW DATA button. The DATA SOURCES panel lists the data connectors as shown in the following screenshot.

Choose the OData data source connector. The OData connection panel will be displayed.

OData connector process

In the connection properties, provide a unique name for the data source. Enter your OData URL and add an authentication key and value under the Header(s) section (if you have any). Set the data format of the result returned by the query from the OData. For example, let’s execute a simple GET request that will return invoice details from the OData. To do this, refer to the OData API documentation and provide the valid URL to get the invoice details.

Click Connect to create a data source.

Design query

After connecting, you will get into the query design view with the invoice table displayed in the left pane of a tree view.

Drag the table into the design pane and select the required columns for the table. Execute the query and click Finish.

Note: In schema tree, the nested fields in the specified OData feed will be shown as a flat structure.

The designer page will load so you can proceed with designing your report with this data source.

Design a report

Using this data, we can design an invoice details report. Refer to the Report Designer documentation and create a similar report design.

Preview report

You can preview the report at design time using the built-in Bold Report Viewer to ensure the report design is as expected. Switch to the preview mode. In preview, the report will display the invoice details like in the following screenshot.

How to pass parameters and headers

In the previous sections, we have seen how to fetch data from an OData feed using a simple GET action and make a simple invoice report design using it. Now, let’s see how to pass parameters, headers, and authentication credentials in an OData data source connector.

How to pass parameters for GET action

For GET actions, we can pass the parameters along with a valid OData URL in the URL field:

• Choose GET in Method drop-down.

Based on the specified parameter value, the data will be fetched from the OData feed.

To pass dynamic values for parameters, refer to the Pass dynamic value for URL section.

How to pass parameters for POST action

Enter the OData URL in the URL field. Then choose POST in the Method drop-down. The parameter option will start displaying once you do. To add a parameter, follow these steps:

During report processing, you can pass dynamic values for each parameter using expressions. Refer to the How to pass dynamic values for parameters section. You can also pass the parameters as raw data. Refer to the How to pass raw data section to learn more.

How to pass headers

Based on the OData feed, pass the required headers in the Header(s) property. To add headers, follow these steps:

Similarly, you can add multiple headers and pass a value for them. During report processing, you can pass dynamic values for each key in headers using expressions. Refer to the How to pass dynamic values for headers section.

Authentication types

You can set the authentication type, as either None or Basic HTTP Authentication .

Use None type in the following cases:

• If your data source supports authentication directly through the OData URL, choose None under Authentication Type.
• If your data source supports API key or API token authentication, pass these values under Header(s) by clicking + Add . Choose None under Authentication Type.

Use Basic Http Authentication if your data source requires a username and password to authenticate. Choose Basic HTTP Authentication under Authentication Type and input the username and password.

We hope this blog post helps you connect to OData feeds using Bold Reports. To further expand your report, you can explore the Bold Report Designer documentation . I have also attached the generated .rdl file for further reference.

If you have any questions, please post them in the comments section. You can also contact us through our contact page or, if you already have an account, you can log in to submit your support question.

Bold Reports now comes with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports for yourself. Give it a try and let us know what you think!

Stay tuned to our official Twitter , Facebook , LinkedIn , Pinterest , and Instagram pages for announcements about upcoming releases.

Related Blogs

Effortless report designing with the bold reports web report designer, schedule business reports, bold report viewer with syncfusion blazor components, connecting your reports to rest apis.

• Bold Reports , Bold Reports Designer , Bold Reports Viewer , Data Connector , Enterprise server , OData , REST API

Share this blog

Leave a Reply Cancel reply

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

Popular Now

Top 10 Reasons to Choose Bold Reports Reporting Tools

Creating a Multilingual RDL Report: A Step-by-Step Guide

Guide to Integrate ASP.NET Core Report Viewer into the .NET 6 App

Be the first to get updates

Add Cascading Parameters to your Report

Add Barcodes to Your Report Using Bold Reports Designer

Add Gauges to Reports Using Bold Reports Designer

Bold Reports Designer: Query Builder Quick Overview

Start embedding powerful reporting with bold reports.

What’s New in the Bold Reports v4.1 Release

7 Unique Features of Tablix

What’s New in Bold Reports v3.1.31 Release

How to Create a Drill-Down Report Using the Report Designer

Get newsletter.

Error: Contact form not found.

Alternatives

Contact info.

US: +1 919.481.1974

UK: +44 20 7084 6215

[email protected]

Toll free(USA):

Fax: +1 9191.573.0306

SQL Server Reporting Services Rest API

By: Scott Murray   |   Comments (17)   |   Related: > Reporting Services Administration

What functionality is available in the SQL Server Reporting Services REST API and how would I use it?

With the introduction of the most recent release of SQL 2017, a new REST API was introduced for accessing certain meta data details concerning SQL Server Reporting Services (SSRS). The SQL 2017 version uses the REST API (RESTful API or representational state transfer) as a replacement for the SOAP access that previously supplied data on many of the catalog and other details surrounding a SSRS catalog. Some of the data points that can be retrieved via the REST API calls can also potentially be retrieved via direct queries against the SSRS database where much of the background data is stored for SSRS. For instance, the queries outlined in this tip for the most part still work well at querying various details surround SSRS and the reports deployed to the SSRS website.  Even so, the direct query method requires direct access to database which many users will not have. Therefore, the REST API alternative provides similar access to this data but uses the web service to serve up the data.

The first step in being able to use the SSRS REST API is to download SQL Server Reporting Services 2017 . Be sure you are getting the latest edition and remember, now, SSRS is a separate stand along install. Once SSRS is installed and running, we can now begin to interrogate reporting services using the API.

Any REST API utilizes a set of basic commands:

While we will not do a full dive into all the constraints and rules around REST APIs, certain restrictions are provided by the above methods for interacting with web services via HTTP. In essence these items provide the basic framework for interacting with the data from an URL service request. The most common format for housing the data as it travels back and forth is via a JSON file while the HTTP protocol provides the framework for providing statuses of our method requests (the infamous 404 error is one of those statuses). Finally, it should be noted that the GET method, noted above, is the default method.

Enough on the theory, let us try some examples. We will start with just using the Chrome web browser to execute our HTTP request; we will later use the Fiddler Free Version ( https://www.telerik.com/fiddler ) as it provides the ability to execute the various HTTP commands using a nice interface and also provides richer details about the server responses.

For our first example, we will execute a simple GET request which will return a list of Reports, and their related elements, on our local SSRS server. Remember the GET method is the default method, so we will not specify it in our commands.

Our first call will be to simply return all the report information that are on the report server. The command uses the report server URL and then references the API, version 2.0, and then references the schema to return. Thus, for our local report server the command would look like: http://localhost/Reports_SSRS/api/v2.0/Reports .

• http://localhost/Reports_SSRS - This portion is the URL to my local report server.
• /api/v2.0 - This portion reference the SSRS API, version 2.0, which SQL 2017 uses.
• /Reports - This portion of the command instructs the request to return data for the Reports Schema (we will discuss the available schemas later).

As illustrated below, we execute the GET method against the SSRS REST API Reports schema which in turn returns a list of all the reports along with multiple properties about each of those reports.

Next, we can add arguments to our request to limit or filter the returned values. The first such command sets the number of records to be returned to just a specified top number, n: http://localhost/Reports_SSRS/api/v2.0/Reports?$top=3.  We use 3 in the below command to return the top 3 reports. We should note that the below results are all in json form.

Subsequently, we can limit not only the number of records returned, but also select the actual fields or attributes to be returned. In the below example, we are requesting just the report name and report path / location: http://localhost/Reports_SSRS/api/v2.0/Reports?$select=Name,Path .

Incidentally, we could also use a select=*, just like a SQL query (with an equal sign in between): http://localhost/Reports_SSRS/api/v2.0/Reports?$select=*.

We can also combine multiple arguments using an “&” between each one: http://localhost/Reports_SSRS/api/v2.0/Reports?$select=*&$top=2 .

As shown below, filter criteria can also be used to filter what values are returned. We are filtering in the below example using “contains” within the description field, specifically looking for the word “map” http://localhost/Reports_SSRS/api/v2.0/Reports?$filter=contains(Description,'map') .

What happens if we enter an incorrect value for the URL or the arguments? A HTTP error will be generated as shown below. Generally, we want a 200 status to be returned, but you will only see this status if you are using a tool to review the http traffic which we discuss below.

As an alternative method of completing the API calls is to use a tool such as Fiddler, cURL, or PostMan. Furthermore, browser add-ins such as Reslet are available for detailed HTTP testing. In the below illustrations, we display the calls from Fiddler, the request status, and finally results of the HTTP method call.

Up to this point we have focused on the GET Command and the Reports schema. However, the list of schema values that can be requested is quite large and includes:

• Mobile Reports
• Favorite Reports
• Linked Reports
• Subscription

Within each of these schemas there are many sub elements or properties that can be requested, updated, or deleted. For instance, within the Reports schema, you can request details around comments, subscriptions, parameter definitions, and data sources. In the below example, a request is made from the Reports schema to pull the Reports data source details; the basic syntax is: /Reports({Id})/DataSources. The Id is the GUID for the report.

Now we will move to using the POST method to add a new folder on the report server. The POST command syntax is similar to the GET command, but we must specify the folder name and path in order for the folder to be created. The basic command is: http://localhost/Reports_SSRS/api/v2.0/Folders and the properties specified are in the format of: {"Name":"Test Folder Add","Path":"/Test Folder Add"}. 

Here is a picture of my report server home page before adding the folder.

Next, we execute the POST in Fiddler as shown below.

The end result is the addition of the new folder.

We could adjust the adjust the description for the folder by calling the PUT method. We need to specify the Folder GUID ID (you can run a GET request to get the folder GUID) and the Properties element in the request.  The command takes the following format: http://localhost/Reports_SSRS/api/v2.0/Folders(d3b75510-fcd4-499e-81fa-deed5b416f4f)/Properties while the request body details take this format: [{"Name":"Description","Value":"Projects just for Scotts Tips"}].  Executing this method is shown below.

Now we have a description for our folder, and of course you could adjust many of the other properties.

We can also delete this folder using the DELETE method. It requires that we know the ID for the folder that we would like to delete; we can again use GET method to obtain that ID.  The DELETE request, http://localhost/Reports_SSRS/api/v2.0/Folders(c10008d0-edc6-40ff-a53c-e8c577847788), is executed below.

Reviewing the report server, we see the noted folder is now removed.

These same or similar commands can be called for most of the other schema objects.  The full schema and related properties / arguments is available on SwagerHub at https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0 . As you can see in the above examples, the new SSRS Version 2.0 REST API provides significant access to the meta data stored for most of the objects in SSRS.

• Extracting API Data Using PowerShell and Loading into SQL Server
• Using APIs and JSON in Power BI
• ODate Filter Operations

About the author

Comments For This Article

Related articles.

• Microsoft Power BI Community
• Welcome to the Community!
• News & Announcements
• Get Help with Power BI
• Report Server
• Power Query
• Mobile Apps
• DAX Commands and Tips
• Custom Visuals Development Discussion
• Health and Life Sciences
• Power BI Spanish Community
• Translated Spanish Desktop
• Power Platform Integration - Better Together!
• Power Platform Integrations
• Power Platform and Dynamics 365 Integrations
• Training and Consulting
• Instructor Led Training
• Community Connections & How-To Videos
• COVID-19 Data Stories Gallery
• Themes Gallery
• Data Stories Gallery
• R Script Showcase
• Webinars and Video Gallery
• Quick Measures Gallery
• 2021 MSBizAppsSummit Gallery
• 2020 MSBizAppsSummit Gallery
• 2019 MSBizAppsSummit Gallery
• Custom Visuals Ideas
• Upcoming Events
• Community Engagement
• T-Shirt Design Challenge 2023
• Community Blog
• Power BI Community Blog
• Custom Visuals Community Blog
• Community Support
• Community Accounts & Registration
• Using the Community
• Community Feedback

SSRS and Odata Data Feed

• Subscribe to RSS Feed
• Mark Topic as New
• Mark Topic as Read
• Float this Topic for Current User
• Printer Friendly Page
• All forum topics
• Previous Topic

• Mark as New
• Report Inappropriate Content

Helpful resources

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Christopher Finlan

Occasional thoughts from an Azure Synapse Program Manager #MSFTEmployee

How to use SSIS to enable oData and other data sources in SQL Server 2016 Reporting Services

Ah, it’s been awhile since I’ve done one of my longer blog posts.  There’s one I’ve been itching to do for the last few weeks around SQL Server Integration Services .  If you follow this blog , you know I have a great affinity for SSIS .  A question came up a few weeks back around how could someone enable SSIS as a data source in Reporting Services , since the currently “documented” way is not only several years old, it is completely unsupported by Microsoft.

Well, a colleague of mine pointed out there was a way to do this using the Data Streaming Destination option in an SSIS package .  This option allows you to query the output like you would any other SQL Server view in Reporting Services .  Needless to say, I was eager to put together a walkthrough for folks so they could try this themselves.

To create a SQL Server Integration Services Project, you’ll need to make sure you have a program called SQL Server Data Tools  installed.  It integrates with Visual Studio , but you don’t need Visual Studio already installed to use this program.  If you do have Visual Studio installed, however, this will add new project types you can select. The version for SQL Server 2016 is located here to download and use – https://msdn.microsoft.com/en-us/library/mt204009.aspx .

To get started, select File – > New -> Project, then select the Integration Services Project option.

I’m going to create a very simple project that exposes an oData data source , which isn’t available in Reporting Services natively ( yet . . ).  I’m using the good ol’ Northwind oData feed , but you could also use an oData feed you expose from a LightSwitch project, for example.

To do this, I’ll add a Data Flow Task to my SSIS project.

Double-click on the source to create a new connection to my oData source.  Click New and enter the information for the Northwind data feed –

Once that’s done, click OK and select the data collection to expose.  Since this is just an example, we’ll leave the Columns or Error Output tabs as is and hit OK to save this.

Now we can add the Data Streaming Destination item from the toolbox to the Data Flow Task

The wizard is pretty self-explanatory, but one thing to keep in mind is you need to have an SSIS Catalog already setup on your SQL Server instance.  Assuming you have that done, walk through the steps and deploy to your catalog in SQL Server.

Right-Click on the provider and select Properties.  Simply check the “Allow inprocess” option and click OK to save.

Head the Start Menu on your server and open the SQL Server 2016 Data Feed Publishing Wizard .  Here’s where you’ll enter the settings to select your SSIS package and the SQL database you want to publish it to.  You can name the SQL view whatever you’d like.  Hit Publish to execute the wizard and create the new view.

But does it work in Reporting Services?  Let’s give it a try – I’ll setup my shared data source as I would any other SQL Server data source in Reporting Services .

We’ll create a mobile report against the shared dataset.  There’s no issues doing this, and it recognizes all the columns properly from the oData source just like it was a SQL Server view.

Finally, I’ll publish the mobile report to the server and try running it in the Reporting Services web portal.  Success!

Keep in mind, the data connection could potentially be slow depending on the amount of data you’re accessing.

That’s it – you not only have a way to use an oData feed with Reporting Services now, but you have a way to use dozens of new data sources available from several third party providers .

Now this was a fun blog post to do.  Thanks for reading and enjoy your weekend!

Share this:

• Share on Tumblr

2 thoughts on “ How to use SSIS to enable oData and other data sources in SQL Server 2016 Reporting Services ”

This question may not be relevant to this article,

Can you set a SSRS 2016 reporting portal and use mobile reporting to pull data off SSAS cubes that reside in sql server 2008 R2 (i.e can i connect 2008 SSAS cube as a datasource with SSRS2016) with the same network/organization.

Thanks! in advance for any insight you may have.

Thanks Sameer Shetty Corning Inc.

Yes, that scenario should work just fine Sameer!

Comments are closed.

• Already have a WordPress.com account? Log in now.
• Follow Following
• Copy shortlink
• Report this content
• View post in Reader
• Manage subscriptions
• Collapse this bar

Learn4D365 Dynamicsblog

Learning blog by Rene Gayer

NAV2013: using OData with SSRS (Reporting Services)

Hi all, probably you are familiar with NAV 2013 and that it is supporting OData services to provide data from NAV to other applications for example for business intelligence.

Reporting Services does not support Odata directly, but with a small workaround it is possible to create server based reporting services reports for NAV by using odata and get all the server based features which are not available by using RDLC (end user access who are not NAV user, subscriptions, additional controls, running in browser, etc.)

(This post will not describe how you are using visual studio to create great reports. If you need assistance for this and Microsoft Dynamics NAV take a look on this book or training video 257-ebook-nav-2009-inside.aspx – Videotraining Microsoft Dynamics NAV 2009- Berichte mit Reporting Services )

This post will describe how you can use a OData feed from NAV with reporting services.

First of all you have to be familiar with the requirements when using Odata in Microsoft Dynamics NAV 2013

Our goal is: Create a report based on Value Entries from NAV using Odata and („server based“) reporting services

Step 1 create a Odata service in NAV 2013

• Open the NAV 2013 windows client
• Navigate to right-corner and use the global search to find the web service page
• 3. Create a new web service entry with page 5802 value entries

Step2 Check that odata service is running

• Open the NAV 2013 Administration console
• Check Odata services is enabled and service is running
• Open a web browser and navigate to http://localhost:portnumber/ServerInstance/OData (sample : http://localhost:7048/DynamicsNAV70/OData )
• Check the listed Odata services and look for your created entry „ValuesEntries“
• Check that you are able to access it by enter the url into a new browser tab
• http://localhost:7048/DynamicsNAV70/OData/ValueEntries

If you are able to access it, all prerequisites exists. Lets move forward by using it for reporting.

Step 3 create a report using SSRS  (Reporting Services)

• Open Visual Studio and create a new report project
• Add a new data source by using the solution explorer
• Because SSRS does not support OData directly use the XML connection type and the url for the connection string (Note: Do not forget to set windows authentication)
• Skip the „shared Dataset“ directory and go directly to the folder „reports“ (Of course you can work with shared datasets)
• Select the Datasource and click Next
• Before moving on designing the query, lets remember that Odata is not supported by SSRS. Because of this we have to use a short xml query to structure the odata feed into typically „fields“
• Enter this query into the query string textbox
• <Query>
• <ElementPath IgnoreNamespaces="true">
• feed{}/entry{}/content{}/properties   
• </ElementPath>
• </Query>
• 11. Check the query before moving on by clicking on Query Builder and then run the result button
• You should now see structured „NAV“ fields in you query and can move forward to create your report like any other reports in SSRS
• Finalized NAV report based on NAV OData

NAV 2013 Rocks .!

Kommentar hinterlassen Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Kommentar *

Meinen Namen, meine E-Mail-Adresse und meine Website in diesem Browser für die nächste Kommentierung speichern.