Atul Kunkulol, Rego Consulting

In our last post we covered how to start a new Ad Hoc Jaspersoft Report. Today we’ll take you through making an Active Project Report in CA PPM. It only takes eight steps.

Credit goes to Atul Kunkulol, who shared this trick in Part 1 of our Free Jaspersoft Webinar series. He’s also responsible for our Jaspersoft Tip | Email Anyone Reports, which gives you an outline of how to email HTML versions of your reports to anyone. That includes non-CA PPM users. To get in depth training, join Rego at regoUniversity in Tempe, April 25-27. Early Bird registration closes February 15th.

Step 1 | Define Report Components

To build a table, you first want to define your components. Let’s create a report that includes Active Projects with ID, Name, Project Manager, Start Date, Finish Date, Hours, Actual Costs, Allocation Costs, and Open for Time Entry Flags included.

After selecting the Project Management Domain and choosing your data (Home > Advanced Reporting > Create Ad Hoc Views), a New Ad Hoc View is ready for input.

New Ad Hoc View, ready for input via Project Management Domain

Since you haven’t selected anything, the right-side of the screen is a massive white field. Not for long. Choose the type of report you’d like (Table, Chart, or Crosstab).

Create a Table, Chart, or Crosstab Ad Hoc Report

We’ll select “Table,” and so the data generates fast while designing the report, we choose “Sample Data.” Now it’s time to do a little dragging and a little dropping. Let’s start with some Fields: Project Name, Project ID, Project Manager, Start Date, Finish Date . . .

Highlight Fields to drag on the Report

As you drag fields onto the canvas, or into your columns row, the table generates.

New Table Report via drag and drop, Project Management Domain

By clicking on the title above the table, we can add a title: Project List.

To see cost find the Project Summary Totals within the Measures. Choose Total Actual Hours and Total Allocation Hours.

Total Actual Hours available via Summary Totals > Project > Total Hours

You’ll notice that as you drag an item over the table, a vertical line (in this case) lets us choose what location we want the measure to appear.

Dark, vertical line indicates placement of new measure.

Step 2 | Remove Inactive Projects

After dragging our new selections onto the report, we might want to limit our selection and remove inactive projects. 

Simply go back to the Fields > General > Is Active > and right click to choose “Create Filter.”

Create Filter to show only active projects via Fields > General > Is Active > Right Click

This will add “Is Active” to your filtering options, now available on the far right side of your burgeoning report.

Create Report Filter “Is Active” via Fields > General > Is Active > Right Click > Create Filter

You can say, “yes I want Active,” by adding a “1” to the text field.

1 means YES and 0 means NO in Filters

You also probably don’t want to see any templates in the report. So we can drag “is Template” over to the Filters. And effectively say, “no, I don’t want templates,” by adding a “0” to the text field.

Step 3 | Filter on Projects by Date

To filter on projects starting this year, go back to your Fields and drag “Start Date” into your Filters area.

Now each one of these filtered attributes, naturally, has different selections. You can see these by clicking on the “equals” down arrow.

Write “YEAR” in Start Date

For the Start Date, we can type YEAR into the text field, or we can indicate a date range, such as 1 January to 1 December. Once we’re done with our filters, we click “Apply.” Clicking on “Apply” causes the data in the report to refresh.

Click Apply to refresh Report

Now it will show us the Total Actual Hours, the Total Allocation Hours, and we can still add other measures we may have forgotten, like Total Allocation Cost, Total Actual Cost (ACWP), and Open for Time Entry, by dragging and dropping, as we did before. Column order can also be changed from the Column row above the Report.

Drag and Drop to desired location (purple line indicates placement)

Step 4 | Select Exporting Options

You may be wondering if this fancy new table can be exported to an Excel or PowerPoint document from this view. The answer is yes. Every report you create, can be exported directly from your current view in many different ways, which you can see by scrolling over the icon that looks like a document with an arrow.

Select export options from the arrowed document icon

The export options include Excel (Paginated), Excel, CSV, DOCX, RTF, ODT, ODS, XLSX (Paginated), XLSX, and PPTX. You could send it to PowerPoint or a comma separated file. The report we’re creating right now would come up great in an Excel document because each column and cell would correspond to a column and cell in Excel. You can export and send this report from this view or you can schedule a report to export and send it.

Step 5 | Create a Calculated Field for a User Friendly Report

One of the things you may notice on our “Is Open for Time Entry,” are attribute values get pulled in and represented as 1 or 0. That won’t mean much to users, so let’s make our report more user friendly.

We can transform our zeroes and ones into meaningful language by creating a Calculated Field. Just click the paper icon with the down arrow, in the Fields’ heading.

New Calculated Field Options

A New Calculated Field window will open, and we’ll change the field name to OTE, Open for Time Entry. As you can see, there are quite a few Functions available. They are similar to Excel. In this case, we want to select “IF” by double-clicking.

First select “IF” then choose the Fields and Measures

Now on the left side, it’s time to choose from our Fields and Measures. One of the challenges you’ll see in the Fields and Measures list is sometimes there are three of each attribute, as in Baseline FTE and Baseline Hours.

