Querying the SSRS 2017 REST API using Fiddler

Fiddler is a tool to test the API Request/Response messages. Let us see how can we use Fiddler to send and retrieve messages from the SSRS 2017 REST API.

URL format for the REST API - http://<ReportServer>/Reports/api/v2.0/<endpointname>

e.g. http://<ReportServer>/Reports/api/v2.0/MobileReports to get the list of all Mobile Reports from the SSRS Portal.

Swagger Documentation - https://app.swaggerhub.com/api/microsoft-rs/SSRS/2.0

Note: The SSRS 2017 REST API is OData compliant and if you need to learn about OData queries you can try them from here. (http://services.odata.org/ODataAPIExplorer/ODataAPIExplorer.html)

OData Tutorial - http://www.odata.org/

a) Download Fiddler, install and open it.

b) Click on the Composer Tab and type the URL to get the list of MobileReports uploaded to the SSRS Portal.

c) Click on Options tab and check the “Automatically Authenticate” option.

d) Go back to Parsed tab and click the Execute button and you can see the HTTP Status code as 200 as shown below.

Note: If you see 401 error then you need to go back and check whether you have done the Step c correctly.

e) Finally you can see the JSON output under Inspectors > JSON tab

Technorati Tags: OData,SSRS,SSRS2017,Api

SSRS 2017 and ReportViewer control in ASP.NET Web Application (.NET Framework)

1. Create a new ASP.NET Web Application (.NET Framework) Project using Visual Studio 2017

2. Select Web Forms Project

3. Run the application by pressing F5

4. Create a new Reports.aspx page with Master Page.

5. Open Nuget Package Manager Console from Tools > Nuget Package Manager

6. Install the ReportViewerControl for WebForms using Install-Package Microsoft.ReportingServices.ReportViewerControl.WebForms


7. Type the following in the Reports.aspx.cs page.
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
         <rsweb:ReportViewer ID="ReportViewer1" runat="server" ProcessingMode="Remote" Width="850px" Height="680px">
             <ServerReport ReportServerUrl="http://<your report server>/ReportServer"  ReportPath="/<folder name>/<rdl report name>"/>
         </rsweb:ReportViewer>
</asp:Content>
8. Run the application by pressing F5 and navigate to Reports.aspx page and you can see the Paginated Report served as shown below.

Is it possible to embed my Mobile Reports in Web Pages using my Report Viewer Control?
No, that is not possible. You can embed only Paginated Reports and if you try to embed Mobile Reports you will get “The operation you are attempting on item ‘<Report name>’ is not allowed for this item type. (rsWrongItemType)“ as shown below.

Technorati Tags: SSRS,SSRS2017

Comments in SSRS 2017

Business users have been looking for the feature of providing comments for Reports in SSRS. Until SSRS 2017 this has been mitigated by embedding the SSRS report in a .NET application and provide ability to input comments on a .NET UI and saving them.

With SSRS 2017, now comments can be directly provided for reports (Paginated and Mobile) using the SSRS Portal.

Comments can be added at Report level and these comments are stored in the Comments table in the ReportServer Database.

You can also add attachments as part of your comments and these attachments will be linked in the Catalog table in the ReportServer Database.

Security can be applied through the SSRS roles for comments also.

Technorati Tags: SSRS,SSRS2017

Hide download menu in SSRS 2017

In previous versions of SSRS there is no way to hide the Download menu from the SSRS Portal which allows users to download Report Builder, Mobile Report Publisher applications. Normally Enterprises don’t want their users to download software and install in their laptops instead they normally push via Group Policy (GPO).

In SSRS 2017, now you can hide the Download menu on SSRS Portal by connecting to the SSRS instance using SSMS and right-click on the SSRS instance and select Properties and click on Advanced and set Show Download Menu as false.

Note: There is no need to restart the Report Server instance after the change.

As shown in the above screenshot, no Download Menu appears now.

Technorati Tags: SSRS2017,SSRS

Tags: SSRS2017,SSRS

Building your first SQL Server Mobile Report

a) Download SQL Server Mobile Report Publisher tool from https://www.microsoft.com/en-us/download/details.aspx?id=50400

b) Open Mobile Report Publisher and click Data

c) Click on Add Data

d) Select Excel and Sheet2

e) Change Title of the Mobile Report and Save Mobile Report to Server (SSRS Report Server – native mode)

f) Here is fully built dashboard.

Download Mobile Report

Technorati Tags: Mobile Report,SSRS2016

SSRS - Parameter Date format in Google Chrome is mm/dd/yyyy instead of dd/mm/yyyy

• To fix the issue, you need to change the accept-languages property in the preferences file for Google Chrome. To do that, type “about:version” in the address path and copy the profile path value.
• Close down the Chrome Browser.
• Open the preferences file from the profile path copied from step 1 and look for the “accept-languages” property
• Change the property from “en-US”,en to “en-NZ”,en and the parameter dates in SSRS will now display in dd/mm/yyyy format in Google Chrome.

Reference: https://productforums.google.com/forum/#!msg/chrome/psPDoulWfzc/7CrsYYO576wJ

Technorati Tags: Google,Chrome,SSRS

SQL Server Reporting Services 2016

SQL Server Reporting Services (SSRS) 2016 is an on-premises solution for deploying and managing reports.

SSRS 2016 supports

• Paginated Reports - Design beautiful reports using updated tools and new features built on HTML5 technology.
• Mobile Reports - Create responsive, interactive reports optimised for mobile devices.
• New Web Portal - Consume both type of reports in one web portal using modern browsers.

Paginated Reports

