Sunday, December 31, 2017

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
ReportViewer1
2. Select Web Forms Project
ReportViewer2
3. Run the application by pressing F5
ReportViewer2a
4. Create a new Reports.aspx page with Master Page.
ReportViewer3
5. Open Nuget Package Manager Console from Tools > Nuget Package Manager
ReportViewer4
6. Install the ReportViewerControl for WebForms using Install-Package Microsoft.ReportingServices.ReportViewerControl.WebForms
ReportViewer5
ReportViewer6
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.
ReportViewer7
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.
ReportViewer8
Technorati Tags: ,

Saturday, December 30, 2017

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.

SSRSComments1

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

SSRSComments2

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

SSRSComments4

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

SSRSComments3

Technorati Tags: ,

Friday, December 29, 2017

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).

DownloadMenuBefore

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.

SSRSProperties1

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

DownloadMenuAfter

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

Technorati Tags: ,

Saturday, October 21, 2017

SQL Database Managed Instance on Azure

Azure-Database-OptionsAzure supported Single and Elastic Pool deployment options for provisioning SQL Databases on Cloud. It now supports one more option which will allow organisations to life and shift their on-premises SQL 2005 and above database instances to a fully-managed PaaS, reducing or eliminating the need to re-architect apps and manage them in the cloud. The key difference is this new offering exposes entire SQL Server Instances to customers, instead of databases.

On the Managed Instance, all databases within the instance are located on the same SQL Server instance under the hood are just like on an on-premises SQL Server instance. This guarantees that all instance-scoped functionality will work the same way, such as global temp tables, cross-database queries, SQL Agent, etc. This database placement is kept through automatic failovers, and all server level objects, such as logins or SQL Agent logins, are properly replicated.

The new Azure Database Migration Service (ADMS) is a fully managed Azure Service that enables seamless migrations of SQL Server instances from on-premises to Azure Database Platforms with only a few minutes of downtime.

Technorati Tags: ,

Saturday, July 29, 2017

Graph Database vs. Relational Database?

graphdbGraph Databases are one of the type of NOSQL Databases with CRUD methods that expose a graph model. Graph databases are generally built for use with transactional (OLTP) systems. Graph Databases provide a novel and powerful data modeling technique that makes the data models flexible. They are not a complete replacement for relational models instead they can be used where immediate and significant practical benefit can be achieved.

Graph Databases are schema free and they lack schema oriented Governance mechanisms. When designing Graph Databases, the focus has to be on modeling the structure of the data and their interconnectivity on how different data connect to each other. Graph based data model can become very complex really quickly.

Examples of list of Graph Databases

  • SQL Server 2017
  • Neo4j
  • FlockDB
  • Pregel
  • InfoGrid
  • HyperGraphDB
  • SAP HANA

The key terminologies in a graph database are:

  • Nodes - These are basically Entities
  • Properties - Information about the node or information about the relationship
  • Edges - Relationships between Nodes

A simple example is an Employee Node. An Employee can have a relationship with their Manager, an employee can have relationship with colleagues and relationship with their own subordinates or relationship with employees in other departments. Nodes can have as many relationships as required to describe the data. Relationships can be Unidirectional or Bidirectional and there is no limit on the relationships per node.

When to use a Graph Database vs. Relational Database?graphdb2

a) When multiple relationships need to be described e.g. Social relationships, Networks, Error logging, Sensor data, Logistics data. These are examples where the relationships don't truly exist but needs to be described to understand the data.
b) Using for non-analytical data e.g. Authorisation, User preferences, Non frequent changing variables if stored in a database table.

If you have high performance requirements especially using SQL Server 2017 you need to use functions to parse JSON data and it will be a row by row execution but indexes can be used to improve performance a bit but still recommend to use relational in those scenarios. If you have Validation and Integrity requirements and if you need them in the backend, then relational is the best choice.

SQL Graph Architecture

Technorati Tags: ,

Wednesday, July 12, 2017

Microsoft Azure Stack - Azure On-premises

