Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Writing Report Syntax Post Reply Post New Topic
Author Message
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Topic: Writing Report Syntax
    Posted: 08 Jan 2014 at 12:19pm
I'm trying to write a formula field that will work like this...

Take the QuantityOnHand in Location 1 and subtract  QuantityOnHand in Location 2 and 3 from it. Displaying the remaining amount.

QuantityOnHand and Location are two columns in same SQL DB, one particular item number might have a QoH in Loc 1, 2, 3 or just one of the locations.

Any help would be greatly appreciated.
IP IP Logged
bwsanders
Senior Member
Senior Member


Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
Quote bwsanders Replybullet Posted: 09 Jan 2014 at 3:23am
I think the cleanest way to do this would be to write a formula to sum the QOH based on location. Once that is done you can do whatever math you would like to do.

ex. If {location} = "whatever place" and {itemid} = "whatever thing" Then SUM({itemid})
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Jan 2014 at 4:46am

MTC

rhoup on the item
create a formula that flips the field between positive and negative values and then you can just sum that formula at teh item group level .
 
//pos_nev_quantityonhand
if table.location=1 then table.quantityonhand else table.quantityonhand*(-1)
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 09 Jan 2014 at 5:32am
I took your formula and used it to define loc 1, 2 and 3 qty, so I have (3) formula fields... qohloc1, qohloc2 and qohloc3. Now I need to subtract qohloc2 and qohloc3 from qohloc1, but they only need to subtract if there a negative number if there positive they need to add up. Loc 1 might show -10 and loc 2 shows 5 then the available should be -5.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Jan 2014 at 5:41am
what is some sample (mock if need be) row level data without the formula field in it?

Edited by DBlank - 09 Jan 2014 at 5:41am
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 09 Jan 2014 at 6:40am
qohloc1 formula field
if {iminvloc_sql.loc} = '1' then {iminvloc_sql.qty_on_hand} else {iminvloc_sql.qty_on_hand} * (-1)

qohloc2 formula field
if {iminvloc_sql.loc} = '2' then {iminvloc_sql.qty_on_hand} else {iminvloc_sql.qty_on_hand} * (-1)

qohloc3 formula field
if {iminvloc_sql.loc} = '3' then {iminvloc_sql.qty_on_hand} else {iminvloc_sql.qty_on_hand} * (-1)

Data from qohloc1 for a particular item...10.00
Data from qohloc2 for a particular item...-10.00
Data from qohloc3 for a particular item...-10.00
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Jan 2014 at 8:43am

Sorry I need to make sure i understand your data and your needed output before I could try and offer a solution for this.

This
"but they only need to subtract if there a negative number if there positive they need to add up. Loc 1 might show -10 and loc 2 shows 5 then the available should be -5."
is confusing me.
 
Is the iminvloc_sql.qty_on_hand always a positive value in your raw data set (before you apply any formula to it)?
Your description above implies that you start out with both positive and negative values.
If that is the case you should just be able to sum the quantity at the group item level.
Your first post implies that the all 3 locations have positive values but you need to know the difference in quantity between loc 1 from loc 2 and 3 combined.
Your more recent post implies that you just want to know the total of all 3 and some might be positive values, other might be negative values.
Please explain further.
Thanks
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 09 Jan 2014 at 8:49am
Sorry about that, the values can be positive and negative depending on the stock level in each location, the combined value needs to reflect basically how many they need to make to fulfill the demand. If they have positive 10 in location 1 and -5 in the other two it should reflect that they need to make 0 in the field I'm trying to create.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Jan 2014 at 8:53am
this is a straight sum of the field placed in the group footer (grouping on the item id)
group on it
insert a summary (sigma button)
select the quantity field
select sum as teh type
select group footer for the reset
this shoud drop/create a summarized field that gives you the value you want in the footer of the group.


Edited by DBlank - 09 Jan 2014 at 8:54am
IP IP Logged
jszawlosky
Newbie
Newbie
Avatar

Joined: 08 Jan 2014
Online Status: Offline
Posts: 16
Quote jszawlosky Replybullet Posted: 09 Jan 2014 at 9:45am
That worked thanks for your help!
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.016 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.