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 in long parametes to reporting services

hello,
how can i pass in long parameter into a report?
if i were to use url its just too short.
i want my users to select tons of numbers and pass it to RS. url parameter
access just does not seems right. does anyone have any advice for me?You could store the multiple values as rows in a db table, and assign a key
value which groups these together for the session. Then pass the key value
in as a parameter, the query could then use the key , and join the params
table to the orig table...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Asha" <Asha@.discussions.microsoft.com> wrote in message
news:F2C123E8-3FA7-458A-863A-BC95FD4BF1B5@.microsoft.com...
> hello,
> how can i pass in long parameter into a report?
> if i were to use url its just too short.
> i want my users to select tons of numbers and pass it to RS. url parameter
> access just does not seems right. does anyone have any advice for me?

Passing a list of numbers to a stored procudure, having a size more than 8000 characters

Hi..

I m working on MS SQL Server 2000.

I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.

I was doing something like..

Create Procedure proc

(

@.Items varchar(100) List of numbers

)

AS Begin

Declare @.SQL varchar(8000)

Set @.SQL =

'

Select Query......

Where products IN (' + @.items + ') '

'

Exec (@.SQL)

This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."

Can any1 please help me out with this ASAP?

In 2000, you can split the list over mulitple parms and then concat them together when you call the sporc like so:

create proc whatever

( @.Items1 varchar(8000),

@.Itmes2 varchar(8000),

...

)

Declare @.SqlStart Varchar(8000),

@.SqlEnd varchar(8000)

set @.SqlStart = '

Select bla

where products in ('

set SqlEnd = ')'

exec (@.sqlStart + @.Items1 + @.Items2 + ... + @.SqlEnd)

That should work although it tedious to split the items on the calling end. Where do the itmes come from, can;t you use a join or something to get at the records you need?

Regards GJ

|||

Change the datatype from Varchar to Text & execute the query directly dont use any variable.

Code Snippet

Create Procedure [Proc]

(@.Items text)

AS

Begin

Exec ('Select Query......

Where products IN (' + @.items + ')')

End

|||

Thanks GJ...

Works fine after splitting the varchar...

I was thinking about using joins as well but I guess its not possible the way my query is.

Anyways... Thanks for ur help both of Us

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