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.
>
>

passing multiple parameters to subreport

Hello Group,
Is it possible to pass multiple parameters down to a subreport? If so how?
I am using this code in the report to show the contents of the parameters on
the top of the report.
Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = ""
For Each sParamItem In Parameter
If sParamItem Is Nothing Then Exit For
sParamVal &= sParamItem & ", "
Next
'-- Remove last comma & space:
Return sParamVal.SubString(0, sParamVal.Length - 2)
End Function
Therefore I was expecting to use this to pass it through like this:
Code.Parameterlist(Parameters!Division.Value)
But that doesnt work unfortunately. Does anybody know an approach that
works....
Thanx
PerryDoes anybody have a clue?
thanx again
Perry
"Perry" <sjaak@.sjaak.net> wrote in message
news:OjSF%23CdzGHA.576@.TK2MSFTNGP03.phx.gbl...
> Hello Group,
> Is it possible to pass multiple parameters down to a subreport? If so how?
> I am using this code in the report to show the contents of the parameters
> on the top of the report.
> Function ParameterList(ByVal Parameter As Object) As String
> Dim sParamItem As Object
> Dim sParamVal As String = ""
> For Each sParamItem In Parameter
> If sParamItem Is Nothing Then Exit For
> sParamVal &= sParamItem & ", "
> Next
> '-- Remove last comma & space:
> Return sParamVal.SubString(0, sParamVal.Length - 2)
> End Function
>
> Therefore I was expecting to use this to pass it through like this:
> Code.Parameterlist(Parameters!Division.Value)
> But that doesnt work unfortunately. Does anybody know an approach that
> works....
> Thanx
> Perry
>
>
>

Passing long text strings to a stored procedure

Hello!

I am attempting to insert a group of records into a SQL Server 2000 database table. The data for each of these records is the same, with the exception of a foreign key (hereafter known as the 'RepKey') and the generated primary key. To improve performance and cut down on the network traffic, I pack the RepKeys in a comma-delimited string and send it as a single parameter, with the intention of parsing it in the stored proicedure to obtain each individual RepKey, or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

My problem is that there may be 1000's of items in this string. Using a varchar(8000) as the parameter type is too short, while using the 'text' data type does not allow me to perform any string operations on it. Any ideas on how to make one network call and insert multiple records that breaks the 8000 character barrier?

One thing that I cannot do is add a table so that the record is stored once, then mapped to each individual rep key. The database structure cannot change. Other solutions that I may not have considered are welcome. Thanks!Can you add a global temp table?
You could write all the data to a disk file then bcp it in to the global table.
Could also create a disconnected recordset on the golobal temp table, diconnect it, populate it then connect it to commit the records then use that.

You could use a text datatype then use substring to parse it in chunks and use char functions on it.|||Nigelrivett idea of a text file sounds interesting. What if the parameter used in your stored procedure was the path to a file containing the list of RepKeys? Once in your procedure you use BULK INSERT into a temporay table (Globle table if needed like nigelrivett suggested) then perform the same looping as you would have done before.

sp_MyProc (RepKeyFile AS varchar(50), ....)

CREATE TABLE #temptable ...
BULK INSERT #temptable FROM @.RepKeyFile
.
.
.
CREATE CURSOR on #temptable
loop through

The only problem is your point on:
or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

I thought that you could create a local text variable and while looping append the RepKey to the local text field, SET @.txt = @.txt + ',' + @.RepKey. However I got an error when trying to create a local variable as type text.

Msg 2739, Level 16, State 1, Server ATLAS, Line 1
The text, ntext, and image data types are invalid for local variables.|||Thanks guys - I ended up using the substring procedure to break off chunks, then used an INSERT..SELECT statement that looks like the following:

SET @.query = "INSERT INTO RepContact([fields])"
SET @.query = @.query + "SELECT RepKey, [@.vars] FROM Rep WHERE RepKey IN (" + @.currentString + ")"
exec(@.query)

@.CurrentString is the current list of keys. Each time through the loop, as long as there are still items, the query is run.

Thanks again - if anyone has any ideas on speeding this up, it would be greatly appreciated. (The insert runs a bit slower than I would have hoped).

Everett|||I was thinking that the text file would hold the keys delimitted by crlf so that the bcp would insert them into separate rows and you wouldn't have to do any further processing.|||I think that I would prefer to leave it as it is and avoid writing to and reading from disk. Wouldn't this make it slower, not faster? Anyways, thanks again.|||>> Wouldn't this make it slower, not faster?

