The following was originally taken from a series of posts on the Axosoft Training/Support Blog. You can find these posts and many more at: http://community.axosoft.com/blogs/trainingsupport/
Custom reports can be broken up into three distinct sections that you’ll want work on before moving onto the next.
- Building your dataset: This is really only important if you’re going to be creating reports from scratch under Other Reports but it also affords you a ton of flexibility. Creating any other kind of report in OnTime will automatically allow you to build your report off of the dataset provided by that report type; this will let you use grid filtering instead of targeting your data through shortcuts or separate reports. You’ll need some familiarity with SQL in order to get the most out of this section.
- Designing your report: This is the fun part, once we have our dataset built we can start laying fields on our report and putting our data together in a logical manner. When I cover this section we’ll talk about some key Active Reports concepts and talk about how to segment and group your data so that it makes sense in your report.
- Scripting: The reporting engine that OnTime uses allows you perform some scripting in the background, this makes our reports crazy powerful but just like building your dataset it requires you to bring some skills to the party if you really want to squeeze your reports for all they’re worth.
Before we get started with the above three sections I want to throw out some resources for you to peruse.
So the most important part of any report is going to be the data you are reporting on. The good news is that most of the reports in OnTime do the heavy lifting for you, just make sure that all of the data is in your grid and you’re good to go. This also works for most custom reports as well, since a defect report uses the same stored procedure to pull data for the built-in defect reports as it does for custom defect reports it knows how to pass parameters to sort and filter correctly.
Things get a little bit more complicated when you need a more complex dataset however, for instance if I wanted to join in some history about my item, like how many times it’s been pulled out of its fixed state for additional work. In this case you’ll need to venture outside of the normal report types that build your dataset for you and step into the strange world of Other Reports.
Okay so maybe that was a bit melodramatic, Other Reports aren’t so different than any other type of report, they just let you build a report off of your own query, which means you can join your own tables, sort and group, perform math functions and all kinds of good stuff. Be warned however, with great power comes great responsibility, you’ll want some familiarity with SQL if you want to build these kinds of reports.
This is a good point to stop and talk about Axosoft’s stance on supporting custom reports built off of custom queries. We’re always glad to point you to the right place in the database and we know it like the back of our hand, but we won’t be able to write SQL queries for you.
So getting back to the subject at hand, let’s build a report off of our own query. First things first we’ll write a query and the best place to do that in my opinion is in management studio:
Once your query is built, be sure to run it to make sure there aren’t any errors and it’s returning the information you want, copy it to your clipboard and jump into OnTime. Inside OnTime click on Reports>Manage Reports and click on Other Reports and then create a new report. Before the reports designer will open allow you to do anything else it will ask you to input your SQL query. Go ahead and paste from your clipboard and hit OK.
Each column returned by your query will now be a field that you can drop onto your report with the reports designer.
So now that we know what data is going to be in our report we’ll want to know how it’s going to be presented to our end users in its report. To understand that, we’ll need to know how the report is built. A report in OnTime is broken up into different sections, these sections allow you to segment the data in your report, you can create a header and footer for the entire report, each page of the report or a group of rows returned. There will also be a section for the actual detail of each row returned. The OnTime Window’s user’s guide explains this a little bit better so allow me to copy/paste.
- A report section contains a group of controls that are processed and printed at the same time as a single unit. ActiveReports defines the following section types:
- Report Header: A report can have one report header section that prints at the beginning of the report. This section generally is used to print a report title, a summary table, a chart or any information that needs only to appear once at the report's start.
- Report Footer: A report can have one report footer section that prints at the end of the report. This section is used to print a summary of the report, grand totals or any information that needs to print once at the report's end.
- Page Header: A report can have one page header section that prints at the top of each page. It is the first section that prints on the page except when the page contains a report header section. The page header section is used to print column headers, page numbers, a page title or any information that needs to appear at the top of each page in the report.
- Note: Bound controls in the PageHeader or PageFooter are not supported. The data in such controls may not be in synch with the data displayed in other sections on the page.
- Page Footer: A report can have one page footer section that prints at the bottom of each page. It is used to print page totals, page numbers or any other information that needs to appear at the bottom of each page.
- Group Header/Footer: A report can consist of single or multiple nested groups, with each group having its own header and footer sections. The header section is inserted and printed immediately before the detail section. The footer section is inserted and printed immediately after the detail section.
- Detail: A report has one detail section. The detail section is the body of the report and one instance of the section is created for each record in the report.
Some of the above information was created by Data Dynamics. The original work can be found here
When you’re creating a report from scratch you’ll have three sections to work with, a page header, a detail section and page footer, you can right click on the area of your reports designer to drop in another section. Most of these are self-explanatory just by name but grouping requires a little bit of configuring to use correctly. Groups are pretty simple so long as you are using the same group criteria every time in this report, simply highlight the group header and change the DataField property to whichever field you want to group by. If you need to do something dynamic with grouping in your report things get a bit more complicated and you’ll want to read through the section in the User’s Guide about grouping data in a report (It’s under Reports>Scripting>Grouping Data).
Now that your report is structured correctly you’ll want to drop your data fields in place so you can start designing things. Things are pretty intuitive from here, just grab a field from the tree menu of your report and drag them into place in the designer. Fields from your query will be listed under Fields>Bound in the tree menu of the reports designer. Bound fields are the columns of data that are returned by your data set (which we covered last in the previous post).
Once you get everything in place you’ve got a report, but we’re not quite done.
Now we come to the final bit that needs to be in place for a custom report, scripting. Scripting in OnTime’s report designer is actually pretty simple provided you have some basic knowledge with .net framework. Scripting gives you that extra bit of leverage you need to make your reports exactly what you need them to be. With scripting, I can do things like creating dynamic grouping options, change attributes of items in the report, create calculated fields that I can drop into my report, parse html tags in large text fields and a host of other things.
To get started I’ll click on the script editor icon of my custom report in the reports designer.
Once you’re in here it’s good to be familiar with some concepts about the reports themselves, the first place to look is going to be the section of the OnTime Window’s User Guide called “Active Reports Concepts” but you probably read over that when I posted the previous blog, now you’ll want to check out the Scripting Section immediately below it.
After reading the docs, the best way to get a feel for how to implement your own scripting is to make a copy of one of the built in reports and dig into it, very often you can repurpose that code to fit your needs.
Beyond reading the docs and digging through the built in reports I also recommend stopping by the forums and asking any questions you may have. Because it involves custom code, support can’t provide specific help for custom reports scripting but we do try to be as helpful as we can on the forums.
So I’ve got my scripting written, the next thing I’ll want to do is preview my report to make certain everything looks alright and I’m not seeing any errors, if all is good, save your report and you’re good to go.