Category Archives: SSRS Design

Learn Databricks, ADF & Paginated Reports at the PASS Community Summit

by

0

Seven days and counting… For the past eighteen years, thousands of data tech professionals would travel to the PASS Summit conference held somewhere in the US, paying for airfare, hotel and conference admission. This year, the PASS organization (Professional Association for SQL Server) is rebooted as the PASS Data Community, led by Red-Gate Software, and the virtual summit is free to attend online. Hundreds of speakers will deliver training sessions and informative lectures on a variety of important data tools and industry skills. This year, I am speaking about Paginated Reports in the Power BI service. I will reflect on the progression of SQL Server Reporting Services (SSRS) through past PASS presentations and then talk about how Paginated Reports integrates with the Power BI platform.

Register to attend the PASS Community summit

Two of my colleagues from 3Cloud are also presenting at the PASS Community Summit next week. There is just no good reason not to attend the summit his year, so register and make time to attend selected sessions. To reach the global audience, some of the keynotes and sessions are presented live. Others have recorded content followed by a live Q&A chat with the presenter.

May I remind you again that the online summit is FREE, so participate, volunteer and attend! There is so much to learn from world-renowned experts. PASS Community Summit speakers are the subject matter experts who write the books, teach the classes, provide expert-level consulting and actually develop many of these products and Azure services.

Data On The Road

As we are traveling in the Data Bus, I have had the opportunity to interview server data community leaders and conference speakers, including those highlighted in this post. Here, Leslie and Andie talk about their topics and passion for data and community. Watch all of the interviews and follow us as we visit conferences and data community leaders in the Data Bus.

Learn Azure Data Factory from Leslie Andrews

Becoming a Data Engineer with Azure Databricks, from Andie Letourneau

Drill-through from Power BI to Paginated Report – Report Recipe #4

by

3

Navigation between reports is the hallmark of an interactive reporting solution, enabling the ability to drill-through and see relevant details and contextual filtered information in a target report. Power BI and Paginated Reports both offer navigation capabilities suited for different purposes. Having the ability to navigate between reports can provide a tremendous amount of power and flexibility.

This is simple math: with two different report types, there are four different possible report navigation combinations. They are:

  • Power BI report to Power BI report
  • Power BI report to Paginated report
  • Paginated report to Power BI report
  • Paginated report to paginated report

Continuing this series of blog posts to highlight our recently published eBook about Paginated Report recipes, this post summarizes the technique detailed in recipe #4, “Drill-through from Power BI to Paginated Report On-premises”.

If you have worked with the native SSRS report drill-through using report actions, that capability is not yet baked into the product. It probably will be some day but likely not soon. That’s OK because this technique, using URL navigation, works quite well. There is one other twist which is that the URL parameters are a little different when comparing on-premises Paginated (SSRS) reports to Paginated reports in the Power BI service. The technique in in this recipe uses an on-prem target report. You can apply the same pattern in the cloud with sight modifications and I will post an example in the near future.

This image depicts the source Power BI report with links that navigate to the target paginated report, showing filtered details:

The mechanism to enable navigation is a report URL produced with a DAX measure. The code emits a URL with parameters that preserve the row and filter context of the selected item or record. This URL measure is exposed as a link in a table or matrix visual in the Power BI report, optional with a URL “link” icon:

To see the instructions in detail, follow this link to the recipe: 04-Drill-through from Power BI to Paginated Report On-premises | Paul Turley’s SQL Server BI Blog

Doing Power BI the Right Way: 9. Choosing the right report type: analytic or paginated

by

15

Part of the the series:  Doing Power BI the Right Way (link)

It has been a few weeks since I last posted, and a while since I started the promised blog series “Doing Power BI the Right Way”. I’ve received a lot of positive comments on the posts in the series this far, and people wondering why it’s taking so long. Well, I am busy working on a book about Paginated Reports and SQL Server Reporting Services. This will be the second edition of a book I co-authored ten years ago and this edition is also a collaborative effort with multiple contributors. We have quite a lot of excellent material that is just starting to go through the process of editorial and technical review. Writing a technical book always takes more time and effort than imagined, and working on this one in my “spare time” is a laughable notion but this is going to be a great book.

Writing this blog series while juggling other commitments has brought me to two realizations: 1. Posting articles in the series will be sporadic, and 2. The topics will be in fairly random order based on the projects I’m working on and the topics that are most top-of-mind at the time.

This brings me to the subject of this post: Paginated and Analytic reports.

