[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1637
  • Last Modified:

How pass a parm from vb asp.net to SSRS report

I have written a web site application in vb asp.net 4.0 that runs on our company't intranet.  Part of the application displays a report when the user clicks a start button.  That all works fine.

Now I want to pass a date parameter from the asp.net page that contains the Start button to the page that contains the MS Report.Viewer control that displays the report.

Here is the vb "code behind" that redirects to the Report.Viewer page:

   mSales_Date_Parm = txtSummary_ManualDate.Text
   Response.Redirect("rptStoresFlashSummary.aspx")

Attached is the code for rptStoresFlashSummary.aspx.  How do I pass the parm (mSales_Date_Parm) on the rptStoresFlashSummary.aspx page to the SSRS report?

Thank you for the help.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="rptStoresFlashSummary.aspx.vb" Inherits="rptStoresFlashSummary" %>

<%@ 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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>

        <rsweb:ReportViewer ID="StoresFlashSummary" runat="server" Font-Names="Verdana" 
            Font-Size="8pt" InteractiveDeviceInfos="(Collection)" 
            WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" 
            ShowCredentialPrompts="False" ShowParameterPrompts="False" Width="1000px" 
            Height="859px">
            <LocalReport ReportPath="Stores_Flash_Summary.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" 
                        Name="Retail_Flash_Detail_DataSet" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>

        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
            SelectMethod="GetData" 
            TypeName="Retail_Flash_Detail_DataSetTableAdapters.Retail_Flash_DetailTableAdapter">
        </asp:ObjectDataSource>

    </div>
    </form>
</body>
</html>

Open in new window

0
EYoung
Asked:
EYoung
  • 15
  • 7
  • 5
  • +1
1 Solution
 
TonyRebaCommented:
You could go several different ways, but in this case I would suggest using a Session Variable and store that date value , then just called that variable in ur other page .
0
 
radcaesarCommented:
ReportParameter myParam = new ReportParameter("ParamName", "ParamValue");
myReport.SetParameters(new ReportParameter[] { myParam });

Its C#, pls convert it to vb.net
0
 
TonyRebaCommented:
Something like
Session("mSales_Date_Parm") = txtSummary_ManualDate.Text
in your page1


and

><p>date: <%=Session["mSales_Date_Parm"]%></p>

in your page2
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TonyRebaCommented:
Also you can try the approach given by radcaesar by using:

Dim myParam As New ReportParameter("ParamName", "ParamValue")
myReport.SetParameters(New ReportParameter() {myParam})

Hope it helps!
0
 
EYoungAuthor Commented:
OK, I added:  Session("mSales_Date_Parm") = txtSummary_ManualDate.Text to my Page1.

Now I don't understand how to modify my Page2 to pass the parm to the report.  Can you modify this asp.net code to show me?

        <rsweb:ReportViewer ID="StoresFlashSummary" runat="server" Font-Names="Verdana"
            Font-Size="8pt" InteractiveDeviceInfos="(Collection)"
            WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt"
            ShowCredentialPrompts="False" ShowParameterPrompts="False" Width="1000px"
            Height="859px">
            <LocalReport ReportPath="Stores_Flash_Summary.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
                        Name="Retail_Flash_Detail_DataSet" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
0
 
TonyRebaCommented:
Where do you need to get the passed value>?
0
 
EYoungAuthor Commented:
The parm (mSales_Date_Parm) needs to be passed to the SSRS report (Stores_Flash_Summary.rdlc).  The mSales_Date_Parm will be displayed in the header of the report.
0
 
TonyRebaCommented:
use

<%=Session["mSales_Date_Parm"]%> to output this value

or you can create a label and set the value to it as:
mylabel.text = Session("mSales_Date_Parm")
0
 
EYoungAuthor Commented:
Where do I put this line in my code?

<%=Session["mSales_Date_Parm"]%>
0
 
EYoungAuthor Commented:
Here are some basic questions:

1.  Once I set the session variable "mSales_Date_Parm" to the correct date in the calling page, is that session variable visible and available in the SSRS report?

2.  Assuming that is true, I set the text box expression in the SSRS report header as follows, but it generated an error saying that the word "Session" in invalid.

="Sales Through:  " & Session("mSales_Date_Parm")
0
 
EYoungAuthor Commented:
Here is the actual error message generated by the .RDLC:

Error      1      The Value expression for the textrun ‘Textbox6.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] 'Session' is not declared. It may be inaccessible due to its protection level.      C:\Program Files\Microsoft Visual Studio 10.0\TTA_Applications\tta-intranet\Stores_Flash_Summary.rdlc      1      
0
 
