Skip to Content

Create Dynamic date columns based on input parameters

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,

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    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

    @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

    Add comment
    10|10000 characters needed characters exceeded

  • May 18, 2017 at 08:45 AM

    Hi Ian

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

    Regards,

    Add comment
    10|10000 characters needed characters exceeded