Before we had Power BI, we had Reporting Services. When the focus of my career and consulting practice became Business Intelligence, starting about fifteen years ago, most of the “BI reports” I created were in SQL Server Reporting Services (SSRS). We could create a variety of charts and reports that allowed users to drill-down from summary to details. We could create reports that allowed a user to drill-through, from one record or report item to another report that presented more details. With a little creativity, we could create reports that allowed users to click items that would filter other items on the same report. It took some work and time to create these interactive “drill-through-to-self” reports. Today, after transforming data into the right format for a tabular data model, this type of interactive functionality just magically happens in Power BI with very little report design effort. But, Power BI is primarily a tool for analyst users to create their own reports and to get answers to business questions rather than for IT developers to create reports that print or export perfectly to a PDF or Excel. Although it is possible to create fairly advanced paginated reports, most bread-and-butter operational reports are really just what I call “lists of stuff”… the results of SQL queries presented as a table or matrix; perhaps with a few groupings and subtotals. Simple business processes and task often require simple reports to help manage them.

Paginated reports, by definition, are reports that allow a lot of data to be presented on multiple pages. Paginated reports can be viewed online, printed or exported to a variety of file formats. Becasue there is so much overlap in the capabilities of SSRS/Paginated Reports and Power BI, the choice between these two report tools is not always clear. I can cite many cases when using Power BI Desktop is clearly the right choice to create an analytic report, and cases where it would make more sense to create an operational report using Paginated Report Builder. I think these are the easier decision points but I would like to explore those cases where we could argue either way. What are the pros and cons of using Power BI to create traditional reports? When would it make more sense to replace a static, paginated report with an interactive report created with Power BI?

The integration of Paginated Reports into the Power BI cloud service continues to march forward at a rapid pace. Just yesterday, Chris Finlan teased a blog post announcing new Power Automate integrations that will allow report to be distributed and exported using flows. Listen up for some big announcements at Microsoft Ignite about Paginated Reports in the Power BI service. Whether you host operational reports on your own report server or in the cloud, Paginated Reports are an important part of most business reporting portfolios.

When do you choose to use Paginated Reports or SSRS rather than Power BI? Do you design reports differently than you would have 5 years ago? Do your users prefer interactive, self-service reports now or do they still want made-to-order static reports?

What are the deciding factors between creating a paginated report or an interactive report?

Do you create paginated reports with a Power BI dataset or SSAS model as the data source or do you only use SQL queries for those reports?

I’m interested in your ideas and questions. Please post them in the comments below this post and then I will extend this post with your input and my thoughts.

Drillthrough from Power BI to an SSRS Paginated Report

by

13

— report recipe
also see: Drillthrough from Paginated Report to Power BI

Navigating from a Power BI report to an SSRS Paginated report with filters and parameters.

Product Versions
SSRS/Paginated Reports (one of the following):
– SQL Server Reporting Services 2016 or newer
– Power BI Report Server
– Paginated Reports in the Power BI service
Power BI Desktop:
– Power BI Desktop May 2019 or newer
All versions of Reporting Services accept parameters in a similar fashion, but subtle changes were made to URL parameter formats in SSRS 2016.  Some adjustments may be required if you want to apply these techniques to SSRS versions prior to 2016.
What You’ll Need
– A Power BI report connected to a data source using import, DirectQuery or SSAS direct connect mode.
Report server:
– Power BI Report Server or a Power BI subscription with Premium capacity (minimum: P1 or A4)
– Moderate level DAX coding skills  
Difficulty level: 200

Designing the Power BI Report

This recipe primarily involves Power BI report design techniques. I’m not going to get into the details of Power BI report design but will cover the basics with a partially-completed report to get you started. If you are less-experienced with Power BI you can use this as an example for future report projects.

The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title).
These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.

Solution

Figure 1 shows the Sales Summary Power BI report, which is deployed to my local Power BI Report Server along with the Sales Detail, a paginated (SSRS) report also deployed to my local Power BI Report Server.

clip_image002

Figure 1

We will start by looking at the target report. This is just a standard SSRS paginated report. A completed copy of this is provided for you to configure and deploy to your report server or Premium Power BI service tenant. The sample report is very simple and could easily be enhanced to include more information in greater detail.

1. Open the Sales Detail.rdl report in Report Builder

There are four report parameters in this report, two of which are used to connect to the SQL Server database. The other two parameters are used to pass filter values to the underlying query.

2. Edit the ServerName parameter and set the Default property to the name of your server or instance