TonyRebaCommented:
You need then to set up the textbox value to:

Session("mSales_Date_Parm")

in the code behind of the page .
0
 
EYoungAuthor Commented:
Sorry TonyReba, but I just don't follow you.

The textbox I want to display is in the SSRS report not on my web page form.

You have suggested I pass the parm in a session variable, but I don't think session variables are passed to SSRS reports.

Here is the basic question:

How do I pass a parm (mSales_Date_Parm) from my web site page to an SSRS report?  I don't know how to pass a parm in a web site application developed in asp.net to an SSRS report.  I know how to do it in a WinForm application but not a WebForm application.
0
 
TonyRebaCommented:
Oh
So then use  this

Dim myParam As New ReportParameter("ParamName", "ParamValue")
myReport.SetParameters(New ReportParameter() {myParam})
Or

If the Report server is directly accessible, you can pass parameters in the Querystring if you are accessing the repoort with a URL and redirect :
http://MyServer/ReportServer/?MyReport&rs:Command=Render&Param1=54321&Param2=product
You can add output formatting by adding the following on the end of the URL:
&rs:Format=Excel
or
&rs:Format=PDF


Sample :
Protected Sub Button1_Click(sender As Object, e As EventArgs)

      Dim rp0 As [String] = begintime.tostring()
      Dim rp1 As [String] = Endtime.tostring()
      Response.Redirect("http://vyof1886382/ReportServer_YOUHOO?/DemoTest/SteppedLayoutReport&rs:Command=Renderrc:&Category=" + rp0 + "&SubCategory=" + rp1)
End Sub
0
 
EYoungAuthor Commented:
This is a local report not a report server.

0
 
CodeCruiserCommented:
Try

StoresFlashSummary.LocalReport.SetParameter
0
 
EYoungAuthor Commented:
CodeCruiser - How do I use that line of code?  I assume it goes in the vb code behind file?

Attached is the code behind section that calls the report.

Thanks
Protected Sub btnSummary_Start_Click(sender As Object, e As System.EventArgs) Handles btnSummary_Start.Click
        If IsPostBack Then

            'First, prepare the Retail_Flash_Detail table 
            Dim targetDir As String
            targetDir = ("\\tta-cwdw\C$\Program Files\Apps")
            Dim p As New Process
            p.StartInfo.WorkingDirectory = targetDir
            p.StartInfo.FileName = "Retail_Flash_Detail.exe"
            p.StartInfo.Arguments = txtSummary_ManualDate.Text
            p.StartInfo.CreateNoWindow = False
            p.Start()
            p.WaitForExit()

            'Second, display the report
            Session("mSales_Date_Parm") = txtSummary_ManualDate.Text
            Response.Redirect("rptStoresFlashSummary.aspx")
        End If
    End Sub

Open in new window

0
 
CodeCruiserCommented:
That will go into the rptStoresFlashSummary.aspx page's code behind. Show us the code from that page.
0
 
EYoungAuthor Commented:
Sorry for the delay in looking into this.  I will work on it today or tomorrow.  Putting out some fires at work...
0
 
EYoungAuthor Commented:
There is no code in the rptStores FlashSummary.aspx.vb page.  Here is all that is there:

Partial Class rptStoresFlashSummary
    Inherits System.Web.UI.Page
End Class

Here is the code for the  rptStoresFlashSummary.aspx page:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="rptStoresFlashSummary.aspx.vb" Inherits="rptStoresFlashSummary" %>
<%@ 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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>    
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <rsweb:ReportViewer ID="StoresFlashSummary" runat="server" Font-Names="Verdana"
            Font-Size="8pt" InteractiveDeviceInfos="(Collection)"
            WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt"
            ShowCredentialPrompts="False" ShowParameterPrompts="False" Width="1000px"
            Height="859px">
            <LocalReport ReportPath="Stores_Flash_Summary.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1"
                        Name="Retail_Flash_Detail_DataSet" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            SelectMethod="GetData"
            TypeName="Retail_Flash_Detail_DataSetTableAdapters.Retail_Flash_DetailTableAdapter">
        </asp:ObjectDataSource>
    </div>
    </form>
