Creating reports and dashboards | Stimulsoft community forum

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.

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.

Aleksey wrote: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:
by StiReportCustomer.Show() appears the error-info:

InvalidOperationException was unhandled
ExecuteReader: CommandText property has not been initialized

Hello,

You can use the following code: Thank you.

Aleksey wrote: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:

Hello,

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

Thank you.