How to get Months in Columns and Group by filed options in Rows of a report

[vinay_kumar]

Hello Experts,

We have a requirement to develop a report as required below:

Consider a field name as "Services", This is the string field with possible values A,B,C,D.

Requirement is to prepare a report by taking Group by with 'Services' field and get the how many requests created every month(In columns).

For this requirement , currently we have seen with by taking report type as "trend" , but it is taking month data also in rows which is not recommended.

Required outcome should be Group choices in Rows and Monthly data in columns.

Anyone please help me to achieve this.

[Adam Stout]

I'm hoping that group isn't actually a string but is instead a reference (or possibly a choice).  Try a column report (in the time series set of reports):

[vinay_kumar]

In General, The choices filed is LIST Field. But we are storing the values in the list field in a String field after sorting it.

Now our requirement is to get the data of particular choice type should come in rows with Month-wise data(Months in columns) when we clicked on Grid.

As of now, we are using Report type as "trend" we are getting the Data for particular choice showing in Rows... But Month-wise data also coming in rows with repetitions of choices again and again for every month.

To avoid of repetitions of choices in rows for everymonth, its better to maintain the Months in columns.

Could you please help me on this.

[Adam Stout]

Once you convert to a string, you have severely limited your options. Why not just sort the list and save it?  Generally, lists are not very report-friendly.   I would look at using a child table that has one value per month and a date field (so you can use time series reports) and a string month label so you can use a pivot.  ServiceNow reporting assuming you have normalized data (which is why we base so much on aggregates).  If you denormalize it (for instance having a separate column for each month), you have nothing but headaches.  Time Cards are structured this way (one card per week) and it has caused me reporting headaches for five years.  The secret is to have a normalized shadow table which maintains the date/value info.  If you need the columns on one record for the UI, use a BR to maintain the shadow table (also look at embedded tables or just a related list).

This is all about data structure.