Need Help From a Experience Report Developer!!!

[see_one]

Hi, I am trying to create a report which is grouped by act_register_date and have a count(column), sum(column) and show them the count and other columns in a repeating frame. Th exact same thing for the previous year side by side and also show the percent difference between each year, also in the repeating frame. As, you know they have to be part of the Group unless I use a formula. But the data is coming from a same table. This is the format.

Date- ShipCount2003 - ShipCount2004- %Diff
7-1-04 - 36 - 22- .94
7-2-04 - 15 - 25- .15
7-4-04 - 41 - 51- .05

I broke each group by act_regsiter_date. The other problem is I can get the result but the date does not show the shipments for 2003
in which there were no shipments for the dates in 2004. Because the date is coming from the query for 2004.
When I try one method it fixes one thing but breaks other and vice versa for other approches. I tried to add a formula in 2004 query group and wrote a cursor for 2003 shipments and place the values in the place holder and then show it in the repeating frame but it only shows the value of last date over and over again.

May be I need to change the format to work with it. See if someone can help me with this.

To get 2004 shipments: A separate Group

SELECT act_register_date, count(reg_num), nvl(sum(est_total_weight), 0), nvl(sum(est_line_haul_amt), 0)
FROM shipment
WHERE act_register_date between :From_date AND :To_date
AND est_total_weight is not null
AND est_line_haul_amt is not null
AND shipment_code = 'MM'
AND code = 1

To get 2003 Shipments: A separate Group
SELECT act_register_date, count(reg_num), nvl(sum(est_total_weight), 0), nvl(sum(est_line_haul_amt), 0)
FROM shipment
WHERE act_register_date between add_months(:From_date, -12) AND add_months(:To_date, -12)
AND est_total_weight is not null
AND est_line_haul_amt is not null
AND shipment_code = 'MM'
AND code = 1
GROUP BY act_register_date
GROUP BY act_register_date


THANKS.

[LKBrwn_DBA]

See if this works for you:

SELECT 
     s1.act_register_date
   , count(s1.reg_num)                 cnt_2004
   , sum(nvl(s1.est_total_weight, 0))  wgt_2004
   , sum(nvl(s1.est_line_haul_amt, 0)) amt_2004
   , count(s2.reg_num)                 cnt_2003
   , sum(nvl(s2.est_total_weight, 0))  wgt_2003
   , sum(nvl(s2.est_line_haul_amt, 0)) amt_2003
   , (count(s2.reg_num)-count(s1.reg_num))*100
     /count(s2.reg_num)                pct_diff
FROM shipment s1, shipment s2
WHERE s1.act_register_date between :From_date AND :To_date
AND s1.est_total_weight is not null
AND s1.est_line_haul_amt is not null
AND s1.shipment_code = 'MM'
AND s1.code = 1
AND s2.act_register_date(+) = add_months(s1.act_register_date, -12)
AND s2.est_total_weight(+) is not null
AND s2.est_line_haul_amt(+) is not null
AND s2.shipment_code(+) = 'MM'
AND s2.code(+) = 1
group by s1.act_register_date;

[see_one]

Hi, LKBrwn_DBA

First of all, I want to thank you for replying to my post. I did try what you suggested but it does not work, it gives me the count of all the shipments, or some number that is not correct, like in 1400 shipments per day, instead of 50 or 30. Anyhow, I have kinda figured it out but it's not doing exactly what I want it to do.

May be, you can suggest something based on your experience.

Here's where I am.

I have created a separate table called date_temp table with one row for date column. I load the table with :From_Date and :To_date range and my date table is loaded for the same range of dates,
e.g, 07-08-04 to 08-080-04 for a month and my table gets populated at the start of the report in After Parameter Form Trigger. That is working fine and that table gets truncated at the end of report.

Now, I have created two groups again. One for the table date_temp as a Parent Group, and the second for my other query, that counts and sums the record as a Child Group.

These are my two queries.

Parent Group
SELECT d.date_tmp
FROM date_temp d
--WHERE d.date_tmp between :From_date AND :To_date(this line is optional)

Child Group
SELECT d.date_tmp, (count(reg_num)), nvl(sum(est_total_weight), 0), nvl(sum(est_line_haul_amt), 0)
FROM shipment, date_temp d
WHERE d.date_tmp = act_register_date(+)
--AND shipment_code = 'MM'
--AND code = 1
GROUP BY d.date_tmp

I have created data and link the d.date_tmp of Parent with d.date_tmp of Child. It works fine. It gives the count for shpmts Grouped BY each date that is in my table and we have shipments and the count 0 for those dates we did not have any shipments, for example Saturday and Sunday of this year.

The real problem occurs here.

As soon as I uncomment either the shipment_code or code criteria in my query, it skips all the dates with no shipment and shows only the Group BY dates with shipment only.

The reason I want it this way because once I figure this out then I need to do the same for previous year. And the dates in this year may be weekdays in the previous year. This is just extra info, you don't have to give the answer for that.

I just want you to give me some logic behind the count and why it is skiping the dates when I add the criteria. Because I need to count the shipment only for shipment_code 'MM' not all others.

I am not sure if you understand my problem but I am just posting it here for someone who might give me some logic behind this.

Thanks.

This is how it looks without the Shipment_Code AND CODE:

Date Shpmts
2004 2004

08-JUL-04 58

09-JUL-04 48

10-JUL-04 0

11-JUL-04 0

12-JUL-04 46

13-JUL-04 60

14-JUL-04 47

[LKBrwn_DBA]

You have to add the '(+)" to all columns:

SELECT d.date_tmp, (count(reg_num))
     , sum(nvl(est_total_weight, 0))
     , sum(nvl(est_line_haul_amt, 0))
  FROM shipment, date_temp d
 WHERE d.date_tmp = act_register_date(+)
   AND shipment_code(+) = 'MM'
   AND code(+) = 1
 GROUP BY d.date_tmp

[see_one]

hey thanks buddy. Yeah, it's working. Why was I not seeing it? I guess, i was not looking at it logically. It would be nice to have a good SQL book that can help me understand the things behind all this. You are a great help.

[see_one]

hey thanks buddy. Yeah, it's working. Why was I not seeing it? I guess, i was not looking at it logically. It would be nice to have a good SQL book that can help me understand the things behind all this. You are a great help.

[kris123]

Hi Buddy,

Im just a newbie in apps development...can u help me pls or give me links
on how to create a menu that will integrate or call all the forms and reports i have created? Say, in a certain apps i have 4 submodules
1. Data Entry (with 5 forms) 2. Maintenance(Update) (with 6 forms)
3. Reports Gen (with 20 reports) 4. Misc. (calls dos.bat programs)

I've been reading the online help but cant find one.

Thanks a lot buddy

[LKBrwn_DBA]

To: kris123; I suggest you open a new thread to allow other experts to also respond.