Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Reporting Services Mobile Report


By:   |   Read Comments (40)   |   Related Tips: More > SQL Server 2016

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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
Mobile Reports Main Screen

 

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.

Report Server Preview Link

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.

New SSRS site

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.

create connection

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.

connection window

Once you have filled in all the details, you click connect. If successful, you will see the below confirmation.

connection complete

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.

connection error

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.

connection maintenance

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.

Settings

 

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.

Grid type

For both the phone and tablet grids, illustrated below, we can alternately adjust the grid rows and columns for each of those display types.

tablet grid

phone grid

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.

Background pallet

 

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.

Add to Master Grid

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.

Preview Example Data

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.

add to tablet 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.

tablet grid results

Subsequently, we can easily drag the graph or chart to another cell on the grid.

move visual

Or we click on the gears (see above screen print), which will open a cut / copy / paste / delete / redo set of buttons.

Edit Visuat

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.

drag visual to larger grid

larger visual

stackable grid

 

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.

simulated data

We can start adding our own data by clicking the Add Data button.

Add Data

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.

Add Data

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.

Add data from SSRS

A list of available data sets is shown; as noted below, you just click on the desired data set.

Available Datasets

Finally, the data set is displayed on the Data tab.

Sales Detail Data Set

A similar process is followed for adding a local Excel Source. First, Local Excel is selected.

Add Excel Dataset

Next, you browse for the file and then the Excel Data set is loaded, similar to the illustration below.

Excel Dataset

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.

Server Error

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.

Visual Option 1

Visual Option 2

 

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.

Preview Option

Now we can review, as shown below, each of the three grids in a preview mode!

Master Preview

Tablet Preview

Phone Preview

 

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.

Save As'

We will select the Save to Server option.

Save to Server

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.

Save to Server location

If all goes well, the Report was Published message will be displayed.

Publish Success

 

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.

SSRS Reprot Server Preview Mode

Clicking on the thumbnail displays the report using the Master Grid format.

SSRS Master View

Using the Chrome developer mode, we can also preview how the report appears on a tablet, shown next.

Tablet Mode

And finally in phone emulation mode.

Phone 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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, April 05, 2017 - 3:56:27 PM - Scott Murray Back To Top

 

Wow.. I have not seen that issue with only one visual showing up.  I would definitely log a Connect issue.


Wednesday, April 05, 2017 - 2:48:39 PM - Keith Bajura Back To Top

I have a weird issue. My mobile report works perfect in the Mobile Report Publisher. It contains 2 charts. But once I publish it, the one one time chart shows the no data icon. If I remove the other chart, and republish, it works fine. This make so sense. Seems like a bug or something.

 


Friday, November 18, 2016 - 9:18:48 AM - Scott Murray Back To Top

 

Jesus.. sorry, I am not aware of a World Wide Importer sample set of reports.  You can certainly use the old SSRS Adventure Works sample reports in SSRS 2016.


Thursday, November 17, 2016 - 6:51:21 PM - Jesus V Vasquez Back To Top

Great article and walkthrough. Any chance you know of any prebuilt reports our there that will use either the adventure works sample db or the new World Wide Importers db? Trying to show proof of concept work for internal staff so we can get buy in for working on new projects without having to spend too much time creating something from scratch. I know there were reports in 2012 we could use for Adventure Works that were great for proof of concept, but now that 2016 has come out they want to know all the bells and whistles 2016 provides. Any help is appreciated, thanks.


Tuesday, August 16, 2016 - 2:15:01 PM - Scott Murray Back To Top

Courtney,

That is my understanding, that you should be able to convert to SQL 2016... see http://www.datazen.com/blogs/post/announcing-end-of-support-for-datazen-products

 

I did not use Datazen, so I am not completely certain of the scenarios.


