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 Clarity PPM (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-Clarity PPM users.
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.
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).
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 . . .
As you drag fields onto the canvas, or into your columns row, the table generates.
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.
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.
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."
This will add “Is Active” to your filtering options, now available on the far right side of your burgeoning report.
You can say, “yes I want Active,” by adding a “1” to the text field.
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.
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.
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.
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.
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.
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.
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.
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.”
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’).
Make sure to use single quote marks around the Yes and No, so when you push the Validate button, your validation is successful.
After a successful validation, click on your Create Field button. Now you can view the new field at the 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.
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.
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.
The result is instantaneous on your report below. Now you can see Total Actual Cost and our other fields grouped by Project Manager.
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.
Choose “Totals Data” to see the big picture 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.”
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.
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.
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.
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.
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.
You wouldn’t want to save the report in the Clarity 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.
