subtotaling a portion of the report data 3

[maxxheight]

Crytstal 8.5 rookie here.

I work for a company that owns some motorcycle dealerships and I have some inventory data compiled from our dealers in a dbf file that I am running a crystal reports against.

Our inventory is broken up into categories (apparel, helmets, etc). The report I built subtotals the data for each dealer by dealer name and then by category (we have 12 dealers, and each dealer has about 30 categories so there are a pile of subtotals.

I would like to add two new fields to the report. One that shows a subtotal by dealer/category for the total inventory that is over 12 months old (this is another field in the report), and then a second field that shows what percentage of the entre inventory that old inventory comprises for that dealer

so it would look like

dealer 1
category 1
subtotal1 (inventory)
subtotal2 (inventory over 12 months old)
% subtotal 2 is of one

I could just have the report only print out items over 12 months old, but then I would not be able to compare the total value to the aged value. If anyone has any advice to offer I would appreciate it on how to create these additional fields and what the formulas would look like

Mark

 

[lbass]

I would create a formula:

if datediff("d",{table.date},currentdate) > 365 then {table.amt}

Then right click on this formula and insert summaries. You could also use running totals adding the condition into the evaluate by formula section, but running totals slow down a report more than formulas with inserted summaries.

-LB

 

[maxxheight]

I think that would work if I had a date, but all I have for the aging information is a "months no sale" field that shows how many months it has been since that item number has sold. What would you recommend for that one.

I tried the following formula but it gives a result of 0.00

if {partsreport.NOSALESDAY} >= 12 then Sum ({partsreport.EXT_COST})

 

[synapsevampire]

Close, try something like:

//@yearoldvalue
if {partsreport.NOSALESDAY} >= 12 then ({partsreport.EXT_COST})
else
0

Now place it in the details, right click it and select insert->summary->sum

Or you might reference the formula within a sum formula:

sum(@yearoldvalue,{table.group})

-k

 

[lbass]

Given your field, SV's formula is the way to go...

-LB

 

[maxxheight]

It is starting to come together but I had to modify the syntax a little bit and add the sum command

//@yearoldvalue
if {partsreport.NOSALESDAY} >= 12 then sum({partsreport.EXT_COST})
else
0


The problem now is that the formuala is looking at all of the records in the subtotal section, and if any of them are over 12 months old then it is showing the grand total for the entire inventory file. The results look somewhat like this

Dealer Category Total value >=12 value
BCC ACC 50,000 0.
BCC HEL 60,000 0.
BCC SPK 40,000 6,500,000 (which is the value of the enitre inventory file)
WCC ACC 90,000 6,500,000
WCC HEL 45,000 0.
WCC TIR 80,000 0.

etc

What I really need the >=12 value to be is the subtotal of the items from the dealer and category listed on the left, not the value of the entire file, and then that value needs to be only the value of the items from that dealer and category that are more than 12 months old.

Hope all this makes sense, you all are a great help to rookies like me.

Mark

 

[lbass]

WHY did you change the syntax? The syntax should be:

if {partsreport.NOSALESDAY} >= 12 then {partsreport.EXT_COST}
else
0

Place this in the details section and then right click on this formula and insert a summary at the category group level and at the dealer level, if you wish.

-LB

 

[maxxheight]

It all makes sense now. Just needed to go home and clear my head. I was adding the field to the footer line and not the detail line. So this morning, I added it back to the detail line, and then figured out how to do the subtotal as I had never done that before.

Works great

Now I just need to work on showing what percentage the subtotal for each group is of the total value of that group.

Thanks a ton for all the help.

Mark

 

[lbass]

You should be able to right click on the detail formula again->insert summary->choose the category group for the group field and then select "Show as a percentage of" and then choose the dealer group.

-LB

 

[maxxheight]

Played with it for a bit but I am running in to a problem with the >12 months inventory value being divided by the grand total of all the dealers >12 months inventory values, versus the total inventory value for just the dealer in that section. I looked in the drop down list, but I only have two options to compare the >12 months figure to, either the grand total of all the dealers inventories, or the grand total of all the dealers >12 months inventory values.

Example
Dealer Category inv value >12 value
BCC ACC 50,000 12,000
BCC HEL 60,000 5,000
BCC SPK 40,000 4,000
subtotal 150,000

WCC ACC 90,000 0
WCC HEL 45,000 3,500
WCC TIR 80,000 2,000
subtotal 215,000

Grand total 365,000 26,500

I would like to have a percentage field over to the right that would take the >12 value as a percent of the dealers total inventory (12,000/150,000 for the first one) but the only options I get are to divide $12,000 by 365,000 or 26,500.

Any more great tips. And again thanks for all the help so far.

Mark

 

[lbass]

Create a formula:

sum({@>12months},{table.category}) % sum({table.invvalue},{table.dealer})

Substitute the correct field names, and then place this formula in the category group header or footer. You can select the formula in preview and click on the "%" icon to get the percentage format.

-LB

 

[maxxheight]

Thanks, that solved the problem.

If you have a boss, tell him to give you a raise and a day off.

Have a great weekend.

Mark