Microsoft have announced the release of Azure Stack which allows enterprises to run Core Azure Services in their Corporate Data Centers.

Microsoft announced at its Inspire conference today, partners like Dell EMC, HPE and Lenovo will start shipping their integrated systems with Azure Stack in September 2017.

This will help lots of Enterprises who couldn't use Azure services on Cloud due to compliance or regulatory reasons.

More information on Azure Stack

Azure Stack Development Kit

Azure Stack Blog

Azure Stack Ideas for improvements

Nice video on how to bring Azure to your Data Center.

Sunday, July 02, 2017

Interactive Azure Platform Big Picture

Currently I am working on a project which utilises a whole heap of Azure services and it’s really handy to have all links in one place related to the Azure Services. The interactive big picture solves that problem and is available under https://azureplatform.azurewebsites.net/en-us/.

Download PowerPoint version - https://azureplatform.azurewebsites.net/Content/azure-platform-overview.pptx

AzureBigPicture1

Technorati Tags: ,

Monday, May 15, 2017

Power BI Premium

As part of the Data Amp event hosted by Microsoft this month, they made an announcement about Power BI Premium. Currently with SQL Server 2016 Reporting Services, you can upload Power BI Desktop reports to the SSRS Web Portal (Power BI Desktop needs to be installed on consumer devices)

With the January 2017 preview release of SSRS, Microsoft announced the ability to upload Power BI Desktop reports to the SSRS Web Portal along with serving them within the web browser without the need for Power BI Desktop installed on consumer devices.

Power BI Premium introduces the ability to manage Power BI reports on-premises with the included Power BI Report Server. Power BI Report Server includes all the features of SQL Server Reporting Services including the ability to publish and serve Power BI Desktop reports. Power BI Report Server will be generally available late in the second quarter of 2017.

So with the release of Power BI Report Server, SSRS in SQL Server 2017 will support only Paginated and Mobile Reports while Power BI Report Server will support Paginated, Mobile and Power BI Desktop reports. Good news is Power BI Report Server also supports Custom Visuals.

With Power BI Premium, if you want to distribute and embed Power BI Reports in your applications you no longer require to purchase per-user licenses.

Power BI Premium Pricing - https://powerbi.microsoft.com/en-us/pricing/

Power BI Premium Whitepaper - https://aka.ms/pbipremiumwhitepaper

Power BI Premium Calculator - https://powerbi.microsoft.com/en-us/calculator/

Sunday, May 14, 2017

Creating a new team and channel in Microsoft Teams

I have been using Slack as a collaboration platform for projects and have started using Microsoft Teams recently and really impressed with the features. Microsoft Teams is a really handy tool if you are working in projects and have to collaborate with a whole group of people.

Let us see how to create a new project and channel in Microsoft Teams.

a) Log on to Microsoft Teams [https://teams.microsoft.com]

b) From the Microsoft Teams account home page, click on the Create a Team link

TeamCreation1

c) Type the team name and description

TeamCreation3

d) Click on the “…” beside the “My First Team Project” team and click on “Add Channel” to create a new channel.

TeamCreation4

d) Provide a name and description to the channel.

TeamCreation5

e) Now let us create an integration using connectors within Microsoft Teams. In this scenario I would like get the RSS newsfeed for Microsoft Teams to be served in the “MS Teams Blog Feed” channel.

f) Click the “…” beside the “MS Teams Blog Feed” channel and click on “Connectors”. Microsoft Teams provides a whole range of connectors for integration with other SaaS applications.

Connector1

Connector2

g) Click on “RSS” and add the MS Teams Blog URL and frequency to pull the feeds from the blog.

Connector3

h) Click on “Save” and you can notice a connector configured for the channel.

Connector4

i) Now you can see the feed items listed under the MS Teams Blog Feed. Now this can be viewed by your team members who have permissions to the team and channel. Really useful to share important blog feeds with the rest of the team related to your project.

Friday, April 14, 2017

Creating a .NET Core project using Command-Line Interface Tools

I love IDEs but normally you don’t get to learn a lot of things about internals if you use the IDE. How about creating a .NET project using notepad? Sounds interesting….