• Modern chart and gauge styles
• Treemap and Sunburst charts
• Updated Report Builder look and feel
• Visual Studio 2015 support
• Plugin free printing

Customisable Parameter Layouts

• Position Parameters across more than 2 columns
• Choose position for each parameter
• Create space between groups of parameter

Pin Report Items to PowerBI Dashboards

• Monitor most important metrics and trends in one place using KPIs
• Pin charts, gauges, maps and images as tiles to Power BI Dashboards
• Drill through to source reports
• Refresh tiles on a schedule

Create Interactive Mobile Reports

• Drag-and-drop to create beautiful, interactive data visualisations
• Prototype in minutes with simulated data
• Access enterprise data sources by connecting to Reporting Services
• Customize layouts for different form factors

Consuming Mobile Reports

• One mobile app for Power BI and Reporting Services Mobile Reports
• Native mobile experience optimised for touch
• Keep data up-to-date with real-time query or scheduled data refresh
• Interact with mobile reports even when disconnected

Technorati Tags: SSRS,SSRS2016,PowerBI

[rsRuntimeErrorInExpression] The value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Input string was in a correct format.

In this scenario, the expression for the textbox was =”Total Value: “ + Fields!TextBox1.Value

Changing that to =”Total Value: “ + CStr(Fields!TextBox1.Value) will fix the issue.

Technorati Tags: SSRS

RenderFormat.IsInteractive and RenderFormat.Name

Sometimes reports require branding not to be printed when exported to Excel. Since SQL Server 2008 R2 and above this can be done using the RenderFormat.IsInteractive property.

Globals!RenderFormat.IsInteractive will return true when viewing the report in preview mode or using ReportManager tool and will return false for the export formats.

It will really good to set the property if you want to

Use complex formatting and interactivity for on-screen display, but optimize for Excel export and post-processing with a more data oriented view.

Print: add/omit text disclaimers, logos, background images, etc.

PDF/Excel/Word: dynamically disable hyperlinks when report is exported

TIFF/Print: define a particular color chart palette for all rendering formats, but use an optimized grayscale palette when using TIFF for fax, or printing

If in your particular situation you have to make decisions based on the actual type of rendering format, you have to write your expression using the exact, case-sensitive name of the rendering extension as registered in RSReportServer.config / RSReportDesigner.config using the property Globals!RenderFormat.Name.

Reporting Services built-in rendering extension names include: "WORD", "EXCEL", "PDF", "MHTML", "IMAGE" (includes TIFF, Print). Note that interactive viewing in preview or report manager uses the "RPL" renderer.

Technorati Tags: SSRS,RDL

SSRS Preview in VS 2013: There was no endpoint listening at net.pipe://localhost/PreviewProcessingService4440/ReportProcessing that could accept the message.

When you preview the SSRS report, a console window opens and don't close that window. If you have done that already then close and open the solution and try preview again.

24 Hours of Pass - Business Analytics Edition

This 24 Hours of PASS: Business Analytics event will take place on February 5, beginning at 16:00 GMT and will feature 12 webcasts followed by 12 hours of on demand replay.

Sessions

Advanced Analysis Techniques

Advanced Analytics in Excel 2013
Excel is “the” analytical tool in Microsoft’s suite for advanced analysts. Of course, you know that...
Dejan Sarka
An Introduction to Predictive Modeling
This session will show you how to solve a real-world data mining problem by demonstrating the...
Carlos Bossy
Introduction to Querying in DAX
The DAX (Data Analysis Expressions) language can be used to query a Tabular and PowerPivot data...
Marco Russo
Predictive Analytics for Absolute Beginners
In this one-hour preview presentation, we cover the main motivation for learning something about...
Mark Tabladillo, Artus Krohn-Grimberghe

Analytics and Visualization

Adding Valuable Techniques to your Data Science Toolbox
Data Science requires us to see and understand complex patterns in data, so we use techniques like...
Mark Whitehorn
End-to-End Power BI
The aim of this session is to excite you with the potential of Microsoft's new self-service suite...
Peter Myers
Power Query: Beyond the Basics
You already know that you can accomplish a lot within the Power Query user interface. If you are a...
Chris Webb
Small Big Data - Phase 0 - Data Hygiene 
In this talk, we will talk about the starting point for every Big Data project - getting your...
Lynn Langit

Big Data

Has Big Data Killed the EDW?
Big Data technologies have given us the ability to process massive amounts of data at a low cost,...
Stacia Misner
Implementation and Design on the Parallel Data Warehouse
If you need to design a data warehouse that scales for today and into the future, then you need to...
Jason Strate

Information Delivery

DataViz You Thought You Could NOT Do with SSRS
Despite SQL Server Reporting Services (SSRS) being a very flexible and adaptable tool when it comes...
Jason Thomas

Strategy and Architecture

Panel: Myths, Misunderstandings, and Successes in Data Analytics
Big Data, Business Analytics, Data Analytics, NoSQL, Relational . . . do we even agree on what we...
Karen Lopez, Joseph D'Antoni, Lynn Langit, Stacia Misner

ODBC error 208 (42S02) Invalid object name

Issue

You may get this error even though the table is available in the database.

Solution

Using qualified table name "database.owner.table" will fix this issue.

The base class includes the field 'ReportViewer1', but its type (Microsoft.Reporting.WebForms.ReportViewer) is not compatible with the type of control (Microsoft.Reporting.WebForms.ReportViewer)

This error normally occurs when different versions of Microsoft.ReportViewer.WebForms.dll are referenced in web.config and in controls.

Clean the solution and delete the related dll files and then again rebuild the solution