Importing MS Excel data to SQL Server 2008

The Import and Export Wizard

The import and export wizard was available even with SQL 2000 has remained an important tool for exporting from and importing into SQL Server data from many different kinds of data sources. It can also be used for transferring data between non-Microsoft data sources. In this article, an example of transferring an MS Excel spreadsheet data to SQL Server 2008 is described. In any of the transformations it is important to realize that data types used in data sources are not exactly the same and that there are differences to be reckoned with. The basic steps to take are to indicate the source of data and the destination to which it needs to be transferred. In order to match the differences some mappings may be necessary if the source and destination are not both SQL Servers.
The MS Excel file PrincetonTemp.xls used in this example is a simple spread sheet data that shows the temperature variations during a year and the maximum recorded temperature. The data type used for the column ’Month’ is text and of the others are numbers.

Figure 1: PrincetonTemp.xls

Invoke the Import and Export Wizard

Bring up the Import and Export wizard from Start | All Programs | MS Server 2008 | Import and Export Data (32 bit). This pops-up the Welcome Wizard as shown. Make sure you read the explanations provided.

Figure 2: Welcome page of Import and Export Wizard

Choose source of data

Click Next. The default page gets displayed. In the 'Choose a Data Source' page click on the handle along the data source and choose Microsoft Excel file as the data source as shown.

Figure 3: Choosing the source of data

Click Next. The 'Select the source from which to copy data' shows up. Use the Browse...button to bring in the location information of PrincetonTemp.xls to the window as shown. The Excel version displayed by default (Microsoft Excel 97-2003) is proper for the MS Access version used in this article. Keep the 'First row has column names' option checked. Note that the MS Access 2007 is not supported.

Figure 4: Choosing the data file

Choosing the data destination

Click Next. The 'Choose the Destination' page shows up with SQL Server Native Client 10.0 as default and the resident server as Hodentek2\Mysorian. The server is configured for Windows authentication. Accept the defaults. In case your server is configured for SQL Server authentication you need to have the information ready. The database is displaying . Click on the handle and choose a database from the drop-down list. Herein TestNorthwind is chosen. You can choose any database including the tempdb. Note that you can begin to create a new database as well, if you choose to do so by using the New...button.

Figure 5: Choosing SQL Server 2008 as the destination

Copying the table

Click Next. The 'specify the Table Copy or Query' page of the wizard shows up. Since we are transferring only one table, accept the default option, ‘Copy data from one or more tables or views ‘.

Figure 6: Choose option to copy a table

Click Next. Since sheet one has the data place check mark for 'Sheet1$' as shown. Only Sheet1 has data in this XLS file. Modify the destination column to read dbo.PrincetonTemp instead of the default [dbo].[Sheet1$] as shown.

Figure 7: Choosing a sheet from the Workbook

Click Next. In the 'Save and Run Package' page of the wizard accept the defaults shown. You could also save it as a package as well for later use.

Figure 8: Choose ‘run’ immediately option

Click Next. The 'Complete the Wizard' page gets displayed. Check if the information is correct (this is a summary of options you have chosen). If it is not correct you can hit the back button and move back to the pages you visited earlier in the reverse order.

Figure 9: The completed wizard

Click Finish. The program starts running and you should see a progress window displaying 'Performing Operation...' as shown.

When the operation is completed you should see the following window and you can keep a copy of the report as to how the import was executed using the Report drop-down button.

Figure 11: Successful execution of transfer

The import in this case was successful as shown above. If there is an error there should be a hyperlink to the message in the Message column of the above window, presently the message is ’12 rows transferred’. Close the wizard. The transfer is finished.

Verifying the import

Open the Microsoft SQL Server Management Studio and login to display the database engine using your Windows credentials. Expand the databases node and the TestNorthwind database node as shown.

Figure 12: A new table in the database

Data type mismatch and the fix

Also check if the data is brought in correctly as shown by right clicking the dbo.PrincetonTemp table and choose 'Select Top 1000 rows'. You can see that the Month names are all showing 'Null'. The ‘text’ data type in the XLS file became nvarchar type.

Figure 13: Null data in the ‘Month’ column

Modify the default mappings

In order to fix this, you can use either Drop table statement or right click and choose delete to delete the table from the TestNorthwind database. In the Delete Object window click OK. Refresh the Tables node by right clicking the Tables and choosing refresh. Now the imported table is gone.
Repeat the process that you did earlier and when you come to the stage shown in Figure.6 click on the table Edit Mappings...button. The Column Mappings page shows up as in the next figure

