Passing Multiple values in Drill Through Report

I have a report called ReportA which has a group header that is summing
detail rows. In the textbox that I am using to some I have selected the
action property and set it to "Jump to" ReportB. ReportB has a parameter of
StudentID. Based on Summed values from the detail section of ReportA I would
like to pass the StudentID's to ReportB. Basically what I'm saying is I
would like for ReportA to pass ReportB a parameter that has multiple values.
Either this way or any way that based on the action property of a textbox I
could pass the multiple values to another report. Any help or ideas would be
appreciated.Should work similar to other multiple value parameter reports. Search this
newsgroup for "multi-value parameters" and "multiple parameters" for
postings from folks who have asked similar question.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"P" <P@.discussions.microsoft.com> wrote in message
news:BCD0DCB4-C644-491D-91FC-79349F89085E@.microsoft.com...
>I have a report called ReportA which has a group header that is summing
> detail rows. In the textbox that I am using to some I have selected the
> action property and set it to "Jump to" ReportB. ReportB has a parameter
> of
> StudentID. Based on Summed values from the detail section of ReportA I
> would
> like to pass the StudentID's to ReportB. Basically what I'm saying is I
> would like for ReportA to pass ReportB a parameter that has multiple
> values.
> Either this way or any way that based on the action property of a textbox
> I
> could pass the multiple values to another report. Any help or ideas would
> be
> appreciated.|||One problem is that I have a cell on a table that is the sum of other values.
I need to set the action property for this cell that is summing to be able
to pass as a parameter StudentID's of the related records that make up the
summed values. If I can get pass this I can accomplish what I need.
Thanks!
"John H. Miller" wrote:
> Should work similar to other multiple value parameter reports. Search this
> newsgroup for "multi-value parameters" and "multiple parameters" for
> postings from folks who have asked similar question.
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "P" <P@.discussions.microsoft.com> wrote in message
> news:BCD0DCB4-C644-491D-91FC-79349F89085E@.microsoft.com...
> >I have a report called ReportA which has a group header that is summing
> > detail rows. In the textbox that I am using to some I have selected the
> > action property and set it to "Jump to" ReportB. ReportB has a parameter
> > of
> > StudentID. Based on Summed values from the detail section of ReportA I
> > would
> > like to pass the StudentID's to ReportB. Basically what I'm saying is I
> > would like for ReportA to pass ReportB a parameter that has multiple
> > values.
> > Either this way or any way that based on the action property of a textbox
> > I
> > could pass the multiple values to another report. Any help or ideas would
> > be
> > appreciated.
>
>

pass in or retrieve a computername using vb.net & sql sp

I have two textboxes on a Winform. I want to be able to enter a Computername like "PapaSmurf" into the first textbox, an application name like "adobe" into the second textbox, have it retrieve the data via a DataGrid/DataTable and a Stored Procedure.

