Using AOP to Export a Timesheet with Excel and Advanced Templating Features
.91a06b5.svg)
Wednesday, January 13, 2021
At United Codes we are using a small APEX application to keep track of our time spent on different projects. One of my first tasks was to integrate AOP, so that we are able to generate reports with an overview of hours based on a set of filters.
We have two options, an internal report and a customer report. The internal report contains an extra task name so it differs a bit from the external customer report. For now, we’ll focus on the customer report. When clicking the Customer report button, we want to export the same data as shown in the highlighted square, below:
The Excel file should look like this:
I started by creating a Dynamic Action with the following settings:
The Data Source is a SQL query which returns the data as shown in the highlighted report above. The page items which are used for filtering are included in the Affected Elements section. I won’t go into all the details of the query, but there are a few things I’d like to point out:
- The tags that you use in the template should be inside a “data” cursor. Mind the double quotes as this should be lowercase.
- If you want to reference other tags in your template in lowercase, make sure to add double quotes to all column names as well.
More information about writing SQL queries for AOP can be found in the SQL Structure section of the AOP documentation.
For the report in Excel, I wanted to group the data per project (without totals for the time being). After a quick look in the AOP documentation (Breaking into Groups), I ended up with the following syntax for my template:
{#TIME_ENTRIES|break:"PROJECT_NAME"} {break[0].PROJECT_NAME}
{#break}{MODULE} {DIMITRI} {JACKIE} {KEVIN} {ROELAND} {SUNIL}{/break}
{/TIME_ENTRIES|break:"PROJECT_NAME"}
This was my ‘customer_report_template’ in Excel. Because I wanted to have a white line after each project, I put the {/TIME_ENTRIES|break:"PROJECT_NAME"} closing tag on a new row.
After I uploaded the template to Static Application Files it was time to test! I hit the Customer report button and… got an error!
After a brief chat with Sunil, it turned out to be a conflicting loop, something specific for Excel. It’s also mentioned in the AOP documentation (Excel: Conflicting loops) and fortunately the solution was really simple. When using a loop in Excel, the starting and closing tag need to create a rectangle. So, I moved the closing tag to the last column.
Now I’m getting the output as expected!
Let’s finish this overview by adding subtotals for each member per project and a grand total at the bottom. Again, in the AOP documentation, there is a section about math operations during a loop. I’m using the new syntax as of AOP version 19.2:
- Subtotal: {break|sum:"KEVIN":true:0}
- Total: {TIME_ENTRIES |sum:"KEVIN":true:0}
This is the final template which generates the expected result:
APEX Office Print is packed with advanced functionality! It might take some time, but once you're familiar with the different tags, you can achieve great things. Happy reporting!
Kevin.
Author: Kevin Thyssen
Comments
Daevy
Thursday, September 16, 2021
Dimitri should work more hours ! 🤣
Bhavin Adhvaryu
Thursday, January 14, 2021
Hello Kevin,
Hope you well and safe.
Many thanks for providing 2 great blogs on AOP as Getting Started with AOP topic. This really help the developers who is new to AOP.
Further to this your first blog was giving good hands-on using Sample Database Application. Can you please put the similar hands-on using Sample Database Application. This would really help us to move along and learn more.
Your help and support would be appreciated.