Depends on the data and environment.
the bcp will be non-logged so the inser will be faster. It will reduce the handshaking across the network and reduce the amount of manipulation needed before the insert into the production tables.
It would probably end up slower but maybe not. It does give an automatic record of the dta inserted from the text files and makes it easy to make the insert asynchronous if you need to.|||I'll try it during the week and advise you of the outcome.

Thanks again for all of your help.

Passing list values in drill through! URGENT

I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp?

thanks!

Any workaround on this?

|||

R4BI wrote:

Any workaround on this?

Not sure if this will work but you might try Join(Parameters!accountnumber.Value,",")

|||

let me again explain the scenario:

Main report: One list which is group by a field customaccountnumber.

CustomAccountNumber is formed like this: IIF(left(Accountnumber),2) ="13", 130000 grp , IIF(left(Accountnumber),2) ="14", 140000 grp ,IIF(left(Accountnumber),2) ="15", 150000 grp ,other grps)))

All the tables inside the list are displayed based on the CustomAccountNumber.

SubReport: This report has one of the parameter Accountnumber

Now, Currently from the main report navigation property i am passing ""fields!accountnumber.uniquename"" as the value for this parameter then the subreport gets only the first grp value e.g. 130001 if clicked on the table based on 130000 grp.

What to do so that it gets the whole grp of values?

Please look at it.. I am desperately looking for a solution.

Thanks,

|||

Rohit,

Have you found your solution yet? If not, please post your SQL code and I'll try to assist you.

Passing list values in drill through! URGENT

I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp?

thanks!

Any workaround on this?

|||

R4BI wrote:

Any workaround on this?

Not sure if this will work but you might try Join(Parameters!accountnumber.Value,",")

|||

let me again explain the scenario:

Main report: One list which is group by a field customaccountnumber.

CustomAccountNumber is formed like this: IIF(left(Accountnumber),2) ="13", 130000 grp , IIF(left(Accountnumber),2) ="14", 140000 grp ,IIF(left(Accountnumber),2) ="15", 150000 grp ,other grps)))

All the tables inside the list are displayed based on the CustomAccountNumber.

SubReport: This report has one of the parameter Accountnumber

Now, Currently from the main report navigation property i am passing ""fields!accountnumber.uniquename"" as the value for this parameter then the subreport gets only the first grp value e.g. 130001 if clicked on the table based on 130000 grp.

What to do so that it gets the whole grp of values?

Please look at it.. I am desperately looking for a solution.

Thanks,

|||

Rohit,

Have you found your solution yet? If not, please post your SQL code and I'll try to assist you.

sql

Passing list values in drill through! URGENT

I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp?

thanks!

Any workaround on this?

|||

R4BI wrote:

Any workaround on this?

Not sure if this will work but you might try Join(Parameters!accountnumber.Value,",")

|||

let me again explain the scenario:

Main report: One list which is group by a field customaccountnumber.

CustomAccountNumber is formed like this: IIF(left(Accountnumber),2) ="13", 130000 grp , IIF(left(Accountnumber),2) ="14", 140000 grp ,IIF(left(Accountnumber),2) ="15", 150000 grp ,other grps)))

All the tables inside the list are displayed based on the CustomAccountNumber.

SubReport: This report has one of the parameter Accountnumber

Now, Currently from the main report navigation property i am passing ""fields!accountnumber.uniquename"" as the value for this parameter then the subreport gets only the first grp value e.g. 130001 if clicked on the table based on 130000 grp.

What to do so that it gets the whole grp of values?

Please look at it.. I am desperately looking for a solution.

Thanks,

|||

Rohit,

Have you found your solution yet? If not, please post your SQL code and I'll try to assist you.

Passing list values in drill through! URGENT

I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp?

thanks!

Any workaround on this?

|||

R4BI wrote:

Any workaround on this?

Not sure if this will work but you might try Join(Parameters!accountnumber.Value,",")

|||

let me again explain the scenario:

Main report: One list which is group by a field customaccountnumber.

CustomAccountNumber is formed like this: IIF(left(Accountnumber),2) ="13", 130000 grp , IIF(left(Accountnumber),2) ="14", 140000 grp ,IIF(left(Accountnumber),2) ="15", 150000 grp ,other grps)))

All the tables inside the list are displayed based on the CustomAccountNumber.