</body>
</html>

Also attached is the code for Retail_Reports.aspx.vb that redirects focus to the above page.  See this section:

"Protected Sub btnSummary_Start_Click..."

Thank you for the help.
Imports System.Data
Imports System.IO
Imports System.Data.SqlClient
Imports System.DirectoryServices.AccountManagement
Imports System.Windows.Forms
Imports System.Windows
Imports System.Web.SessionState.SessionStateItemCollection
Imports System.Diagnostics

Public NotInheritable Class SessionStateItemCollection
    Inherits NameObjectCollectionBase
    Implements ICollection, IEnumerable
End Class

Partial Class _Default
    Inherits System.Web.UI.Page

    Public mKey As String
    Public mEmailAddress As String
    Public mSelectString As DataSourceSelectArguments

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            pnlSummary_Automatic.Visible = True
            srGet_Summary_RunDailyAt()
            txtSummary_ManualDate.Text = FormatDateTime(DateAdd("d", -1, Today), DateFormat.ShortDate)

            pnlDetail_Automatic.Visible = True
            srGet_Detail_RunDailyAt()
            txtDetail_ManualDate.Text = FormatDateTime(DateAdd("d", -1, Today), DateFormat.ShortDate)

            Session("mKey") = ""
        Else
            btnSummary_Delete.Attributes.Add("onclick", "return confirm('Are you sure you want to DELETE this summary record?');")
            btnDetail_Delete.Attributes.Add("onclick", "return confirm('Are you sure you want to DELETE this detail record?');")
        End If
    End Sub

    Protected Sub rdlSummary_AutoOrManual_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles rdlSummary_AutoOrManual.SelectedIndexChanged
        If rdlSummary_AutoOrManual.Items(0).Selected = True Then
            pnlSummary_Automatic.Visible = True
            pnlSummary_Manual.Visible = False
        ElseIf rdlSummary_AutoOrManual.Items(1).Selected = True Then
            pnlSummary_Automatic.Visible = False
            pnlSummary_Manual.Visible = True
        Else
            pnlSummary_Automatic.Visible = False
            pnlSummary_Manual.Visible = False
        End If
    End Sub

    Protected Sub srGet_Summary_RunDailyAt()
        Try                     'Select existing daily run time for Summary report
            Dim sqlConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString_CWDW_Retail_NoProvider").ConnectionString
            Dim sqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(sqlConnectionString)
            Dim sqlCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT RunDailyAt FROM ReportRunTimes WHERE ReportName = 'Stores_Flash_Summary'", sqlConnection)
            sqlConnection.Open()
            Dim dr As SqlClient.SqlDataReader
            dr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.Read() Then
                txtSummary_AutoRunTime.Text = LCase(Format(dr.Item("RunDailyAt"), "h:mm tt"))
            Else
                txtSummary_AutoRunTime.Text = ""
            End If
            dr.Close()
        Catch
        Finally
        End Try
    End Sub

    Protected Sub btnSummary_Save_Click(sender As Object, e As System.EventArgs) Handles btnSummary_Save.Click
        If IsPostBack Then
            Try
                DataSource_CWDW_Retail_Summary.UpdateCommand = "UPDATE ReportRunTimes SET " _
                    & " RunDailyAt = '" & txtSummary_AutoRunTime.Text & "'" _
                    & " WHERE ReportName = 'Stores_Flash_Summary'"
                DataSource_CWDW_Retail_Summary.Update()
                Label8.Visible = False
            Catch ex As Exception
                Label8.Text = "Invalid Time.  Setting not saved."
                Label8.Visible = True
            End Try
        End If
    End Sub

    Protected Sub btnSummary_Start_Click(sender As Object, e As System.EventArgs) Handles btnSummary_Start.Click
        If IsPostBack Then
            'First, prepare the Retail_Flash_Detail table 
            Dim targetDir As String
            Dim p As New Process
            targetDir = ("\\tta-cwdw\C$\Program Files\Apps")
            p.StartInfo.WorkingDirectory = targetDir
            p.StartInfo.FileName = "Retail_Flash_Detail.exe"
            p.StartInfo.Arguments = txtSummary_ManualDate.Text
            p.StartInfo.CreateNoWindow = False
            p.Start()
            p.WaitForExit()

            'Second, display the report
            Session("mSales_Date_Parm") = txtSummary_ManualDate.Text
            Response.Redirect("rptStoresFlashSummary.aspx")
        End If
    End Sub

    Protected Sub rdlSummary_All_or_One_Reciptient_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles rdlSummary_All_or_One_Reciptient.SelectedIndexChanged
        'Handle event
    End Sub

    Protected Sub gridStoresRetailSummary_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles gridSummary_StoresRetail.SelectedIndexChanged
        'Session("mKey") = Trim(gridStoresRetailSummary.SelectedRow.Cells(1).Text)
        txtSummary_EmailAddress.Text = Trim(gridSummary_StoresRetail.SelectedRow.Cells(3).Text)

        If UCase(gridSummary_StoresRetail.SelectedRow.Cells(4).Text) = "Y" Then
            chkSummary_Sun.Checked = True
        Else
            chkSummary_Sun.Checked = False
        End If
        If UCase(gridSummary_StoresRetail.SelectedRow.Cells(5).Text) = "Y" Then
            chkSummary_Mon.Checked = True
        Else
            chkSummary_Mon.Checked = False
        End If
        If UCase(gridSummary_StoresRetail.SelectedRow.Cells(6).Text) = "Y" Then
            chkSummary_Tue.Checked = True
        Else
            chkSummary_Tue.Checked = False
        End If
        If UCase(gridSummary_StoresRetail.SelectedRow.Cells(7).Text) = "Y" Then
            chkSummary_Wed.Checked = True
        Else
            chkSummary_Wed.Checked = False
        End If
        If UCase(gridSummary_StoresRetail.SelectedRow.Cells(8).Text) = "Y" Then
            chkSummary_Thu.Checked = True
        Else
            chkSummary_Thu.Checked = False
        End If
        If UCase(gridSummary_StoresRetail.SelectedRow.Cells(9).Text) = "Y" Then
            chkSummary_Fri.Checked = True
        Else
            chkSummary_Fri.Checked = False
        End If
        If UCase(gridSummary_StoresRetail.SelectedRow.Cells(10).Text) = "Y" Then
            chkSummary_Sat.Checked = True
        Else
            chkSummary_Sat.Checked = False
        End If
    End Sub

    Protected Sub btnSummary_Apply_Click(sender As Object, e As System.EventArgs) Handles btnSummary_Apply.Click
        mEmailAddress = Trim(Left(Trim(txtSummary_EmailAddress.Text), 60))
        mKey = "Stores_Flash_Summary-" & mEmailAddress

        Dim mSun As String = ""
        Dim mMon As String = ""
        Dim mTue As String = ""
        Dim mWed As String = ""
        Dim mThu As String = ""
        Dim mFri As String = ""
        Dim mSat As String = ""

        If chkSummary_Sun.Checked Then
            mSun = "Y"
        End If
        If chkSummary_Mon.Checked Then
            mMon = "Y"
        End If
        If chkSummary_Tue.Checked Then
            mTue = "Y"
        End If
        If chkSummary_Wed.Checked Then
            mWed = "Y"
        End If
        If chkSummary_Thu.Checked Then
            mThu = "Y"
        End If
        If chkSummary_Fri.Checked Then
            mFri = "Y"
        End If
        If chkSummary_Sat.Checked Then
            mSat = "Y"
        End If

        Try                 'Insert new record
            DataSource_CWDW_Retail_Summary.InsertCommand = "INSERT INTO ReportRecipients" _
                & " (mKey, ReportName, RecipientEmail, Sunday, Monday, Tuesday," _
                & " Wednesday, Thursday, Friday, Saturday)" _
                & " VALUES('" & mKey & "', 'Stores_Flash_Summary'" & ", '" _
                & mEmailAddress & "', '" _
                & mSun & "', '" & mMon & "', '" & mTue & "', '" & mWed & "', '" & mThu & "', '" & mFri & "', '" & mSat & "')"
            DataSource_CWDW_Retail_Summary.Insert()
        Catch
            Try                     'Update if existing record
                DataSource_CWDW_Retail_Summary.UpdateCommand = "UPDATE ReportRecipients SET " _
                    & "RecipientEmail = '" & mEmailAddress & "', " _
                    & "Sunday = '" & mSun & "', " _
                    & "Monday = '" & mMon & "', " _
                    & "Tuesday = '" & mTue & "', " _
                    & "Wednesday = '" & mWed & "', " _
                    & "Thursday = '" & mThu & "', " _
                    & "Friday = '" & mFri & "', " _
                    & "Saturday = '" & mSat & "' " _
                    & "WHERE mKey = '" & mKey & "'"
                DataSource_CWDW_Retail_Summary.Update()
            Catch
                lblSummary_Error.Text = "Error applying record"
            Finally
                gridSummary_StoresRetail.DataBind()
                srSummary_ClearFields()
            End Try
        Finally
            gridSummary_StoresRetail.DataBind()
            srSummary_ClearFields()
        End Try
    End Sub

    Protected Sub btnSummary_Delete_Click(sender As Object, e As System.EventArgs) Handles btnSummary_Delete.Click
        DataSource_CWDW_Retail_Summary.DeleteCommand = "DELETE ReportRecipients WHERE RecipientEmail = '" & Trim(txtSummary_EmailAddress.Text) & "'"
        DataSource_CWDW_Retail_Summary.Delete()
        gridSummary_StoresRetail.DataBind()
        gridSummary_StoresRetail.SelectedIndex = -1
        srSummary_ClearFields()
    End Sub

    Protected Sub btnSummary_Clear_Click(sender As Object, e As System.EventArgs) Handles btnSummary_Clear.Click
        srSummary_ClearFields()
    End Sub

    Private Sub srSummary_ClearFields()
        Session("mKey") = ""
        mKey = ""

        txtSummary_EmailAddress.Text = ""
        chkSummary_Sun.Checked = False
        chkSummary_Mon.Checked = False
        chkSummary_Tue.Checked = False
        chkSummary_Wed.Checked = False
        chkSummary_Thu.Checked = False
        chkSummary_Fri.Checked = False
        chkSummary_Sat.Checked = False

        lblSummary_Error.Text = ""

        gridSummary_StoresRetail.SelectedIndex = -1
    End Sub

