SQL Server Reporting Services 2016 with SharePoint

SharePoint just uses the SSRS add-in to render reports. With SSRS 2016 you need to use the SSRS 2016 add-in for working with SharePoint 2013.

Depending on the SQL Server version you can look up what combination of SharePoint works with Reporting Services (SQL Server Version).

Although the designated platform for SharePoint Server 2010 were Windows Servers 2018 (x64 Bit) or Windows Servers 2018 R2 (x64 Bit), it was still possible to install on Windows 7. In fact this was how I could work with Share Point Integrated mode for Reporting Services as detailed in my book. I used the versions highlighted in the above image.

The details of using the SSRS in SharePoint Integrated mode are available here:

Vertical alignment property of Text is missing in Report Builder 3

The latest Report Builder v3 used with SQL Server Reporting Services has the vertical alignment property missing. In order to have proper formatting, this property should be back in Report Builder.

KanaType and KanaType sensitvity in SQL Server

This is specifically for data in Japanese language databases. There are two types of Japanese phonetic scripts, the Hiragana and the Katakana. Of course there is also the Kanji which are modified/simplified Chinese characters used in Japan together with Hiragana and Katakana.

While dealing with Japanese data Kanatype is used to distinguish between Katakana and Hiragana. Katakana is usually used for words not found in Japanese, like, Bus, Laser, harassment etc.


For example, the 'dataset' in Reporting Services have this property:

DataSetDefinition.KanatypeSensitivity

Here is a piece of text in Hiragana
ふるい　もの　の　なか　に　ある　かくれた　うつくしさを　「さび」　と　いいます。 また、かざらない　そぼくな　うつくしさ　を　「わび」　と　いいます。ふたつ　を　あわせて、「わび・さび」　と　いいます。

Source:     http://gakuu.com/haikyo-hiragana-reading-practice/

Here is a piece of text in Katakana

The same text written in Katakana using online translator:
フルイ　モノ　ノ　ナカ　ニ　アル　カクレタ　ウツクシサヲ　「サビ」　ト　イイマス。 マタ、カザラナイ　ソボクナ　ウツクシサ　ヲ　「ワビ」　ト　イイマス。フタツ　ヲ　アワセテ、「ワビ・サビ」　ト　イイマス。

Source:  en.calc-site.com/letters/convert_kanaen.calc-site.com/letters/convert_kana

Saving Power BI reports to SQL Server Report Server - Part 3

The readers of my posts Saving Power BI reports to SQL Server Report Server - Parts1 and  2
  might have been under the impression that any report created in Power BI can be hosted on the Report Server. I am partly to blame for this and I apologize. Let me correctly present what kinds of reports can be saved to what kind of report server. The other reason for the problem was created by the Power BI itself for giving the wrong guidance for the users. It must be changed.

The only kind of reports that can be saved to Report Server (in fact you are only saving it to the Web Portal URL) are the ones created with data from the Analysis Services server.

In order to save you need to configure a stand-alone Report Server whose database is stored not on a SQL Server 2016 but an earlier version. In my case I use the SQL Server 2012.

After you fulfill the above conditions then only you can host the report.

I was able to get the correct server by an answer to my question on StackOverflow.com.

http://stackoverflow.com/questions/43402339/invalid-url-error-while-saving-power-bi-report-to-sql-server-reporting-services

Saving Power BI reports to SQL Server Report Server - Part 2

In an earlier post the method to save a report from Power BI to SQL Server Report Server was described. In order to do so you need to use the Technical Preview of Power BI Desktop.

In this post the installing of the Technical Preview will be described. The present Windows 10 Professional is a x64 bit machine and therefore we start with the .msi file shown here.
PBIDesktopRS_x64.msi

When you double click the above file you will be launching the wizard.

Click Next> to open the License Terms window.

Accept terms and click Next>. The default directory for the program is displayed. If needed change. Herein default is accepted.

Click Next>.  You are now ready to Install.

Click Install. It is installed. It may take 10 to 15 minutes.

Click Finish and if you have agreed to launch the program, the Power BI gets launched.The splash screen shows that this Power BI Desktop goes with SQL Server Reporting Services.