SubReport: This report has one of the parameter Accountnumber

Now, Currently from the main report navigation property i am passing ""fields!accountnumber.uniquename"" as the value for this parameter then the subreport gets only the first grp value e.g. 130001 if clicked on the table based on 130000 grp.

What to do so that it gets the whole grp of values?

Please look at it.. I am desperately looking for a solution.

Thanks,

|||

Rohit,

Have you found your solution yet? If not, please post your SQL code and I'll try to assist you.

Passing a null parameter from a report to a subreport

Hi Group,
I have a report with a subreport and I'm trying to make them work in the
report designer in VS2003.
The report has some parameters and one of them is DepartmentIndex, which can
be NULL.
This parameter is being passed down to the subreport.
I set the DepartmentIndex parameter to accept both blank and null values and
gave it a default value of " " (one space),
in both the report and the subreport.
If I run the SUBREPORT alone and check the null checkbox for this parameter,
the subreport works fine and displays the records with a null value in the
DepartmentIndex field.
But if I try to run the REPORT and check the null checkbox, it displays an
error :
An error occurred while executing the subreport '<subreport_name>': One or
more parameters required to run the report have not been specified.
Though, the report data is displayed correctly (showing the rows with null
DepartmentIndex).
Can anybody help, please ?
Thanks,
Andrei.I got the same error and I would someone can help?
One or more parameters required to run the report have not been specified
Linh
"andrei" wrote:
> Hi Group,
> I have a report with a subreport and I'm trying to make them work in the
> report designer in VS2003.
> The report has some parameters and one of them is DepartmentIndex, which can
> be NULL.
> This parameter is being passed down to the subreport.
> I set the DepartmentIndex parameter to accept both blank and null values and
> gave it a default value of " " (one space),
> in both the report and the subreport.
> If I run the SUBREPORT alone and check the null checkbox for this parameter,
> the subreport works fine and displays the records with a null value in the
> DepartmentIndex field.
> But if I try to run the REPORT and check the null checkbox, it displays an
> error :
> An error occurred while executing the subreport '<subreport_name>': One or
> more parameters required to run the report have not been specified.
> Though, the report data is displayed correctly (showing the rows with null
> DepartmentIndex).
> Can anybody help, please ?
> Thanks,
> Andrei.
>
>|||How do you pass parameter into subreport?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Linh Phan" <LinhPhan@.discussions.microsoft.com> wrote in message
news:FA2946C8-ECD9-47E1-AC80-B7AA2EF041B2@.microsoft.com...
>I got the same error and I would someone can help?
> One or more parameters required to run the report have not been specified
> Linh
> "andrei" wrote:
>> Hi Group,
>> I have a report with a subreport and I'm trying to make them work in the
>> report designer in VS2003.
>> The report has some parameters and one of them is DepartmentIndex, which
>> can
>> be NULL.
>> This parameter is being passed down to the subreport.
>> I set the DepartmentIndex parameter to accept both blank and null values
>> and
>> gave it a default value of " " (one space),
>> in both the report and the subreport.
>> If I run the SUBREPORT alone and check the null checkbox for this
>> parameter,
>> the subreport works fine and displays the records with a null value in
>> the
>> DepartmentIndex field.
>> But if I try to run the REPORT and check the null checkbox, it displays
>> an
>> error :
>> An error occurred while executing the subreport '<subreport_name>': One
>> or
>> more parameters required to run the report have not been specified.
>> Though, the report data is displayed correctly (showing the rows with
>> null
>> DepartmentIndex).
>> Can anybody help, please ?
>> Thanks,
>> Andrei.
>>|||Hi Lev,
I just figured it out by right click on each subreport and set the missing
parameters for each. It worked well.
Thanks!
Linh
"Lev Semenets [MSFT]" wrote:
> How do you pass parameter into subreport?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Linh Phan" <LinhPhan@.discussions.microsoft.com> wrote in message
> news:FA2946C8-ECD9-47E1-AC80-B7AA2EF041B2@.microsoft.com...
> >I got the same error and I would someone can help?
> >
> > One or more parameters required to run the report have not been specified
> >
> > Linh
> >
> > "andrei" wrote:
> >
> >> Hi Group,
> >>
> >> I have a report with a subreport and I'm trying to make them work in the
> >> report designer in VS2003.
> >> The report has some parameters and one of them is DepartmentIndex, which
> >> can
> >> be NULL.
> >> This parameter is being passed down to the subreport.
> >> I set the DepartmentIndex parameter to accept both blank and null values
> >> and
> >> gave it a default value of " " (one space),
> >> in both the report and the subreport.
> >> If I run the SUBREPORT alone and check the null checkbox for this
> >> parameter,
> >> the subreport works fine and displays the records with a null value in
> >> the
> >> DepartmentIndex field.
> >> But if I try to run the REPORT and check the null checkbox, it displays
> >> an
> >> error :
> >> An error occurred while executing the subreport '<subreport_name>': One
> >> or
> >> more parameters required to run the report have not been specified.
> >>
> >> Though, the report data is displayed correctly (showing the rows with
> >> null
> >> DepartmentIndex).
> >>
> >> Can anybody help, please ?
> >>
> >> Thanks,
> >>
> >> Andrei.
> >>
> >>
> >>
>
>