The default value for this parameter LocalHost which connected to my local default instance of SQL Server. You can leave this setting if your SQL Server instance is the same as mine.

3. Close and save any changes the ServerName parameter

4. Preview the report and make sure it connects and displays data from the sample database

5. Select different values for the Year and Country parameters using the parameter dropdown lists and click the View Report button to see values in the report change, and to confirm that the parameters are working correctly.

6. Use the Save As… option on the File menu to save the report to a folder on your report server or a workspace in your Premium Power BI tenant

Note: Depending on the Report Builder menu option and deployment target, the terms “save” and “publish” are used interchangeably, meaning that the report has been deployed to a server or service where it can be ran and viewed by users.

Get the address for the published paginated report

1. Open Notepad or your favorite text editor

2. In a web browser, enter the address for your report portal or Power BI portal where you published the Sales Detail paginated report

Tip: If you don’t know the address for your web portal or report server, you can use Report Server Configuration Manager to lookup the Web Service URL and Web Portal URL. By default, the web portal address is http://SERVERNAME/Reports and the report server address is http://SERVERNAME/ReportServer (where SERVERNAME is the name of the server where Reporting Services is installed) but this is configurable and might be different in your environment.

3. Navigate to the folder or workspace and run the newly published report, verify that the report runs and returns data

4. Use Ctrl-C to copy the full address for the report from the browser address bar to the Windows clipboard

5. Paste the address into the text editor

The next step can seem a little tricky but it’s not really complicated. You need to get the path to the report on the report server rather than the report portal.

If you published the paginated report to your on-premises report server, do the following:

1. In your text editor, copy and paste the report address to a new line

2. In the new address line, locate the text “Reports” and change it to “ReportServer”

3. Copy the modified address line to the address bar in your browser and press Enter

The browser should navigate to a text menu page similar to Figure 2.

clip_image004

Figure 2

The address for the report is actually not yet in the correct format but it was sufficient to navigate to the Home folder on the report server. The following steps are used to obtain the correctly-formatted report address.

1. Use the links displayed on the report server menu page to navigate to the folder to which you published the Sales Detail report and click the report name link to run the report

2. When the report is displayed, copy the complete address from the address bar and paste it to a new line in your text editor

This is the correct path for this report on the report server. You can new add parameter values to the end of the address.

3. Append parameters and values to the address in the address bar like this. Each parameter is preceded with an ampersand character:

&Year=2018&Country=Canada

4. Press Enter and verify that the parameter is changed in the report

Open the starting report in Power BI Desktop and add drillthrough navigation for the paginated report

1. Make a copy of the Sales Summary-Starter.pbix file and rename it Sales Summary.pbix

2. Open Sales Summary.pbix in Power BI Desktop

If you will be using Power BI Report Server to deploy report on premises, make sure that you use the version of Power BI Desktop designed for your server version. If in doubt, you can download and install Desktop from the download menu in your report web portal.

3. On the Home ribbon, click the Edit Query button to open the Power Query editor

4. You will see three query parameters. Two parameters are used to manage database connection information in a similar manner to the Sales detail paginated report.

Tip: Using parameters in the Power Query editor can help manage variable information like connection information, file paths and addresses.

In the starter report file, there are three text type query parameters. Modify these parameters, using the following instructions:

Parameter NameDescription
ServerNameSQL Server address or server name. You can use “LocalHost” if it is a local instance.
DatabaseNameSSRS2019Recipes
ReportURLPath to the deployed SSRS/paginated report

5. If necessary, change the ServerName parameter current value to connect to your SQL Server

6. Copy the report server address for the deployed Sales Detail report from your text editor and past it into the current value ReportURL parameter.

Build the dynamic target report address link as a measure in Power BI

The ReportURL Parameter value is exposed as a column in a table named Constants. You will use this to build a dynamic link in the report.

1. On the Home ribbon in the Power Query editor, click Close & Apply to process queries and return to the report designer.

2. Figure 3 shows the report designer in Power BI Desktop. The objective is to add links to each item displayed in the table visual, allowing a user to see a paginated report with details related to a selected year and country.

3. Expand the Sales Summary Subcategory Country table in the field list on the right

clip_image006

Figure 3

4. Click the ellipsis (…) to the right of table name to see the context menu and select New Measure

5. The formula bar is displayed above the report canvas with placeholder text: Measure =

Note: You can use the down arrow on the right side of the formula bar to expand the formula editor window, and the up arrow in the same location to collapse the formula editor

