Subtotal by Alpha Character in a field

[spiced]

I'm new to Crystal reports and am using V7. I am sorting by Component ID# which consists of an Alpha then digits or some other character. What I want to do is subtotal the Alpha characters.

Thanks,
Darrel L. Spicer

 

[dgillz]

Create a formula field that isolates these characters, such as left({fieldname},5), or whatever it takes to extract these characters, then subtotal on the formula field. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[spiced]

The ID numbers start with A and end with Z if there is a way for the formula to only use the Alpha and not the numbers for the subtotal without specifying each letter in a formula.
I would be able to use a select and use "Component ID" "is like" "A*" and it would select all the A's and the report design would give me a grand total.

 

[dgillz]

Left({IDnumber},1) should do the trick. All the As will be in one group, all the Bs in the next, etc. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[spiced]

OK what would be the best way to do this? I can't get the subtotal to work only grand total. Then where would I edit the formula?

 

[dgillz]

You have a grand total, but not totals for As, totals for Bs, etc? Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[spiced]

Correct

 

[dgillz]

In the design window, right click on the field(s) you want subtotaled. Select insert, summary and choose the formula field you created - the left({IDnumber},1) formula - as your group by field, and it should be a sum operation. Thats it, you're done. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[spiced]

"left({EMComponent ID},1)" I tried this and it shows up in the report just like its typed in. Ok lets start from scratch, what steps do I use to create a formula?

 

[dgillz]

Insert, field object, formula field. Name the field and enter exactly as above, execpt dump the quotes. When you do that crystal takes the formula to be equal to that literal string and it will not calculate anything. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[spiced]

left({Equipment.Component ID},1) Ok this will give me the first letter of each ID# but no subtotal.

 

[dgillz]

Ok, the subtotal is step 2. In the design window, right click on the number you want subtotaled. Select "insert summary", make it a sum, and sum by the formula field you just created for the first letter of each ID.

Now you should have subtotals. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[spiced]

Thanks it's working now I just need to get the format done.

 

[dgillz]

What do you need with formatting? Please let me know. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[spiced]

Just how the final arrangement is going to look. I know I will have other questions in the future, but right now I have a lot of other things that also have to get done.
Thanks again,
Darrel