Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: select matching price from list Post Reply Post New Topic
Author Message
riker
Newbie
Newbie
Avatar

Joined: 07 Dec 2010
Online Status: Offline
Posts: 16
Quote riker Replybullet Topic: select matching price from list
    Posted: 14 May 2012 at 7:55am
The report I'm writing is to compare the cost of materials we used to purchase to the cost of in house manufacturing. The only thing I'm having a hard time getting is the old purchased cost. In our ERP once we change the costing method for in house production it wipes out the purchased price. The best way I've found to get the cost is to look at the price on old PO's. Because of quantity breaks I need to select the cost for equivalent quantities from the a list of PO's.

I've got it to show me the price for quantities that are exactly the same or prices that are within a range around the qty, but I can't get it to do it together, if that makes since... It also is showing all PO's with matching qty's not just 1 of them. I need to be able to use it in a formula. I know this probably isn't enough to go on but any help would be greatly appreciated!!
IP IP Logged
c16271
Groupie
Groupie
Avatar

Joined: 24 Aug 2010
Location: United States
Online Status: Offline
Posts: 48
Quote c16271 Replybullet Posted: 14 May 2012 at 11:43am
Make it a bit more clear, you're right...it's really hard to formulate any type of response given what you wrote...
 
If I understand somewhat correctly, it sounds like you should be able to return the exact matches using SQL rather than through Crystal Reports. Lean off of the DB to do that type of work..
 
IP IP Logged
riker
Newbie
Newbie
Avatar

Joined: 07 Dec 2010
Online Status: Offline
Posts: 16
Quote riker Replybullet Posted: 15 May 2012 at 2:50am
I'll try...
Right now I've got a main and sub report. On the main report I pull the job information, Part Number and quantity. In the main I also have the manufacturing cost calculated.

In the sub-report I am pulling the Purchase order cost for the part number. This is where my problem is. I can get it to give me the PO cost if the quantity on the PO matches the quantity on the job, but it shows me all of the matching PO's when I only need to see one. I also have to factor in when the quantities don't match and was thinking I could do that with a shared numbervar range. Again it works but I see all of matches not just 1.

So how can I get it to select the single price I need then use it in a formula? If I can...
IP IP Logged
c16271
Groupie
Groupie
Avatar

Joined: 24 Aug 2010
Location: United States
Online Status: Offline
Posts: 48
Quote c16271 Replybullet Posted: 15 May 2012 at 5:13am

Assumming the following:

 
Links from master to sub-report are:
 
1.QTY
2.Part Number
 
Correct?
 
If that's true, then I would write the query in the sub-report to return me a unique list of PO's with QTY, PART_NO and AMOUNT.
 
Your subreport query should look something like this:
SELECT pla.po_header_id,
       pla.item_id,
       pla.amount
FROM   po.po_lines_all pla
WHERE  pla.po_header_id =
           (SELECT MAX (po_header_id)
            FROM   po.po_lines_all p
            WHERE  p.po_header_id = pla.po_header_id
                   AND pla.po_line_id = p.po_line_id
                   AND p.item_id = pla.item_id)
 
 
This way, when you link to your subreport, you'll only get the max(po_number). Note, the subquery could also be written to use the last_updaet_date or creation_date (if you have that information)
 
Make sense?
IP IP Logged
riker
Newbie
Newbie
Avatar

Joined: 07 Dec 2010
Online Status: Offline
Posts: 16
Quote riker Replybullet Posted: 15 May 2012 at 7:53am
I did have both of those links for the sub-report. But took out the QTY link because it only returned the exact matches which only works half the time. I tried using the variables I created to work in a range around the QTY but I can't use those in the selection formula. (still learning here) I'm going to see if I can get the SELECT, FROM and WHERE commands to do what I need. Thanks for your input.
IP IP Logged
riker
Newbie
Newbie
Avatar

Joined: 07 Dec 2010
Online Status: Offline
Posts: 16
Quote riker Replybullet Posted: 31 May 2012 at 5:24am
Same problem, different approach....
I'm no longer trying to use the price from a po, I'm trying to get it from a price list with quantity breaks. Here's what my data looks like...

