Post

6 followers Follow
4
Avatar

Allow missing functions Formula Columns in Reports

Running into problem building reports that revealed access to functions limitations in Reports that are available in Conditional Formatting.

The request is this: Please make the same functions available in reports as is available in Conditional Formatting for fields in said reports.

Here’s an example of the problem:

Say you want a report that shows Past Due items and items coming due in the next 7 days including a list and a chart. In order to have a bar chart with values PAST DUE and COMING DUE, you can use a Formula Column. Also, in the items list, the due dates can be highlighted RED if Past due:

Build a report on Work Item.:
Include columns for $DueDate and Name Conditionally format $DueDate to be RED TEXT using this formula
ToOrganizationalDateTime($DueDate)<Today()&&$State=‘Active’
This highlights PAST DUE items in RED. Crucially, you have to use ToOrganizationalDateTime because the value of DueDate from the database will contradict the local timezone and items will have improper highlighting otherwise!!!

Create a calculated formula column and call it PastComingFuture: Make it TEXT calculated using:
If($DueDate<Today(),"Past Due",If(DateDiff($DueDate,Today(),"d")<=7,"Coming Due","Future”))
The formula above has a problem as we will see.

Setup the filter so that PastComingFuture CONTAINS “Due” (this will ensure the list only includes Past Due and Coming Due items (nothing due more than 7 days out)
Setup a bar chart to show the breakdown between the PastComingFuture values (this will show a bar for against our calculated report field)

If you build a report like this and run it, you quickly find that conditional formatting formulas are more flexible than custom columns in reports. Specifically, you CANNOT use the ToOrganizationalDateTime() function in a report column. This makes it impossible to match up the Past Due and Coming Due with the formatted text. You can do a workaround by using DateDiff, but this is basically just adding a 1 to a date. This may not be correct for all hours of the day.

Please allow functions like ToOrganizationalDateTime() in custom formula columns. This may seem like a fringe case, but the functions are severely limited in reports. Even something as simple as ToString() is unavailable.

Boris Krutiy Not planned

Please sign in to leave a comment.

4 comments

0
Avatar

I'm looking for the same type of functionality. I need teh ability to report on the age of a project. I would like the ability to create formula something like: ()today - start date. Thank you.

Leo Lovely 0 votes
Comment actions Permalink
0
Avatar

I'm looking for this functionality, but in terms of using the Hours() function. 

In a formula column in a report, I'm getting: Error: Function Hours() can be used only in calculated field
 
Chana Lewis 0 votes
Comment actions Permalink
0
Avatar

I have similar requirements, can we make ALL Formula Functions consistent across both Reporting Formula columns as they are in the System Configuration.  I would like to use the Left() function within a report to restrict the number of characters returned from Title and Name fields.  But this also isn't available within the reporting module.

David Blackeby 0 votes
Comment actions Permalink