Create Dynamic date columns based on input parameters

[JerushaLala]

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,

[Former Member · Accepted solution]

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

[JerushaLala]

Hi Ian

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

Regards,