1 Reply Latest reply on Oct 19, 2010 10:20 AM by philmodjunk

    Financial period comparison report

    PaulChristie

      Title

      Financial period comparison report

      Post

      Hello can someone help me please?

      It is probably easier than I think but I can't quite get my head around this.

      I run a manufacturing plant but sell to clients on a job to job basis.

      I have tables for a job description (Job no, Customer details etc), Job parts detail (Multiple line items Qty, description etc.)

      Naturally 1 customer can have many jobs with many line items in the detail.

      I can generate reports with summary fields that show on single lines Customer name, Job total price for a given period.

      So say for a date period of 1 month it will show for each customer the total dollar value of all jobs done in that period and show every customer in the found set .

      E.G

      Period, 10/2010

      Name Total sales

      Johns Metals $30,000

      Pauls metals  $300,000.00

      Etc

      What I want to do is show comparative periods on the same report so we can have say the following

      Name Oct Nov Dec

      Johns metals $30,000 $20,000 $500

      If anyone can help I would be much obliged

      Thx, Paul

        • 1. Re: Financial period comparison report
          philmodjunk

          Here's a "month" calculation first posted by Comment that may prove useful to you:

          cMonth : DateField - Day(DateField) + 1

          This returns the date of the first day of the month for any date in datefield.

          There are several ways that can be used to get your comparitive columns of data, but the details depend on the structure of your data.

          If you have a Data table like this:

          CompanyID (serialnumber)
          CompanyName
          MonthlyTotal (Number)
          cMonth
          DateField

          Then a series of one row portals all based on this relationship:

          ReportTable::CompanyID = Data::CompanyID  //sort relationship by cMonth

          can be set up with the first row showing Row 1, the second showing row 2 and so forth to give monthly columns of data.

          This is just one general approach, there are variations on this you may need to use depending on your data and your report's requirements.