Creating Custom Reports in TheBooks
: 13 Oct 2010
Have you ever run a report in TheBooks, and think to yourself, “This report would be perfect if I could just add this one additional column.” We’ve been listening! The latest version of TheBooks now lets you use TheBooks data with Microsoft SQL Server Report Builder to create and customize reports. You can integrate these reports into TheBooks Performance Reports interface. You can make the reports private, usable only by the person adding the report, or public, shared across your organization. You can add your custom logo and your company information, and format the report so that it is indistinguishable from any other reports generated by TheBooks. As with TheBooks standard reports, you can send these custom reports out in any format supported by TheBooks, and to any contacts you have defined within TheBooks.
Creating the Report
To create your custom report, you must first install the Microsoft SQL Report Builder application on your computer. To begin creating a report, open Report Builder and select the Table or Matrix icon.
You will select TheBooks as your data source, and then begin to design your query. You will select the entity you want to include in your report, and then the fields you want to include from within that entity.
Adding fields to your report is as simple as dragging them into the Drag and drop column fields section of the screen. You can also define computed fields to return information that is not available in an entity field. For example, to add net profit to our report, we would drag Change in OTE, Realized Gains, Charged Trading Commissions, and APS Adjustments into the formula bar, and then apply the appropriate operators.
You can arrange the fields into rows and columns, and decide if you want to perform any functions, such as total or average a particular field. The Report Builder allows you to add parameters so that you can select a specific set of data to be returned in your report. In addition, you can add filters at the data source, so that only the data that matches our parameters is returned. This allows the report to run more efficiently.
Integrating Your Report into TheBooks
Once you are satisfied with your results in Report Builder, you can then integrate your new report into TheBooks Reports interface. Go to Reports and select the Manage Custom Reports option. You will add the report to TheBooks from this screen, enter the Report Name, a Description of the report, and determine the Visibility, that is, whether it is a public or private report.
Your report now appears on the Performance Reports screen, under the Custom Reports tab. You can now select your Options, which have been determined by the parameters you created in Report Builder, and generate this report from within TheBooks.
The Finished Report
This is a sample of an Instrument Returns by Month report that was created in Report Builder, and integrated into TheBooks. Parameters were defined to specify an account, an account group, and a specific time period. Net profit was calculated for each market grouped by month and totaled by sector. The company logo and company information were added to the header and footer of the report.
Summary
I have shown how easy it is to produce custom reports from within Report Builder using the data produced by TheBooks. Creating the reports and integrating them with TheBooks Reports does not require any programming experience as all the necessary capabilities are provided by Report Builder and TheBooks.