CODEWITHSUNDEEP
crystal-reports
Joseph Gagnon
Joseph Gagnon

Reputation: 2135

Crystal Reports - How to conditionally sum a value

I'll start this off by saying I know almost nothing about Crystal Reports. I can look at the layout and view formulas, but beyond that I know next to nothing.

We have an existing Crystal Report that displays, among other things, a quantity available summed for a collection of item numbers (products). What this means is that individual data records in a database table (known as F41021) will have an available quantity, but what we are interested in is the sum of available quantity for each item number. So, there could be dozens of individual records contributing to the sum of available quantity for item XYZ.

Each individual record of information also has a branch location, where a quantity of item XYZ was produced. In most cases, we don't care which branch a given quantity of item XYZ was produced, but there are exceptions where we do. In these cases we want to only sum those quantities that were produced in a specific branch, and NOT include in the sum, those produced in other branches.

This need to isolate available quantity to a specific branch applies only to certain "families" of item numbers. The items that have this special condition is anything where the item number contains the following patterns: PK, PJ or TP. Any other items that do not match this criteria can calculate the available quantity as the sum of what's available at ANY branch.

Formulas

Here are the relevant formulas in the report that give us the available quantity:

AVAILABLE =
    if {F41021.LILOTS}<>" " then 0
    else if {HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*72H*" then ((({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM})*1.5))/100
    else (({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM}))/100

You can see the available quantity calculation as (({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM}))/100 (The divide by 100 is to scale the value from data representation to actual value). The if-then-else logic is to handle special cases.

CONDITIONAL_AVAIL = 
    if {@PROD_FAMILY} in {?PRODUCT FAMILY(IES) for ALWAYS AVAIL (500)} then 500 
    else if {HOMEDEPOT_SKUS_.CURRENT FOSS INV SKU} like "*72HF" then 1000
    else if Sum ({@AVAILABLE}, {F4102.IBLITM})<={?NO AVAIL QTY THRESHOLD} then 0
    else if Sum ({@AVAILABLE}, {F4102.IBLITM})>={?MAX AVAIL QTY to DISPLAY} then {?MAX AVAIL QTY to DISPLAY}
    else round(Sum ({@AVAILABLE}, {F4102.IBLITM}),0)

The result of this formula is what is displayed on the report. The report has some parameters defined to allow the user to adjust some key pieces of data prior to execution. These can probably be ignored for the purposes of this discussion. As you can see, the AVAILABLE formula is summed for each item number grouping (F4102.IBLITM).

Data

Here is a sample of data from the F41021 table for an item number that has inventory in more than one branch:

Branch  Qty On      Qty Hard    Qty Soft
        Hand        Committed   Committed
(LIMCU) (LIPQOH)    (LIHCOM)    (LIPCOM)
B50     2321        0           2
B50     1           0           0
B50     602         0           0
B70     5           0           0

In this example, the desired quantity available should be (2321 - 2) + 1 + 602 = 2922.

First try

I attempted to update the AVAILABLE formula as follows:

AVAILABLE =
    if {F41021.LILOTS}<>" " then 0
    else if {HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*72H*" then ((({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM})*1.5))/100
    else if (({HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*PK*") or
             ({HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*PJ*") or
             ({HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*TP*")) then
             (
                if {F41021.LIMCU} = "B50" then (({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM}))/100
                else 0
             )
    else (({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM}))/100

Item numbers that contain PK, PJ or TP are the items we want to sum only for a specific branch (B50). F41021.LIMCU is the branch for each F41021 record that the report is operating upon. When I run the report, the final result for available quantity (for the sample item) is 0, definitely not what is needed or expected.

Second try

Slight modification to the formula:

AVAILABLE =
    if {F41021.LILOTS}<>" " then 0
    else if {HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*72H*" then ((({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM})*1.5))/100
    else if (({HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*PK*") or
             ({HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*PJ*") or
             ({HOMEDEPOT_SKUS_.HOMEDEPOT#COM SKU} like "*TP*")) then
             (
                if {HOMEDEPOT_SKUS_.F5} = "B50" then (({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM}))/100
                else 0
             )
    else (({F41021.LIPQOH}-{F41021.LIPCOM}-{F41021.LIHCOM}))/100

Note that F41021.LIMCU has been replaced with HOMEDEPOT_SKUS_.F5, which is a column in another table being used by the report. This column is set to "B50" for each SKU item (equivalent to item number) in that table, that needs to be totaled only for B50, and left blank for the rest. In this case, when I run the report, the final result for available quantity is 2927. It has obviously added in the 5 quantity on-hand (LIPQOH) from B70.

I don't understand why I'm getting the results I'm seeing, nor how to get what I need.

Upvotes: 0

Views: 1195

Answers (2)

Joseph Gagnon
Joseph Gagnon

Reputation: 2135

It's always the little things. My first attempt at the modified AVAILABLE formula was on the right track. What made the difference was that my {F41021.LIMCU} = "B50" needed to be {F41021.LIMCU} like "*B50". Why? Because for some reason unknown to me, the values for the LIMCU column are left-padded with some number of blanks. SMH.

Upvotes: 0

MilletSoftware
MilletSoftware

Reputation: 4026

Step 1: Create a row-level (not summary) formula that simply returns the Quantity if the record belongs to the target branch and zero otherwise.

Step 2: Sum that formula.

Upvotes: 1

Related Questions