SSRS: The data source DataSource1 cannot be found in SSRS

Details

Written by Ranjeet

You might be familiar with error. However when I saw this below error when was refreshing the reports, I was little surprised. Thought there must be something wrong with report after redeploying.

An error has occurred during report processing. (rsProcessingAborted). The data source 'dsNewHires' cannot be found. (rsDataSourceNotFound)

When you see this error simply refresh your reports by moving backup to report folder and reopening the reports instead of just blindly checking your report setting. I hope this small article will also save your time sometimes. 

0 Comments

SSRS: how to add report description in SSRS 2012 report server display

Details

Written by Ranjeet

While browsing the reports on report server report folder. User often ask that there is no any description. Can you please add description? Same thing is being asked to me. To fulfill this request I went to visual studio and tried to find the option in report by right clicking on report and selecting properties options. But I couldn’t find it. See below. There is only two things to modify, File name and its physical path. Then question is how to add description which can display in report server.

Register to read more...

0 Comments

SSRS: Cannot deploy data source DataSource1 to the server because it already exists

Details

Written by Ranjeet

It is very often when we see below warnings when we try to re deploy some reports which contains data source. Although I reploy multiple times but didn’t went through this error. I started getting this when did some changes in Data Source folder on report server.

Warning               1 Cannot deploy data source DataSource1 to the server because it already exists and OverwriteDataSources is not specified. 

Solution: There is a property name OverwriteDataSource. If you make it true this warning will go away. I did the same and it got resolved.  

Below is a snapshot of the option available in report properties. Just right click on the parent folder of the project and click properties.  

0 Comments

SSRS: Expression to subtract 7 days from today() in SSRS

Details

Written by Ranjeet

It is very common in every programming language to work with date functions. Recently I was filtering some report in SSRS and has to pass date parameter. When passed the parameters and tried to subtract 7 days from today. I needed to remember the syntax. Although help provided by Microsoft while writing expression is enough. I am just writing down here the build expression I used to subtract 7 days from today.

=DateAdd("d",-7,Today()) 

or

=DateAdd(DateInterval.Day,-1,Today())

Below is the output in the report. 

Register to read more...

0 Comments

SSRS The execution failed for the shared data set DataSet01 (rsDataSetExecutionError) in SSRS

Details

Written by Ranjeet

Recently I created few reports in SSRS. When I deployed it and published for user to view. Users started complaining sending below errors.

Error:

An error has occurred during report processing. (rsProcessingAborted) The execution failed for the shared data set 'DataSet01'. (rsDataSetExecutionError) Cannot create a connection to data source ' Data source for shared dataset'. (rsErrorOpeningConnection)

Resolution:

Initially I tried to add the users in report folder giving the access to browse and Content manager. But it didn’t work. Then did some silly like going in report builder and changing the properties of data source. After doing some research finally I reached to solution. Please follow below steps to give report view permission to end users.

• Open reports by browsing “http://servername/Reports/Pages/Folder.aspx”
• Open Data Sources Folder

  Register to read more...

  0 Comments

SSRS: Rownumber cannot be used in sort expressions while creating group in SSRS

Details

Written by Ranjeet

While creating group or say while creating row group to show the table rows in group which contains 10 rows per group. I encountered below error.

An error occurred during local report processing. The definition of the report '/ReportName' is invalid. A sort expression for the tablix 'Tablix1' uses the function RowNumber. Rownumber cannot be used in sort expressions.

It happens because SSRS use automatic sort. So, we need to remove the sort option from group. To resolve this issue, I followed below steps.

• Right click on group
• Choose group properties
• Go to the sorting tab
• Then Choose sort by option and then
• Delete

It will take the error out and you get the resolution. 

0 Comments

SSRS: How to keep row header visible while scrolling report down in SSRS

Details

Written by Ranjeet

While browsing SSRS reports. It is very difficult to keep track of which column the data we are viewing in page while scrolling it down. In this article I will let you know how to get this done. So, that each page of the report repeats column header while scrolling it down.

• Open the report in design mode
• Click on any of the column header
• In the below window, click on "Column groups" and select “Advance Mode” option
• Then on left side panel, click on "Row groups"  and click on Static
• Press F4 or go to View-> properties windows
• Change value of “FixedData” to True

Once this is done, change the background color to while so that it will better visible. 

0 Comments

SSRS: How to display row header on each page of report in SSRS

Details

Written by Ranjeet

While browsing SSRS reports. It is very difficult to keep track of which column the data we are viewing in next page if column header is repeating at each page. In this article I will let you know how to get this done. So, that each page of the report repeats column header.

• Open the report in design mode
• Click on any of the column header
• In the below window, click on "Column groups" and select “Advance Mode” option
• Then on left side panel, click on "Row groups"  and click on Static
• Press F4 or go to View-> properties windows
• Change value of RepeatOnNewPage to True

0 Comments

SSRS: Grant sufficient permissions have to Windows User Account Control (UAC) in SSRS

Details

Written by Ranjeet

I was working on creating new SSRS report and deploying it. When I tried to open the report server and server manager. I encountered below error.

User 'DomainName\UserName' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

OR

The permissions granted to user 'xx\xx' are insufficient for performing this operation

To troubleshoot this I tried to do a lot arounds but it couldn’t help. Then by doing some R and D. I got very easy solution. Just follow below steps

• Login to the server where SSRS is installed
• Open http://<servername:80>/reposts
• Click on Home Folder
• Click on Folder Settings
• Add your domain account here by
• Click on New Role Assignment
• Enter your domain account name in “Group or Username”
• Check Content Manager
• Click Ok 

Just follow above steps. 

0 Comments

TABLE: Query to find minimum maximum and average row size in each table in SQL server

Details

Written by Ranjeet

I was working on data analysis for a project. I required to get minimum, maximum and average row size in each table. Firstly I tried to query many system tables and then DMVs but I got very simple way by using DBCC SHOWCONTIG. Below is the example. 

Query to get result for all the table

DBCC SHOWCONTIG WITH TABLERESULTS

Query to get result for a specific table

DBCC SHOWCONTIG([dbo.<TableName>]) with TABLERESULTS

Register to read more...

0 Comments