The program is now ready to create/save reports.

 NOTE: If you already have configured a SQL Server 2016 Reporting Services server you will not be able to upload the reports from Power BI. You need to install the SQLServerReportingServices.exe mentioned in previous post and configure a Standalone SQL Server Reporting Services which will provide a http://<domain>/ReportServer.

Saving Power BI reports to SQL Server Report Server

If you use the Technical Preview version of Power BI Desktop. You can save reports to your on- premises SQL Server.

NOTE: If you already have configured a SQL Server 2016 Reporting Services server you will not be able to upload the reports from Power BI. You need to install the SQLServerReportingServices.exe mentioned below and configure a Standalone SQL Server Reporting Services which will provide a http://<domain>/ReportServer.

You can download this for both x32 and x64 versions. With this you can,

•     Connect “live” to Analysis Services models – both Tabular and Multidimensional (cubes)

•     Visually explore data and create an interactive report

•     Save that report to your report server running the Technical Preview

•     View and interact with the report in your web browser

If you do have Power BI for Desktop on your computer you still need the (January 2017) Technical Preview. This can sit alongside your present Power BI.
=====================
Technical Preview of Power Bi for Desktop download link
https://www.microsoft.com/en-us/download/details.aspx?id=54610
=====================
Preview version details below:
Version:            Date Published:

0.1                1/11/2017

File Name:                                                               File Size:

PBIDesktopRS.msi                                      102.2 MB

PBIDesktopRS_x64.msi                           118.3 MB

SQLServerReportingServices.exe    49.6 MB


System Requirements:
Supported Operating System

Windows 10 , Windows 8, Windows 8.1, Windows Server 2012, Windows Server 2012 R2, Windows Server 2016

    You'll need
        .NET Framework 4.5.2 or later
        SQL Server Database Engine (2008 or later), to store the report server database
        SQL Server Analysis Services (2012 SP1 CU4 or later), to store your data models
    Links:
        Get SQL Server 2016 Evaluation
        Try SQL Server vNext CTP1.1

New in SQL Server 2016 Reporting Services: File Share

This is feature is applicable only for Native Mode.

In order to specify a single file share account ( a new feature of SQL Server 2016) you must get to the Subscription Settings page of the SQL server 2016 Reporting Services Configuration Manager shown here.

The file share account that you set up here allows you to set a single set of credentials in multiple subscriptions that deliver reports to a file share.

When you want to change credentials you just change the credentials of the file share and you need not change the credentials of every subscription in the shared folder. In the subscriptions in the file share each subscription will specify; Use file share account.

However you may also configure individual subscriptions with specific credentials as well or, have some on file share and some with individual specific credentials.

Uploading a Moble report to a Report Server

This post is about displaying a Mobile Report created by the SQL Server Mobile Report Publisher after publishing the report to a SQL Server Reporting Services which is actually the Web Portal URL of the Reporting Services.

The present Reporting Services configuration is pertinent to SQL Server 2016 Reporting Services on the local machine.

While it should also be possible to publish it directly from the SQL Server Mobile Report Publisher, it was not possible because there were errors connecting to the Report Server although the SQL Server 2016 Database Engine; the Reporting Services were all up and running and an empty folder 'Reports' was created on the Web URL. I have not tried to verify the problem yet. This could be because of the constant upgrades coming to the OS automatically.

In this post, I consider uploading a report to the Web Portal URL after saving the file created by the SQL Server Mobile Report Publisher to a local folder.

You need to make sure the Reporting Services database is accessible which the same as the SQL Server Database Engine  running; the Reporting Services has started and the Service and Web Portal URL have no problems.

Then use the Upload button on your Web Portal URL to upload the report created and saved (to local file system) to the computer. Once it gets uploaded the report gets into the WEB Portal URL as shown.

The Northwind Report has three charts, the creation of one of them is detailed in my earlier post here. I have just added two more grid items this upload.

You can view each of them on the WEB Portal URL as well as shown here.

http://hodentek8/Reports_OHANA/mobilereport/Northwind

Herein http://hodentek8/Reports_OHANA is my Web Portal URL.

Creating a report using SQL Server Mobile Report Publisher

It is relatively easy to create reports using the SQL Server Mobile Report Publisher. You can source your data from two kinds of data sources, all though more sources  may be added in future versions:

Excel.xlsx files
Reporting Services Web Portal

