locked
Simple MicrosoftReportViewer Report with parameters RRS feed

  • Question

  • User-1880328226 posted

    I want to share with the forum a simple way to create a MicrosoftReportViewer Report with parameters. The steps I am using below are for Visual Studio 2008, but they will most likely work with other versions of Visual Studio. I say simple, but there are a lot of words here. I’m trying to make it simple for those that have never created any type of Report before, which requires more words.

    First, you need to create a dataset file in your website for the report to use. This is mandatory because the design of MicrosoftReportViewer requires a dataset file.  Now, you need to add a datatable to this dataset. To do this right-click on the blank screen, select Add \ DataTable. It will create a new DataTable called DataTable1. Right-click on the header of this new datatable and add the names of the columns (fields) of the database that you are going use in your report. You must include them all. Save the new dataset. 

    Next, create a new Report (.rdlc) file. Simplest way in VS is to right-click on project and Add Report. Do not use Report Wizard. Default name is Report.rdlc. Name is not significant. With the report file open, in the menus at the top, select Report \ Data Sources. It should show the name of the dataset with datatables you created as follows: DataSet1_DataTable1. Add this to the report.

    Next, you will need to create a Web Form (.aspx) to hold the report file. Be sure you select to put the code in a separate file. While in the .aspx file, preferably in the Source mode, from the Toolbox drag a MicrosoftReportViewer object onto the form. This is the best method since VS will include all the necessary registers needed for the object. Once the object is created you will need to add a line that points to the .rdlc file. It should look something like this:

    <rsweb:ReportViewer ID="ReportViewer1" runat="server">

    <LocalReport ReportPath="Report.rdlc"></LocalReport>

    </rsweb:ReportViewer>

    Next go into the .aspx.vb (source) file for this web form. (Note: for this example, I’m not demonstrating how to create a dataset from a SQL table, but the code will utilize these functions. So, if you don’t know how to do this you’ll need to look elsewhere. The SQL code I am using obtains data via a Stored Procedure.) 

    My demonstration will load the data needed for the report directly using the form’s load event. I did not include any error checking to keep the code simpler. Below is the code and I’ll explain each section.

    Imports System.Data

    Imports System.Data.SqlClient

    Imports Microsoft.Reporting.WebForms

    Partial Class Default1

        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

            'This line simply gets the needed variable for the stored procedure sent by a QueryString

            Dim strMyVariable As String = Request.QueryString("myVariable")

            'The following creates the datatable from the Stored Procedure

            Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("myDB").ToString)

            Dim cmd As New SqlCommand("myStoredProcedure", conn)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@myVariable", strMyVariable)

            Dim da As New SqlDataAdapter(cmd)

            Dim ds As New DataSet

            da.Fill(ds, "myDataTable")

            'Here is the key part

            'DataSet1_DataTable1 is the name of the datasource you added to the .rdlc file

            'ds.Tables("myDataTable") is the datatable you created above

            Dim rds As ReportDataSource = New ReportDataSource("DataSet1_DataTable1", ds.Tables("myDataTable"))

            ReportViewer1.LocalReport.DataSources.Add(rds)

            'That's it!

        End Sub

    End Class

    Finally, in VS open the .rdlc file and there should be a pull-out to the right or left called Website Data Sources. At the top should be all the fields in those datatables you created. If you drag one of them to the body, it will automatically create a textbox with something like the following:

    =First(Fields!RecordID.Value)

    This will show the first record of the datatable you created on the report. If you want to show all the records, in the toolbox drag a Table onto the .rdlc file. Then drag a field into the Details area of the Table. It should create a textbox with something like this.

    =Fields!RecordID.Value

    That’s it! From this point it’s just designing the report to look like you want with the Toolbox items. One thing to remember is that you cannot put datatable fields in the Page Header and Page Footer areas of the report. Another neat thing is that if you right-click in the Table one of the selections is Expression. This gives you all sorts of Expressions you can do with the data. For example, if you right-click in the footer of the table, select Expression, go to Common Functions \ Aggregate and double-click Sum, and then go to fields and double-click one of the fields that can be Summed, then close the Expression with a parenthesis, it will sum that field in your report. Many more things you can do.

    Once you are done and save everything, you should be able to browse the .aspx file and the report will come up. The MicrosoftReportViewer control is very unfriendly with its error messages. I have found if I get some weird error message it is usually a typo I made or I tried to do something fancy in the report that it didn’t like (like trying to add a field to the header or footer, trying to use a field that is not in the datatable of the dataset, etc.). That is why it is best to drag and drop things when you can so you get most errors on the design end. 

    Hope this helps somebody.

     

     

    Sunday, January 17, 2010 1:55 PM

Answers

  • User-1734134863 posted

    Hi karensguy,

    Thank you for your contribution to this SSRS forum, I think this solution will help other SSRS lovers.

     

     

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 19, 2010 8:15 PM