How do I create an advanced report?
Start on the OTBI Catalog page.
Hello, my name is Mike. In this first of our three-part series on OTBI functionality. We’ll be looking at creating and saving an advanced report. I’m going to create a new analysis in the worker assignment real-time subject area.
Click Create and select Analysis.
I’ll use the browser search to quickly find the subject area I’m looking for.
Search for worker assignment and select the Workforce Management – Worker Assignment Real Time subject area.
In the Criteria list, expand Worker, then search for and double-click Person Date of Birth.
Click to collapse the Worker criteria.
Click to expand Worker Assignment.
Double-click Assignment Count.
I’ve added some criteria, but the results at this point aren’t particularly useful. So now I’m going to edit the formula to calculate the age of the person.
Click the Results tab.
Click the Criteria tab.
For the Person Date of Birth criteria, click the Settings icon and select Edit Formula.
Click Insert Function.
I’m looking for the difference between two dates. Before I add this function, I can see the syntax and an explanation.
Click to expand Calendar/Date and select TimeStampDiff.
Click OK.
I want to change the interval to ‘year’.
In the Column Formula section, select interval and change to sql_tsi_year.
Then I’ll change this part of the formula to Current_Date.
In the Column Formula section, click to select timestamp2.
Click Insert Function.
In the Functions list, click to select Current_Date.
Click OK.
Before I save this, I’ll also change the column heading.
Under Column Heading, click to select the Custom Headings option.
In the Column Heading field, enter Age.
Click OK.
Now I can see the current work assignments by age. But I’d really like is to see this by age bands.
Click the Results tab.
Click the Criteria tab.
In Worker criteria, click the Settings icon and select Edit Formula.
Click the Bins tab.
Click Add Bin.
For my first age band, I want to see everyone under 25.
In the Operator field select is less than.
In the Value field enter 25.
Click OK.
I’ll enter 0-25 as my first band name, and OTBI will sort these bands alphabetically. The zero at the front of the name means this will appear first.
In the Edit Bin Name field, enter 0-25.
Click OK.
Click Add Bin.
In the Operator field select is between.
In the first Value field, enter 25.
In the second Value field, enter 45.
Click OK.
In the Edit Bin Name field, enter 25-45.
Click OK.
I can add as many bins as needed, but right now I only need 4.
Click Add Bin.
In the Operator field select is between.
In the first Value field, enter 46.
In the second Value field, enter 65.
Click OK.
In the Edit Bin Name field, enter 46-65.
Click OK.
Now I’ll add my last one.
Click Add Bin.
In the Operator field select is greater than.
In the Value field, enter 65.
Click OK.
In the Edit Bin Name field, enter 66+.
Click OK.
Then I’ll edit this bin name for those who haven’t entered any date of birth at all.
In the All other values row, click the Edit Bin Name icon.
In the Edit Bin Name field, enter No Date of Birth.
Click OK.
Click OK.
And now that I’m looking at the information by age bands, I want to change my column heading.
In the Age criteria, click the Settings icon and select Edit Formula.
In the Column Heading field, enter Age Band.
Click OK.
Click the Results tab.
Now I’ve got something that looks more like a usable report. The Age Bands appear in blue, which means I can click on them for more information. However, for this report, I don’t want to allow that so I’m going to remove that ability.
Click the Criteria tab.
On the Criteria tab, click the Age Band settings icon and select Column Properties.
Click the Interaction tab.
In the Column Heading section, in the Primary Interaction field, select None.
In the Value section, in the Primary Interaction field, select None.
Click OK.
Click the Results tab.
Now I have the report that I want. I want to use Age Bands in other reports, so I’ll save it so I can re-use it.
Click the Criteria tab.
In the Age Band criteria, click the Settings icon and select Save Column As.
Since Age Band is based on the common Worker dimension, I want to save it to a common folder that’s not related to a subject area.
In the Folders list, click to select the Custom folder.
Click the UP icon.
Click OK.
Once it’s saved, I can drag into any report where I want to use it, as long as the Worker dimension appears in one of the subject areas in the report.
In the Catalogs list, click to expand Shared Folders.
Click to expand Custom.
Click to expand Subject Area Contents.
The last thing I want to do is save the report.
Click the Save As icon.
In the Shared Folders > Custom folder list, click to expand HCM and select HCM Other Reports.
In the Name field, enter Age Band Summary.
Click OK.
Now that the report is saved, it can be used as needed. This concludes the Creating an Advanced Report tutorial, part 1 of our 3 part series on OTBI functionality. Thanks for watching.