title: How to create Template based User Customisable reports in Excel
body: <p>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. </p>
  
  <p>I want to design a solution for end user (who has knowledge of Excel &amp; 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. </p>
  
  <p>I designed this type of application using C# &amp; 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). </p>
  
  <p>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. </p>
  
  <p>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 &amp; few variable report parameters (which will come from database as per query), rest I will use from my earlier solution. </p>
  
  <p>I don't know </p>
  
  <ul>
  <li>How to define report parameters in
  Excel ?</li>
  <li>How to generate multi page
  report (when database returns dataset
  of more than one record) ?  </li>
  <li>How to
  paginate report like crystal report
  does when results are to be displayed
  on multiple page ?</li>
  <li>How to define
  Report header &amp; footer area in Excel
  to be displayed on multiple page
  report ?</li>
  </ul>
  
  <p>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# &amp; Crystal report). </p>
  
  <p>Thanks in advance for sparing yours valuable time, support and guidence. </p>
  
  <p>Regards. 
  Gopal Parikh. </p>