The reason why you see three attributes with the same name is because one might be at the project level, one at the team, and one at the task. It’s one of the areas we’ll probably see some improvement down the line. At worst, you might have to try it three times to get the correct number.

It’s still a really useful ability, and we can use Google to get a lot of the formulas and functions.

Three identical options indicate the Fields and Measures available on the project level, team level, and task level

Now on the left side, we’ll select “is Open for Time Entry” from the Fields and Measures list. The list is organized alphabetically, and you’ll find this selection in the “I” for “is” category, not “O” for “open.”

Populate Formula box by selecting Fields and Measures and Functions

Now we’ll rewrite the formula a little so the report will tell us Yes, if it’s a 1 and therefore open for time entry and no, if it’s a 0, and it’s not open for time entry.

That formula is IF(“Is Open for Time Entry”—1, ‘Yes’, ‘No’).

Single quote marks around ‘Yes’ and ‘No’ are required for validation

Make sure to use single quote marks around the Yes and No, so when you push the Validate button, your validation is successful.

Validate formula before choosing Create Field

After a successful validation, click on your Create Field button. Now you can view the new field at the bottom of your Fields list.

See the “New Field” at the very bottom of your Fields list

Now it’s time to drag and drop the New Field in your report, so you can see if projects are open for time entry or not.

Drag and drop your New Field onto the Report

To change the name from New Field, simply right-click, choose Edit Label, and name it Open for Time Entry. We could have also done this in the last screen when we created the calculated field.

Report now shows totals by Project Name

Step 6 | Organize Report by Project Manager

So our report is currently showing you the totals by Project Name, and it includes Project Manager, but let’s suppose you want to organize your new report by Project Manager and see Status, Stage, and Portfolio.

It’s actually quite easy. Above your report you can see a row titled “Columns” where each of your current columns are listed. Below that is an empty text field that says “Groups.” Try dragging and dropping your “Project Manager” field into the Groups row.

From the Columns row, select Project Manager and drag it into Groups row

The result is instantaneous on your report below. Now you can see Total Actual Cost and our other fields grouped by Project Manager.

Report reorganizes to show fields grouped by Project Managers

While many of the results in the screenshot above show 0.00, that’s only because we’re in a demo environment using sample data.

You have more options within this view. You might click on the magnifying glass and choose to display the report in detail or by totaled data, or both.

Click on the magnifying glass icon to display the report in detail, by totaled data, or both

Choose “Totals Data” to see the big picture by Project Manager.

Totals Data by Project Manager

Now you can see how many projects are being led by each person, as well as the total hours and costs, or you can switch your view to include the detail with the totals by choosing “Details and Totals.”

Details and Totals Data by Project Manager

This gives you the best of both worlds. It’s pretty amazing.

Step 7 | Add a Status Indicator to Track Risk At-A-Glance

Now, it may seem high-maintenance, but who has time to read the entire report to see what projects are in trouble? Nobody. So let’s fix that.

To highlight projects that are in trouble, and skip filtering through the entire report, one of the things you can do is add an indicator from the Fields, such as the Status Indicator. So we’ll drag and drop that from the General Fields section onto the report.

Add the Status Indicator to track Project Risk

The Status Indicator is based on the last status of the Project. Usually Projects are On Track or At Risk. One of the things you won’t see in here are the chevrons, or the icons. But you can add background color to indicate status by saving this as a report and adding conditional formatting.

So we’ll go there in a future post. But before we do, let’s add two items to our filtering options. From your General Fields on the left side of the screen, add the “Stage” filter – by dragging and dropping – to your Filter box on the right side of the screen.

Bring Stage and Portfolio ID into Filters

It will also help to add the “Portfolio ID” from the Portfolio Field. We’ll change the “equals” dropdown to “is one of” in both of our new filters.

Choose “is one of” dropdown for Stage and Portfolio ID

Now when we hit the Apply button, our filters are applied to our New Ad Hoc Report View immediately.

Step 8 | Save and Specify Access

And since we’re done with the preliminaries, let’s Save. You can save and create an ad hoc view, or save and create a report. We’ll choose the latter.

Three options for saving a report

One of the perks of saving is that you can give others access to your saved file. Let’s change the Data View Name and Report Name to “WB Active Project List,” the WB indicates this report is for our web tutorial.

The Data View of the report is more interactive, like a portlet. The Report itself is like a paper copy, where you can see it, print it, and schedule it. There are additional differences, but we’ll leave them for another time.

If you want a personal copy saved, save it in your “Users” folder. If you want everyone with access rights to have access, save it in the “Shared” folder.

Data View vs Report View

You wouldn’t want to save the report in the CA PPM folder because that’s where upgrades occur, and you don’t want your custom work saved there. So we’ll specify the Shared folder for both the Data view and Report view and Save.

One of the saving options is to the Custom Report Template rather than Default. If you choose Custom, this is how you could add your templates and company logo. We’ll choose Default Report Template, and click Save.

Now you can go over to the report and see how the conditional formatting looks, which is where we can alter status indicator colors. We’ll do that soon.

Any questions in the meantime?

Join our mailing list below to get regular notifications of training and webinars. Here’s the link to join Rego at the largest, dedicated CA PPM knowledge-sharing event of the year in Tempe, AZ.