Create a report with Reporting services

See the clip

What is Reporting Service?

Reporting service is a free application coming with sqlserver so if you have sql server in your organization then probably you have Reporting service as well. It is a repository for your reports you can create reports by some tools then publish your report to this repository then your applications including windows and web can send request to this repository and get the report in a browser. Since you can create complex report to show different charts and graphs, it is one of the handy tools to use specially in Intranet web applications. Also you can use reporting service for internet applications However, you will be charged for that.

What do you need to know about Reporting services?

1- How to create a report

2- How to publish a report

3- How to see the report in the server

4- How to access a report from other .net applications (Next post)

5- How to create a chart report (there is a sample in clip)

6- How to create a parameterized report (future posts)

7- How to pass parameters to a report from other applications (future posts)

How to create a report?

When you install sqlserver2005 you have a special template will be added to your visual studio which is "Sql server Business intelligence development studio". You can use this template to create a report then as I explained you have to publish your report then other applications can address that report. Open visual studio from file menu select New Project then you can select Business intelligence project. In this list you can find Report server project wizard or Report Server project. Using Wizard help you to speed up implementation of you report.

Let's select Report Server Project. As you see in solution explorer you have two folders one is shared data source and another one is Reports. You need a connection to Database in Reporting service we call that Data source. But if you have multiple reports over one data base you better create one Shared data source to share between them. Because if database changed then you need just change shared data source and everything should be all right. The reports folder is where you create your Reports. I recommend you to use wizard to create the basics of your report then change report to the way you like.

Create a share data source:

Right click on Shared Data sources folder and select Add new data source then you will see this window:

All you need to do provide an alias name for the shared data source and a correct connection string. The connection string implies to which data base shared data source can connect. You may click on Edit then you will see a familiar window to define a connection string. You can use windows or sql server authentication. Then click on ok. You are done. Now you have a shared data source that can be used in different reports in your project.

How to create a report?

All you need right click on Reports and select Add New Report then you will see the wizard window. As I said you better use wizard then customize your report the way you like later on. (I am going to take a report from a table that has suburbs, postcodes, states)

Click on next in next page select the shared data source as you see you can select a custom data source but I do not recommend this. Using shared data source is more maintainable.

In Next step you need to define your query in our case we are retrieving data from a table but you can retrieve data form view as well. If you take a look at the next picture you will see the query builder button when you go to that page in left top corner of window there is a button which is generic query designer then you can add the tables and views you want and define the query with query designer assistance.

In next step you need to define your report type in our case we want Tabular:

In next step you have the ability to define which fields you need to show in report also you can define group by to show details of one product (please see the clip to see a sample of this) right now we just choose the fields we want to have in our report.

Select a style for your Report and click on next you are done

You have three views: Data, Layout and Preview

In the Data view you can see and modify your query also you can add other queries you may use in the report (I will explain how to add another query for this report and why you may need to do that in future posts)

In layout you can change look and feel of your report or add other element please see your toolbox when you are in this view then you will see these elements (I will talk about some of these elements in next posts):

In the Preview tab you can see the result which is your report.

How to publish a report to reporting server

Next step is that you need to publish you report to Reporting Server to do this you need to right click on your project in solution explorer and select properties. Then you need define 4 properties at least:

TargetServerUrl: the reportserver address usually it is the same server as your sqlserver with report server virtual directory see the picture

TargetRootFolder: define in which folder the report should be deployed in your reporting server

TargetDatasourceFolder: define in which folder the data sources should be deployed in your reporting server

OverwriteDataSources: define whether you prefer to overwrite previous deployed data sources.

I highly recommend specify the same folder for TargetRootFolder and TargetDatasourceFolder because if you are getting Reporting services for an internet site from a shared reporting services in internet you will be offered just one folder so you need to have all of them in one folder.

Click on ok again right click on your project select deploy then after a while it will be deployed to the server.

How to check report in your server?

Go to your reporting service through a browser like this: http://yourserver/Reportingserver in my case I have to use http://falcon/Reporingserver then in that page find the folder in my case TestProjectReport then you will see all reports in that folder select your report in my case Report1

Then you will see the result.
The very power full point about Reporting service is that you can export data to different sources like pdf, Excel, Web services and etc.

Please see the clip to get some more information. Also you will learn how to create Chart reports.

See the clip

Using Ajax in Asp.Net

See the Clip

Download the Source Code

Before learning programming with Ajax It is necessary to understand Ajax and when it is worth full to use.

What is Ajax?
Ajax is a library to help you call methods of server side in client (browser) without refreshing the whole page but part of page. Let’s say you have list of countries as soon as user selects a country you need to show cities of that country in another dropdown. Imagine you have different elements in this page. What you need is just updating city dropdown. This is the best case to use Ajax since other part of page will stay in the page and users just feel that city dropdown is updated.

Ajax Help you to call server methods in client side without updating the whole page.

When using Ajax is worth full?
After working with Ajax a lot of programmers decide to use this technology a lot however, this is obviously a big mistake. Ajax should be used when implementing a functionality is completely impossible with javascript or massive data should be loaded to client. Let me give you an example if you need change data of a datagrid based on a dropdownlist it is not worth to write this code with javascript. If you need to just hide a part of data like a panel based on a check box it is not worth full to write this code with ajax.

There is a trade-off between using Ajax and JavaScript. Never use Ajax when you are able to implement the same functionality with JavaScript as long as you are not forced to load massive data to client.

How to create Ajax application?
If you are using visual studio 2005 you may need to download ajax library. If you are using visual studio 2008 you have ajax library. After installing ajax you can create Asp.net Ajax-Enabled Web site from file new Web site. If you take a look to this project you will understand that there are some codes being added in web.config to enable ajax also at least for default page you will see a tag which is ScriptManager.

Ajax Elements:
In each page that you need to use ajax technology you defiantly need a ScriptManger control which has no user interface but it mostly prepare ajax technology for that page. This control will add some library to browser and some javascript. It is important to put this control as the first control in html view in your page. Then you can use an update panel to place controls that are going to be updated between postback. For example let’s say we have a page with different content but a grid should be updated without posting back the whole page so we put that grid inside the Update Panel. If your task is time consuming you can use updateProgress control to show a picture identifying you are loading data. In this post I am going to talk about UpdatePanel. I will examine other controls in next posts.

A sample code:
In this sample I used the same product table was created for other samples of website which is a simple product table I need to show information of this table in a Gridview based on price. So when 200 is defined as price we need to show all products that are less than 200. Take a look to this picture. Then download the code and also see the clip.