SSRS Report in DotNet

Information

Step 1: Create Dataset

Step 2: Create and Bind .rdlc file with Dataset.

Step 3: Add ReportViewer and call your .rdlc file.

So your .aspx file looks like this.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Test SSRS Demo by Jayendrasinh Gohil</title></head>
<body> <form id="form1" runat="server"> <div> <asp:Button ID="btndisplay" runat="server" Text="Display Report" OnClick="btndisplay_Click" /> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" InteractiveDeviceInfos="(Collection)" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="908px" Visible="false"> <LocalReport ReportPath="Report.rdlc"> <DataSources> <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSet1" /> </DataSources> </LocalReport> </rsweb:ReportViewer> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData" TypeName="testreportDataSetTableAdapters.Get_Address_spTableAdapter"></asp:ObjectDataSource> </div> </form></body>
</html>

Step 4: New bind your report upon the button click event.
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Configuration;using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page{string Connectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

        }

    }
    protected void btndisplay_Click(object sender, EventArgs e)
    {
        BindReport("Employee", 14);
    }
    private void BindReport(string p, int p_2)
    {
        SSRSReport report = new SSRSReport();
        SqlParameter[] sqlParams = new SqlParameter[] {
         new SqlParameter("@Add_Source_Id", 25) ,
         new SqlParameter("@Add_Source_Type", "Employee")
      };
        string ReportDataSource = "DataSet1";
        bool bind = report.CreateReport(Connectionstring, "Get_Address_sp", sqlParams, ref ReportViewer1, ReportDataSource);
        if (bind)
        {
            ReportViewer1.Visible = true;
        }
    }

}

I created a class for binding the report, which is very important.

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data;using System.Configuration;using System.Data.SqlClient;using System.Web.UI.WebControls;using Microsoft.Reporting.WebForms;

/// <summary>
/// Summary description for SSRSReport/// </summary>public class SSRSReport{


       public SSRSReport()
       {
              //              // TODO: Add constructor logic here              //       }

    public bool CreateReport(String Connectionstring,string StoreProcedureName ,SqlParameter[] Parameter,ref Microsoft.Reporting.WebForms.ReportViewer ReportViewer,string ReportDataSource)
    {
        bool reportbind = false;
        using (SqlConnection con = new SqlConnection(Connectionstring))
        {
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = StoreProcedureName;
            com.Parameters.AddRange(Parameter);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(ds);
            ReportDataSource datasource = new ReportDataSource(ReportDataSource, ds.Tables[0]);
            if ( ds.Tables[0].Rows.Count > 0)
            {
                ReportViewer.LocalReport.DataSources.Clear();
                ReportViewer.LocalReport.DataSources.Add(datasource);
                reportbind = true;
            }
        }
       return reportbind;
    }

}
If you want to create a SSRS report then just copy and call this.

1 comments

SSRS Report Example

Information

Introduction

SQL Server Reporting services are very good features provided in SQL to build a report of user choice and deploy it to make use of it anywhere as per the requirement. In this article we are going to see on how to create a simple report then build and execute the report. Reports can be exported to multiple formats including delimited text, XML format, PDF format and Microsoft Excel as well.

Let's jump into the process and see the step by step process of how to create and execute an SSRS report.

Steps:

Go to SQL Server Business Intelligence Development Studio and create a new project as shown below:

Now a wizard will / may open based on the existing option selected; if it opens then just skip that window by clicking Next. It will open a new window which shows the basic configuration as shown in the screen below:

Next window will show an option to build a query based on which the result set will be displayed as a report. We can write our own query to fetch the output as shown in the screen below.

Clicking on Next; a window will open with the option to select how the result should display as shown in the screen below:

Clicking on Next will open the window below; if you can see the option here to group the data and show as per our requirement.

Clicking on the Next button will open a window which gives options to design the report based on the templates available as shown in the screen below:

Now when clicking on the button an interesting thing appears, which is nothing but where to deploy the report. We can give as per our requirement

Clicking on Next will open a window as shown in the screen below. Here we can give our customized report name:

Clicking on Finish will open a window as shown in the screen below, which shows the structure of the report as shown below:

Now press F5 to build and execute the process. It will show the result as shown in the figure below:

Conclusion

So in this article we have seen how to create, build and execute a basic report using SQL reporting services.

1 comments

Create SQL SSRS Report with out wizard

Information

Introduction

