Skip to Content
0

Create Dynamic date columns based on input parameters

May 18, 2017 at 07:45 AM

57

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.

Example:
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.

Regards,

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
0

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

@Month

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

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

@Col1

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

@Col2

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

Then create corresponding datacolumns

@DataCol1

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

Repeat

You can then sum and group datacol formula as required

Ian

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

Hi Ian

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

Regards,

Share
10 |10000 characters needed characters left characters exceeded