Tuesday, August 16, 2016 - 9:29:40 AM - Courtney Back To Top

 Hello Scott, I was curious if you can confirm something for me. I have been unable to verify if report models built in Datazen will be converted to SSRS 2016. We are preparing to install SQL Server 2016 and Reporting Services and I am trying to gauge the effort there. Any assistance is greatly appreciated!


Thanks.

 


Monday, August 08, 2016 - 1:13:16 PM - Mark Back To Top

Currently I am running my mobile report in the following fashion:

http://localhost/Reports_sql2016/mobilereport/folder name/Report Name

 

I see the report, but it's in the Portal. Can we run mobile reports in the way we run paginated reports, thus no Report manager is shown?


Monday, August 08, 2016 - 11:43:06 AM - Scott Murray Back To Top

 Mark and Marc,

I do not believe that functionality is available like it is on paginated reports.  I have read that your requested functionality may be on the product roadmap.  You will need to check with MS on that... I am just the messenger.


Monday, August 08, 2016 - 10:22:53 AM - Marc (with a c) Back To Top

 

Hi Scott, I reviewed your links regarding the parameters.

There it is explained how to provide a datasource with a parameter that comes from some selection / filteres WITHIN the report. 

It says: "Now when someone selects a value in your report, the dataset is queried again with the parameter value to return the appropriate result set."

My question is, how is it possible to take parameters from external like it is possible in the paginated reports? Lets say I create a smartphone app and add mobile reports. Now I want to pass the UserId (or whatever we have available on smartphones) during start of the app to pre-load it using the users defaults, settings... How can I pass a value to the mobile report?

 


Monday, August 08, 2016 - 9:50:09 AM - Mark Back To Top

 

 

But that parameter is for dataset. Isn't it just a filter for the received data? Also is there a way of accesing that parameter inside of a report?

Thanks


Monday, August 08, 2016 - 9:20:41 AM - Scott Murray Back To Top

 Mark... you can add a Mobile Report with parameters: 

 

please see this link:  https://msdn.microsoft.com/en-us/library/mt703703.aspx

 


Monday, August 08, 2016 - 9:17:02 AM - Scott Murray Back To Top

Carl...

You can certainly view mobile reports on a PC, so it is really up to you whether you like the smaller grid when viewing on the PC.  What I was trying to say in the conclusion is that the mobile reports size and formatting will be more conducive to viewing on a mobile device.  You can still view on a PC, but the overall grid will be smaller in size than a normal paginated report.


Monday, August 08, 2016 - 9:12:11 AM - Scott Murray Back To Top

PK:

 

 

I am creating mobile report using SQL server mobile publisher. Is there a way create solution for all the mobile reports that has been published on report server.

---Not sure about this question and what you are trying to do?

 

Also, how can we deploy the reports in new server?

---> you deploy reports like you would other reports.

 

Do we need to save all the report to local repository and then upload to new server.

 

-->  You would have a local file server to save these items before deploying to report server.

 

Thanks,

PK


Monday, August 08, 2016 - 4:53:20 AM - Mark Back To Top

 Is there a way to create a mobile report with a parameter?

 

Thanks


Monday, August 08, 2016 - 4:50:57 AM - Carl Thompson Back To Top

 Hi Scott,

 

Thanks for the reply.

I realise that a case by case decision still needs to be made regarding which type of report will suit which particular business need and that there will still be a need to develop reports as paginated reports.  I guess I was just wondering / hoping that for those reports that lend themselves more to a mobile report design these do not need to be developed as a paginated report for viewing on a PC as well as a mobile report for viewng on mobile devices.  I would expect that no-one would really like to have to duplicate the effort in creating 2 separate versions of a report and then maintaining each version moving forward.  If the report design is more suitable as a mobile report I'm hoping that the Master Grid design is suitable for viewing on PC?

I just wanted to clarify a little further as in your 2nd paragraph you state "Of course, if viewing on a computer, an even different view will be generated" when referring to mobile reports which makes perfect sense and intimates that mobile reports can be viewed on PC.  However, the 2nd line of your conclusion states "The tool allows us to develop SSRS reports which are formatted just for report viewing on a tablet or phone." which would indicate that mobile reports are not a suitable design for reports that will be viewed on PC.