Open in new window

0
 
EYoungAuthor Commented:
Can someone help me with this question?  Thank you.
0
 
CodeCruiserCommented:
In the page load event of the above page, use

StoresFlashSummary.LocalReport.SetParameter
0
 
EYoungAuthor Commented:
When I put "StoresFlashSummary.LocalReport.SetParameter" in the Page_Load event, it generates an error saying that StoresFlashSummary is not declared.

Also, where exactly in the Page_Load event should it go?
0
 
CodeCruiserCommented:
In the FlashSummary.aspx.vb  page where you have

<rsweb:ReportViewer ID="StoresFlashSummary" runat="server" Font-Names="Verdana"
0
 
EYoungAuthor Commented:
0
 
EYoungAuthor Commented:
See previous note about solution.
0
 
CodeCruiserCommented:
That solution is exactly what i mentioned right?
0
 
EYoungAuthor Commented:
No.  The solution was very different and had to do with checking to see if this was a post back in the rptStoresFlashSummary.aspx.vb code behind form.  Here is the code that finally fixed the problem:


Imports Microsoft.Reporting.WebForms

Partial Class rptStoresFlashSummary
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim mParmSales_Date_Parm As Microsoft.Reporting.WebForms.ReportParameter
            mParmSales_Date_Parm = New Microsoft.Reporting.WebForms.ReportParameter("Sales_Date_Parm", CType(Session("mSales_Date_Parm"), String))
            StoresFlashSummary.LocalReport.SetParameters(mParmSales_Date_Parm)
        End If
    End Sub
End Class


CodeCruiser - I do really appreciate your help with this question though.  I have benefitted from many of your suggestions, answers, and posts over the years.  Glad you are an active member of EE.  Best regards to you, EYoung
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 15
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now