End user reporting w/FlexCel. Feasible?

FLX TMS FlexCel for VCL & FMX
1

One nice thing about Fast Reports & like solutions is the report can be designed outside the app and be self-contained, i.e. query, design, and parameter prompting are all in one file. The file can be loaded and run at run-time.


All the parts seem to be there for FlexCel to do the same, from data acquisition to design. What's not clear is if it includes a way to prompt the user for parameter values.

For instance, choose store location from a list and set a date range for a sales report. The app could include a form to prompt to get these values, but I'm looking for a plug-n-play solution. Compose report outside app, drop in a directory, and app knows what to do with it.

Is it possible to use FlexCel as a substitute for report solutions?
2
It is indeed possible, even if it takes a little of effort that a "premade" app wouldn't need. I actually developed long time ago an app that is just that. (I should someday clean it up and publish it, but I never get the time). This particular app was actually ASP.NET, but it should work the same as delphi. It has been years since I don't see it, but last time I saw it it was kind of a monster with some hundreds of reports... that's the "problem" when you let users do what they want :)

But well, I can give you some ideas on how it works:

1. The users create the templates and put them in folders and subfolders. From there, the app picks them up and shows the available reports in a treeview

2. The data is all extracted with DirectSQL. In DirectSQL you can have parameters, which are read from a config file, which is also an Excel file. So to add a new report to the app, the user creates 2 files:
My Report.xlsx
My Report.parameters.xlsx

Here is an example of the parameters file (sorry, in Spanish since the app was in Spanish for Spanish users)

Basically, here we have 4 parameters, named mindat, maxdate, mytime and myformula (this is the names you will use in the DirectSQL query). Some are of type date, one is a time and one is a formula)
the 2 first parameters will be used in DirectSQL, so you can do a "select * from where date >= @mindate and date <=maxDate". The other parameters will be added as expressions to the report, so you can use them in cells to calculate values.

then there is a description of each parameter. This will be used in the dialog the app uses to ask for the parameter. Depending on the type, it will show say a datetime picker for a date, or a combobox for a list.

Finally you have a list of valid values, so for example the user doesn't enter a date more than a year from now. and the last column is if the parameter is required or not. (if it is required, the app will ask you to complete the value).

Here is an example of how the app would look like (very old screenshot, this app is more than 10 years old...)


As you can see, there is a list at the left (7) which shows the available reports. Those reports can be nested in subfolders.
Then at the right, you can see how it asks for the parameters of this report: A start and end date. There is also a button to see the report.

Here is another image with one parameter of each kind:



And well, that is it. The app had also a monitoring module with some other "alarms.xls" file (yes, the app is from before xlsx existed), where you could define some variables which if passed some threshold, FlexCel would email you to warn you. It could also define programmed report which would run at definite dates/time and be emailed to users. the nice thing about all of that it that I just made a completely generic app, the alarms, programming the reports, or the reports themselves were all defined by the users in Excel files.

As said, this isn't as simple as having FlexCel automatically ask for parameters, but to be honest, I find the "pre-made" approach kind of restrictive. How would FlexCel ask for those parameters? In a win32 dialog? And if this is a web app as in this case? Or if the app is themed and the win32 dialog is just ugly? Or if you want to have some more stuff in that dialog? (like in this case, where we show the parameters in the same page where you can choose more reports, not in a modal dialog).
And it really isn't that much work, if I remember correctly, creating the parameters.xls definition and some code to read that file and auto-create a form to enter them was a thing of days. But I had a control over what I could do that wouldn't have been possible in a canned approach.

3

Thank you for the very complete answer. I agree w/FlexCel flexibility and capability. I've used in several project. Never made the jump to reporting, but love the idea.


When it comes to prompting the user for parameter values, you're in agreement with creating a form dynamically based on the parameters spreadsheet. Correct?

For a parameter's possible values, is it possible to specify DirectSQL for the values, e.g. 'select * from StoreLocations'?
4

Yes, the idea would be to create a form dynamically based in the parameters.


For the DirectSQL, not the full SQL, as that would be too big of a security risk. Imagine your users sending a "Delete from StoreLocations" as parameter.

What you normally do is to define the SQL in the template, but customize it with SQL parameters. The users creating the template should be more "trusted" than the final user, so they shouldn't write malicious sql. (even so, you need to set up the most restrictive permissions you can, sometimes it is not malice, but an error that can wipe the dataset or publish private data).

What you would do would be to write something like

select * from StoreLocations where date > @date

in the template, then use FlexCel.AddParameter to set the @date parameter from your dynamically created form.
5

These end users are DB developer level users creating end reports for the software end users.


Scenario I'm describing is like a sales report where the user is prompted to select the store location from a data driven list to report a store's sales.

Report: select sum(Sales) from StoreSales where StoreLocationId=@StoreLocationId
Store Location Param Possible Values: select StoreLocationId, StoreName from StoreLocations

Looking to implement the equivalent of this in SSRS:

6

7

I posted an image of SSRS Report Parameter Properties window, the Available Values section. It appeared in the editor, but doesn't appear in the post.

8

I haven't seen the second post, but, if you really want to put the full SQL in the db, that is possible too.

You would have to do a 2-stage run: First open the template with the API, and write the DirectSQL statements from the db into the config sheet of the template. 

xls.SetCellValue(row, 1, sqlFromDB);


After that, you the second stage is to actually run the report.