passing a coma delimited group of numbers to a collection for sql

I have a sql statement and one of the arguments I want to pass is a comma delimited set of numbers. It keeps getting turned into a string. How do I keep that from happening. Here is kind of what it looks like
Select FirstName
from User
where NameID in (5,6,7)
or
Select FirstName
from User
where NameID in (@.NameIDList)
There is no error code just nothing returns. If I take out the @.ANameIDList and put the values I want, it returns the correct results.
Thanks,
Bryan
PS the link to the original thread it herehttp://forums.asp.net/1046154/ShowPost.aspxHey,
Right, because unfortunately you can't pass in a list, instead it looks for all the numbers as a string. Instead, you have to create a dynamic string, assign it to a string variable, and do it that way:
declare @.sql varchar(8000)
set @.sql = 'select FirstName from User where NameID in (' + @.NameIDList + ')'
exec(@.sql) -- maybe without parens

Pass Group into variable then query NOT IN variable?

Greetings all.

I'm looking for a clever way to pass a group into a variable and then query the variable using NOT IN

For example

DECLARE @.myvariable varchar(100)

SET @.myvariable = (1, 2, 3, 4, 5)

SELECT * FROM myTable

WHERE myField NOT IN @.myvariable

I swear I've seen this and I'm sure it's simple but my brain is not calculating the algorithm.

Thanks for all help,

Adamus

This works, but does require the dreaded dynamic SQL:

if object_id('myTable') is not null drop table myTable

go

create table myTable (myField int)

go

insert myTable values (1)

insert myTable values (2)

insert myTable values (3)

insert myTable values (4)

insert myTable values (5)

insert myTable values (6)

insert myTable values (7)

go

DECLARE @.myvariable varchar(100)

SET @.myvariable = '(1, 2, 3, 4, 5)'

exec('SELECT * FROM myTable WHERE myField NOT IN ' + @.myvariable)

Ron

|||

try

Code Snippet

DECLARE @.myvariable varchar(100),

@.stmt varchar(8000)

SET @.myvariable = (1, 2, 3, 4, 5)

SET @.stmt = 'SELECT * FROM myTable WHERE myField NOT IN ' + @.myvariable

exec sp_sqlexec @.stmt

|||

That's it!

Thanks,

Adamus

|||

Rice31416,

Unfortunately, your example demonstrates the use of "the dreaded dynamic SQL." That what you are executing in the EXEC() function.

Perhaps you meant something else...

|||

Dale,

I suspect you meant:

EXECUTE sp_executesql @.Stmt

|||

Yup, thanks.

Leftover from days gone by. Also need to change varchar to nvarchar...

|||

Arnie Rowland wrote:

Rice31416,

Unfortunately, your example demonstrates the use of "the dreaded dynamic SQL." That what you are executing in the EXEC() function.

Perhaps you meant something else...

This was the answer I was looking for but what do you mean by "unfortunately?"

Is there something bad about using this approach?

Adamus

|||

Hi Adam,

Using Dynamic SQL is sometimes a necessary 'evil'. At least some folks consider its usage to be tandamont to 'evil'. (My comment about the "dreaded" was to reflect back to Rice31416 that while he wrote that his approach didn't use "the dreaded dynamic SQL", in fact, his approach was using dynamic SQL."

I recommend this article from Erland Sommarskog. It gets into advanced issues, but keep it for reference.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Arnie,

Yes, I meant that it *does* involve dynamic SQL. And yes, I got lazy and used "execute" in the example instead of sp_executesql!

Ron