How to Integrate SSRS Report in Power BI

Combining the strengths of different reporting tools can create a powerful, unified view of your business data. If your team relies on SQL Server Reporting Services (SSRS) for detailed, paginated reports but uses Power BI for dynamic, interactive dashboards, you can bring them together. This article will show you how to embed live visuals from your SSRS reports directly onto your Power BI dashboards.

Why Integrate SSRS with Power BI?

Before diving into the "how," it's helpful to understand "why" this integration is so useful. For many companies, SSRS is the long-standing workhorse for operational reporting - think pixel-perfect invoices, financial statements, and detailed inventory lists. Power BI, on the other hand, excels at creating visually rich, interactive dashboards for high-level analysis and exploration.

By bringing them together, you get several key benefits:

• Create a Single Source of Truth: Instead of having users jump between the SSRS web portal and the Power BI service, you can create a centralized hub. Your Power BI dashboards can act as the main entry point for all your business data, whether it's an interactive chart or a link to a detailed operational report.
• Combine Interactive and Operational Data: Imagine a sales dashboard. You can have interactive Power BI visuals showing sales trends by region alongside a pinned SSRS visual displaying a list of the top 10 open invoices. This combination provides both high-level insights and detailed, actionable data in one place.
• Seamless User Experience: Users can view key metrics from your critical SSRS reports without leaving the Power BI environment they use daily. When they need more detail, a single click on the pinned item takes them directly to the full report in SSRS.

Prerequisites: What You'll Need to Get Started

To successfully pin SSRS items to Power BI, you need a few things in place. Make sure you have the following before you begin:

• An SSRS Instance: You'll need SQL Server 2016 Reporting Services or newer, as older versions do not support this integration. Your server must be configured in native mode.
• The SSRS Instance Must Be Registered with Azure Active Directory: This is the key that enables communication between your on-premises SSRS server and the cloud-based Power BI service.
• A Power BI Pro or Premium Account: This feature is not available for free Power BI users. You'll also need edit permissions for the destination dashboard.
• Proper Permissions: The user performing the integration needs to have appropriate permissions in both SSRS (to view the reports) and Power BI (to edit the dashboard).
• Browser and Network Access: Your SSRS server needs to be accessible from the internet, as the Power BI service in the cloud needs to communicate with it to render the report items.

Step-by-Step Guide: How to Pin SSRS Visuals to a Power BI Dashboard

The primary method for this integration is using the "Pin to Power BI" feature directly from the SSRS web portal. It involves a one-time server configuration, after which any user with the right permissions can pin report items.

Step 1: Configure Your SSRS Instance

Your SSRS administrator will need to carry out this first step. It requires registering your SSRS environment with Power BI within the Reporting Services Configuration Manager.

1. Open the Reporting Services Configuration Manager on your SSRS server.
2. Navigate to the Power BI Integration page.
3. Click Register with Power BI.
4. Sign in with your Azure Active Directory administrator credentials.
5. Follow the on-screen prompts to complete the registration. You'll likely see a confirmation message, "Reporting Server successfully registered," once complete.
6. Click Apply to save the changes.

Once this is done, the Power BI integration is enabled for your entire SSRS instance. You won't need to do this step again.

Step 2: Navigate to Your SSRS Report Portal

