BLOGS

ByHariharan Rajendran

Basic Errors in Stretch Database

This post explains that what are the basic errors that you could encounter during the configuration of stretch database in SQL Server 2016 and how can you overcome those errors.

Check the below error,

Check my article on Technet wiki for a solution for the above issue.

https://social.technet.microsoft.com/wiki/contents/articles/37457.beginner-s-errors-in-stretch-database-sql-server-2016.aspx

ByHariharan Rajendran

Name mismatch (DataSet1) in SQL Server Mobile Report Publisher

This post explains a very simple error message that we may face if any issue with a shared dataset.

For example, take a below scenario

You have a shared dataset deployed to the report manager portal. The name of the dataset is Sales and you need to build a mobile report using Sales shared dataset.

When you use the Sales Shared Dataset from mobile report publisher, you may get an error message if any issue with the query used in Shared dataset or data source issue. The error message usually will say DataSet1 even though we used the proper name for the shared dataset.

It is because the shared dataset by default considering the name ‘DataSet1’ when you create a dataset. It can be identified and modified if you open the dataset file in notepad.

Have you seen this scenario? Share your comments below.

ByHariharan Rajendran

Embed Power BI Report in Web page

This article explains that how can we show your Power BI report to everyone.

We have variety options in app.powerbi.com for the specific report.

The notable options are,

Publish to Web

Embed in SharePoint Online (preview)

Export to PowerPoint (preview)

Let us discuss “Publish to Web” Option

Open your Power BI report and click File option. You can see the options as like below.

Choose “Publish to Web” and click “Create embed Code”

Then click “Publish”, it will create a code for us.

We can share the link to anyone or else can embed the iframe in any web page or in SharePoint.

Check the report,

https://app.powerbi.com/view?r=eyJrIjoiN2UwNzRmYzYtNTAxYy00NTE1LTkxZTAtYzgyZTk1MzFhYWEyIiwidCI6Ijk2ODY3NjIxLTgxNDAtNGRjYS1iZTliLTMyMTkxMmU0NTY2NCIsImMiOjEwfQ%3D%3D

Sachin Tendulkar’s Cricket Data – Embedded Report

 

ByHariharan Rajendran

SQL Query – Network Bandwidth Optimization

This article explains that how can we improve the performance when we access the SQL Query via network.

Scenario: I have taken SQL Server installed on Virtual Machine with 7GB of RAM and accessing the SQL Server database from local SQL Server management studio.

Step 1: Up and Run VM with SQL Server installed.

Step 2: Enable the DNS name of the Virtual Machine.

Step 3: Enable the TCP port with 1433 in portal.

Step 4: Create TCP port inside virtual Machine. Type WF.msc in command prompt. Right click on Inbound Rules and choose New.

Step 5: Make sure SQL Server services are up and running.

Step 6: Enable the SQL Server as mixed mode authentication.

Step 7: Create a new user login and check.

Step 8: Access the database from local SSMS.

Performance Improvement with NitroAccelerator

Step 1:

Install the “NitroAccelerator” in local machine, http://nitrosphere.com/trial/?ref=83095

Step 2: Run the query in local SSMS without Nitro Accelerator.

Step 3: Start the accelerator and test the query performance.

ByHariharan Rajendran

Add Data to Mobile Report Publisher

Recently, I saw a question raised in MSDN forum.

The question was “How to add data into Mobile Report Publisher?”

The answer is very simple, Mobile Report Publisher will use dataset for data.

If there are no datasets deployed to report server then we can’t see anything when using mobile report publisher data tab.

Steps to create dataset and make available for Mobile Report Publisher.

Step 1: Create a shared Dataset

Step 2: Deploy the shared dataset to report server

Step 3: Make sure the deployed shared dataset is deployed successfully and check it in report server as well.

Step 4:  Click the Add data in Mobile Report Publisher.

Step 5: Choose the available dataset from report server.

Data will be added and start build the mobile report

ByHariharan Rajendran

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.

SSRS Error

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.

SSRS

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.

SSRS 2

ByHariharan Rajendran

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———-

SQL1

Copy and paste the result and execute the script.

SQL2

ByHariharan Rajendran

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.

Dataset_SP1

Dataset_SP2