0

I am using iReport and want to be able to take 4 date inputs (2 date ranges) dateRange1 and dateRange2.

Each will disaplay the amount of items and cost given within the respected ranges.

So far I have the first dateRange1 is working but I am having trouble figuring out to implament the second date range.

Here is the code I have along with some screenshots.

SELECT
 COMPANY."NAME" AS COMPANY_NAME,
 CUSTOMER."NAME" AS CUSTOMER_NAME,
 SO."NUM" AS SO_NUM,
 SOITEM."STATUSID" AS SOITEM_STATUSID,
 PRODUCT."ID" AS PRODUCT_ID,
 PART."NUM" AS PART_NUM,
 SOITEM."QTYFULFILLED" AS SOITEM_QTYFULFILLED,
 SO."COST" AS SO_COST,
 PART."ID" AS PART_ID,
 SO."DATECOMPLETED" AS SO_DATECOMPLETED,
 CUSTOMER."ID" AS CUSTOMER_ID,
 SOSTATUS."ID" AS SOSTATUS_ID
FROM
 "CUSTOMER" CUSTOMER INNER JOIN "SO" SO ON CUSTOMER."ID" = SO."CUSTOMERID"
 INNER JOIN "SOITEM" SOITEM ON SO."ID" = SOITEM."SOID"
 INNER JOIN "SOSTATUS" SOSTATUS ON SO."STATUSID" = SOSTATUS."ID"
 INNER JOIN "PRODUCT" PRODUCT ON SOITEM."PRODUCTID" = PRODUCT."ID"
 INNER JOIN "PART" PART ON PRODUCT."ID" = PART."DEFAULTPRODUCTID"
 AND PART."ID" = PRODUCT."PARTID",
 "COMPANY" COMPANY
WHERE
 UPPER(CUSTOMER."NAME") = $P{customerID}
 AND SO."DATECOMPLETED" BETWEEN $P{From} AND $P{To}

This wis a screenshot of the design view.

enter image description here

Alex K
  • 22,315
  • 19
  • 108
  • 236
Ashton
  • 363
  • 1
  • 4
  • 21
  • 1
    Hi @Ashton, can't really understand what you need to do with second dateRange2 AND (SO."DATECOMPLETED" BETWEEN $P{From} AND $P{To}) OR ( SO."DATECOMPLETED" BETWEEN $P{From2} AND $P{To2}). or just use 2 subreport each with the different dates – Petter Friberg Mar 10 '16 at 23:32
  • @PetterFriberg what im wanting is for the person to be able to display the qty and cost from date range 1 and have 2 more columns for QTY and Cost again but for a SECOND date range (date range 2) so it compares what the customer was buying from lets say 2010-2011 and how much they're buying from 2012-2013 – Ashton Mar 11 '16 at 18:21
  • @PetterFriberg please check out my new question if you know anything about subreports and Fishbowl!! http://stackoverflow.com/questions/36064526/report-compiles-fine-but-displays-blank-while-used-in-fishbowl?noredirect=1#comment59775554_36064526 – Ashton Mar 17 '16 at 17:29

1 Answers1

1

Within Fishbowl you can set any of the parameters to include a separate title. This allows you to use the same type of parameter multiple times. In the case of date ranges it's used as $P{dateRange1DateCreated} $P{dateRange2DateCreated} and then the second set would be $P{dateRange1DateModified} $P{dateRange2DateModified}.

Mamof
  • 181
  • 5
  • 13
  • How will this work within iReport? I am trying to produce a second column that says "Date Range 2: " and it will display the prices of the items selected from, lets say June 2005-july 2005 compared to (date range 2) june 2010-july 2010. How would I type this in my SQL query in iReport? – Ashton Mar 10 '16 at 22:08
  • 1
    dateRange1 and dateRange2 need to be used together to get the range element. In the sql it's so.datecompleted BETWEEN $P{dateRange1} AND $P{dateRange2} – Mamof Mar 10 '16 at 22:18
  • what im wanting is for the person to be able to display the qty and cost from date range 1 and have 2 more columns for QTY and Cost again but for a SECOND date range (date range 2) so it compares what the customer was buying from lets say 2010-2011 and how much they're buying from 2012-2013. Can I do this with your answer? – Ashton Mar 11 '16 at 20:08
  • Also Mamof - this is a new question i have please take a look! http://stackoverflow.com/questions/35949454/totaling-up-costs-and-displaying-each-item – Ashton Mar 11 '16 at 20:39
  • 1
    Yes it could be done using the date ranges in a single report by doing sub-selects. Or you could do multiple sub-reports. You may want to take a look at the Compare Sales By Month report in the sales section. – Mamof Mar 11 '16 at 20:43
  • Momof - would i simply have the same SQL for each of the subreports and the master since it's teh same data? – Ashton Mar 11 '16 at 21:19
  • 1
    The master report would have the data for pulling the part information. The sub-reports would be pulling the sum total of the data for the associated part. The Compare Sales by Month report has a very similar setup for what you are trying to do. You can view that report and sub-reports to see an example. – Mamof Mar 11 '16 at 21:23
  • Wonderful. Thank you! – Ashton Mar 11 '16 at 21:41