Microsoft Excel offers intuitive tools that make it easy to access, analyze, graph and distribute critical data. ESSA has developed a custom reporting tool to leverage these benefits — InExcel. InExcel is an interface which combines the power of parameterized relational database queries with the graphical and analytical features of Microsoft Excel.
Understanding InExcel reports:
- InExcel report – at least one or more queries
- one or more template Excel files* (base format, e.g., with resident graphs)
- one or more VBA macros (to perform additional processing)
- Query
- one or more parameters
- a destination in a template file* (worksheet, cell range)
These queries are run sequentially and the results displayed “in Excel,” where they can be formatted and graphed for analysis or distribution (below).
Multiple panes to manage queries:
Fig. 1 (above): ESSA’s InExcel interface. The upper left pane displays the list of available reports for the selected database. The upper right pane displays the list of queries associated with the selected report. The middle left pane (static) provides 2 topics: (a) destination settings and (b) parameters. The middle right pane provides the current values for these topics, for the selected query. Lastly, the bottom panes provides a view of all the primary database table contents in the database, which is used to locate the desired parameter values.
Fig. 2 (above): Specify parameters easily by right-clicking the parameter topic. The bottom-two panes are used to find specific parameter values.
Fig. 3 (above): Specify destination settings by right-clicking the destination topic (i.e., the location of any workbook to be used as a template, the target cell to paste query results, and whether to run a macro after query results have been pasted into Excel).
Analyze and/or distribute results “in Excel”:
Fig. 4 (above): Example results. For this report, the results of different queries are placed on their appropriate worksheet (as determined by each queries destination settings). Resident tables and graphs are updated according to the new query results.