Microsoft Excel was originally developed as an application for creating reports, calculating numbers and storing data. Its core strength lies in its capacity of letting users develop their own spreadsheets to keep inventory of data and information they want.
Excel is the go-to choice for analyzing business statistics and data. As a freeware, MS Excel is downloadable online free of charge. However, many users do not take advantage of its powerful features to create and customize more sophisticated reports.
This is where the Excel Report Builder comes in, which makes reporting easier and more detailed.
What is Excel Report Builder
The Excel Report Builder is a user-friendly report generator application designed for users who wish to create, customize and manipulate their reports. This tool enables users without programming experience to conveniently and easily transform reports in standard form, master-detail or columnar forms. It can obtain data from various databases and embed it in Excel’s report template files, execute SQL statements through query editor, and supports command line mode. It works with different MS Excel versions, from 7.0 to XP.
Here are some of the benefits of using the Excel Report Builder:
- Has a Graphical User Interface (GUI) design. This style environment enables you to visually design your reports within MS Excel. Having this interface means that there is a minimal learning curve and you can be operational with little delay.
- On-screen preview facilities.
- Saved reports can be printed and viewed as plain Excel documents.
- Having the ability to create mailing labels and columnar reports.
- Reports are constructed and printed quickly.
- Allows for page size to be scaled down automatically with the purpose of printing several report pages on each printer sheet.
- Supports the use of various sizes, fonts, colors and styles.
- Has Portrait or Landscape reporting features.
- Possibility of calling this tool from other programs (such as put_rep1.exe).
- No programming or any technical knowledge required to produce reports.
How to use Excel Report Builder
Using the Excel Report Builder is simple; you first need to install it before you use it for reporting. To install this program, you need to extract files in excelrep.zip to the directory, and run the setup.exe file from the directory.
To start the Excel Report Builder, click the program from the Start Menu on Windows. Following a few seconds, the program’s main dialog form will appear. The program has a menu of functions that consists of the File Menu, Report Menu, Options Menu, and the Windows Menu.
Under the File Menu, there will appear submenu items that will create a new report, open a previously created report, close the current report, save the current report, save the current report under another name, and exit the program. The File submenu are easily located on the button panel.
The Report Menu contains submenu items that enables you to edit the layout of the report and create a report using specified parameters in the layout.
This menu displays your newly-generated reports.
The Windows menu item contains cascade, tile horizontal, tile vertical and arrange submenu options.
Furthermore, the Excel Report Builder assists users in the construction of various types of reports such as Form Report (used for making notices, agreements, or references), Columnar Report (allows users to fill in multiple tables at once where they can be at various locations in document form) and the more complex Master-Detail Report.
To edit an existing report, go to the Report Menu, click “Open” and select the desired file with the *.PR extension (for instance: NAME.PR). Once you have changed paramaters, you must save changes by clicking on the “Save” button. Additionally, you may create an improvised report using the altered parameters even without saving them.
To edit the form report, just click on the “Edit Report” button. To generate reports based on existing parameters, press “Run report”. To end the editing process, press “Close” to return to the program’s main window.
Generating reports in Interactive mode
To run Excel Report Builder in interactive mode, start the program, then choose “Open” from the File Menu. Select your desired file with the .PR filename extension. Once you have loaded parameter files, press “Run Report”. The report will be created as specified by the parameters designated in the PR file. (The put_rep1.exe setup will automatically run). A message indicating completion will be shown after termination.
Generating reports in command line mode
First, enter the command: put_rep1.exe C:\BVISTA\NAME.PR to create a report. The C:\BVISTA\NAME.PR is the parameter file path name previously generated by the program. Then, the report will be generated specified by the parameters indicated in the aforementioned filename. A message indicating completion will be displayed following termination.
Using Excel Report Builder with Dynamics GP
The Excel Report Builder is part of a SmartList builder module that makes it easy to create new reports that can be rendered in excel with data from Microsoft Dynamics GP, which is an accounting software that uses various Microsoft SQL Server versions, from 2005, 2008 to 2012 to store data and information. This software is an accounting package obtained by Microsoft with Dynamics GP written in a programming language named Dexterity.
To use this program, open then navigate to the main window, go to the Microsoft Dynamics GP button, then click on “Tools”, “Smartlist Builder”, then “Excel Report Builder”. This is the only window you’re going to need to generate a new report using Excel Report Builder. You can incorporate your report data from data connections, tables from dynamics GP or other SQL server tables or views.
Hit the “publish” button to publish your report or you might want to go back and check permissions. Using this program allows you to have the ability of publishing it on the reports library within GP or automatically publish to a file.
All the reports will be based on the administration tab, where you can view all other Excel reports that come in with the system and the new ones you add on Excel Report Builder. Select a report and click to view. It’s going to open up in Excel and provide you the data you’re looking for.
Using Excel Report Builder with this program automatically creates the connection to the data and helps users save time and effort in putting together the information needed. Once this connection is established, for security reasons you can save it with Excel Report Builder and use it all over again. If you refresh the data, it’s going back to SQL server and retrieve the data.