BLOGS

ByHariharan Rajendran

Avoid Using Dataset Filters in SSRS

If you are Microsoft SQL Server Report Developer, then you have a chance to notice the dataset properties as it is the place where we supply the data subsets to the report.

Let us understand the filters in SSRS first and analyse why dataset filters should be avoided.

We have three different main places to filter the data in SSRS

  1. T-SQL Script
  2. Dataset Filters
  3. Run-time Filter using Parameters

Filter in T-SQL Script

When we understand the business requirements, need to categories what are the filters static and dynamic.

Always use the static filters in T-SQL script. Dynamic filters can be done through parameters but still need to add the filter in T-SQL Script with parameters.

For example,

If you want to filter country values as the US on your country table.

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=’US’

Note: In this case, we are restricting data in database server itself.

Dataset Filters

You can filter in SSRS report dataset.

Go to Dataset -> Filters

RD

Note: In this case, we are taking all the data to report server and filtering the data in the report.

Run-time filter using Parameter

It is a special kind of filter because this will be the requirement from business in most of the cases.

First 2 filters won’t be visible to end users but this filter will be visible and user needs to filter the value while running the report.

This can be achieved by adding the parameter in T-SQL script,

SELECT State, City, Country FROM dbo.COUNTRY

WHERE Country=@Country

RD2

Why should we avoid dataset filters?

The main reason is to improve the performance of the report.

In case 1 (Filter in  T-SQL Script), we are filtering the data in database layer itself. It means we are taking only required data to the reporting layer and showing the result. The performance of the report will be good as it is processing only fewer records.

In case 2 (Dataset filter), we are taking all the records from table database and filtering in the report server. It means report server has to make process and filter the data and show the result. We are giving burden to report server so it will affect the performance.

 

ByHariharan Rajendran

Enable Azure Advisor

Microsoft Azure Team released one of the expected feature in azure.

It is like a personal assistant that helps us to follow best practices, efficient resource usage and optimize the deployments.

Check below to get the Azure Advisor,

Mostly it will be available in left side of the menu,

Azure

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you are not able to identify then enable the favorite,

Azure1

Azure2

ByHariharan Rajendran

Cascading Parameter in SSRS

Cascading parameter means, one parameter values are based on another parameter value.

This article explains this concept in a very simple way with the below scenario.

Pre-Requisites:

  1. Main Dataset with 2 parameters
  2. Dataset for parameter 1 distinct values
  3. Dataset for parameter 2 distinct value with parameter 1 in where clause

Main Dataset:

Select EnglishProductCategoryName as ProductCategoryName, EnglishProductSubcategoryName as ProductSubCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Where EnglishProductCategoryName=@Category and EnglishProductSubcategoryName=@SubCategory

Dataset 2 for Parameter 1 Values:

Select distinct EnglishProductCategoryName as ProductCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Dataset 3 for Parameter 2 values with Parameter 1 in where clause:

Select distinct EnglishProductSubcategoryName as ProductSubCategoryName from [dbo].[DimProductCategory] ProdCat

INNER JOIN [dbo].[DimProductSubcategory] ProdSubCat

on ProdCat.ProductCategoryKey = ProdSubCat.ProductCategoryKey

Where EnglishProductCategoryName=@Category

Assign the available values for both parameters

Run the report.

Initially, it will show the first parameter with drop down values and the second parameter will not show any values.

As per the above scenario, we are passing the first parameter value to the second parameter.

We need to choose a value of the first parameter so that we can see the second parameter values.

Based on the first parameter value, second parameter values are changing.

ByHariharan Rajendran

Query Editor (preview) in Azure Portal

Microsoft Azure added a new feature called “Query Editor (Preview)” for SQL database. It is a portal based query editor.

Now user can execute the SQL query directly in the portal itself instead of using SQL Server Management Studio (SSMS) to access the data.

Find a Query Editor in SQL Database

Query Editor (preview) is available under Tools in SQL Database.

QE1

Authorization Type

At present, it supports 2 authorization types.

  1. SQL Server authentication
  2. Active Directory password authentication

QE2

Steps to run a query

  1. Go to SQL Database in Azure
  2. Click Tools and choose “Query Editor (preview)”.
  3. Authenticate yourself by clicking “Login”. Pass the password of the SQL Server and click “Ok”.
  4. Once you are authenticated successfully, Type the script or import using “Open Query”.
  5. Click “Run”.

QE3

QE4

Open & Save Query

If you have SQL script file on your machine and want to run those scripts in portal Query Editor then you can use “Open query” option to import the .sql file.

In another case, if you want to store the written query in the editor then click “Save query”. SQL script file will be downloaded.

QE5

ByHariharan Rajendran

T-SQL Basics – Day 1

If you are reading this article then you are looking an very easy way to understand the T-SQL Scripts.

Let us learn T-SQL with a scenario, consider you have a sheet with some employee information. Your boss asking questions from that sheet and you need to answer him. In simple, your task is “Read from Sheet”.

Convert the general words into technical terms,

Sheet – Table (Employee) in a Database

Questions – T-SQL Statement

Answer – Output of T-SQL

Read – Select

Read from Sheet – Select from Table

If you are familiar with above terms then you can easily learn T-SQL Statements.

Let’s start,

Boss: What are the information available of an employee?

You: Read all the field name from Sheet

Tab1

T-SQL: SELECT * FROM Employee

Tab17

Note: “*” denotes all the fields from a table.

Boss: Do we have employee Name, Age & Country?

You: Yes

Tab2