Yes, let’s create our first .NET Core project using Command-Line Interface tools without any IDE.

Open the Command Prompt and navigate to the folder path where you would like to create the new .NET Core project.

Create a new folder named “NETCore”.

CmdLine1

Create a new folder named “FirstCoreApp” and navigate to the folder

CmdLine2

Type “dotnet new”

CmdLine3

You can see the C# project got created successfully with 2 files. Program.cs and Project.json Note: Project.json will be seen only if you have .NET Core Tools preview 2 and below. From .NET Core Tools preview 3 onwards you will see FirstCoreApp.csproj file.

CmdLine4

Program.cs file contents will be similar to the contents when creating a console application using the VS IDE

You can build the application at command line using the command “dotnet restore”

CmdLine6

You can then run the application using the command “dotnet run”

CmdLine7

Now we have successfully created a .NET Core application just using Command Line Tools without any IDE.

Technorati Tags: ,

Sunday, April 09, 2017

Create KPIs using SSRS 2016

Pretty much every organisation would love to store KPIs (Key Performance Indicator) in a centralised portal and manage access. SSRS 2016 allows user to create KPIs and track progress against those KPIs through the new Web Portal. Visualisations can also be set for those KPIs.

In SSRS 2016 when you create KPIs you can either set values manually or as a Dataset field. Let us see how to do both the options.

Set Manual KPIs

a) Navigate to the SSRS 2016 Web Portal and click New > KPI

KPI1

b) Type KPI name as Sales 2017 KPI, Description of the KPI, data format for the KPI, select value as Set Manually, Type 200 as value, select goal as Set Manually, Type 1500 as Enter goal, select status as Set Manually, type –1 as status and select the visualisation you like and click “Create”

KPI2

c) The new KPI will be displayed as shown below.

KPI3

Set KPIs using Dataset

a) Create a new table and load data using the script below on an existing database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KPIs](
    [KPI_Name] [varchar](100) NOT NULL,
    [Target] [int] NOT NULL,
    [Progress] [int] NOT NULL,
    [Status] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT [dbo].[KPIs] ([KPI_Name], [Target], [Progress], [Status]) VALUES (N'Sales KPI', 1500, 200, -1)
GO

b) Create a new dataset using SQL Server 2016 Report Builder. Note: Make sure to create the connection first in the SSRS Web Portal.

KPI4

KPI5

Select “connKPI” and click Open

KPI7

b) Click on “Create” button

KPI8

c) Expand “Tables” tree and select “KPI” table and click the save button on the menu bar and provide the name “KPI” as dataset name.

KPI10

d) Now your dataset has been successfully created. Now navigate to your SSRS portal. URL format: https:// (use it only if you have configured SSRS for HTTPS) or http://<servername>/Reports and you can see the the dataset named “KPI”

KPI11

e) Now follow the same steps as what you followed for creating a manual KPI instead of set manually select “dataset field” as shown below.

KPI12

Monday, April 03, 2017

Monitoring Microsoft Flow runs

Once a flow has been created how do we know that it successfully ran or failed or it is still running. You can monitor the flow runs by clicking the icon as shown below.

AddtoTwitterFlow16

You can see all runs including success, failure, running and cancelled along with checks (no new data) and failed checks.

AddtoTwitterFlow17

Technorati Tags: ,

Sharing Flow with Teams using Team Flows

When we create a flow using Microsoft Flow, the person who created the flow only will be able to maintain it. Team Flows will allow the person who created the flow to assign additional owners to maintain the flow. Team Flows is currently in “Preview” only.

a) Click on “Invite another owner” icon beside the “Blog post notification to Twitter” as shown below.

AddtoTwitterFlow14

b) Add another owner who is an user as part of your office 365 subscription.

AddtoTwitterFlow15

Note: Adding another owner will allow them to edit, update and delete the flow. They can also add additional owners to this flow along with viewing flow history. Also everyone listed as owners will be able to access all embedded connections and can use them in that flow only.