Figure 14: Column mappings window

The month column data type for the destination is nvarchar (255). The Source had 'Text' as data type for this column. We need to cast it properly. Click on nvarchar in the 'Type' column and change it to ‘char’ as shown. Click OK. Change destination table name from[dbo].[Sheet1$] to [dbo].[PrincetonTemp] as done previously. Click Next.

Figure 15: Modify mapping

In the 'Save and Run Package' page accept defaults as previously. Click Next. The 'Complete the Wizard' page shows up. Click Finish. You get the wizard announcing 'The execution was successful'. Close the wizard.
Refresh the Tables node of the Northwind database in Management Studio. Now right click the PrincetonTemp and choose to select top 1000 rows as before. You will see that all the data in source is in the destination.

Figure 16: The Source data is imported correctly

Error while updating WCF Service...

There was an error downloading http://localhost:5705/UserService.svc
Unable to connect Remote Server No Connection could be made because the target machine actively refused 127.0.0.1:5705 Metadata contains a reference that cant be Resolved
'http://localhost:5705/UserService.svc' could not connect to 'http://localhost:5705/UserService.svc'
TCP error code 10064:No Connection could be made because the target machine actively refused 127.0.0.1:5705, enable to connect
Remote Server No Connection could be made because the target machine actively refused 127.0.0.1:5705

There is normally 4 reasons for this:

• The service is not running
• The meta data exchange is not enabled
• No mex endpoint is defined
• port is blocked on firewall

Tips on improving performance in Asp.Net

Improve Perfomance in ASP.net

1. Use Foreach loop instead of For loop for String Iteration.

2. Check “Page.IsPostBack”. To avoid repetition code execution.

3. GIF and PNG are similar, but PNG typically produces a lower file size. (True, but some browsers not supporting PNG format)

4. Turn off Session State, if not required.


5. Select the Release mode before making the final Build for your application.
This option is available in the Top Frame just under the Window Menu option. By default, the Mode is Debug

6. Disable ViewState when not required.
EnableViewState="false"

7.Use Caching to improve the performance of your application.

8. Use StringBuilder instead of String class.Strings occupy different memory location in every time of amended where stringbuilder use single memory location

9. Avoid Exceptions: Use If condition (if it is check proper condition)

10. Code optimization: Avoid using code like x = x +1; it is always better to use x+=1.

11. Data Access Techniques: DataReaders provide a fast and efficient method of data retrieval. DataReader is much faster than DataSets as far as performance is concerned

12. Use Repeater control instead of DataGrid , DataList as it is efficient, customizable, and programmable.

13. Use single css file instead of multiple css file.

14. Don't make the member variables public or proteted. try to keep private and use public/protected as properties.

15. Use strString=string.Empty instead of strString=""

16. Use Server.Transfer instead of Response.Redirect.

Format dates in SSRS reports

Displaying dates in different formats in your reports is a very common requirement that one faces while working with Sql Server Reporting Services (SSRS).

Let us see how we can display dates in different formats :

=Format(Fields!myDate.Value, “M/d/yy”) - 2/11/08
=Format(Fields!myDate.Value, “MM/dd/yyyy”) - 02/11/2008
=Format(Fields!myDate.Value, “d-MMMM-yy”) - 11-December-08
=Format(Fields!myDate.Value, “d-MMM-yyyy”) - 11-Dec-2008 =Format(Fields!myDate.Value, “M/d/yyyy H:mm”) - 2/11/2008 13:50
=Format(Fields!myDate.Value, “MMM-dd-yyyy”) - Feb-11-2008

how to get the column names and corresponding datatypes in particular table using SQL Server?

SELECT column_name 'Column Name',data_type 'Data Type',character_maximum_length 'Maximum Length' FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name ='Country'

For ex.:select column_name 'Column Name',data_type 'Data Type',character_maximum_length 'Maximum Length' from INFORMATION_SCHEMA.COLUMNS where table_name='CreativeWork'

Join query from table with multiple foreign keys to same table primary key...

select t1.OfficeName,t2.OfficeName from Contact t3 INNER JOIN Office t1 ON t1.OfficeId=t3.OfficeId INNER JOIN Office t2 ON t2.OfficeId=t3.CoOfficeId

Find third heighest o_id from test table.......

select TOP 1 o_id from test where o_id not in(select distinct TOP 2 o_id from test order by o_id DESC) order by o_id desc

