How do I create a summary report using time-based (months) as columns

[Jason73]

I'm trying to create a budget view for maintenance on items.  Below is an example of the spreadsheet that I currently use to do this.

The goal would be to create this:

However, it seems I can't create a time-based report given ServiceNow is looking for time-based fields, rather than time-based columns.  Any thoughts or direction on how I can do this?

Thanks!

[Adam Stout]

I'm a little unclear as what you mean by a time-based field vs.time-based column but have you looked at a trend chart?

[Jason73]

Sorry for the confusion, relatively new to ServiceNow.  Yes, I have tried numerous trend charts.  It seems I can't self-select columns to be used as data points for trending (e.g. January Forecast, February Forecast, etc.).  I would like for the "Trend By" to use the monthly forecast columns... instead I only have options that appear to be based on the date / time the record was last touched:

I'm trying to figure out a way to create a report that allows me to trend the sum of forecasted spend by month.  Very simply, I need to create the graph I made in excel (in the original post) in ServiceNow.  I can change the table construct if needed.  Hope that helps clear it up a little.

[Adam Stout]

Got it.  

1) Can you normalize your data so there is a date field (Jan 1, Feb 1, etc.) and a value on each row so you have 12 rows per year instead of 1 row per year?  With this, a normal line or trend should work.  Typically, we are operating on the base data (e.g., counting the number of incidents created per month or summing the number of hours per month on time cards) not a summary line, but it should work fine.

2) You could use multiple data sets, one per month.  However, there is a limit (it may be 7) on the number of data sets you can have.

I would try to go down the path of #1 and then you can use multiple data sets to represent planned and actual.

[Jason73]

The only way I could think to make that work is to create a separate record for each product, for each month.  While that wouldn't matter to me on the back-end, I'm not sure I could deliver a decent user experience as I wouldn't want the user to have to create 12 records for each product / item.  Is it possible to have a one (the product record) to many (the 12 months) relationship between two tables and have a single interface to fill out?  The form I have now looks like this, and is relatively easy for the user.  I also expect to eventually be able to pull data directly in from another data source via an import set / transform map and automate it even further.