Hi Everyone,
I just have a query about creating Ad-hoc reports using StimulReport. I was wondering if anyone on the forum here could offer some tips or ideas on how to go about doing it. The Ad-hoc reports would be for the End users of my product. They would need the ability to select the data from my SQL Server Database and with as much automation as possible, create the report.
I am unsure how to approach this. Would I need a certain component (either purchased or created by myself) to let the users build the SQL Query first outside of StimulReport, then pass this over to the report to build the Dictionary. My database is quite complex so it would probably require special TableViews with simplied stuctures to make it easier for the users to be able to query on the data they need.
Any suggestions would be appreciated.
Thanks, Brendan
Creating Ad-hoc Reports
Creating Ad-hoc Reports
That should be easy with Stimulreport. You can do all the data access inside the report, so that is very easy. Maybe you should provide a skeleton report, which contains the connections string and some often used views (or even all important views to make the database access for the end user easier). In the newest version you could make the skeleton report a master report all reports your customers create, inherit the items from the master.
Then you just need a file dialog, in which the user can select the report file and two buttons: design and print. All the logic is done inside the report. You can even do a form inside the report to ask for one (or more) parameters.
I had a similar problem and with stimulreport it is no problem at all.
Marco
Then you just need a file dialog, in which the user can select the report file and two buttons: design and print. All the logic is done inside the report. You can even do a form inside the report to ask for one (or more) parameters.
I had a similar problem and with stimulreport it is no problem at all.
Marco
Creating Ad-hoc Reports
Hi Marco,
Thanks for your response.
How would one populate the Dictionary that contains my Table Views.
Would it be a matter of providing the SQL Selects inside the skeleton report?
Some of my tables for my users may have over 1 million + rows of data so would this be practical?
For Example, if my user wanted to create a very basic report to get a list of Customers from a specified country how would it be achieved. Would the Select need to be altered to include a WHERE Country = 'Russia' or would a Datafilter on the Report Databand work. As I'm aware, the Datafilters are applied client side so I would end up querying the entire table to filter on it rather than it being filtered by the Database engine.
Thanks for your response.
How would one populate the Dictionary that contains my Table Views.
Would it be a matter of providing the SQL Selects inside the skeleton report?
Some of my tables for my users may have over 1 million + rows of data so would this be practical?
For Example, if my user wanted to create a very basic report to get a list of Customers from a specified country how would it be achieved. Would the Select need to be altered to include a WHERE Country = 'Russia' or would a Datafilter on the Report Databand work. As I'm aware, the Datafilters are applied client side so I would end up querying the entire table to filter on it rather than it being filtered by the Database engine.
Creating Ad-hoc Reports
You can add any data source you like. Be it a table, a view or stored procedure.
Your create the data source and then create the fielda. You even can created new calculated columns based on the existing columns.
All data source be filtered by a parameter.
E.G. you could use:
select * from MyCutomerTable where ID = {CustomerNo}
where {CustomerNo} is a variable CustomerNo in the report, to be seen under dictionary.
You should set a default like -1 and the report, either in the report code or via a form page could be change the value, before rendering begins.
This way you could provide all the tables / view you needed, nothing would be really loaded and when a variable is changed the right data is loaded.
If if gets too complicated, you could provide some master reports, one for invoices, one for lists, one for statics and so on. You get the point.
I hope that helps somehow.
Marco
Your create the data source and then create the fielda. You even can created new calculated columns based on the existing columns.
All data source be filtered by a parameter.
E.G. you could use:
select * from MyCutomerTable where ID = {CustomerNo}
where {CustomerNo} is a variable CustomerNo in the report, to be seen under dictionary.
You should set a default like -1 and the report, either in the report code or via a form page could be change the value, before rendering begins.
This way you could provide all the tables / view you needed, nothing would be really loaded and when a variable is changed the right data is loaded.
If if gets too complicated, you could provide some master reports, one for invoices, one for lists, one for statics and so on. You get the point.
I hope that helps somehow.
Marco
Creating Ad-hoc Reports
Hi Macro,
Thanks again.
I see what you mean by using Variables to put filters on the query.
However I'm not sure if this would work in my case. I probably haven't provided enough information in what I'm trying to acheive as I'm not so sure myself :biggrin:
Maybe a basic use case of what my user would be trying to do would help.
If I provided them with 1 Datasource for my Customer Table, I need to let my User report on this table any way they wish.
If we assume the table has some of the following fields:
Code,
FirstName,
Surname,
DOB,
Country
If I provided them with a Master Report to this Table I would maybe fill it's Datasource through StimulReport with a:
SELECT Code,FirstName,Surname,DOB,Country FROM Customer
What my user would then like to do with this from here is to Query this Datasource any way they wish.
They could create 3 different reports from it.
One could create a report to list customers by Country (e.g. WHERE Country = 'France')
One could create a report to list customers between a certain DOB (e.g. WHERE DOB BETWEEN x and y)
One could also create a report to list customers with a particular Surname (e.g. WHERE Surname = 'Bloggs')
Or even a combination of all 3
All these 3 samples could easily be achieved using the Datafilter property of the Customer Databand (as I'm filling my Datasource with a SELECT [columns] from Customer. But performance can be affected alot when this table has 100,000+ rows of data.
However I think what I need to do is to be able to apply the filter on filling the Datasource.
I should also point out that my Users have no knowledge or SQL whatsoever so they wouldn't be able to edit the Datasource and enter the WHERE clause. So this task will probably be somewhat difficult I imagine.
Regards,
Brendan
Thanks again.
I see what you mean by using Variables to put filters on the query.
However I'm not sure if this would work in my case. I probably haven't provided enough information in what I'm trying to acheive as I'm not so sure myself :biggrin:
Maybe a basic use case of what my user would be trying to do would help.
If I provided them with 1 Datasource for my Customer Table, I need to let my User report on this table any way they wish.
If we assume the table has some of the following fields:
Code,
FirstName,
Surname,
DOB,
Country
If I provided them with a Master Report to this Table I would maybe fill it's Datasource through StimulReport with a:
SELECT Code,FirstName,Surname,DOB,Country FROM Customer
What my user would then like to do with this from here is to Query this Datasource any way they wish.
They could create 3 different reports from it.
One could create a report to list customers by Country (e.g. WHERE Country = 'France')
One could create a report to list customers between a certain DOB (e.g. WHERE DOB BETWEEN x and y)
One could also create a report to list customers with a particular Surname (e.g. WHERE Surname = 'Bloggs')
Or even a combination of all 3
All these 3 samples could easily be achieved using the Datafilter property of the Customer Databand (as I'm filling my Datasource with a SELECT [columns] from Customer. But performance can be affected alot when this table has 100,000+ rows of data.
However I think what I need to do is to be able to apply the filter on filling the Datasource.
I should also point out that my Users have no knowledge or SQL whatsoever so they wouldn't be able to edit the Datasource and enter the WHERE clause. So this task will probably be somewhat difficult I imagine.
Regards,
Brendan
Creating Ad-hoc Reports
Also condition may look as following:
where MyExpression is a string variable with the where clause (without 'where'). Be carefull with DateTime and numeric format.
Thank you.
Code: Select all
select * from MyCutomerTable where {MyExpression}Thank you.
Creating Ad-hoc Reports
And since you could create this string in C#, your problem should be solved.
I myself, didn't know that fact, so I learned something new. :biggrin:
I myself, didn't know that fact, so I learned something new. :biggrin:
Creating Ad-hoc Reports
Thanks Marco, Edward.
I'll see what I can do from here :feelgood:
I'll see what I can do from here :feelgood: