Showing months in calendar order on pivot tables


#1

I have build a pivot table that displays months as columns. The months are sorted in alphabetical order and I would like to show them in calendar order. How can I make this happen?

Also, I have seen on some of the Kreporter videos that previous versions of Kreporter allowed you to ‘add rows’ to a pivot table report. In the version I am running 4.2, this facility is not there. Can anyone advise?

Many thanks


#2

Jonnie,
‘add rows’ functionailty hasn’t be ported to KReporter 4.x yet.

Regarding the months: How does the month field look like in your report?
You may use a customSQL like DATE_FORMAT({t}.{f}, ‘%Y-%m’) to obtain values like 2018-01, 2018-02
(use {tc} instead of {t} if this is a custom module / custom field created with studio)

Val


#3

Hi Val,
In my report I am showing columns (labeled by month). The columns are sorted in alphabetical order but I am hoping to show them in calendar order (January, February, March…) together with the year. i.e. November -17, February - 17. I have attached an image to show what I mean.

In this image example November’s figure is actually for 2017 and therefore I would like to show this first (in calendar order) followed by the 2018 in calendar order.

How can I achieve this?

Also, on the pivot reports is it possible to ‘set’ the column widths as when the report is generated the account name field is tiny and the totals field at the end always needs to be widened?

Many thanks


#4

Use customFunction in manipulate tab.
You have a date field telling which month, don’t you?
You may use DATE_FORMAT({t}.{f}, ‘%Y-%m’) to obtain values like 2018-01, 2018-02
(use {tc} instead of {t} if this is a custom module / custom field created with studio)


#5

Hi Val,

Thank you for your reply.

What I need to do is to re-order the months. Currently the report displays the months by alphabetical order and I would like to show them in calendar order. Is this possible?

Many thanks,


#6

Jonnie,
can’t you re-order the months by using DATE_FORMAT({t}.{f}, ‘%Y-%m’) ?
Your field is a date field, isn’t it?

Val