SSRS Error Message – Cannot compare data of types System.Decimal and System.Double
I got a below error message when I execute the report. The error message is very straight forward and it highlights the exact issue.
Basically, this error occurred due to different data types. There are many situations where we get this error so I thought to document this scenario.
Error Message,
An error occurred during local report processing.
An error has occurred during report processing.
The processing of FilterExpression for the dataset ‘Dataset1’ cannot be performed. Cannot compare data of types System.Decimal and System.Double. Please check the data type returned by the FilterExpression.
Error Reason:
In Dataset1, I used a filter property and filtered “Unit Price” greater than 12. When I choose Unit Price column, it automatically picked the data type as float as it has values like xxx.xx in table.
The Original data type of the Unit Price column in the database is different, it is money data type. There is a mismatch of the data type.
Solution:
There are two ways to handle this issue.
1. We can filter the value (Unit Price > 12) in script level and directly pass the script in dataset Instead of using dataset filter properties. We also will get good performance. Check my post on …..
2. If we want to use the dataset filter then we need to convert the unit price column into SSRS supported data types, in this case, float.
convert(float,[UnitPrice]) as UnitPrice
Available data types in dataset filter.
Generate SELECT Statement
This article explains that how to generate SELECT statement with all the table columns.
In the Production environment, most of the tables will have more than 100 columns. If those tables are used in a report with all the columns then it will be difficult to type all the columns.
To ease the process, we can generate the SELECT statement and can use that for any purpose.
Use the below code and pass the table which you want to use in the SELECT statement.
In this example, I used FactInternetSales table.
—–Code——-
Declare @Column varchar(500), @tableName varchar(25)
SET @tableName =’FactInternetSales’
SET @Column = ”
select @Column = @Column + Column_Name + ‘, ‘ from [AdventureWorksDW2014].INFORMATION_SCHEMA.COLUMNS
where table_name=@tableName
select ‘SELECT ‘ + SUBSTRING(@Column, 0, LEN(@Column)) + ‘ From ‘+@tableName as SELECTScript
—–Code———-
Copy and paste the result and execute the script.
Dataset Filter in SSRS
In My previous post, I said: “Avoid using Dataset in SSRS”. If you read that article then probably you may have the following question.
What is the purpose of filters in dataset properties?
Yes, I said to avoid but we have some scenarios where we need to use the dataset filter properties.
Consider a scenario, you are a report developer and you need to develop a report for the given requirement. As part of development, you need to use one of the stored procedures and want to display certain rows as a result in the report.
You don’t have enough access to modify the stored procedure to add the filter condition and no other write access.
In this scenario, you can’t filter the data in script level so you need to use report dataset filter to filter the data.
Pass the stored procedure directly into the dataset and filter the respective column with a value.
Performance is important but not all the cases. Still, we can get good performance, use dataset filter when we extract less number of records from database layer.
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
- T-SQL Script
- Dataset Filters
- 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
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
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.
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,
If you are not able to identify then enable the favorite,
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:
- Main Dataset with 2 parameters
- Dataset for parameter 1 distinct values
- 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.
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.
Authorization Type
At present, it supports 2 authorization types.
- SQL Server authentication
- Active Directory password authentication
Steps to run a query
- Go to SQL Database in Azure
- Click Tools and choose “Query Editor (preview)”.
- Authenticate yourself by clicking “Login”. Pass the password of the SQL Server and click “Ok”.
- Once you are authenticated successfully, Type the script or import using “Open Query”.
- Click “Run”.
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.
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
T-SQL: SELECT * FROM Employee
Note: “*” denotes all the fields from a table.
Boss: Do we have employee Name, Age & Country?
You: Yes
T-SQL: SELECT Name, Age, Country FROM Employee
Boss: What is the age of Richard? Or what is the country name of Richard?
You: 42 or UK
T-SQL: SELECT * FROM Employee Where Name=’Richard’
Note: Need to use WHERE keyword when we say specific data.
Boss: Who are all crossed age 40?
You: Read from sheet
T-SQL: SELECT * FROM Employee WHERE Age > 40
Note: Need to use WHERE keyword when we say specific data.
Boss: Whose names start with letter K?
You: Read from sheet
T-SQL: SELECT * FROM Employee WHERE Name LIKE ‘K_%’
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
T-SQL: SELECT *, CASE WHEN [Marital Status] =’M’ THEN ‘Married’ ELSE ‘UnMarried’ End MStatus FROM Employee
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.