Now, open your web browser and go to your SSRS report server web portal (e.g., http://myserver/reports). Log in using your credentials. Find the paginated report containing the visual you want to pin.

The items you can pin include:

• Charts
• Gauge Panels
• Maps
• Images

You cannot pin an entire report or a data table (tablix) directly, only individual visual elements.

Step 3: Pin the Report Item to Power BI

Open the report. Once it has rendered, you'll notice a new Pin to Power BI Dashboard button on the toolbar at the top. If you don't see this button, it means either the SSRS server hasn't been configured correctly or you're looking at a report item that can't be pinned.

Follow these clicks:

1. Hover over the individual chart, map, or gauge you want to add to your dashboard.
2. Click the Pin to Power BI Dashboard icon in the report viewer toolbar.
3. You may be prompted to sign in to your Power BI account if you aren't already logged in. Use the same credentials you use for the Power BI service.

Step 4: Choose a Dashboard and Pin the Visual

After clicking the pin icon, a dialog box will appear. Here, you'll choose exactly where the SSRS visual should go.

1. Select the Workspace where your target dashboard is located.
2. From the Dashboard dropdown, choose the specific dashboard you want to add the visual to.
3. Set the Update Frequency. This determines how often the tile in Power BI will check the SSRS report for fresh data. The shortest interval is usually 15 minutes.
4. Click Pin.

You'll see a confirmation once the item is successfully pinned. You can pin multiple items from different SSRS reports to the same Power BI dashboard.

Step 5: View the Pinned Item in Power BI

Now, head over to the Power BI service (app.powerbi.com) and open the dashboard you just pinned the item to. You will see your SSRS visual appearing as a new tile on the dashboard canvas.

Notice a few things about this tile:

• The title of the tile is taken from the report or item title in SSRS.
• It displays a subtitle indicating when it was last updated.
• When you click the tile, it doesn’t filter other visuals on the dashboard. Instead, it acts as a hyperlink, opening the full SSRS report in a new tab.

An Alternative Approach: Power BI Report Server

It's worth mentioning another way to achieve a similar goal: Power BI Report Server. Instead of pinning an on-premises SSRS item to the cloud-based Power BI service, Power BI Report Server is an on-premises solution that can host both Power BI reports (.pbix files) and paginated SSRS reports (.rdl files) together.

With this approach, you create one unified on-premises portal where users can access both report types. It’s perfect for organizations with strict data governance policies that require keeping all data within their own firewall. While it doesn't involve "pinning" visuals in the same way, it achieves the same goal of a centralized reporting hub, just in a different environment.

Best Practices and Known Limitations

While this integration is incredibly useful, it’s important to understand its boundaries to set the right expectations for your users.

Limitations

• Not Truly Interactive: The pinned tile on the Power BI dashboard is essentially a screenshot of your SSRS visual. You cannot interact with it (e.g., drill down, hover for tooltips) within Power BI itself. Interaction only happens after you click through to the full SSRS report.
• Snapshot, Not Live Stream: The data on the tile is not real-time. It updates based on the frequency you set during the pinning process (minimum of 15 minutes). For visuals that need sub-minute updates, this may not be the right solution.
• SSRS Security Applies: When a user clicks a pinned SSRS tile, they are redirected to the SSRS server. If they don't have permission to view that specific report in SSRS, they'll see an access denied error.

Best Practices

• Use for KPIs and Summaries: This feature is best for showcasing high-level KPIs and summary visuals from operational reports. Don't use it for displaying dense tables or complex charts that require interactivity.
• Design for Pinning: When creating SSRS reports, think about how visuals will look as small tiles on a dashboard. Keep them clean, simple, and with clear titles.
• Educate Your Users: Make sure your users understand that clicking an SSRS tile will navigate them away from the Power BI dashboard to a different report environment. Labeling the tiles clearly (e.g., "Click for Full Invoice Report (SSRS)") can help manage expectations.

Final Thoughts

Integrating SSRS items into a Power BI dashboard offers a fantastic way to bridge the gap between your detailed operational reporting and high-level summary interactive analytics, creating a single, convenient BI monitoring hub that benefits everyone from executives to sales teams. It empowers your team to have all the context they need in one place, combining the unique strengths of both platforms to quickly understand a problem.

As powerful as this is, modern reporting often extends beyond a company's internal servers. Teams frequently need to blend operational data viewed through a reporting server with data from advertising and revenue SaaS platforms like Ads Managers, payment gateways such as Stripe and PayPal, or Google Analytics and Search Console. Since these live outside of your company's servers, we created Graphed to seamlessly integrate these data sources with Power BI or SSRS. We found that stitching all this information together can be time-consuming, so we developed Graphed to make connecting your favorite SaaS sales & marketing data as simple as asking a question.