6. Replace the placeholder text with the following new measure:

Detail Report Link =
VAR SelectedYear = SELECTEDVALUE( 'Sales Summary Subcategory Country'[Year] )
VAR SelectedCountry = SELECTEDVALUE( 'Sales Summary Subcategory Country'[Country] )
VAR RptURL = SELECTEDVALUE( 'Constants'[Report URL] )

RETURN
RptURL & "&Year=" & FORMAT( SelectedYear, "0000") & "&Country=" & SelectedCountry

The purpose of this measure is to build the address text used for our link to the detail paginated report. The SelectedYear and SelectedCountry variables get those respective values. The RptURL variable gets the report path the parameter. This DAX script assembles the URL in the appropriate format.

As you enter the text, use the Intellisense and autocompletion features offered by the editor to build the DAX code. These features can help with proper code syntax and save you from typing errors.

7. Press Enter to validate the code and save the measure

8. With the measure selected in the field list on the right, switch to the Modeling ribbon

9. Use the Data Category drop-down to select Web URL (see Figure 4)

clip_image008

Figure 4

Add report links to the report

With the Detail Report Link measure added to the data model and categorized as a web URL, you can add a link to the table that will dynamically generate a context-specific link for report navigation.

1. Before you go any further, click the floppy disk icon on the toolbar to save your work

2. Select the table on the right side of the report canvas that shows sales by product category grouped by years

3. Drag the new Detail Report Link measure from the field list to the Values field well below the SalesAmount field. This adds a new column to the table visual showing a long URL in blue “link” text (see Figure 5)

clip_image010

Figure 5

Tip: It is easy to deselect a visual so before you begin changing properties, make sure that you still have the table visual in the report canvas

4. In the left-most blade in the panel to the right of the report canvas, click the paint roller icon to see the properties for the table visual, shown in Figure 6

clip_image012

Figure 6

5. Expand the Values group

6. Locate the URL icon property and switch the state of the property to On

7. Verify that the web addresses displayed in the last column of the table are replaced with a small blue link icon, which you can see in Figure 7

clip_image014

Figure 7

Test and publish the completed report solution

A this point, navigation should work from the report in Power BI Desktop, just as it should after the report is deployed.

1. Hover over the link icon on a few different rows to see the parameters at the end of the address in the tooltip

2. Test the report navigation by clicking a link after noting the corresponding year and country.

3. Publish or save the paginated report to your report server or Premium Power BI tenant

4. Test drillthrough navigation in the deployed reports

5. Navigate to the deployed copy of the Power BI report

6. Locate a row with values for a specific year and country and click the corresponding link

The paginated report opens in the browser

7. Verify that the paginated report parameters reflect the selected year and country

clip_image016

Figure 8

Final Thoughts

Report navigation is the essence of data exploration and gives users the ability to understand their data in proper context. Dashboard and summary reports help business users see the big picture but having access to details let’s them understand the low-level details behind the numbers. Power BI as an ideal tool for summary-level, interactive reporting and paginated reports are great for consuming detail-level data. Using this technique allows you to have both of these capabilities. Power BI reports can be published to your on-premises Power BI Report Server or to the cloud Power BI service. Paginated reports do not have to be in the same location and can be deployed on-prem in a standard SSRS report server, Power BI Report Server or in you Premium Capacity Power BI Service tenant.

A couple of things to keep in-mind when using this technique are that there are string size limits when passing parameters in a web URL. You should avoid passing sensitive information in clear text parameters. When navigating between reports located on different services or services, users may be prompted for their credentials to login to a service.

Can’t We Just Get Along? Making SSRS, Power BI and Excel Play Well Together

by

0

Please join me and other 2017 PASS Summit speakers for 24 Hours of PASS: Summit Preview on July 19th and 20th.  24HOP is a series of 60 minute on-line sessions presented back-to-back for 24 hours, from the same professionals who will deliver preconference and main conference sessions during the Summit the first week of November this year.  These online sessions are free of charge and normally attended by thousands of individuals to gain insight and knowledge about the topics.  24 Hours of PASS features free educational webinars delivered over 24 hours. Topics covered in this edition include Performance Tuning, SQL Server 2017, Linux, DevOps, Azure, PowerShell, SSRS, Power BI and much more. Browse all sessions. These webinars provide a sneak peek at some of the best practices, expert tips and demos you’ll find at this year’s PASS Summit in Seattle. Continue reading →

Modern Reporting with SQL Server 2016 and 2017 Reporting Services full day pre-conference session

