Can I use a System.Array for a SQL Report?

SQLRS is not really a full .net language, you can use some code in it but you have to embed the class inside the report or some such...

never had to go that far...  no linq in there as far as i know.

qwert231: hmm... not sure, i know we can pass parameters to a report is this a multi-select case ? if so you might do it 2 ways:

1) small # of selections pass multiple params like n1,n2,n3 where each is one param.

or

2) you need more like an "IN (n1..n#)"  possibly pass one param to the report but you might have to get fancy inside the report if you have to pass the params as one, string.split and make an array inside the report...

like 1-2-3-4-5-6-7-8

Split('-')

in the report to get int[] params = {1,2,3,4,5,6,7,8}; or the equiv in report.

ok.... building a string like that in sql is kind of tricky, i don't have an example right now but there is a way you have to do that due to the way sql evaluates quotes.  double quotes have a special meaning to tsql.

but you are going to run the sql in a report right?

do this to work on the query:

make 2 sql report datasets

1)  the query you want to run with the CustomerID in (@ParamName)

2)  a query that uses a select to drive a dropdown combo box on the report preview / run

 make that dropdown a multiselect.

in the report parameters use the result of the dropdown to be the parameter to the man dataset.

for testing you may just make a table that has your id's to test with.

if you pick select all from the top of the drop down then the report runtime will do the IN (....) for you.

the results will have all the rows based on your list of id's

then we can figure out own to pass the parameters...

Hey:  can you write a query that picks the set based on ... idea time here.......  check this out:

make a table with 3 columns

ID - int - auto inc - not null

BatchID - possibly a second int or a date and time ??

CustomerID - your data

when the user selects the id's on the page use the DateTime.Now as an ID for this run of the report.

write the "batchID" and the "customerID" to this table.

then pass that datetime to the report as a simple parameter.

now in your sql IN (...)  you can write:

IN (  Select CustomerID from ReportBatchTableName Where BatchID = @BatchIDParameter )

now the report can run and has the list of id's and skip the dropdown list we do not need it now.

then in sql server write a small sql proc that does a

Delete

From  ReportBatchTableName

 Where BatchID > -- 8 hours old?  Getdate() -8

then use the sql agent to have that proc run at least every day at say 2am or run it more often like 8 hours ( 3 times a day)

so the job clears out the old selects and the table never grows to big.

and now the parameters are simple, no array to pass, just a datetime to pick the right set of id's

as long as you do not have some huge number of folks running this report at the same time (down to the second)

this will work fine, even if two folks try to run this at the same time it will be almost impossible for them to get the same excact date time value as that will be down to like a tenth of a second in res. of the time.

just make sure that when you write the data that you start with a trasaction and get the datetime 1 time at the start of the write.

dpending opn what you use the details of that vari.

say it's classic ado.net to sql then:

DateTime dtNow = DateTime.Now;

using  (  sqlconn = new .....){

   using ( sqltransaction .... ){

      using ( SqlCommand ....    ){

         create 2 named parameters here

          foreach ( string CustID in SOmeCOllection){

              P1 = dtNow;

              P2 = CustID;

              SqlCmd.ExecuteNonQuery();

          }

      }

   }

    SqlTran.Commit();

    SqlConn.Close();

}

the named parameters -- you create the params one time and the sql is pre-parsed/ compiled one time.

so in the loop it runs like a sproc almost, verry fast. no making strings in the loop. or as little as possible.

now you can run the report ...

Report.Parameters.Add( New Paramter("BatchID",dtNow);

...

transaction scope makes sure that they are witten as a set and that 2 or more users can't write the same rows

index the table on that dattime column - to make the select fast for the report and for droping the old rows.

i bet that works very nice....