In this post I will be considering getting data from an Excel file.

If you have used Power BI before, it is even more easier as the UI follows some of the same ideas.
In order to follow this you must install the SQL Server Mobile Report Publisher (SSMRP). You may follow these earlier posts 1, 2  to install SSMRP.

Kindly follow the screen shots that steps you through the procedure to create a simple report.

You launch the SSMRP from its shortcut in All programs.

By default it opens displaying the Layout tabbed page.

The toolbar has Layout, Data, Settings and Preview. I have parked it in Data that is what I am going to do first.

Click on Add data to get some data into the UI. You have two options, Excel and Report Server.

This time around I will be using data from an Excel file.   I will be using the same file I used in several of my recent posts 1, 2, 3. However, I opened the .csv file in Excel and saved it as a file with the .xslx extension. You start browsing for the file in your hard disk.

FFDL.xslx is the file that is found while browsing.

Click Import at the bottom of the UI after placing a check mark for the FFDL file a shown above to bring data in as shown.

 This  sample data is from Northwind database on my SQL Server.
Click on the Settings icon on the FFDL file at the bottom if you need to remove/export etc. as shown.

Now click on Layout and you get a number of rectangles on the right onto which you can bring a chart from a variety of charts under Layout. You can reduce the columns or rows of rectangles using the vertical and horizontal scroll controls at the top

Herein, I just dragged and dropped a bar chart on to one of the rectangles.

When you click on this chart it enlarges as shown.

Obviously this chart does not reflect our data as the year axis is all wrong.

Go back by clicking on the back arrow at the top which will take you back to Layout. Now click on Data.

As you can see the chart at present is relevant to a Simulated table. Click on the handle for Main Series to display the following:

Click on FFDL. You should be seeing this displayed.

After changing to Avg click on Preview. Click on the small icon for report to zoom it up to full window.

You can change quite a lot of other things, like adding category; adding  a report title and some of these can be done in the Settings and some in the Layout items.

When you target a smaller foot print device  using the dropdown in Layout shown here:

You will see the following:

Oops! That did not work. I wonder what the problem is. I went back to Master and it is still OK. Next I try changing to Tablet and the data does not show. Something is broken! Is it possible that the last nights OS update did this? I wonder.

I apologize for not getting it all done in one go (it took me 4 or 5 attempts to post this). I have a serious browser problem that I have not been able to address yet. Review here for my browser problems.

Installing Microsoft SQL Server Mobile Report Publisher

In order tow rok with Microsoft SQL server Mobile Reporter you must first download the program from the link below.

Download from here:
https://www.microsoft.com/en-us/download/confirmation.aspx?id=50400

You would have downloaded a SQL Server.MobileReportPublisher.Installer.msi installer which will take you through the steps to install. The installation is quite straight forward. However, you just very the requirements mentioned in my previous post here.

Here are some installation files. As there are various 3rd party programs you may have to agree for those license terms also. You will find details of those after the installation.

 A shortcut gets added to All programs.

Click the shortcut to launch. The UI gets displayed as shown.

Mobile Reports Publisher and SQL Server

SQL Server Mobile Report Publisher (SSMRP) is an authoring tool that can be used to quickly creating Reporting Services mobile reports that are optimized for mobile device with a variety of form factors. You can have an assortment of visualizations, from time, category, charts, tree maps, and custom maps.

With SSMRP you can,

• Connect mobile reports to a range of data sources including on-premises SQL Server and Analysis Services
• Layout reports on a design surface to accommodate flexible mobile reports
• Save mobile reports to a Reporting Services server and interact with them in a browser or with Power BI on mobile devices(iPad, Android Phones and tablets and Windows 10 devices).

What do you need to create Reporting Services mobile reports?
You need the SQL Server Mobile Report Publisher

Version: 1.0.3923
File Name: SSRS.MobileReportPublisher.Installer.msi
Date Published: 9/22/2016
File Size: 25.3 MB

System requirements:
Supported Operating System
Windows 10 , Windows 7, Windows 8.1
.NET Framework 4.5 (or later)
Visual C++ Redistributable for Visual Studio 2012 (x86)

Download SSMRP here:
https://www.microsoft.com/en-us/download/details.aspx?id=50400