Advanced custom reporting features [in-depth guide]
Guide contents: Report types | Modify | Layout | Groups | Calculated Groups | Columns | Calculated Columns | Filters | Settings | Saving | Copy | Example custom report | Search options / Use folders | Delete a custom report
Workamajig offers a very robust and customizable reporting system. There are three types of reports that we call listings, standard reports, and custom reports. This guide will tell you about the different ways you can customize many of the listings and reports.
Report types
Views
These are screens such as the views with any of the Today pages, such as projects or client invoices. Each of these screens comes with pre-defined views. If you have the right to modify and memorize reports, then do be aware that any changes you make here will be seen by all other users who access these views.
Standard reports
These are found by going to the Menu > Everyone > Report center > Standard. In the different folders, you will see reports with a clipboard icon that has a white top. These are our standard reports and many of them are not customizable since they touch multiple areas of data and have detailed calculations that take place within the report. But do offer a wide range of filters when running the report.
Custom reports
Custom reports are broken out by what we call Datasets, and access to each dataset can be controlled by security group rights. Custom reports can be accessed by going to Menu > Everyone > Report center > Custom. This will open a new screen with the dataset you have access to. Once you create a custom report, it will show in the Other section by default and have a clipboard icon with a black top.
Graphs
Each role in the main menu has its own Graph pages in the system. The graphs themselves use the custom repot datasets as the backend of their data and functionality. Its best (and sometimes easier) to prove out your data and results using a custom report prior to trying to graph it.
NOTE: Datasets do not follow the individual rights established in the security group settings.
Modify
The Modify button is at the top of the report next to the print button. This is how you access the layout of the report.
You can modify a standard listing or create a new listing option by selecting the Modify button on the screen. You can then change the name of the listing option to a more descriptive name. When modifying a custom listing option, there are up to four separate tabs for the layout, columns, grouping, filter, and advanced.
When you customize a listing option, you can change the field columns in the list, the sort order, subtotaling options, and the conditions used to search for records. You can also control which groups of users can use that listing option or make that listing option private to your login.
Layout
The Layout tab is where you will find the Groups and Columns.
Groups - When added, you will see a grouped row that is collapsable in the report.
Columns - This is the main data shown in your report. The repot will display one row of data based on the columns added.
Group Rows Together - Wen checked, this will sum the rows of data together based on how all the columns are grouped. This option is only available in Custom Reports and not in Views or Standard Reports.
NOTE: You will need at least one Column in order to Save the report.
Groups
Click + Group.
The Grouping tab allows users to set the fields to group or subtotal by. A common use of the grouping option would be to group projects by client name. When you group by a field, the system will automatically sort on that field first. You can create as many sub-groups as you want on a listing option.
Available columns: This is usually the same list that is shown for the columns. And allows you to choose what variable to group by.
Calculated Groups
Add a calculated group: This allows you to create one grouping of labeled data. It helps reduce clutter stair-stepping) if you have multiple groups with the same end-line result.
- For example, you can combine two fields as one, such as client name and billing contact:
- Which will produce an end result of "Client Name - Contact Name".
- Or another example where you get a bit fancier with the result:
- Which will produce an end result of "Client = Client Name / Contact = Contact Name".
- The ## symbols allow you to enter data in between them that the system will ignore and display as plain text.
Automatically expand groups: The automatically expand groups option, when checked, will always expand the listing options when the listing is using grouping fields.
Column: Shows the name of the actual field.
Caption: This shows the name that will be at the top of the report for that data. Typically not visible in the report itself, whereas the column's caption above is.
Subtotal: The subtotal option is ignored for non-numeric fields and can only subtotal numeric fields in that listing option. Grouping fields can also be sub-totaled. You may group a listing by client name and project type but may only want sub-totals by client name and not by project type, for example.
Page break: If this is checked, then the PDF you create from this report will start a new page each time the data comes across another grouping of that data.
Color: The color option for each grouping field can be set to show the grouping rows in a different color.
Columns
This is where you can adjust the field columns, add calculated columns, and set locked columns.
Available columns: The Columns tab allows you to customize the fields that show in the custom listing option. When you select the Columns tab, the list of available fields can be selected and moved to the selected side of the screen and ordered the way you need to see the field columns.
Add a calculated column: This allows the user to name a field column that calculates an amount based on other available numeric fields in that listing option.
Locked columns: This option allows the user to specify the number of field columns from the left side that can be locked so that when users scroll to the right, those field columns stay visible on the screen.
Use advanced grouping options: (only available in custom reports). Unchecked by default. This column allows you to bring "like rows" together into a single line by way of the Group by settings.
NOTE: When using this feature, all of the underlying information is still there. You are deciding to create a "displayed" version of the information.
Column: Select the column you want to display from the Available columns list. The display order is top is the first/left column when displayed/printed.
Caption: Enter a descriptive name for the field. This is the name that will appear at the top of the listing. You may want to shorten the name to fit better if the display width is not very wide. The name here can be useful if you are exporting the data from Workamajig into another application for ease of importing on that side.
Word wrap: If this is checked, then the values in that field will make the row expand to show all of the data entered.
Print width: Defines the width of the column when printing. This defaults to 1 inch. This roughly corresponds to the display view. The width is measured in inches. The system will approximate an inch on the web page as 72 pixels per inch (i.e. The system sets the width of the column to 72 * number of inches on the web page).
Sort: Drop-down box. This allows you to define whether the sort for the column will be in ascending or descending order.
Sort order: Set to O by default. This defines which column will be used to sort by and in what order. If all are set to O, the top/first column will be used and is based on the alpha-numeric sort pattern. You do not have to select a sorting option for each field. You also have the option to re-sort the list while viewing it by clicking on a column heading. If you have added groups to the report, the list will always sort first by the report groups and then by the other fields selected to sort on. You can select more than one field to sort the list.
Group by: Only available in custom reports (this excludes listings and many standard reports). Must check Use advanced grouping options as described above.
- Group: Will allow a single row per selected column. This is typically used for grouping all users or project info into one row. This should not be used with a number field.
- Sum: Most common use of this feature. This will sum up the "number" column. This should not be used for top-level numbers that may already be summed.
- Average: Will average the number column.
- Count: Will count the number of entries. Useful for counting the number of projects, etc.
- Maximum: Will display the highest amount from all entries in a number column.
- Minimum: Will display the lowest amount from all entries in a number column.
Subtotal By: When check, the default is Sum. But you can choose from Average, Min or Max.
Calculated Columns
From the columns tab, there is an option called Calculated column. From here, you can create a simple (1+1) calculation.
NOTE: You cannot make a calculation of a calculation, meaning the calculations run off the results of the report and do not calculate as part of the report for which another calculation can be made from its results. Also, note that line percentage calculations DO NOT subtotal properly at the grouping level. For more complex calculations like these, you can export the data to Excel by clicking the Excel button in the upper left of the screen, then make your complex calculations there.
Creating the calculated column
When you modify a report or listing, there is a tab called Columns. From this section of the layout, there is a button called Add A Calculated Column. Upon clicking this button, you will need to choose a Caption, Data Type, and the desired calculation. On the left will be a list of available columns that you can bring over to the calculation area.
From the Layout, click on + Calculated Column.
You will then see a screen like this.
Caption - Enter a name for your field that will be visible in the header of the report.
Data type - Number, Text, or Date.
Depending on what kind of calculation you are doing, you have the option to choose a specific Data Type. This will determine how the field will behave in reports. The most commonly used is Numeric and is the default when you create a new calculated column.
Formula - This is where you enter the calculation based on the Available Field Names and simple math equations.
Example Simple Calculations:
If you are simply trying to get a total of two or more available fields, you can bring over the first field into the calculation area, then just add a + after the field. Now bring over the other field(s).
You should get something similar to this:
[Total Labor] + [Total Expenses]
There is no need to add an equal sign after this as it will calculate just how you placed them. Also, you can use (rounded brackets) around the [square brackets] to calculate other data first, similar to algebra.
For example:
[Total Gross] / ([Total Labor] + [Total Expenses])
Then if you get errors with the above, you can try the following:
case when [Total Gross] is null then o else [Total Gross] / ([Total Labor] + [Total Expenses]) end
If you are looking to do a more advanced calculation, please contact support@workamajig.com for guidance. However, please note that we do are not exclusive report writers.
Calculate After The Main Query - The purpose of this option is so you can calculate the sum total of a columns row value when Group Rows Together is checked, including other calculated columns.
NOTES:
- This is for advanced users.
- This option is only available in custom reports and not in views or standard reports.
- You cannot use Calculate After with other Calculated Columns that are also checked as Calculate After. To get this type result, you will want to include the formula of that other columns calculation combined into this one.
Sort - Ascending or Descending. Basically saying do you want A or 1 at the top, or Z or -1 at the top.
Sort Order - This will determine which column will be the first to sort by when you run the report. If blank, then the first column will determine the sort alphanumerically. This also helps if you want to establish a multi-sort.
Group By - Group, Sum, Average, Count, Max or Min.
Subtotal - When checked, this will display a subtotal for the column.
Subtotal By - Default is Sum. But you can choose from Sum, Average, Max or Min.
Print Width - Only effects the width when printed to PDF. Does not control what you see on screen. The idea being you want to set all the columns to not exceed 8.5 inches width total.
Available Columns - Lists out the available columns that be can included in the Formula.
Filters
There are two kinds of Filters:
Add a new filter - which will filter the data before the report is run.
Calculated Filter - which will filter the data out of what was run.
Here is an example of a typical filter used for time reporting, where you want to be able to choose the date range of the time entires.
This is where you can change, remove and add 'Search Options' to the listing/report. You can add as many different conditions to a listing as you want. You can either enter the values onto the report or you can set up the condition to prompt the user on the listing screen.
Available columns: Typically the same list from your columns tab. Allows you to bring over a column by which to filter your result.
Show advanced options: This adds the brackets "(((" and ")))" options into play. Think of these like algebra when using them.
(((: You have three levels deep of using brackets. These allow you to group a set of options together. Especially useful when using the "or" segment.
Column: Name of the actual field being used.
Condition: Determines how the field is to be filtered. Depending on the type of data (i.e. Date, Numeric, Text) you will see a limited set of the following variables:
- Equal To
- Not Equal To
- Equal To My Name
- Between
- Not Between
- Any Of
- Greater Than (Numeric Specific)
- Less Than (Numeric Specific)
- Greater Than or Equal To (Numeric Specific)
- Less Than or Equal To (Numeric Specific)
- Begins With
- Does Not Begin With
- Ends With
- Does Not End With
- Contains
- Does Not Contain
- Is Blank
- Is Not Blank
- Is After (Date Specific)
- Is Before (Date Specific)
- Is On or After (Date Specific)
- Is On or Before (Date Specific)
- Is On or After Today (Date Specific)
- Is On or Before Today (Date Specific)
- Month To Date (Date Specific)
- Year To Date (Date Specific)
- Today (Date Specific)
- Yesterday (Date Specific)
- Last Week (Date Specific)
Value 1: If the filter does not have the Prompt checked, then you typically need to supply a value here. This is a great way to have the report pre-filtered so you do not have to enter the value in the Search Options.
Value 2: Typically used with the Between condition. To have it between two dates, or two values.
Prompt: If checked, this will make it a user-receptive filter on the front side of the reports Search Options. You must also supply a Prompt Label.
Prompt Label: If Prompt is checked, then you need to label the filter. Typically it will be the same as the Column name, but you can make this suit your need or give a brief description of the filter.
))): Used for the closing of the bracketed data.
and/or: Allows for numerous ways to filter your reports. The thing to note is that when a report is run, it goes through the filters, starting from the top down. It's easy to get messed up with your filters here. So feel free to experiment with the filters until it works the way you want.
NOTES:
- Based on the type of Condition you select, you may only be able to enter values for one, both, or none.
- The condition of "Contains" tends to be the best for doing more general searches vs. having to know the exact spelling and spacing of the value.
- Select the Prompt Label to allow the user to enter a value prior to running the search. This is useful if you want someone to be able to search by a specific value. Rather than have them modify the custom listing each time, you can have them enter the criteria on the listing page. If you select this option, you must enter a prompt text. This is the text that they will see on the listing page telling them what kind of criteria to enter. If you were allowing them to search on Project Number, then the logical prompt text would be "Project Number", for example.
- If you want to create a more sophisticated search that may require additional criteria such as OR statements and bracketing of conditions, then advanced options allow you to create complex Where clause options that create a SQL Where condition. If you are not familiar with SQL, contact support@workamajig.com for assistance. Most situations will not require using advanced conditions.
Settings
The Settings tab contains many of the options for printing and security.
Title: In the Title text box, enter a descriptive name for this listing option. In the Printed Sub Title text box, enter a subtitle that you would like to appear directly below the printed title. You can select alignment options using the drop-down box to the right of the printed subtitle text box.
Title Alignment: In the Print Orientation drop-down box, you can select the orientation of the printed PDF file.
- Align Left, Align Center, or Align Right.
Sub title:
Sub title alignment:
- Align Left, Align Center, or Align Right.
Orientation: Determines who the report should print to PDF. While the margins are not customizable, you can set the Print Width of the Columns to fit within either 7.5 or 10-inch widths respectively.
- Portrait or Landscape.
Show search conditions: If this is checked, then the filters used for this report will be displayed at the top of the PDF printout. This is most useful to replay what the data being shown is in regards to.
Data set: This shows what custom report dataset this was based on.
Display in group: Custom Reports Only. Allows you to choose what Report Group you want this custom report to show under.
Owner: Designates who is the main user of this report. Defaults to the person initially creating the report.
Private: If this is checked, then only the Owner and any Administrator of the system can see and access this report.
Visible to: Select the Security groups (all users within those groups) that you would like to be able to view this custom listing. By default, all of the security groups are given access to view the listing.
Saving
When done making changes to the reports layout, click Apply Changes at the top.
This will take you back to the report, at which point you can run the report to ensure your changes work and do not generate an error.
If all is good and oyu want to fully save your applied changes, then click on Memorize from the top of the report.
This will overwrite the custom report of the listing you have open with the changes you have applied.
Copy
From the Modify screen, you can click More and Copy View.
Example custom report
I want to see how many hours I worked on each project and using what service. I will create a custom report using Menu > Reports > select any group: top right corner click New Custom Report > Traffic > Time Detail Data.
- Add the columns: Project Full Name, Service Description and Actual Hours Worked
- Click on the Grouping tab
- Add User Full Name.
- Click on Filter.
- Add User Full Name and change the Condition to Equal to My Name.
- Click Apply.
- Click Screen.
- You will see every time entry I have made in Workamajig.
- Now we will use the Advance Grouping Options and the Grouping tab to see if I can get a better-looking report.
- Click Modify.
- From the Columns tab, check "Use Advanced Grouping Options". Change the Actual Hours Worked column to Group By: SUM.
- Now to get a total of all hours and add color to the group, to make it easier to see.
- Click on the Grouping tab.
- Check the Subtotal box and click into the Color box and select yellow.
- Click Apply.
- Click Screen.
- We should see that it did group and provide a total for each Service Description. But we still see that the Project Full Name is repeated. This is because the grouping function is looking to group like things. The Project Full Name is the same, but the Service Description is different, so Workamajig is only able to display groupings to that level.
- To have the project show up only once would be better. So we have a choice. We can remove the Service Description column, this will allow the Project number to group into 1 row. Or we can use the Grouping tab to group by User Full Name and Project Full Name. Since we want to see the Service Descriptions, for this we will use the later choice.
- Click on Modify.
- Click on the Columns tab.
-
Highlight the Project Full Name line and clicked the icon to remove the line.
- Click on the Grouping tab.
- Add the column for Project Full Name, check the Subtotal checkbox and add color.
- Click on Apply.
- Click on Screen.
There we go. We now have a report that provides me with the total work done. We can also see how much was done on each project and what service the time was put towards.
I will now click Memorize > New Report to save this report for future use.
Search options/use folders
The search options are based on what filters are in use for this listing/report.
Some listings also have the ability to use folders. These are for the contact management section only; such as leads, companies, contacts, and opportunities. These are enabled in System Setup and are only needed if you want further control of what records users have access to, beyond what is capable with security group rights.
Delete a custom report or view
In order to delete a custom report or listing, you must have the security right to modify custom reports and or custom listings.
1) Click Modify
2) Click Delete Layout/Report
3) Confirm Deletion
By doing this, you are not deleting any data from the system. You are only removing a report.
NOTE: There is no Undo. You will need to recreate the report/listing if it was inadvertently deleted.
Export a custom report or view
Please refer to the Importing custom reports for more info on exporting and importing custom reports.