My Stored Procedure (I'm using SQL 2005) looks like this:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%Microsoft%' AND WORKSTATION_NAME LIKE '%B001321D14F%'.

It's obvious that I can't use a static Workstation_Name.

It looks like I will need to setup a parameter but how do I set it up so that whatever is in the ComputerName textbox on the form get's passed into the Stored Procedure Parameter or maybe I have it backwards and the SP needs to somehow pull it from the Winform textbox.

Any ideas?

Wallace

You can use parameters to pass in the fields use ADO.net in VB.net

change the stored procedure to:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

@.ComputerName as varchar(50),

@.WorkStation as varchar(50)

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%' + @.ComputerName +'%' AND WORKSTATION_NAME LIKE '%' + @.WorkStation + '%'.

Then the ado.net:

Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnection").ToString)

sqlConn.Open()

Try

Dim command As SqlCommand = New SqlCommand("uspSelectAnyPkgName", sqlConn)

command.Parameters.AddWithValue("@.ComputerName", ComputerName.Text)

command.Parameters.AddWithValue("@.WorkStation", WorkStation.Text)

command.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

sqlConn.Close()

sqlConn = Nothing

GC.Collect()

End Try

You'll probably want to use a datareader to populate a grid since you're selecting.

Adamus

|||

Hi Adam,

It appears as if this may work but I left out one important detail. I have no idea on how to write a loop (since I am new to vb.net) in the Stored procedure or in vb.net, which ever it may be, to get for instance, an application name that starts with "KB" (Like as in the Microsoft Knowledge Base) from ComputerName "MYPC".

The "command.ExecuteNonQuery()" gives me the error, "Line 1: Incorrect syntax near 'uspSelectAnyPkgName'".

I used the following SQL DataAdapter and Fill method but it grabbed every software package name and every computer name other than:

SqlDA_SinglePkgName.Fill(AllTablesDataSet1.uspSelectAnyPkgName)

So, it appears that the Fill method or the command.ExecuteNonQuery are not the appropriate methods or I'm not passing something in.

Ideally, I just want the specific software package on one computer to be pulled from the DB.

Your help again would be appreciated.

Thanks,

Wallace

|||

Adamus,

Thank you for your help. This worked great.

Wally

pass in or retrieve a computername using vb.net & sql sp

I have two textboxes on a Winform. I want to be able to enter a Computername like "PapaSmurf" into the first textbox, an application name like "adobe" into the second textbox, have it retrieve the data via a DataGrid/DataTable and a Stored Procedure.

My Stored Procedure (I'm using SQL 2005) looks like this:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%Microsoft%' AND WORKSTATION_NAME LIKE '%B001321D14F%'.

It's obvious that I can't use a static Workstation_Name.

It looks like I will need to setup a parameter but how do I set it up so that whatever is in the ComputerName textbox on the form get's passed into the Stored Procedure Parameter or maybe I have it backwards and the SP needs to somehow pull it from the Winform textbox.

Any ideas?

Wallace

You can use parameters to pass in the fields use ADO.net in VB.net

change the stored procedure to:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

@.ComputerName as varchar(50),

@.WorkStation as varchar(50)

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%' + @.ComputerName +'%' AND WORKSTATION_NAME LIKE '%' + @.WorkStation + '%'.

Then the ado.net:

Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnection").ToString)

sqlConn.Open()

Try

Dim command As SqlCommand = New SqlCommand("uspSelectAnyPkgName", sqlConn)

command.Parameters.AddWithValue("@.ComputerName", ComputerName.Text)

command.Parameters.AddWithValue("@.WorkStation", WorkStation.Text)

command.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

sqlConn.Close()

sqlConn = Nothing

GC.Collect()

End Try

You'll probably want to use a datareader to populate a grid since you're selecting.

Adamus

|||

Hi Adam,

It appears as if this may work but I left out one important detail. I have no idea on how to write a loop (since I am new to vb.net) in the Stored procedure or in vb.net, which ever it may be, to get for instance, an application name that starts with "KB" (Like as in the Microsoft Knowledge Base) from ComputerName "MYPC".

The "command.ExecuteNonQuery()" gives me the error, "Line 1: Incorrect syntax near 'uspSelectAnyPkgName'".

I used the following SQL DataAdapter and Fill method but it grabbed every software package name and every computer name other than:

SqlDA_SinglePkgName.Fill(AllTablesDataSet1.uspSelectAnyPkgName)

So, it appears that the Fill method or the command.ExecuteNonQuery are not the appropriate methods or I'm not passing something in.

Ideally, I just want the specific software package on one computer to be pulled from the DB.

Your help again would be appreciated.

Thanks,

Wallace

|||

Adamus,

Thank you for your help. This worked great.

Wally

Pass a Parameter into a textbox on a report?

I have a report that is unsing a multi-value parameter selection list. Based on the parameter I select, I would like to display the selection in a textbox directly on the report. Would someone know what statement I place into the textbox to capture the parameter:

Some details: 'read_perflvl' is the data field in the original database. 'perflvl' is the name of my report parameter.

I dropped the 'read_perflvl' field into the text box on my report and it defaulted to

"=First(Fields!read_perflvl.Value)" , and this actually worked fine if I selected only one parameter. But if I selected two or more from the multi-valued parameter list, it only shows the first one (clearly due to the FIRST function it is referencing) in the textbox.

Is there a different prefix to the statement that I can use so that it will display all of the parameter values I select?

Thanks for any suggestions.

Dear esloat,

try the following expression in the text box.

-right click on the text box and select expression from the drop down menu.

-then write the following expression:

=parameters!perflvl.value

I think this should display the value of the selected parameter in the text box.

Sincerely,

Amde

|||

Amde, Wow, great minds think alike. Just as I received your message I had pasted that exact expression into the textbox - I lifted it from the code block that was refering to the parameter in the report.

Unfortunately when I run the report I get a non-discript '#ERROR' message returned. The rest of the report runs fine, but the value of the textbox displays as an error.

Might this be due to a conflict between a string and numeric value? The originating database field is a string. Does the parameter expression store the result as a numeric value?

Anyway, I'll keep trying and post if I resolve the error message. Thanks.

|||hi , u havnt tld me frm where to pass frm asp.net to reports . so u can do as report parameter id=new report parameter()
id.name="ur id name";
id.values.add(textbox1.text);
reportviewer.serverreport(id)

..in reports ,, use same field(=fileds!database.value)

deploy report,, and use in asp.net as
reportviewer.reportpath="report path" as "/ report project1/reports"