Atul Kunkulol, Rego Consulting

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.

8 Data Warehouse Domains via Jaspersoft Ad Hoc Views > Create > Select 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.

Data Chooser via Ad Hoc Views > Create > Project Management Domain, View Data

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.

Choose Crosstab Report

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.

Add Calendar Periods field called “Month Start Date

The Ad Hoc report takes shape.

Month Start Date added to Crosstab report Columns

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.

Limit Month Start Date via 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.”

Limit the Month Start Date to “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.

Choose current month and next six months via MONTH and MONTH+6

Our report responds immediately.

Crosstab report limited to current month and next six months

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.

Click the hamburger icon and drag the measures’ box up the screen to view more measures

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.

Make sure you get your measures by month from the Calendar Periods

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.

Add Team Allocation Hours to Rows

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.

Add Project Name to the Rows Field

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.

Add Resource/Role drill down capability to Rows

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.

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.

Drag Stage to Filters from Fields > General > Stage

From the Stage equals down carrot, select, “is one of.”

Choose “is one of” from the Stage dropdown

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.

Apply Stage and Portfolio Name Filters

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.”

Save Ad Hoc View As

We’ll save as “WB Project Allocations Rego” and select the Shared folder.

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.