By: Scott Murray | Comments (44) | Related: > SQL Server 2016
Problem
So you want to develop mobile reports for SQL Server Reporting Services (SSRS)? What options are available? Are there any new features in SQL Server 2016 that can streamline the process? How do I get started?
Solution
The amount of development and the number of new tools Microsoft is commissioning for Business Intelligence and Data Analytics is staggering. It seems that the sleeping giant woke to the world of reporting and analytics. Currently in the CTP 3.2 preview edition of SQL Server 2016 is a new and mobile friendly (and KPI friendly) version of Reporting Services. Along with the new mobile friendly website, a new SQL Server Mobile Report Publisher tool is now available to design, develop, and deploy reports that are structured for mobile viewing on many various devices. You can download the Mobile Report Publisher tool at: https://www.microsoft.com/en-ca/download/details.aspx?id=50400.
This application is based upon the Datazen tool; Microsoft recently purchased Datazen and has repositioned its mobile report developer into the new SSRS Mobile Report Publisher. The tool is rich in features, and I feel is very intuitive to use. One main advantage of the tool is that when presenting on a mobile device, the tool deploys reports that are flexible when viewed on various devices. That means that on a small device like a phone, the report will adjust the various objects for the best viewing on that device; if viewing on a larger sized screen such as on a tablet, then the report will again adjust for the best viewing experience for the end user. Of course, if viewing on a computer, an even different view will be generated. At this point though, the Mobile Report Publisher is still in preview mode. Likewise, the support of these reports is currently available in SQL Server 2016 CTP 3.2.
For our examples in this tip, we will use the Adventure Works databases as the basis for our mobile reports. The 2016 versions of the AdventureWorks database is available at: https://www.microsoft.com/en-us/download/details.aspx?id=49502. Additionally, this link provides access to the data warehouse version of the Adventure Works database along with various sample script files.
Creating a Mobile Report in SQL Server 2016
Let us get started creating our first mobile report in the Mobile Report Publisher tool. I think you will find the tool quite easy to work with. As shown below, the opening window has several main components including:
- a design grid with cells in rows and column
- a chart, gauge, map, and navigation visualization toolbox
- a view type and formatting area
- setting and data area
- general new / save / save as tool bar
Connecting to SQL Server 2016 Reporting Services
One of the first steps you will want to do is connect the Publisher to a SQL Server 2016 SSRS instance running at least CTP3.2. That version of SQL Server 2016 has a Preview mode which displays a new look and feel of SSRS. As shown below, SSRS contains a link for you to "Preview" the new reporting services home page.
After you click the preview link, the new look for SSRS will appear. I am planning on completing articles on the new SSRS home page in future tips, but suffice to say the new structure of the site contains areas for KPI's, mobile reports, and the old fashion paginated reports (paginated reports are what SSRS displays in the current versions). Right now, not all the functionality is "working" on the Preview site, but the mobile piece is in place.
Thus, we will want to connect to this new site in the Mobile Report Publisher. In order to do so, we need to make note of the new site address: http://<server name>/Reports_Preview/. Our first step is to click on the Connections button as shown below. I liken this connection to connecting to your report server from ReportBuilder.
Next, you will fill in the server address, user name, and password. The server name takes the form of <server name>/Reports_Preview. Notice the "_Preview" at the end of the server address. Without that suffix you will not be able to connect; I found that out the hard way after much trial and error. Also, I wonder if that address will change once Microsoft puts the new site into production with SQL Server 2016.
Once you have filled in all the details, you click connect. If successful, you will see the below confirmation.
If you enter incorrect information you will get an error, although, as shown below, the error may not be completely helpful with what is wrong. I entered an incorrect server name which generated the below error message.
Now that we have a connection to the server, we can maintain that
connection or add a new connection by clicking on the connection screen
again.
Some basic settings can also be set for the report in question by clicking the Settings button; these settings include the Report Header (which can be different than the file name). We can also set the several date / time options that impact how times and dates are displayed on the device consuming the report. Finally, we can set whether the report data is cached or encrypted on the device consuming the report.
Designing the Layout of Mobile Reports in SQL Server 2016
We next can set the grid layout and formats for our reports. However, before we do too much work, we need to understand how the grids work. The publisher uses the concept of a master grid which can feed a tablet and a phone grid. As shown below, the default grid is the Master grid and it manages which visualization are available for the phone and tablet grid. The actual number rows and columns for each grid can be controlled by using the slider bars shown below to adjust the number of cells per row and column.
For both the phone and tablet grids, illustrated below, we can alternately adjust the grid rows and columns for each of those display types.
Furthermore, you can adjust the default color pallet for the 3 grids very easily. Note that all three grids change when you make this change. Finally note that you can also change the background style and decide if you want the color pallet change to just impact the color accents.
Add Visualizations to SQL Server 2016 Mobile Reports
Now let us start adding some visualization to the Master design grid; as illustrated below, the process is basically a drag and drop set of steps.
Pretty easy and we can even click on the preview button and get a pretty cool report. You are probably saying the same thing I did...."but, wait, I have not added any data sets yet. How do I have a preview already". The Mobile Report Publisher actually uses sample data, so you can see what the visualization looks like even before datasets have been added. We will cover adding data later in this tip.
If we move to the Tablet or the Phone grid, you will notice that the visualizations that were added to the Master grid actually do not automatically get moved to the other two design grids. Notice in the below screen print, the two visualizations that were added to the Master grid now show in the Control Instances area. We can then manually drag any of the available visualizations onto the grid. We would need to do the same for the phone grid.
We can put the visualizations in any spot on the grid; it does not have to be in the same grid position as it was on the Master grid.
Subsequently, we can easily drag the graph or chart to another cell on the grid.
Or we click on the gears (see above screen print), which will open a cut / copy / paste / delete / redo set of buttons.
You can also increase the size of a particular visual so that it covers more than one grid cell. That allows you to show more details on a particular visual while potentially stacking smaller visuals either above / below or side by side to the larger visual.
Adding Data to our SQL Server 2016 Mobile Reports
We have worked with the formatting up to this point, so let us move to getting some data for our charts. As shown subsequently, when we click on the Data tab, we are able to see the simulated or sample data used to generate the examples used initially in the visualizations.
We can start adding our own data by clicking the Add Data button.
Currently two options are available to add data to the reports. Either data can be retrieved from a local Excel File or from a Data Set on a SQL 2016 Report Server. We will show the process of adding data from both sources starting with SSRS Server.
The previous connection we established earlier in the tip will now come in handy for connecting to the SSRS server in order to look for available data sets. If you have multiple connections established, they will all show up here. Basically, you just click on the server to begin the process.
A list of available data sets is shown; as noted below, you just click on the desired data set.
Finally, the data set is displayed on the Data tab.
A similar process is followed for adding a local Excel Source. First, Local Excel is selected.
Next, you browse for the file and then the Excel Data set is loaded, similar to the illustration below.
One item to note, when you are attempting to retrieve a SSRS dataset, the dataset must have been created using the Report Builder version 3.2 Preview tool. When I attempted to use a dataset created in SQL 2014 and upgraded it to SSRS 2016, the following error resulted. You also must be careful to not use overly large datasets at this time. I ran into issues with a dataset of about 100,000 rows.
Now that the data sets are loaded, we can now "attach" or connect a dataset to a particular visualization. As shown below and still within the data tab, you will select the visualization you would like the data to be attached to. Remember, the simulated or sample data is used by default. The options available for each visualization depend on the visualization type. For the first screen print below, the arrow visualization uses a two value comparison, so we first must tell the system which source data set to be used as listed near the boxes marked #2 & #4 below. Next, we specify the data field to be used for the visualization in boxes #3 & #5. The second screen print is for the column chart; the publisher takes certain liberties to assume which field is the date field for the time service. You must then select the source data set, #2 in the second screen print and then the field for the measures, #3 in the second screen print. You would follow a similar process for each visualization.
Preview Data with SQL Server 2016 Mobile Reports
We are finally ready to review our work via the preview option. The grid design that is selected, right arrow in the below screen print, controls the preview you will see.
Now we can review, as shown below, each of the three grids in a preview mode!
Deploy Mobile Reports with SQL Server 2016
Our last steps are to deploy our newly developed report to the Report Server. This process is achieved by using the Save As button.
We will select the Save to Server option.
The Report Name will be prefilled if a Report Title has been filled in on the settings tab. The server is the connection we created earlier in the tip. We can use the location field to save the report to a particular folder. When complete, we select save.
If all goes well, the Report was Published message will be displayed.
Review the Final Mobile Reports in SQL Server 2016
Now we can navigate to the Report Server and switch to the New Report Server Preview Mode. Notice in the below screen print, our report appears along with a thumbnail of the report itself in the Master Grid design.
Clicking on the thumbnail displays the report using the Master Grid format.
Using the Chrome developer mode, we can also preview how the report appears on a tablet, shown next.
And finally in phone emulation mode.
Conclusion
We covered a lot of ground on the new SSRS Mobile Report Publisher tool. The tool allows us to develop SSRS reports which are formatted just for report viewing on a tablet or phone. The tool allows for the creation of a master grid design and then a design for tablets and a design for phones. To complete the process, we must connect to a dataset which has been previously create and saved on the report server or, alternately, a local Excel File can be used. Once the data set is established, each visualization can be "attached or connected" to the dataset, and certain key field assigned to data points for the visualization.
The Mobile Report Publisher is an exciting tool in the Microsoft BI stack; I hope to complete some future tips describing the various visualizations available and what settings can be used to customize those visualizations. Of course, please note that at this time, all the tools for SQL Server 2016 are in beta or preview mode.
Next Steps
- Review all the SQL Server 2016 tips on MSSQLTips
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
