how to generate the reports with different sql queries?

[siegfriedls]

hi, everybody:

what i am doing now with stimulsoft is to develop with vb.net+stimulsoft.report and generate a win-form which man can get various reports through different search conditions.

the detail is:

1) there is a sql server table named "Customers" which contains the stored data, for instance, customer's name, customer's address infos etc. three columns of this table would be required to generate a report:

- "CustomerName(varchar)": the name of customers who made the orders.
- "OrderStart(datetime)": the start-time when the customer made an order.
- "OrderEnd(datetime)": the end-time when the customer finished an order.

2) in my win-form there are different searches man can produce and each represents the equivalent sql query.
for instance, through my "date search field" of the win-form would the sql query be produced for the wanted time span:

SELECT CustomerName, OrderStart, OrderEnd
FROM Customers
WHERE OrderStart >=@orderStart AND OrderEnd =@orderStart AND OrderEnd <=@orderEnd AND CustomerCity =@customerCity


suppose now i just want a report that the customers in a particular city, paris or london, who made the orders:

SELECT CustomerName, OrderStart, OrderEnd
FROM Customers
WHERE CustomerCity =@customerCity

so what i need is with different sql queries(with different sql parameter variables) would various reports be generated. i have researched the sample "SqlParameter" and the report would be generated by pre-defining sql parameter in the report template, in "SqlParameter" is "@countryID", but this method would not fit my situation because i have different sql parameters, by pre-defining would not work. i have searched the forum and knowledgebase but unfortunately i didn't find the useful informations.

how can i programmatically generate the report according to the different sql queries? without pre-defining the sql parameter in the report template how can i send my sql parameters to the report? any advice or idea? thanks in advance for your help.

[Alex K.]

Hello,

In this case, you can use variable in query.
For example you query must be "SELECT CustomerName, OrderStart, OrderEnd FROM Customers {Variable}"
and in this case you can set for variable different expressions :
Variable = "" - without filters
Variable = "WHERE OrderStart >="2011.01.05" AND OrderEnd <="2011.01.12""
Variable = "WHERE CustomerCity = "London""
and etc.

Thank you.

[siegfriedls]

Hello,

In this case, you can use variable in query.
For example you query must be "SELECT CustomerName, OrderStart, OrderEnd FROM Customers {Variable}"
and in this case you can set for variable different expressions :
Variable = "" - without filters
Variable = "WHERE OrderStart >="2011.01.05" AND OrderEnd <="2011.01.12""
Variable = "WHERE CustomerCity = "London""
and etc.

Thank you.

hi, Aleksey

thanks for your reply and i think this is a good tip for me.

i have another question: how can i send different sql queries to the stimulsoft.report and get various reports?

i have pre-defined a report template named "StiReportCustomer" in which i've set the "Report Title", "HeaderBand", "DataBand" etc up.
in "Dictionary" panel i've also built a "Sql Connection" with "Data Source" up.
in "Data Source" i wrote the query "SELECT CustomerName, OrderStart, OrderEnd FROM Customers"

so after i got the different sql queries from my win-form, how would these sql queries passed to the report programmatically?
i've tried some methods posted in the forum and mentioned in sample "SqlParameters" but i got the error.

the code is below:

Code: Select all

Dim StiReportCustomer As New Stimulsoft.Report.StiReport
Dim connSql As New SqlConnection("Data Source=12345;Initial Catalog= CatalogABC;Integrated Security=False;User ID=ABC;Password=123")

StiReportCustomer.RegData("TestConn", connSql)
StiReportCustomer.Complie()
.... 
....
....
Dim sqlQuery As String = "SELECT CustomerName, OrderStart, OrderEnd FROM Customers WHERE CustomerCity ='London'"
Dim sqlCmd As New SqlCommand(sqlQuery)
Dim sqlSource As New StiSql("TestCon","Customers","Customers",sqlCmd.CommandText)

StiReportCustomer.Dictionary.DataSources.Clear()
StiReportCustomer.Dictionary.DataSources.Add(sqlSource)

StiReportCustomer.Show()

by StiReportCustomer.Show() appears the error-info:

InvalidOperationException was unhandled
ExecuteReader: CommandText property has not been initialized

[Alex K.]

Hello,

You can use the following code:

Code: Select all

Dim newSqlCommand As String = "SELECT CustomerName, OrderStart, OrderEnd FROM Customers WHERE CustomerCity ='London'"
DirectCast(report.Dictionary.DataSources("Customers"), StiSqlSource).SqlCommand = newSqlCommand

Thank you.

[siegfriedls]

Hello,

You can use the following code:

Code: Select all

Dim newSqlCommand As String = "SELECT CustomerName, OrderStart, OrderEnd FROM Customers WHERE CustomerCity ='London'"
DirectCast(report.Dictionary.DataSources("Customers"), StiSqlSource).SqlCommand = newSqlCommand

Thank you.

thank you Aleksey, with your codes it works!
:biggrin:

[Alex K.]

Hello,

We are always glad to help you!
Let us know if you need any additional help.

Thank you.