Skip to Content

Create Dynamic date columns based on input parameters

May 18, 2017 at 07:45 AM


avatar image

Hi Experts.

Im creating a high level balance sheet and profit and loss report. The input parameters are a from and to date. As management usually wants to see the previous 6 months figures.

what I want to so is get create date column headings and the corresponding figures per column.

user enter parameters FromDate: 01/11/2016 ToDate: 31/05/2017

Output should be:

Account Nov 2016 Dec 2016 Jan 2017 Feb 2017 Mar 2017 Apr 2017 May 2017
Motor 1000 2000 3000 35000 4000 5000 6000

I actually have come across a thread once that actually did this, but cannot seem to find it.


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Ian Waterman May 18, 2017 at 08:07 AM

You can use a Cross tab and group on date and select by month.

Downside is if no data then month column will be missing.

You can then use a manual cross tab and use a formula for each month

Create a Month Formula


totext({DateField}, 'yyyy-MM')

If you want a dynamic period you will need to create a formula for each month colum


totext({StartDateParam}, 'yyyy-MM')


totext(dateadd('m', 1,{StartDateParam}) , 'yyyy-MM')

Then create corresponding datacolumns


If {@Month} = totext({StartDateParam}, 'yyyy-MM') then valuefield else 0


You can then sum and group datacol formula as required


10 |10000 characters needed characters left characters exceeded
Jerusha Lala May 18, 2017 at 08:45 AM

Hi Ian

Thank you very much. I will try this out. Looks like it should work.


10 |10000 characters needed characters left characters exceeded