All in all, I'm trying to figure that if we offer our internal customers the opton of having mobile reports designed will we have to double up our efforts to replicate these as paginated reports also?

Many thanks for taking the time to reply to my comments.

Carl

 


Monday, August 08, 2016 - 2:56:22 AM - Prashant Kumar Back To Top

 Hi Scott,

 

I am creating mobile report using SQL server mobile publisher. Is there a way create solution for all the mobile reports that has been published on report server. Also, how can we deploy the reports in new server? Do we need to save all the report to local repository and then upload to new server.

 

Thanks,

PK

 


Friday, August 05, 2016 - 9:55:58 AM - Scott Murray Back To Top

 Carl...

I guess you could create all reports as Mobile reports although I am not completely sure you would want to.  Paginated Reports serve a completely different focus / purpose than mobile reports.


Friday, August 05, 2016 - 5:38:17 AM - Carl Thompson Back To Top

 Hi Scott,

Thanks for taking the time to create this tip.  This answered a few questions I had about Mobile Reports.

However, please could you clarify whether Mobile reports could be used on a PC?  Is the Master Grid what is required for rendering the report on a PC?  I'm trying to determine whether all reports can be created as mobile reports or whether reports that are expected to be viewed predominantly on PC still need to be developed separately as paginated reports?

Thanks,

 

 


Friday, July 29, 2016 - 3:42:01 AM - Marc Back To Top

MOBILE REPORT PARAMETERS?

 @Scott: Thanks for the Url to explanation of Report Parameters. Unfortunately this article does not address the issue. In case we wanted to inplement moblie report into an existing application, the interaction of the application with the report must be realized bypassing parameters from external to the report to initialize it with correct data (for example a user id or a language that is already used in the application). So people are missing this option to consume a parameter and value from external inside of the mobile report.

Any thoughts?


Wednesday, July 27, 2016 - 7:09:33 AM - Scott Murray Back To Top

 Anupam,

 

You will need to deploy the report to your report server and then navigate to that site from your mobile.


Wednesday, July 27, 2016 - 6:11:54 AM - ANUPAM AWASTHI Back To Top

 

 Hi Scott,

 

I have create a report but not sure how I can access report from mobile. Do we need to install publisher in mobile to access report ?

 

 

 

Thanks,

 

Anupam  

 

** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.


Wednesday, July 20, 2016 - 9:53:33 AM - Scott Murray Back To Top

Pam.. I am not familiar with being able to show only the dashboard in full screen mode.


Wednesday, July 20, 2016 - 1:45:55 AM - Pam Back To Top

 I created a dashboard using SQL Mobile Report Publisher and published it to the server. After Publishing when I view that dashboard from the server I can also see the top shared area in screen. (The area with “SQL Server Reporting Services, Favorites, Browse, Settings, Download, Help etc”). Is there any way that I can view only the dashboard in full screen?

 


Wednesday, July 06, 2016 - 10:19:41 AM - Scott Murray Back To Top

 

Link on adding parameters...https://msdn.microsoft.com/en-us/library/mt703703.aspx

 


Tuesday, July 05, 2016 - 12:05:32 PM - Patrcia Back To Top

 Hi, 

I'm creating a report that has multiples screens many of them are charts and for those I'm using mobile report publisher tool, the others are long grids with a lot of information and in this case i want to use Reporting Services. How can i pass parameters on the url from Mobile report to a reporting services and vice versa. Now is working but the url has no parameters.

Thank you.

 


Friday, June 24, 2016 - 8:39:48 PM - scott Back To Top
  1.  Great idea.... will put it on my todos

