Inventory Report to find percentage 4

[hencyt]

I have an inventory report that shows on hand quantities for items with transactional activity. The transaction qtys are grouped and subtotaled by year. The user wants to know the percentage of the yearly transaction subtotal to the qty on hand- i.e. does the qty on hand = 300% of what was used last year? I need to be able to make the percentage and the year parameters, the user wants to be able to change those. So the user wants to complete this phrase "The qty on hand equals ___% of what was used in year ____."

I can't figure out how to set up the formula so that I can base the record selection off of it.

So far I know that I need QOH/sum of transactions by year to get the percentage, but how do I include the parameters and selection criteria?

I hope this question makes sense.

Thanks in advance,
Sunny

Crystal 8.5, SQL db

 

[synapsevampire]

Why would you base the record selection off of the percentage?

The percentage isn't a choice, unless you're stating that you only want to see parts that have that percentage (and perhaps greater).

This is dependent upon the database layout, but you might return the percentage from the database.

As for limiting rows, you're probably best served to just create a formula which demonstrates the percent:

sum({table.amount},{table.year})/sum({table.amount})*100

And filtering sections based on the parameter amount entered as this will be evaulated after the record selection anyway.

-k

 

[hencyt]

Thanks K for your reply. The user wants the report to only show those records that are at or above a percent level entered by him. That is why I want to base the record selection off the percentage.

I created a formula as you suggested for the percentage, but when I go to choose that formula from the record selection box- it doesn't show up there, why not?

Here is the formula.

if (Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}) = 0 )then 0 else
({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}))*100


Thanks for your help!
Sunny

 

[lbass]

Create a discrete number parameter {?Percent} and then go to report->edit selection formula->GROUP and enter:

if Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}) <> 0 then({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}))*100 >= {?Percent}

This assumes that quantity on hand is being compared to quantity used within the same year.

You can create the date parameter {?year} (multiple value, number parameter) and then in the edit selection formula->RECORD->formula area, add:

year({table.date}) = {?year}

-LB

 

[hencyt]

Thanks very much. I always forget about group selection.

I did what you suggested, and it is limiting the records now- so that is good. But it isn't showing me all the records that fit the criteria. If I run the report without the group selection formula listed above there are 157 pages. If I put the group formula back in (the only change I make)- Then it goes down to one page. All the records displayed on the one page fit the criteria (greater than or equal to 10%) but there are ALOT more reocrds that fit, that aren't shown.

Above----This assumes that quantity on hand is being compared to quantity used within the same year. --- Yes this is what I want.

I did not create the date parameter you suggested yet- as I wanted to work this issue out first.

Since that didn't work I thought I would try this--

I have this formula (QOH/percent)in my report that displays the percent value for the item to the user:

if (Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}) <> 0 )then
({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}))*100

which matches the group selection criteria. So I thought I could set the group selection to this:
{@QOH/percent} >= {?PERCENT}
But that tells me that formula cannot be used because it must be evaluated later.

What am I missing? Why did the original group selection leave out some of the records?

THANKS!!!
Sunny

 

[lbass]

Please share the contents of {@year}. I assumed this was something like: year({table.date}). You said you wanted to only show those records which meet the criteria based on a summary percentage, so I took that to mean that you wanted to return those GROUPS which meet the summary criterion. Since you are grouping on years, the formula I gave you will return those year groups with a summary that meets the criterion. If this is not what you meant, please describe your report structure and provide sample data that shows the kind of results you are looking for.

-LB

 

[hencyt]

ok thanks for the reply.

You are right.
{@year} = year({IM5_TransactionDetail.TransactionDate}).

And yes I do want to show only those records that meet or are greater than the discrete number parameter {?Percent} based on the year summary.

I do want it to return the year groups based on a summary- and the group selection formula you gave me does return some of them- but not all.

this is the current report setup:
---------------------------- cur qty OH
item# Yearly trans ttl for item
BX195 yr2001=10 qtyOH is 10000% of used qty 1000
yr2002=20 qtyOOH is 5000% of used qty
ttl all yrs:30

The % in the report is the value I would like restricted by the parameter {?percent}. Any year's %value that is greater than or equal to the {?percent} it should return.

Does that help?

Thanks,Sunny

 

[lbass]

So you have a group on item# and then a group on {@year}, correct? I think the formula should work fine as it is, so what groups are being eliminated that you think should not be? The following example BEFORE group selection:

item# yr trans ttl %ofUsedQty cur qty OH
GH1 BX195
GH2 2001 10 10000% 1000
GH2 2002 20 5000% 1000
GH2 2003 0 -- 1000
GF1 30

...would give you the following results, if {?percent} = 7000:

item# yr trans ttl %ofUsedQty cur qty OH
GH1 BX195
GH2 2001 10 10000% 1000
GF1 30

Note: You would have to use a running total in GF1 to get only the sum of the displayed values, in this case, "10".

Can you show us a sample of what results are not showing up, but you think should?

-LB

 

[IdoMillet]

To identify the issue, you can place your key formula:

if (Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}) <> 0 )then
({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}))*100

in the detail section and see if it produces the results you expect.

LB, asked you above: "So you have a group on item# and then a group on {@year}, correct?" That's a key question. If you are not grouping on Item#, then the sum is for all items in a given year rather than for the item in question...

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[hencyt]

Thanks for the replies. Yes I have group#1 on item number and grp#2 on year. LB your results are exactly what I am looking for. Thanks for mentioning the running total-I need to do that.