T-SQL: SELECT Name, Age, Country FROM Employee

Tab18

Boss: What is the age of Richard? Or what is the country name of Richard?

You: 42 or UK

Tab3

T-SQL: SELECT * FROM Employee Where Name=’Richard’

Tab13

Note: Need to use WHERE keyword when we say specific data.

Boss: Who are all crossed age 40?

You: Read from sheet

Tab4

T-SQL: SELECT * FROM Employee WHERE Age > 40

Tab14

Note: Need to use WHERE keyword when we say specific data.

Boss: Whose names start with letter K?

You: Read from sheet

Tab5

T-SQL: SELECT * FROM Employee WHERE Name LIKE ‘K_%’

Tab15

Note: Need to use WHERE keyword when we say specific data. Use LIKE keyword when you want to play with the letters or pattern in a value. There are few other patterns available for LIKE which will discuss in the next article.

Boss: Convert the values of a Marital Status column into detailed values.

You: Add a new column

Tab6

T-SQL: SELECT *, CASE WHEN [Marital Status] =’M’ THEN ‘Married’ ELSE ‘UnMarried’ End MStatus FROM Employee  

Tab16

Note: Use “CASE WHEN XXXX THEN XXXXX ELSE XXXXX END” Syntax when you want derive something. We can discuss the detailed version above syntax in next article.

 

ByHariharan Rajendran

Limitations of Custom Reports in SQL Server Management Studio

I have discussed Custom Reports in my earlier post, this post highlights the limitation of custom reports in SQL Server Management studio.

Microsoft provided few custom reports for R services. When I use those custom reports, SQL Server Management Studio has stopped working for all the reports.

SSMS_Stopped

Then I started to check the reports in visual studio. When I check the reports, it used some of the expressions which SQL Server Management not support.

Limitations,

  1. The dataset should be embedded in the report.
  2. Expressions – Regular Expression like sum, count will work but report specific expressions will not be supported, it could be an expression to open drill down reports and etc.
  3. Parameters – If the report has a parameter that should have a default value.

Download the modified version of R Services Custom Reports.

Download

ByHariharan Rajendran

SQL Server vNext vs SQL Server – Installation Features

Recently, Microsoft released SQL Server vNext which is a platform that gives us choices of development languages, data-types for on-premises and in the cloud.

It also opens up a channel to bring the power of SQL Server in Linux.

There are many features available in SQL Server vNext, check out Whats’s New in SQL Server vNext.

This post gives you the small comparison on SQL Server and SQL Server vNext in terms of installation.

Screenshot from SQL Server 2016,

SQLServerFeatures

Screenshot from SQL Server vNext,

vNext

As you can see, there is a new update on SQL Server Integration Services.

Integration Services Scale Out gives performance booster for package execution by distributing executions to multiple machines.

New Service account to support SSIS Scale Out Master and Worker

vNext_ServiceAccounts

Scale Out Master Configuration page,

vNext_ScaleOutMaster

Scale Out Worker Configuration page,

vNext_ScaleOutWorker

Start play with latest version of SQL Server vNext.

ByHariharan Rajendran

Cascading Parameter in SSRS – Different Approachs

I saw a query raised by a user in one of the SQL forum. He has explained his query with a scenario.

Scenario: Need to create a simple report with 3 parameters, those parameters are “FromDate”, “ToDate” and “Type”. Two parameters (“FromDate” & “ToDate”) should react when I change the Type parameter value.

The possible values in “Type” field is “Yes” and “No”. If the end user selects “Yes” then “FromDate” and “ToDate” should show one year back from today. i.e. 2016-01-10  to 2017-01-11

If the value is “No” the date should be one month back from today. i.e. 2016-12-10 to 2017-01-11.

By seeing the above requirement, I felt cascading parameter option would be the solution for above requirement but it will not give 100% result because once the value is entered or set on parameter textbox, it won’t be changed until we refresh a page or open the report again. This could be unavoidable.

I thought to document this scenario with possible solutions.

Solution 1:

As usual, create a main query dataset with all the above mentioned three parameters. It could be like below,

Dataset 1

SELECT <column names> FORM <tablename>

WHERE FromDate=@FromDate and ToDate=@ToDate and Type=@Type

The above script in the main dataset will create 3 parameters in parameters folder.

Dataset 2

Create another dataset with following Script,

SELECT

      CASE WHEN Type=’Yes’ then  DATEADD (d,-365,GETDATE()) ELSE DATEADD (d,-30 ,GETDATE()) END as FromDate,

       CASE WHEN Type=’Yes’ then GETDATE() ELSE GETDATE() END as ToDate

  FROM tablename

  Where Type=@Type

Dataset 3

Create another dataset for Type value “Yes” and “No”.

Values of the parameter,

For “Type” parameter set the available values from above dataset3.

For “FromDate” parameter set the default value from dataset 2 and do the same for “ToDate”

Report_Dataset

Run the report, you should see the result as like below.

ReportParameter2

Solution 2:

Follow the same steps as you did with solution 1.

Create Dataset1 & Dataset 3.No need to create dataset 2 as we are going to use an expression in parameter itself.

Values of the parameter,

For “Type” parameter set the available values from above dataset3.

For “FromDate” parameter set the default value with “Specify values” option. Click “Add” and write the below expression.

=IIF(Parameters!Type.Value=”Yes”,DateAdd(“d”,-365,Today), DateAdd(“d”,-30,Today))

For ToDate Parameter,

=IIF(Parameters!Type.Value=”Yes”,Today,Today)

ReportParameter

Report Parameter1