Friday, June 24, 2016 - 5:02:26 PM - Scott Back To Top

 Hi Scott, an absolutely incredible tutorial and walk through - now that SQL 2016 went GA, do you have or plan anymore "excellent" follow-ups or additions?  For example, we'd love to see " a real in-depth tutorial on building a fairly detailed "Sales" and perhaps a "Finance" Dashboard using the AW DW 2016 sample !!

Thank you for the superb contribution to our learning journey!

Best Regards,

 

Scott Futryk

CEO & Co-Founder

Anywhere Anytime Communications

Silicon Valley, CA  USA

 


Saturday, June 11, 2016 - 11:36:36 AM - Denis Back To Top

Hello,

 

My company used the Datazen to build some dashboards some time . Just upgrade SQL Server to the 2016 version but we have a problem .

When importing the Dataset for mobilebuilder it does not recognize the fields of date type , as modified mascara field in analytics to various formats but even so it does not recognize .

 

Someone had the same problem ? You know what can be ?


Thursday, April 21, 2016 - 2:42:32 PM - Scott Murray Back To Top

https://www.mssqltips.com/sqlservertip/3428/sql-server-data-tools-ssdt-is-missing-after-installing-sql-server-2014/

 


Thursday, April 21, 2016 - 1:42:50 PM - RLC Back To Top

 

 ok yes here is the problem I dont seem to have that installed? I installed absouloutly everything that was available in the mssql rc2 iso , where would i find the ssrs ssdt-bi tool?


Wednesday, April 20, 2016 - 12:04:19 PM - Scott Murray Back To Top

RLC.. you need to develop the datasets in SSRS---SSDT-BI and then publish them to the report server or use report builder to do the same.  Once you develop the shared dataset, you just publish like you would a report... either upload manually or deploy.


Wednesday, April 20, 2016 - 11:51:47 AM - RLC Back To Top

 Hi Scott yes i have a connection how do i publish datasets to ssrs the database server and SSRS are on the same machie and i have the adventurwork , adventutrworksdw and a linked 2012 sql server on there. Also just as a side note I was following another tutorial that had me install the datazen server and in have 2 dataaset published in it (the datazen server is on port 81 not 80 however so ssrs should not be affected.

 


Wednesday, April 20, 2016 - 7:19:22 AM - Scott Murray Back To Top

 sivaprasad: I would check to make sure your dataset is not timing out.  How much data is there and how long does it take to return.

 


Wednesday, April 20, 2016 - 7:17:26 AM - Scott Murray Back To Top

RLC... Do you have a connection to the server?  That is the first step.  Next you need to have published a shared dataset in order to use it on a mobile report.


Wednesday, April 20, 2016 - 5:00:21 AM - sivaprasad Back To Top

 

 Hi Scott,

Thaks for good Information...

We are sucussfully login SSRS Portal and we created dataset also,Then when we are trying to access from Mobile report Publisher unable to connect the Data Connection.We are provided correct credentials...Can you help on this error

Error: underlying connection was closed unexpected error on send

 

 

 

 

 

 


Tuesday, April 19, 2016 - 7:05:43 PM - RLC Back To Top

 Hi 

 

how do I get the datasets to show up in the add data from server screen mine are currently blank

 


Tuesday, April 19, 2016 - 8:51:09 AM - Scott Murray Back To Top

Sure.. will try to get one done.


Tuesday, April 19, 2016 - 8:13:16 AM - Robert Back To Top

 

 Thanks for the update.  Could you post an article how to?  Thank you.


Monday, April 18, 2016 - 9:40:26 AM - scott murray Back To Top

 

You can not use parameters in the old fashsion SSRS sense, but you can use filters by clicking on different series values.


Monday, April 18, 2016 - 8:28:05 AM - Robert Back To Top

 Thanks for the article,  I started to begin doing some Mobile Test Reports. I have a question:  Can we use parameters on mobile reports?   I'm looking forward to learn if this can be, so I could make a presentation.  Thank you.

 


Learn more about SQL Server tools