There are literally hundreds of results that aren't showing up that should. Here is an example with actual data.

Without group selection or parameter I see this record:

item# yr trans ttl %ofUsedQty cur qty OH
GH1 FR280-1
GH2 2002 50.40 496% 249.95
GH2 2003 21.78 1148% 249.95
GH2 2004 0.05 543378% 249.95
GF1 72.23

If I enter percent parameter value 1000 (with group selection criteria added) I should see 2003 and 2004 for this item but I don't see this item at all.

In fact, when I enter percent 1000 I only see 4 items. When I change the parameter value, more items show up, and all returned meet the criteria- but it isn't showing ALL the items that should meet the criteria. For instance, if I change the paramter to 50 then I should see most of the 150 pages of items, but I only see 8 items (the same 4 that were returned for 1000 plus 4 more). All with percents at or above 50, but there should be many more.

Ido- I don't think I understand what you mean. I did put that formula in the details (the details are suppressed) and still no luck. Did you mean just to put the field in the details? Or to suppress the details based on that field?

Thanks again for all your help. I hope I am making the problem clearer and not more confusing. It is probably something simple I have forgotten to do.

Sunny

 

[IdoMillet]

Sunny,

What happens if instead of using a Group Selection condition you apply the condition (in reverse of course) to the suppress attribute of the section?

Cheers,
- Ido



CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

You created the parameter as a number parameter, correct? And if you mean 1000%, you are entering 1000, right?

Also, please copy your group selection formula into the post. Thanks.

-LB

 

[hencyt]

Yes I created the parameter as a number parameter and placed it in the RH. For group selection formula- I copied and pasted from your post, LB into the report.

if Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}) <> 0 then({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}))*100 >= {?Percent}

IDO- I removed the group selection formula listed above and tried to conditionally suppress the GH1 by this formula:

if Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}) <> 0 then({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}))*100 <= {?Percent}

I entered 10000 for the percent and the return items are very strange. They are all 0% except for one item.

Thanks again.
Sunny

 

[lbass]

I wonder whether the number parameter is the problem. If 1.0 = 100%, then maybe the number parameter needs to be multiplied by 100 also (or the *100 removed from the other side of the equation). Do you get the correct results if you do this?

-LB

 

[hencyt]

LB-

I changed the group selection formula to this:

if Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}) <> 0 then({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}))*100 >= ({?Percent} *100)

When I enter 1000 for the percent- before I changed the formula it returned 4 items (all greater than 1000) and after I changed it- it returned 1 item- the biggest one of the four where percent = 260,120.

It is as if there is another record selection somewhere telling it to pick from only a small group of items. Becuase the returned items always meet the criteria, but there are many more that do that are not returned.

Thanks so much for sticking with me on this.

Sunny

 

[lbass]

I recreated your report design, and I am getting the correct groups returned with the group select, using a number parameter that matches the percent (no adjustments). I think we will have to see a sample of your detail level data to determine what's going on. The sample should show at least one full Group #1 with all Group #2's represented. Do you have any smaller groups without a ton of records? If this isn't feasible, then I think you need to unsuppress your details and add the formula as Ido suggested to the detail section. The on-hand quantity should appear the same in all Group 2's as should the summary formula.

Not sure what else to try here.

-LB

 

[hencyt]

Thanks.

Here is the sample data- with group selection removed. No years of this item shows up no matter what percent criteria I use.

item# yr trans ttl %ofUsedQty cur qty OH
GH1 BT16001 1513.22
GH2 2001 -832.92 182%
GH2 2002 -1476.84 102%
GH2 2003 -975.52 155%
GH2 2004 -986.78 153%
GF1 -4276.06

This item shows up no matter what percent I use (as long as the % is at or less then 260,120)

item# yr trans ttl %ofUsedQty cur qty OH
GH1 PM089N 1513.22
GH2 2003 64.00 -260120%
GF1 -59377.44



In the second example there are other values in other years that aren't included for some reason(thus the high subtotal).

Without the group criteria entered that item looks like this:
item# yr trans ttl %ofUsedQty cur qty OH
GH1 PM089N 1513.22
GH2 2003 64.00 -260120%
GH2 2002 -56,618.19 294%
GH@ 2004 -2823.25 5897%
GF1 -59377.44



Wait- I think it isn't showing the positive percents when the group selection is entered. Let me see if I can figure out why that is....

Thanks!
Sunny

 

[lbass]

How are you getting positive percentages when calculating the following?

1513.22/-832.92*100

This should yield "-182%" not "182%", so yes, I agree with you that the signs are probably the problem.

-LB

 

[hencyt]

How should I change this formula so that it will just show the absolute value? I know I can use ABS function but it doesn't like it wherever I put it.

if (Sum ({IM5_TransactionDetail.TransactionQty}, {@Year}) <> 0 )then
({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/(Sum ({IM5_TransactionDetail.TransactionQty}, {@Year})))*100


THANKS AGAIN.

Sunny

 

[hencyt]

I got it. I changed the original field to ABS first. Then changed all the formulas. It works now. YEAH! Thanks for your help LB and Ido!

Here is the final group selection formula:

if (Sum ({@abs trans qty}, {@Year})) <> 0 then({IM_TDH1_Raw_Inventory_On_Hand_VIEW.TotalQtyOnHand}/Sum ({@abs trans qty}, {@Year}))*100 >= {?Percent}


Sunny