How to Select fifth record in sql?

select TOP 1 * from test where o_id not in(select TOP 4 o_id from test) ;

Find Last Five record from table without sorting in sql.

select * from test where o_id not in (select top ((select count(*) from test) - 5) o_id from test)

RegisterForEventValidation can only be called during Render();

Introduction:

Here I will explain how to solve the problem RegisterForEventValidation can only be called during Render(); during export of gridview to excel or word or csv using asp.net.

Description:

During export a gridview data to excel, word, notepad or csv I got error like

RegisterForEventValidation can only be called during Render();

This error occurs whenever we are trying to render control to response. To solve this problem I have added EnableEventValidation="false" to @Page directive of aspx page that should be just like this

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportGridviewtoExcel.aspx.cs"Inherits="ExportGridviewtoExcel" EnableEventValidation="false"%>

By Setting EnableEventValidation="false" property to @Page directive of aspx page the problem has solved automatically.

I hope it helps to solve your problem.

Happy Coding………

how to resize the textbox height dynamically or how to change or increase the textbox height dynamically based on text using javascript

Introduction:

In this Post I will explain how to  increase the textbox height dynamically based on text writing into text box.

Description:

I have one textbox the requirement is like if any user enters text in texbox that height of textbox needs to be adjusting with text.I have written one JavaScript function to adjust textbox height here one more thing is that we have  to set the TextMode="MultiLine" property for textbox to adjust textbox height

Lets see the aspx Page


