Server error in '/Reports' application when exporting to excel in SSRS

I was exporting the report data into excel and got an error "Server Error in '/Reports' Application" as below :

Server Error in '/Reports' Application.
--------------------------------------------------------------------------------

Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.

Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".


<!-- Web.Config Configuration File -->


<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>


Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.


<!-- Web.Config Configuration File -->

<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>

The reason was simple, as my report has large number of rows and SSRS 2008 R2 generates word/excel of 'office 97-2003' file format. And in 'office 97-2003' format, excel supports up to 256 columns and 65k odd rows. To fix it I did Paging in SSRS Reports for 50k rows per page. and it worked fine for me.




Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Add Page footer in SSRS Report

In reporting we may require to add Page number, UserID or any other information at footer. So to get it, follow the below steps :

1) In the report Designer View click any where in the rectangular area.

 2) Now from the menu select Report->Add Page Footer. a new area will appear under the Design rectangular area, this is the footer area.

a)



b)




3) Now, Drag and drop two text boxes in the footer area.

4) Right Click the Right TextBox and click on Expression.

5) Double click on the Built in Field "UserID" and press ok.

6) Repeat the same for Left TextBox, and put the  code ' =Globals!PageNumber & "/" & Globals!TotalPages '.

We are done here, and will get the page number following with UserID.



 Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Repeat header rows on each page in SSRS Reports

I wanted to Repeat header rows on each page in SSRS Reports. Since I had a report containing large number of transactions, I required to split the output into multiple excel sheets and with that repeat the header row on each sheet and on every page on Preview. For achieving this, I followed the below steps.

1) At first, checked 'Repeat header rows on each page' and  'Repeat header columns on each page'




2) Select the table and in right to the grouping pane, click the down arrow and then click 'Advanced Mode'.

3) In the row group pane, click the static tablix member and same will be highlighted in the table as well.

4) Now in the property window, for the static member in row group pane, set the following properties:
a) Set KeepTogether = True.
b) Set KeepWithGroup property as per your choice.
     For static row above the group, set 'After'
     For static row below the group, set 'Before'
c) Set RepeatOnNewPage = True

5) That's it. Save the changes and run the report and check the output.




Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Can not open the Dropdown Arrow in SSRS

I am using SQL 2008 R2 report services and faced the problem in opening the Drop-down Arrow in SSRS report on IE browser, though I was opening it on the server it self and had admin rights. Then I googled and found a simple solution by adding report server URL to view compatibility list.

To do so, follow the below steps :

1) Go to the server and open the site on IE.
2) Go to the IE settings and click on Compatibility View Settings :

3) Put 'localhost' under Add this website : and clict Add

4) URL gets added and you are done, close the window.

5) And  Dropdown Arrow is opening now :

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Date Range Validation (Start Date and End Date) in SSRS

      DateRange Validation or in other words Start Date and End Date validation is not a inbuilt validation in SSRS, so we require to implement VB script here. Follow the below steps :


1) Lets say we have a reporting table with four columns whose filters have parameters @StartDate and @EndDate :

2) Now Go to Menu → Report → Report Properties → Code
    And put the function code as below :

Function Script :

Function CheckDateParameters(StartDate as DateTime , EndDate as DateTime) as Boolean 
Dim RetValue As Boolean 
RetValue = "False" 
if(StartDate < EndDate) then 
RetValue = "True" 
else 
RetValue = "False" 
end if 
Return RetValue 
End Function 

3) Add a parameter @ValidateDateRange of Boolean type, make it hidden then go to its Default Values section, check radio button Specify Values and open exepression and put the code as  =Code.CheckDateParameters(Parameters!StartDate.Value, Parameters!EndDate.Value)

4) Add a Text Box and write the error message as "Start Date cannot be greater than End Date !" in its text.

5) Now go to the Tablix Properties of the original table :

6) Go to Visibility Section, check last radio button "Show or Hide on .." and put the expression =IIF(Parameters!ValidateDateRange.Value,True,False) :

7) Similarly go to the Visibility Section of Error Message TextBox and put the expression as

8) So now if we run the report with Start Date greater than End Date then we get the below ouput :

and its done.



Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Paging in SSRS Reports

        Paging in SSRS Reports or Displaying Fixed Number of Rows per Page in SSRS Report does not have any inbuilt property, so it needs some work around.

To achieve  Paging in SSRS Reports, take an example we have a report output as :

which has over 1000 records and we wish to implement paging of 10 records per page.
Follow the below steps :

1)  See the table design for the above report as :

2) Right click and Add a parent Row Group.

3) Then go to the Parent Group Properties and Click on Group Expression.

4) Put the code =CEILING(RowNumber(Nothing)/10) in it and press ok.

5)  On "Page Breaks" options, Check the option "Between each instance of a group".

6) Now delete the Group column with selecting "Delete columns only" option.

7) Save & Run the report and we will get the below output, 10 records per page :

That's it.




Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Merge Cells Vertically in SSRS

          Rarely we need to use Merge Cells vertically in the SSRS reports. Most of the times number of rows remain fixed in such reports. So taking an example below.
         Lets say we wish to Merge Cells vertically to display the data for different banks with Transaction numbers and transaction amount on Daily basis. See below:

      To get the output, we will require to follow the below steps :

1) Drag & Drop a Matirx.







2) Delete Row group and related rows.

3) Delete Group Expression from ColumnGroup :

4) Insert Column Left, Outside Group :

5) Insert row above, inside the group :

6) Add the desired number of rows :

7) Add another column left, Outside Group :

8) Bind the Column Group cells with the desired fields, and put the static row header in adjacent left column :

9) Now in First Column, select the desired cells and right click, you will get  an option of "Merge Cells".

10) Merge the Cells and give the requested Names :

11) Run the report and you will get the below output :

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Check Current Isolation Level

        To check the Current Isolation Level, execute the below script :

Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Transpose a Table in SSRS

        Some times we require to Transpose the table to see the data with more readability. Lets say we have an output as below :



         And we wish to see the data as below :




To achieve it, we need the following steps to do.

1) From the Tools Drag a Table.

2) Delete the Row group with related row :

3)  Delete Additional Columns.

4) Add a Column Group (checked with "Show Detail data").

5)  Insert the required number of rows(In our case its 3).

6) Bind Dataset Field Values to the Cells.

7) Add the Row Header in previous column.

8) Run the report and you will get the below output.







Reference: Govind Badkur(http://sqlserver20.blogspot.com)