Posts

Power BI Premium Reports on Mobile Devices

Can we surface Power BI reports deployed to Power BI Premium Report Server on mobile devices? Yes, and we have three quick options:

  1. Since Power BI reports render in HTML5, they should render in any modern browser.
  2. On iOS and Android devices, consider Power BI Mobile for optimum viewing experience. In Power BI Mobile, go to the Settings and click Connect to Server. Then plug in the report server URL, e.g. http://<servername>/reports. Note that the Power BI Mobile Windows app doesn’t support Power BI Premium Report Server yet. Also, by default your reports will only work if you are on your company intranet or the mobile device connects to your company VPN. To make reports available outside corporate network, you can configure a Web Application Proxy to tunnel in.
  3. For embedding reports on a page, have an HTML page with iframe that request the report by URL passing the embed parameter as explained here.

Power BI can’t reach to your report server? Don’t forget to configure the firewall on the Power BI Premium Report Server to allow inbound connections over port 80, as explained here.

SSRS Tabular Query Designer

Analysis Services Tabular has gained a lot of momentum for implementing organizational semantic models but its toolset has been lacking. SSRS developers had to rely previously on the MDX Query Designer to send MDX queries to Tabular. If DAX queries were preferred (and they often are for better performance since DAX is the native Tabular query language), developers had to type in DAX manually. And if the query would take parameters then the ugly workaround was to use … the DMX Query Designer.

As Chris Finlan announced yesterday, this will all change starting with the newly released Report BuilderSSDT 17 (currently in release candidate state), and SSMS 17 (also in RC state), as they now feature a Tabular Query Designer. SSMS also includes DAX Query Editor, as Christian Wade explains here.

If you’re familiar with the MDX Query Designer, you already know how to use the Tabular Query Designer. When you connect to Tabular, the query designer will discover it and default to DAX query mode. The dropdown allows you to switch to MDX should you prefer to send MDX queries to Tabular.

Similar to its MDX counterpart, in most cases you’d still need to know DAX to customize your queries. If you connect to Tabular version 2016 or above, you’d be able to drag and drop metadata to autogenerate the query. I think version 2016 or above is required because the designer relies heavily on the DAX SUMMARIZECOLUMNS function, which was introduced in 2016, when autogenerating queries.

The Design Mode lets you examine the underlying DAX query and customize it as needed. Sorry, no color coding or syntax checking in SSRS, so you’d probably rely on DAX Studio to code and test your queries or the new DAX Query Editor in SSMS which has syntax checking and IntelliSense.  The Calculated Member option is disabled and you’re on your own defining custom DAX measures. No big surprises here is it works exactly in same way as in MDX (of course you need DAX formulas, such as VALUE to convert to a numeric value).

It was about time for the Tabular Query Designer to appear to help us writing DAX when designing traditional SSRS reports connected to Tabular. Watch out for a bug where auto-generated parameter datasets error out when hierarchies are used. I attach two reports. Report.rdl demonstrated an auto-generated DAX query. ReportEx.rdl demonstrates a customized DAX query.

Download Files

Power BI Reports in SSRS Techinical Preview

From the glimpse to the first public preview…it’s great to see one of most requested feature coming to life: ability to render online Power BI reports in on-premises SSRS. Alas, Microsoft is keeping us in suspense and no official date and release vehicles have been announced yet but we can now see and test it using the VM that Microsoft put on Azure (read the Chris Finlan’s steps to get started).

At this point, the integration supports only Power BI Desktop files that connect to Analysis Services (Multidimensional and Tabular). Attempting to deploy models connected to something else or with imported data, doesn’t work and you’ll get an error. Custom visuals and R visuals are not supported yet. For the most part, the integration is limited to report viewing only (similar to what you get if you embed Power BI reports in Power BI Embedded). That’s will be probably fine to start with but it will be nice to have Q&A, Quick Insights, Analyze in Excel, and ability to create custom reports online.

It’s great that Microsoft decided to expose the connection string as a regular SSRS data source. This will allow you to change the credentials settings, such as to impersonate the user when Kerberos is not an option and the SSAS is on another server. When the Power BI Desktop file is uploaded, it’s saved in the report catalog as any regular SSRS report (now referred to a paginated report). This means that you secure and manage Power BI reports the same way you work with paginated reports. Speaking of management, I hope that at some point Microsoft will add support for subscriptions and caching. What’s need is unification among the four types of reports: paginated, mobile, Power BI, and Excel. While waiting, we get a handy bonus feature: ability to add comments to reports, such as to get someone to formally approve what they see on the report.