<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Automatic Resize TextBox</title>
<script type="text/javascript">
function SettingHeight(txtdesc) {
txtDescription.style.height = txtDescription.scrollHeight + "px";
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:TextBox ID="txtDescription" runat= "server" TextMode="MultiLine" onkeyup="SettingHeight(this);"onkeydown="SettingHeight(this);" />
</form>
</body>
</html>

Demo

Introduction about SSRS Report

SSRS has now become a defracto reporting tool and has become a necessity rather than luxury to become familiarise with it.I have found many peoples who have interest in the BI side, but didnot get any chance to work with because of many reason(may be they are not getting the exposure in their work field, lack of time to spend on the subject, frequent movement of projects etc.).Henceforth, I thought of writing this series of articles (SSRS/SSIS/SSAS) where basically I will talk about those features which I have touched upon as of now in my real time project. I will try to compile this series of articles more of step by step hands on approach so that people can refresh/learn by looking into it.Afterall, one picture is worth a thousand words.

Data Source

For this experiment, we will use the below script to generate and populate the Players table which is named as (tbl_Players)

-- Drop the table if it exists IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')     DROP TABLE tbl_Players GO SET ANSI_NULLS ON GO --Create the table CREATE TABLE tbl_Players (  PlayerID INT IDENTITY,  PlayerName VARCHAR(15),  BelongsTo VARCHAR(15),  MatchPlayed INT,  RunsMade INT,  WicketsTaken INT,  FeePerMatch NUMERIC(16,2) )  --Insert the records INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)  INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)  INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)  INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12) INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890) 

The partial output after running a simple Select query

 Select * from tbl_Players 

is as under

We will also have the below stored procedure created in our database whose script is as under

If Exists (Select * from sys.objects where name = 'usp_SelectRecordsByPlayerName' and type = 'P')     Drop Procedure usp_SelectRecordsByPlayerName Go -- Create the  stored procedure Create Procedure [dbo].[usp_SelectRecordsByPlayerName] ( @PlayerID int ) As Begin  Select    PlayerID   ,PlayerName   , BelongsTo   , MatchPlayed   ,RunsMade   ,WicketsTaken   ,FeePerMatch  From  tbl_Players  Where PlayerId = @PlayerID End 

(A) Creating a SubReport in SSRS

A report within another report is a sub report. That is there will be two reports one the Master and the other the child where the master will invoke the child report. The child report or sub report can accept parameters from the master report and will execute its work. Moreover, it can be executed independently. Let us step into action.

Step 1:We will have two reports. The steps to be followed for both the reports are same only in the case of master report we will execute the below query

SELECT              [PlayerID]              ,[PlayerName]              ,[BelongsTo]        FROM [SSRSExperiment].[dbo].[tbl_Players]   

And for the Subreport we will execute the below query

SELECT        [PlayerName]             ,[MatchPlayed]       ,[RunsMade]       ,[WicketsTaken]       ,[FeePerMatch]   FROM [SSRSExperiment].[dbo].[tbl_Players] WHERE  [BelongsTo]= @CountryName 

Note that, we are passing the @CountryName parameter. So at runtime based on the parameter value passed , the sub report will be generated. Once the reports are created we will have two reports in our project as shown below

Testing the PlayerSubReport alone yields the below result

Step 2:Add a SubReport control in the main/master report.

Step 3:Right Click on the Subreport -> Subreport Properties

Step 4:From the General section of Subreport Properties window, select the subreport name (here it is PlayerSubReport) from the dropdown as shown below

And from the Parameters tab after clicking on the Add button, let us eneter the Parameter name as"CountryName"and the value as " =First(Fields!BelongsTo.Value, "DataSet1")". Once done , click on OK button

Step 5:That's it. Now let us run the report

So our Subreport has been generated.

(B) Creating a DrillDownReport in SSRS

Creating a drill down / Tree view report in SSRs is very simple.Let us see the following steps to do so.

Step 1: Open BIDS and creatre a new Shared Data Source

Step 2: Create a Table type report as shown below (The steps for doing so has been described in Part I series).

Step3: Next add Parent Group for Belongss To field as depicted under

Step 4: From the Tablix Group window, let us choose [BelongsTo] from Group By DropDown and check Add Group Header checkbox, then click OK.

At this point if we the report looks as under in the design view

While running the report gives the below impression in the Preview tab

Step 5: From Row groups, choose [Belongs To] Details and then choose Group Properties

Step 6: From the Group Properties window that opens up, choose Visibility tab. Then select Hide radio button and check the Display can be toggled by this report item checkbox.Then from the drop down that will be enable,select the name of the group (which is BelongsTo1 here) then click on OK

Step 7: Delete the [Belongs To] Details column

Step 8: Now it's all done. Run the application and the output is as under

(C) Working with Expressions and Custom code

In this section we will see how to use expression and custom code in our Report. We will learn these through some examples

Objective

What we are going to do is that, if a player has made more than 500 runs and has taken more than 10 wickets, then we are going to display the row and the particular columns with green color

For this, let us first click on the report design body and choose the report properties

The Report Properties window opens up as shown under

Navigate to the Code tab for writing the below custom code

Public Shared Function SetColor(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String SetColor= "Transparent" If RunsMade   >= 500  AND  WicketsTaken >= 10 Then SetColor= "Green" End IF End Function 

The code is pretty simple to understand. We have declare a function by the name SetColor which accepts two integer variable,the first for the run and the next for the wickets taken. We set the initial value of the Setcolor to "Transparent" . Now if the condition meets , we will set the SetColor to "Green".

Next let us choose the RunsMade column and right click to open the TextBox menu from which we will choose the TextBox Properties

In the TextBox Properties window that appears, click on the Fill option and click the fx Button.

Enter the below expression in the Expression Window

=Code.SetColor(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

Again the argument passing code snippet is very simple. In the first one, we are passing the RunsMade values while in the second we are passing the WicketsTaken values in the SetColor function that we just created.

Click OK button and repeat the same for the Wickets Taken column.Once done, let us run the application and the result is as under

Let us add some more customization to our report. If the above criterion satisfies, we will make the Player Name value as bold and the Belongs To as Italic.

So let us add two more functions in the Custom Code as under.

Function : SetBoldFontWeight

' Function to set the font weight as bold Public Shared Function SetBoldFontWeight(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String SetBoldFontWeight= "Default" If RunsMade   >= 500  AND  WicketsTaken >= 10 Then SetBoldFontWeight= "Bold" End IF End Function 

Function : SetItalicFontWeight

'Function to set the font weight as italic Public Shared Function SetItalicFontWeight(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String SetItalicFontWeight= "Default" If RunsMade   >= 500  AND  WicketsTaken >= 10 Then SetItalicFontWeight= "Italic" End IF End Function 

The Custom Code area will now look as under

Next in the PlayerName column right click to bring the TextBox properties and from the Font option, choose the fn for Bold

In the expression editor enter the below code

=Code.SetBoldFontWeight(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

In the Belongs To column right click to bring the TextBox properties and from the Font option, choose the fn for Italic

In the expression editor enter the below code

=Code.SetItalicFontWeight(Fields!RunsMade.Value,Fields!WicketsTaken.Value).

We are done. Run the report and the result is as under

We will look into one more situation where we will generate alternate row color based on the Player ID column.

For this purpose add the below function in the custom codewindow

Public Shared Function SetAlternateRowColor(ByVal PlayerID As Integer) As String SetAlternateRowColor= "Yellow" If PlayerID Mod 2 = 0 Then SetAlternateRowColor= "Green" End IF End Function 

In this code, we are setting the color for even rows as Green .And for all the columns, let us write the below expression for the BackgroundColor

=Code.SetAlternateRowColor(Fields!PlayerID.Value)

The result is as under

Similarly we can set the alignments, hide rows depending on condition and many more stuffs by using expression and custom code.

(D)Working with Calculated fields

A Calculated field is a field that is derived from another field.

Objective

Suppose we want to twice the match fee for every player who ever has bagged more than 10 wickets and made a score of more than 500 runs. In such a case, that row will be will be green colored and the Calculated column value will be made bold.

Solution

Step 1: As a first step let us write the custom codes

Function: SetColor

Purpose: This function will set the color for the entire row to green if the runs made are more than or equals to 500 and wickets taken are more than or equals to 10.

'Function to set the color  Public Shared Function SetColor(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String SetColor= "Transparent" If RunsMade   >= 500  AND  WicketsTaken >= 10 Then SetColor= "Green" End IF End Function 

Function: SetBoldFontWeight

Purpose: This function will set the Row data to Bold for the Calculated column where the runs made by the Player are more than or equal to 500 and wickets taken are more than or equal to 10.

' Function to set the font weight as bold Public Shared Function SetBoldFontWeight(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer) As String SetBoldFontWeight= "Default" If RunsMade   >= 500  AND  WicketsTaken >= 10 Then SetBoldFontWeight= "Bold" End IF End Function 

Function: DoubleMatchFee

Purpose: Doubles the player match fees if the runs made by the Player are more than or equal to 500 and wickets taken are more than or equal to 10.

'Function to double the match fee Public Shared Function DoubleMatchFee(ByVal RunsMade As Integer,ByVal WicketsTaken As Integer,ByVal OriginalMatchFee As Integer) As Integer DoubleMatchFee= OriginalMatchFee  If RunsMade   >= 500  AND  WicketsTaken >= 10 Then DoubleMatchFee= OriginalMatchFee * 2 End IF End Function 

Step 2: Choose Data Set. Right Click and choose Add Calculated field

The DataSet Properties window opens up. Enter a Calculated field Name and click on the Expression button (fx)

Next Add the below expression in the expression window

=Code.DoubleMatchFee(Fields!RunsMade.Value,Fields!WicketsTaken.Value,Fields!FeePerMatch.Value)

Click OK.

Step 3: Drag and drop the DoubleMatchFee column to the Report Designer.

Right click on the DoubleMatch Fee column and from the text box properties choose Font and let us write the below expression against the Bold Font Weight

=Code.SetBoldFontWeight(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

For all other columns, let us enter the below expression against the Fill color obtained from the Text Box Properties

=Code.SetColor(Fields!RunsMade.Value,Fields!WicketsTaken.Value)

And we are done. Let us run the report and the output is as under

Hope that we are now comfortable to work with calculated filed.

(E) Sorting of Columns

In this section we will look into how to perform sorting of columns in the report. Column sorting is an important feature and it can be done from the database side also. But if the same data source is being use by various other application and each demands the result to be displayed in different order, then it is better to do the sorting in the client applications only as opposed to the database.

Let us look into the below steps as how can we perform the same in our report.

Step 1: Right click on the column header and choose Textbox Properties.

Step 2: Choose Interactive Sort from the Textbox properties dialog and

1. Check the checkbox for Enable interactive sort on this Textbox
2. Choose Player ID field from the Sort by drop down.

And we are done. Let us perform the same for PlayerName field.Now run the report

As can be seen that the sorting has been enabled on two columns and the report is presented in descending order of Player ID.

(F)Custom Paging in SSRS report

This section will give us the way of generating custom paging in our report. We will also learn the use of Global variable in doing so.

Step 1: In the report design screen, right click and from the context menu, choose Add Page Footer

Once the Page Footer is added, we can then add controls to it.

Step 2: Let us add four textboxes from the Report Item toolbox onto the designer as shown under

Step 3:Choose the 2nd textbox and click on the Expression

Step 4:From the expression window that opens, let us write the expression

=Globals!PageNumber

Similarly for the 4th Textbox, let us write the expression as =Globals!TotalPages.

Step 5:Run the report and the output is as under