In this article we are going to see how to create a report without using the wizard that was used in the first article. The idea behind this article is that we will have full control over the creation of report and we can customize it at any level as per our requirement.

You can follow through my article at the below url.

Steps

Let's open BIDS and create a project under Report Server Project as shown in the image shown below:

Now right click on the report on the right hand side and select Add New item Ã Report as shown in the screen below:

Now you will see the report designer window as shown in the screen below.

Here we have 3 tabs as Data, Layout and Preview. The Data tab is used to select what data to show in the report. Clicking on the DatasetÃ New Dataset as shown in the screen below will help us to select the data source.

Now we can write our own query to fetch the details which we can show in the reports as shown in the screen below:

Now we can shift to the next tab which is the Layout tab, where we are going to design the report based on our requirements. Drag and drop the table control from the tool box to the layout screen; then your screen should look similar to the one below:

Now go back to the Dataset tool pane, and select the columns that should appear in which part of the report as shown in the screen below:

We can change the properties of the table report structure by pressing F4 which will open the properties tab. You can see the screen below for the different options available to format or customize your report.

Now press F5 to run the application. It will ask for the Login credentials as shown below. Once we give the credentials it will fetch the report.

Without running the project itself we can check the result by clicking on the 3^rd tab in the project which is nothing but the Preview tab as shown in the screen below:

Conclusion

In this article we have seen on how to create a report in BIDS without using the Wizard and how to customize the report based on our requirement.

1 comments

SQL SSRS Report Deploying Process

Information

Introduction

In this article we are going to see how to deploy a report created and executed using SSRS. Once the report design is completed, we can follow the steps below to deploy the report and use it online. Check for my previous article on how to create a basic report.

Steps

Once the report is created now we need to do some basic configuration as shown in the steps below.

Right click on the project name in the Solution Explorer and then select the Properties from the popup menu. You will see the window as shown in the screen below:

In the options available OverwriteDataSources make it always false since if there is already an available data source it will get overriden and close the existing data source.

Once we are done with this configuration and checking the options now we are in the process of deploying the application. For this go to the Solution Explorer and right click on it. There is an option given as Deploy, click on Deploy as shown in the screen below:

Clicking on the Deploy button will deploy the report and shows the output in the Output window at the bottom as shown in the output below.

Deploying to http://localhost/reportserver

Deploying data source '/Data Sources/AdventureWorksLT'.

Warning : Cannot deploy data source AdventureWorksLT to

the server because it already exists and OverwriteDataSources

is not specified.

Deploying report '/SSRS Sample/ReportDesignerExample'.

Deploy complete -- 0 errors, 1 warnings

== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==

== Deploy: 1 succeeded, 0 failed, 0 skipped ==

After successful deployment of the REPORT we can see the output in the browser window as shown in the screen below:

Note â€“ this is a sample image, since IIS is not deployed in my system I wasn't able to take a screen shot of the report.

So we can navigate to the uri http://localhost/reportserver and check the report online.

Conclusion

So in this article we have seen on how to deploy the report and how to configure the deployment process. Hope this was useful, do let me know your comment on this article.

1 comments

Explain about SSRS Report With Example

Information

SQL Server Reporting Services [SSRS] is Reporting system based on SQL Server. It provides a set of tools & services enabling us to create, manage, and deliver reports for entire organization. It is a Microsoft Product released in year 2000.
SSRS Architecture intentionally not given in the starting of article. Before looking on each component of architecture, I preferred to make practical approach to it. In the end of article Architecture has been explained.
Create SSRS Report
First of all go to MS SQL Server 2005=>SQL Server Business Intelligence Development Studio.

Move to File=>New=>Project=>Report Server Project

After creating new report project, we get two folders in Solution:-
Share Data Sources=>here we set database credentials.
Reports=>here we add report files

SSRS Data Source
Right click on Shared Data Sources folder & add data source. Following window panel will be opened where we need to provide data server details & database name.
To confirm that defined database is successfully connected click on 'Test Connection'.

After successfully adding data source, we can add a report in Report folder. Right click on Report folder & Add-New-Report. Window panel will be opened. Select Report item & provide report name 'Header_Report'.

SSRS Report Design
After adding new report, we see report has 3 sections in different Tabs:

Data-Here we put SQL Query or Procedure to fetch data from database that we have to show on report.
Layout-This is the designing section where we format report by dragging tables, rectangle, lines etc from Toolbox. And Data field on report from Dataset panel.
Preview-This panel shows how the report will display to end user.