Everyone is asking about SSRS support for Power BI reports. Progress has been make and I hope it won’t be long before we get the real thing.

A Glimpse of Embedding Power BI Reports in SSRS

The first public demo of the highly anticipated Power BI report embedding feature in SSRS 2016 on premises came from Microsoft Ignite. Scroll to the 58 minute in the Ricardo Muti’s “Create a modern enterprise reporting and mobile BI solution with SQL Server 2016” video and enjoy! I expect more details at SQL PASS SUMMIT at the end of this month. Thanks to Dan English for pointing out this video.

100716_0242_AGlimpseofE1.png

SSRS UX Changes in SQL Server 2016

SQL Server 2016 RC3 (last and feature complete RC) just came out for public review. It includes a couple of interesting UX enhancements. The first one is more of a teaser but shows you that Microsoft is committed to fulfill and go beyond its reporting roadmap. SSRS in native mode plays a central role in this roadmap as the on-premises BI reporting platform.

The new portal (the old Report Manager portal is gone BTW) now includes sections if you upload Power BI Desktop files and even Excel workbooks! For SQL Server 2016 RTM timeframe, clicking a file of these two types simply opens it on the client with the corresponding application (Power BI Desktop for PBIX files and Excel for Excel workbooks). So, no embedded web rendering yet but I guess these features won’t be there if Microsoft isn’t prepared to travel the full distance after RTM.

041616_2211_UXChangesin1.png

Second, we now have branding of the portal and mobile reports, as Chris Finlan explains in his “How to create a custom brand package for Reporting Services with SQL Server 2016” blog.

041616_2211_UXChangesin2.png

SSRS Treemap and Sunburst Charts

Years ago, I wrote a blog about how, with some code wizardry, you can create heat maps with Reporting Services. Moving to SSRS 2016, you don’t have to do this anymore thanks to the new Treemap chart type!

Speaking of new charts, SSRS also adds a Sunburst chart.

 

Integrate SSRS 2016 Reports with Power BI

As I said in the “Microsoft Unveils BI Roadmap” blog, SSRS will play an important role of the on-premises BI portal. With this post, I’ll start covering the new SSRS 2016 features. One of the Power BI features announced at the PASS Summit was the forthcoming integration between SSRS 2016 and Power BI that will allow users to pin SSRS report items to Power BI dashboards. You can test pining reports items to Power BI with SQL Server 2016 CTP 3.0 and later. The newly released CTP 3.2 also features the new sleek SSRS portal (we’ve come a long way!). The items you can pin currently include charts, gauge panels, maps, and images. Pinning a report item takes a few clicks:

  1. Open the report and click the Power BI button (a new toolbar button in SSRS 2016). If this is the first time you do this, you’ll be asked to authenticate with Power BI and grant SSRS special permissions it needs for pinning. Then, SSRS switches to a special page mode that shows which items can be pinned.

  1. Click the tile you want to pin.
  2. In the “Pin to Power BI Dashboard” window, select the dashboard you want to pin the report to and the update frequency that specifies how often Power BI will query the report for data changes.

And, the report item is pinned to the dashboard as a regular tile. You can click the tile to navigate back to the SSRS report.

As you can see, Power BI is becoming a focal point for cloud hosting of the main MS report artifacts: Power BI reports, Excel reports, and SSRS reports.

Introducing Office Online Server

Continuing the line of thought from my previous blog “Microsoft Unveils BI Roadmap”, one nagging question still remains. What to do with all these Excel files now that SSRS came with a vengeance and has been promoted to THE on-premises BI platform? It looks like Microsoft is thinking along these lines. Interestingly, Excel Services will be removed from SharePoint Server 2016 and replaced with Office Online Server, as explained here. And this page gives more details about Office Online Server which is currently in preview. In a nutshell, Excel rendering would work pretty much in the same way as in SharePoint Server 2013. The most common scenario that Microsoft plans now is to have the Excel workbook in SharePoint. When you open the workbook, SharePoint will redirect you to the Office Online Server using a WOPI frame (some sort of an Iframe), with some special tokens that enable Office Online Server to retrieve the workbook from SharePoint, and then render it as usual. This will work for Power Pivot models as well. Office Online Server (OOS) also supports loading Excel files from OneDrive which is how Power BI Excel rendering works today.