by

1

I am thrilled to be presenting a full-day preconference session before the PASS Summit, on October 31st.  Please join me for a deep tour of the new capabilities and BI platform integrations for SQL Server Reporting Services 2016 & 2017.  The session will also review the essential skills and tasks to setup and configure the report server and web portal, report design and modern report solution planning. Continue reading →

Reporting Services 2016, The Force Awakens session at Summit 2016

by

2

THANK YOU…

everyone who attended my session today titled “Reporting Services 2016, The Force Awakens”  We had a packed house with attendees standing along all of the walls in two joining session rooms.  This is indeed a very popular topic!   The session was recorded and will be available to watch with all the Summit 2016 session recordings.  You can download the slides deck here.  Samples and demo material are available from these resources (which are on the References & Sample slide):

Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports
Amazon (samples)

Self-paced online SSRS 2016 training
EdX course DAT214x: “Analyzing and Visualizing Data with SQL Server Reporting Services”
EdX.org (samples)

The Essential Guide to SQL Server 2016 Reporting Services
SQL Server Pro Magazine / SQLMag.com
Link (samples)

My Blog
SqlServerBiBlog.com

Reporting Services 2016, Power BI and PASS Summit 2016

by

2

What am I most excited about as I prepare for the PASS Summit here in Seattle this week?  A lot of things.  Preparing for my session, which will be on Thursday at 1:30, by far the most popular and interesting topics are about integration and tool choice.  Today’s public announcement on the SSRS product team blog about on-premises Power BI integration with Reporting Services is really big news.  It’s great to see two of the technologies I love working together.  Whether in the cloud or on-premises, Power BI and Reporting Services can be used together.

Thursday’s session is titled “Reporting Services 2016, the Force Awakens”.  The consensus among industry users is that Reporting Services was stalled and left alone for far too long.  Technology and industry trends have moved forward over the past five years or so but Microsoft have invested in other tools and products, aside from Reporting Services.  The SSRS server architecture is capable but the rendering and delivery components needed to be modernized.

The good news is that the modernization effort has been underway for quite some time.  Resources that were directed to other investments have been recommitted, not only to the Reporting Services product, but to integration across the product stack.  It’s really hard to build a reporting platform that addresses every need.  I think Microsoft have a history of creating false expectations by putting experimental and incomplete features  out in the industry that weren’t on the committed roadmap.  I know better than to believe that the pattern won’t ever be repeated but I firmly believe that there is a well-defined reporting and BI roadmap and product teams are making rapid progress against it.  It is once again an exciting time to invest in the Microsoft analytics, reporting and BI tool set.

I’ll publish the entire deck after the 2016 Summit.  Until then, here are some selected slides…

Comparing the progress of new features added to SSRS over the release cycles, the product really slept from 2010 until 2016.

2016-10-25 (1)

Microsoft’s reporting & analytic platform has shifted significant as they moved from products to services, and from servers to the cloud.  The dust is settling on this major refocusing effort.  Capabilities that were introduced in Azure, Cortana Analytics and Power BI are now becoming available to “the box”, on-premises or in hybrid cloud scenarios.  We can expect more announcements on this as the roadmap is played-out and gaps are filled-in.

2016-10-25 (2)

Reporting Services is a central player in the on-prem delivery of the BI, analytics and reporting tools offered by Microsoft.  These include Excel, Paginated reports created with SSDT/report designer or Report Builder, Mobile Reports and Power BI Desktop.

2016-10-25 (3)

…more to come!

check back for updates.

Reporting Service 2016 Unplugged

by

0

This month’s SQL Server Pro Magazine article:

In SQL Server 2016, my favorite report tools get some love with a few cool new features including, a little polish added to a few existing features, and a complete Report Portal overhaul.

Reporting Services reached a certain maturity as a rock-solid reporting platform somewhere just after 2008 – or at least it got past puberty.  Most report developers would agree, the versions that followed SQL Server 2008 R2 were more like a midlife crisis for the product, because the feature set stalled for about five years.  The latest version is not a complete product rebirth by any means, but it is a good indication Reporting Services is not headed for retirement any time soon; it now plays an important role in the larger Microsoft reporting ecosystem.

Rather than rehashing information posted about new and upcoming reporting features available elsewhere, I’ll share my experience thus far with the latest community technical preview (CTP 3.2) and some insights from the product developers.  As with any preliminary software build, your experience may differ.

Design Tools…

SSRS_2016-01-23_21-05-34[1]