1/2012     <<<group 1 (date, month)>>>                                                                           
     FLG9906     <<<group 2 (part number)>>>                                                                 
     PPAP025997   01/27/2012      22.00      $24.94   $0.57   $59.40 <<<group 3 (job number)>>>
<<<details>>>        2.70                                        59.40                         
<<<details>>>          2.70                                        59.40                         
                                                                                
2/2012     <<<group 1 (date, month)>>>                                                                           
     02250146-15F <<<group 2 (part number)>>>                                   
     PPAP026013   02/16/2012 61.00   $709.37   $1.66   0.00 <<<group 3 (job number)>>>
<<<details>>>           0.00          1.00          10.00          0.00          0.00          
<<<details>>>           0.00          2.00          5.70          0.00          0.00          
<<<details>>>           0.00          5.00          4.50          0.00          0.00          
<<<details>>>           0.00          10.00          3.48          0.00          0.00          
<<<details>>>           0.00          25.00          3.20          0.00          0.00          
<<<details>>>           0.00          50.00          2.85          2.85          173.85          
<<<details>>>           0.00          100.00          2.53          0.00          0.00     

In the first example the numbers in group 3 are the production QTY (22), mfgcost ($0.57), totalmfgcost ($24.94) and estpurchcost ($59.40). The data in the details for it is the base unit price, which is for parts without any qty breaks. When that is the case I'm able to get the estpurchcost.

In the second example the base unit price is 0.00 because we have qty breaks. You can see the quantity and corresponding price. The 4th column is a formula to select the proper price based on the job qty. Which it does. For a 61 pc job it selected 2.85. Then in the 5th column is the formula to figure the estpurchcost, it also works. The problem is I don't want to show the details but just 1 line per part number. When I move the formulas the the group header/footer it only shows the the top or bottom value, usually 0.

I'm also using the formula that selects the proper price to create a numbervar, it look like this...

shared numbervar n1 :=
if
{jobhead.prodqty} >= {vendpbrk.breakqty}
and
{jobhead.prodqty} < next ({vendpbrk.breakqty})
then
{vendpbrk.pricemodifier}
else if
isnull ({vendpbrk.pricemodifier})
then
{vendpart.baseunitprice};

I then use that var in the estpurchcost like this...

shared numbervar n1;
if
{vendpart.baseunitprice}<>0
then
{jobhead.prodqty}*{vendpart.baseunitprice}
else
n1*{jobhead.prodqty}

How can I get the selected values from these formulas to display on the group header?

Hope this makes sense.
IP IP Logged
c16271
Groupie
Groupie
Avatar

Joined: 24 Aug 2010
Location: United States
Online Status: Offline
Posts: 48
Quote c16271 Replybullet Posted: 31 May 2012 at 5:42am
Have you tried suppressing the details section of the report? That will result in you only showing:
 
G1
    G2
        G3
 
Also, the reason you're only seeing "zero" in the group header is because you're only pulling the first record.
 
Would taking the sum of the values the formula returns help? If so, sum the field and place the summarized field in your g3 or wherever. That should give you a total...
 
Hope this helps.
 
 
 
IP IP Logged
riker
Newbie
Newbie
Avatar

Joined: 07 Dec 2010
Online Status: Offline
Posts: 16
Quote riker Replybullet Posted: 31 May 2012 at 8:24am
It won't let me summarize any of the formulas...

But I just noticed something else...

This formula isn't working exactly right. The first 'if' is returning a 0 if there is no 'next' breakqty. If the top qty break is 100 and the prodqty is 101 it returns the 0, again there is no 'next'. How could I get it to show the top break price when the production qty is higher?

shared numbervar n1 :=
if
{jobhead.prodqty} >= {vendpbrk.breakqty}
and
{jobhead.prodqty} < next ({vendpbrk.breakqty})
then
{vendpbrk.pricemodifier}
else if
isnull ({vendpbrk.pricemodifier})
then
{vendpart.baseunitprice};

any ideas?
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.063 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.