But wouldn’t be nice to extend SSRS 2016 and integrate with OOS so that you can deploy Excel reports to a report server installed in native mode? This would allow SSRS to handle all of the important Microsoft report formats (SSRS, Datazen, Power BI Desktop, and Excel). If OOS doesn’t require a SharePoint license, this will be a great value proposition as well as you will save you a SharePoint Server Enterprise Edition license. This is precisely the wish I communicated to Microsoft and posted it on the Excel wish list. Please vote if you like the idea.

You know that there is an Excel wish list site, right?

SSRS Style Templates

As you probably know, the SSRS Report Wizard allows users to select predefined report styles. These styles are basic “skins” that apply to report foreground and background colors.

3124.styles.jpg-550x0

A question came in from a student as to how to modify/add styles. You can alter the existing style templates or add new ones by editing the StyleTemplates.xml file in the \Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles\<language> folder. You need to make this change on the client machine where SSDT is installed.

On the subject of styles, recall that starting with SQL Server 2008 R2, SSRS supports report parts to promote report reuse and reduce maintenance.

How to Test SSRS Data Alerts without Corporate Mail Server

Reporting Services data alerts were introduced in SQL Server 2012 to allow business users to receive e-mail notifications based on rules they specify. Data alerts require SSRS to be configured in SharePoint integration mode. However, you’d probably run SharePoint on a VM for development and demo purposes and you might not have access to a functional mail server. The following steps allow you to configure your VM to test data alerts with a local SMTP server:

  1. Assuming your SharePoint VM is running Windows Server 2012, open Turn Windows Features On and Off and add the SMTP Server feature. This will install a local SMTP server.
  2. Open IIS Manager 6.0 and start the SMTP Virtual Server. Configure the SMTP server to allow relay.
  3. Because SSRS 2012 integrates natively with SharePoint, you can’t use the report server configuration files to directly change the SSRS settings. Instead, you must use PowerShell as I explained here. Open Windows PowerShell ISE and execute the following script to configure the SSRS e-mail settings. Replace <your SSRS application name> with the name of your SSRS application which you can obtain from the SharePoint Central Administration ð Manage Service Applications. Execute the script.

    051014_2250_HowtoTestSS1

    Add-PSSnapin Microsoft.SharePoint.PowerShell

    $app=get-sprsserviceapplication -Name “<your SSRS application name>”

    $emailCfg = Get-SPRSExtension -identity $app -ExtensionType “Delivery” -name “Report Server Email” | select -ExpandProperty ConfigurationXml

    $emailXml = [xml]$emailCfg

    # $emailXml.SelectSingleNode(“//SMTPServer”).InnerText = “<email server name>”

    $emailXml.SelectSingleNode(“//SendUsing”).InnerText = “1”

    $emailXml.SelectSingleNode(“//SMTPAuthenticate”).InnerText = “2”

    $emailXml.SelectSingleNode(“//SMTPServerPickupDirectory”).InnerText = “C:\inetpub\mailroot\Pickup” # You probably don’t need this; I was experimenting with dropping the messages to a pickup folder

    $emailXml.SelectSingleNode(“//From”).InnerText = ‘teo.lachev@prologika.com’

    Set-SPRSExtension -identity $app -ExtensionType “Delivery” -name “Report Server Email” -ExtensionConfiguration $emailXml.OuterXml

  4. If you haven’t configured the data alerts before, go to SharePoint Central Administration ð Manage Service Applications ð <Your SSRS Application>, and then click Provision Subscriptions and Alerts. If you have admin permissions to the SQL Server that hosts the SharePoint databases, enter your credentials and click OK to grant the SSRS service account access to the SQL Server Agent (the SQL Server Agent Service must be running for the data alerts to work). If you don’t, then click the Download SQL Script link and give the script to the DBA to execute the script in order to grant the SSRS service account these permissions.
  5. Now, create a data alert as usual and run it from the Manage Data Alerts page. After you manually run the alert, you shouldn’t see an error in the Status column.051014_2250_HowtoTestSS2
  6. Go to the SMTP service queue folder, the default one is C:\inetpub\mailroot\Queue. You should see files with extension *.EML. You can open these files with Microsoft Outlook to see the details of the e-mail notification that SSRS sends to the recipient. It should look like the one below. In this case, I’ve create a report that shows the status of the SharePoint timer jobs and I wanted to be notified every time the Status=3.

051014_2250_HowtoTestSS3