Creating reports and dashboards | Stimulsoft community forum

Hi !

I've made a report in which the columns are retrieved by the following sql query "SELECT * FROM CustomerOrder WHERE ToClientId=@ClientId", where the parameter @ClientId is of type int. I set the parameter value in runtime, and it works fine.
I changed the query into this "SELECT * FROM CustomerOrder WHERE ToClientId IN (@ClientIdList), where @ClientIdList is of type Varchar. In runtime I'm trying to pass the following value "1,2" and I get the error "Conversion failed when converting the varchar value '1,2' to data type int.". When I'm setting the parameters value to "1" or "2" it works fine. The problems occurs when I'm trying to set multiple values.

Any thoughts ?
Thank you.

Are you using SQL Server?

SQL Server does not natively support multi values parameters. Only SQL Server 2008 can do this. Otherwise you have to do a workaround using XML or temp tables.

Marco

I remember this problem appearing here on the forum before if you can find the thread.

I think you need to use a report variable to hold multiple values for an IN (x,y,z) sql statement.
As far as I know, Parameters can only contain one value.


EDIT: I think this was it
http://forum.stimulsoft.com/Default.aspx?g=posts&t=366

And how do i set the variable's value ?

I've changed the query into this "SELECT * FROM CustomerOrder WHERE ToCustomerOrderId IN ({ClientIdList})

Now what ?

Ok, the variable's name is ClientListId and not ClientIdList. My bad. :biggrin:

Anyway, it still doesn't work.:redeye:

I get the following error : The name 'ClientListId' does not exist in the current context at stiReport.Compile();

Here's the code :

if (Constants.SqlConnection == null)
{
stiPreviewControl.Report = null;
return;
}
stiReport.RegData("Connection", Constants.SqlConnection);
stiReport.Compile();

//Set the paramter's value
//stiReport.CompiledReport.DataSources["CustomerOrder"].Parameters["Parameter1"].ParameterValue =
// Procedures.GenerateParameters(dataGridView);

stiReport["ClientListId"] = Procedures.GenerateParameters(dataGridView);

stiReport.Render();
stiReport.Show();

Please help me, `cause it is really starting to annoy me !

Hello, mir.

Please try once more

Do not be upset.

ClientListId must be declared in your Dictionary as a variable. Also if you follow all steps as in the topic Brendan showed you, all would work as expected.

Thank you.

mir wrote:Please help me, `cause it is really starting to annoy me !

No problem, I'll prepare the test solution for you.

Edward, thanks, but it won't be necessary. I'd resolved the problem. Thank you very much.

I have one more question :

I've made a master detail report that shows the orders for every client. I have made also a total for every client, and now i want to make a GRAND TOTAL for all the clients. Any suggestions ? Thank you !

Example (lets make a count of all the orders) : i have 3 clients. first client has 3 orders, second client 5, and the third one has 8. For every client i would make a count of the orders, and at the end of the report i would like to have a grand total count that shows the total number of orders, 3+5+8=16

Please declare a variable "ClientListId" in the report's Dictionary. Type of the variable please set 'object'.

SQL query should like something like this:

select * from Categories where CategoryID in ({ClientListId})

Setting of the Variable must be done exactly as Brendan suggested:

report.Load(....); //Load Template
report.Compile();
report["ClientListId"] = "1, 2, 3, 4";

report.Show();

Thank you.