All these 3 section circled in below image.

SSRS Toolbar
Before moving to design report just we can briefly go through Report Items available in Toolbox.

Textbox: To add any custom text on report we use textbox.
Line: Drawing line on report.
Table: Creating a table having rows & columns, header & footer. We can format table according to our requirement.
Image: Adding image to report.
Chart: facilitate to add different type of charts to report.
Subreport: We can add a report in another one report. Like having Header & Footer report on a report.

Toolbox items shown in below image:-

Now I dragged 3 textboxes on report & put text 'Dhania Hospital', & 'Health is Wealth', & 'Bhiwani Haryana 127021' respectively.

After previewing report in Preview tab, report will appear as shown below.

After finishing Header_Report, now we are creating new report AdmittedPatientList.

After adding new report, move to Data section of report. Select <New Data Set>. A new window Dataset will be opened. Here choose Command type [store proc or text query] we need to use to fetch data from database. Here in this report stored procedure USP_GET_INPATIENT_REPORT.

After adding dataset, click on Run button to execute command to get data. Define Query Parameters window opened where we need to pass values to procedure parameters[@FROM_DATE,@TO_DATE etc.]

After execution of command, data shown in below panel & Report Datasets occupied with data fields we have in USP_GET_INPATIENT_REPORT procedure.

After adding dataset to report, now we move to design report. Move to Layout section of report & drag a table on it. As we drag a table we get 3 sections in it:-

Header: here we put data that need to be shown header of report. We can have more than one row in header just by clicking on Header row & add new row.
Details: this is the part of table where we drag data fields from dataset panel.
Footer: here we add items we need to show in footer of report.

SSRS Subreport
In this report I am adding subreport item to add Header in report. Sub report Header_Report that we created previously chosen in Subreport property.

Now we are adding 2 more rows in header section of report by just right clicking on left most of header column.

Report designer provide Expression Window to help developer to use different formula, functions, operations. We can directly drag any data field like patient name, address etc from Dataset window or just right click on any row cell & select EXPRESSION option.
EXPRESSION Window has been shown in below image

By default table have 3 columns only so we can add more columns as requirements by right click to header of a column & option to add column in left or right of selected column.
We can merge no of columns to accommodate more space required for a field. For example in our current report we have to merge all columns in header section to put text 'List of in patient from X Date to Y Date'.
New row added in header section to put name of column name like S.No, Patient Name etc. To format text of cell just right click on cell & select PROPERTY. Here in property window we can set font size, font type etc.

Row Formatting: To format a row we need to open property window of a row by just selecting row & right clicking it & go to Property option.
In property window we can set border font, type, back color, text alignment, padding etc.
Now we dragging data fields like patient name, address in detail section of table just below their corresponding headers like patient name address. It is shown in below image.

Now report is ready to use. Move to Preview panel of report & pass required parameters [FROM_DATE, TO_DATE etc] of report.

Publish SSRS Report on Report Server
After creation of report we need to publish it on report server so it could be available to end user.
To publish report on sever we need to set credential of report server. Go to property of Solution explorer as shown below image.

In Property window set 'TargetServerURL' field with Report-Server [i.e. NSARORA] and report virtual folder [i.e. ReportServer$NSARORA].
Field 'TargetReportFolder' contain folder name in report server where published reports saved. In detail it is shown in below image.

After making setting for report server credential now we can deploy reports on server.

As deployment of report starts output window shows the deployment.

0 comments

A to Z

SSRS Report in DotNet

SSRS Report Example

Introduction

Steps:

Conclusion

Create SQL SSRS Report with out wizard

Introduction

Steps

Conclusion

SQL SSRS Report Deploying Process

Introduction

Steps

Conclusion

Explain about SSRS Report With Example

Blog Archive

Search This Blog

Total Pageviews

Labels

Followers

About Me

Popular Posts

Advertisement

A to Z

SSRS Report in DotNet

SSRS Report Example

Introduction

Steps:

Conclusion

Create SQL SSRS Report with out wizard

Introduction

Steps

Conclusion

SQL SSRS Report Deploying Process

Introduction

Steps

Conclusion

Explain about SSRS Report With Example

Blog Archive

Search This Blog

Total Pageviews

Labels

Followers

About Me

Popular Posts

Subscribe To

Advertisement