Today we’ll walk through how to create an Ad Hoc Jaspersoft Crosstab Report. Our report will show allocations and assignments by project, filtered on status, stage, and portfolio.
In recent Jaspersoft posts, we’ve shown you how to start your ad hoc report from scratch , email Jaspersoft reports to anyone, create an active project report in CA PPM, and add status indicators to Jaspersoft reports. Today’s tutorial comes from Atul Kunkulol, who shared this trick in Part 1 of our Free Jaspersoft Webinar series.
To get in depth training, join Rego at regoUniversity in Tempe, April 25-27.
CA PPM | Create a Crosstab Ad Hoc Jaspersoft Report
1 | Start with Data
From Advanced Reporting Home and Ad Hoc Views, click on Create. Under Select Data you’re faced with the choice of two options: Project Management or Resource Management Data. The one you pick is dependent on the type of report you need.
Both Project Management and Resource Management have data for allocations and assignments, but stage isn’t available in Resource Management data, so we’ll choose the Project Management folder.
|Choose the Project Management Data Warehouse folder to get access to Stage data.|
Now in the Data Chooser, it’s the same drill we used previously. Choose the source you’re looking for by dragging the Projects folder into the Selected Fields, and press OK.
It’s best practice to choose the actual data folders you’re looking for, instead of taking the whole folder, but due to overview purposes, we’re operating generally.
2 | Select Crosstab and Calendar Period
Last time we made a table in Jaspersoft. This time we’ll create a Crosstab report that shows Allocations and Assignments by Project. And we’ll filter it by Status, Stage, and Portfolio, and limit the report to six months, since nobody wants to look at old stuff.
There are three different types of reports: Crosstab, Table, and Chart. Choose Crosstab from the dropdown next to data type.
With the Crosstab report, we want to see Calendar Periods. So find that option from the Fields column on the left, and select the “Month Start Date” by dragging and dropping the selection onto the Columns row of your New Ad Hoc View.
The Ad Hoc report takes shape.
3 | Filter to Next 6 Months of Data
To limit the data to six months, right-click on the Month Start Date box within your column’s row, and select Create Filter.
This will make the Filters box pop up on the right-side of the screen, and in this case we’ll select the Equals down carrot and choose “is between.”
The box pops up with red invalid dates. To select data between our current month and the next six months, fill in the top red row with “MONTH” and the bottom row with “MONTH+6,” and Apply.
Our report responds immediately.
Tip | Adjust Visible Measures
To view more Measures, move the Measures column up the screen by clicking and dragging the hamburger icon to the far right of the purple Measures’ header.
4 | Measures by Month + Team Allocation Hours
Here’s something to keep your eye on. If you want measures by month, you should get the data from a monthly calendar. DON’T go to Measures > Projects > Summary Totals > Project > Total Hours.
INSTEAD go to Measures > Calendar Periods > Project, and select Measures from there.
From Measures > Calendar Periods > Team > Hours, select Team Allocation Hours and drag and drop them into the Rows field on your New Ad Hoc View. The report responds accordingly, and this is a good time to Click the top of the burgeoning report to add a title, like Project Allocations.
5 | See Data by Project
Now let’s say we want to see our data by Project. We can add Project Name by dragging and dropping it into the Rows field via Fields > Projects > Project Name.
6 | Drill Down to Resources and Roles
This is a good start. Now let’s go a step further and add a drill down capability into teams by dragging and dropping Resource/Role into our Rows field via Fields > Team > Resource/Role.
You may notice we’re seeing less data. The reason for this is because we’re only focused on Sample Data, at the moment, not Full Data. But you can still get the gist of what’s provided.
Now when you click the + icon next to the Project Name, the data view expands to show data by Resource/Role.
7| Filter by Stage
So we can use it later in a Dashboard, let’s add Stage to the Filters section of your screen by dragging and dropping via Fields > General > Stage.
From the Stage equals down carrot, select, “is one of.”
8 | Filter by Portfolio
And let’s also drag and drop Portfolio Name to the Filters via Fields > Portfolio > Portfolio Name. Change its equals dropdown to “is one of,” and Apply.
9 | Save Crosstab Report
Now Save by selecting the disc icon under New Ad Hoc View. If you’re not interested in Scheduling the report, we don’t need to create it and can select “Save As.”
We’ll save as “WB Project Allocations Rego” and select the Shared folder.
And that’s all there is to it.
We just created an Ad Hoc Jaspersoft Crosstab report that shows allocations and assignments by project, filtered on status, stage, and portfolio—limited to six months for new data.
Stay tuned for creating charts and dashboards. And checkout regoUniversity for onsite CA PPM training at a price you can afford.
Join our mailing list below.