How to create Template based User Customisable reports in Excel

Question

How Excel can be used for generating End user customisable reports. Data needs to be fetched from oracle / SQL server database based on some query and reports needs to be generated in Excel.

I want to design a solution for end user (who has knowledge of Excel & database query writing) for Report generation as per his changing requirement, so that he has no dependancy on development team for day today modification in report requirements.

I designed this type of application using C# & Crystal report earlier, which requires three things : 1. Crystal report template having database query 2. filter criteria (on which data will be read from database). 3. Dynamic User control (e.g. text box, drop down etc) generation component as per filter criteria Control required (defined in database table by end user).

If End user is acquainted with Crystal report designing then he can design his own crystal report template along with database query embedded in crystal report only. My software generates dynamic user controls as per defined in database for that report template. These filter criteria are passed to query defined in Crystal report so that desired report can be generated as per his filter criteria. This helped him in catering day today reporting related changing requirements.

Now I want to design similar solution using Excel i.e. replacing Crystal report template with Excel template. My initial proposal is like this that I will replace crystal report with excel template having few fixed parameter & few variable report parameters (which will come from database as per query), rest I will use from my earlier solution.

I don't know

• How to define report parameters in Excel ?
• How to generate multi page report (when database returns dataset of more than one record) ?
• How to paginate report like crystal report does when results are to be displayed on multiple page ?
• How to define Report header & footer area in Excel to be displayed on multiple page report ?

I know that these things can be done using macro programing(defined in that Excel report template) but that will not be easy for end user, I want to have a simple generalised solution which I can implement once in my c# program so that end user (having little excel knowledge) can design / modify report as per his need without having much trouble of programming, like I did in my previous solution (c# & Crystal report).

Thanks in advance for sparing yours valuable time, support and guidence.

Regards. Gopal Parikh.

Answer 1

This will be a very challenging project, mostly because Excel isn't really a reporting tool the way that Crystal Reports is. I've created a great many reports in Excel, and every one of them has been a custom solution, tailor-fit to the users needs. Creating a generic reporting tool in Excel will require a lot of 'gap-filling' in Excel's feature-set.

How to define report parameters in Excel?

There really aren't report parameters in Excel, at least not in the same way there are for Crystal Reports. If you plan on using C# and VSTO, then you can create a form (or use the task pane) for the user to input parameters. You could also try using VBA/VSTO and use the native Excel data objects (QueryTables, ListObjects and PivotTables), which may provide you with some pre-built options for parameters. If you want the user to be able to create their own reports, then you'll need to create some kind of report wizard to help them set up and manage these 'parameters'.

How to generate multi page report (when database returns dataset of more than one record) ?

How to paginate report like crystal report does when results are to be displayed on multiple page ?

Both of these are similar problems, if I understand them correctly. All of the pagination features you see in Crystal Reports (e.g., "keep section together", "repeat headings", etc.) will need to be coded in Excel, because they just don't exist. Because Excel doesn't really have anything analogous to Crystal Reports' groupings, this would be a challenging issue to tackle, I would think. How do you define a region in Excel as something you would "keep together" when printing on a new page? How do you add blank spaces at the end of the previous page (Excel will typically try to scale the size)? The object model is there, and it certainly seems doable, just really time consuming and hard.

how to define Report header & footer area in Excel to be displayed on mulitple page report ?

This isn't actually too hard, as there are page header and footer objects you could use. You would have to manually update the values however, as you typically can't have 'dynamic' information there (no formulas, no data objects, etc.). You could also use the "Rows to repeat at top" if you needed more flexibility.

In general, Excel is really the wrong tool for this kind of work. Excel can be a powerful reporting tool, but generally takes someone pretty knowledgeable in Excel, and some of the features are just not there even for the power users (without writing some hefty VBA).