Group within a group 2

[CrystaLv]

Hello,
I am stuck with a problem (I am using CR9)

I have groups of products with names that are like this

Product_Description
MNB-9-Spring
MNB-9-Fall
M-NN-8-Winter
M-NN-8-Summer
AA-BB-NN-4-LA
AA-BB-NN-4-CA


When I group I need to group
MNB-9 in one group and calculate total for this MNB-9 group.
Also I need calculation for MNB-9-Spring and MNB-9-Fall groups which is fine - I know how BUT
what about others with 3 or 4 dashes?

The only consistency here is that entry AFTER 2nd, 3rd or 4th dash makes product unique - before it is a group.

So I need to group by Product Description and AFTER that grab everything that is a sub-groups and calculate within...
MNB-9
M-NN-8
AA-BB-NN-4

Can you see how am I suppose to accomplish this?

Thank you very much for your time

 

[IdoMillet]

Find the location of the last "-" using InStrRev()

Then break the field to 2 parts (Use Left() and Right() function.

- Ido

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

http://www.MilletSoftware.com

 

[Madawc]

Do a formula field, something like

if {product} in [MNB-9-Spring, MNB-9-Fall]
then "a) Fall and Spring"
else "b) Other"

You could add a higher level group using this. Or you could use Left({product}, 4) if you just want part.

Also look at Crosstabs - automatic simple totals which can be different from the report groups.

It helps to give your Crystal version, 8.5 or 9 or whatever.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[CrystaLv]

Idomillet,
why 2 parts? Please, explain what am I doing with Left and Right function. Thank you

Madawc,
I stated that I am using CR9. 'Spring' and 'Fall' were just an examples. I can not hard code because names of the products are so different from 1 word to combination of words with 2-4 dashes. But so far I see only part AFTER last dash is changing. Thanks

 

[lbass]

Create a formula:

left({table.proddesc},instrrev({table.proddesc},"-")-1)

Insert a group on this, and then insert a second group on {table.proddesc}. Then you can right click on your detail fields and insert summaries at both the inner and outer group or report levels.

-LB

 

[CrystaLv]

WOW! Thanks!

 

[CrystaLv]

I am getting 'string length is less then zero or not an integer' error.

 

[CrystaLv]

I know it is because some products do not have dashes in their names, right? So I am trying to add 'If Not IsNull' or something.

 

[bdreed35]

Try this:

if instrrev({table.proddesc},"-") = 0
then {table.proddesc}
else left({table.proddesc},instrrev({table.proddesc},"-")-1)


~Brian

 

[CrystaLv]

THANKS BDreed35!

 

[CrystaLv]

Hi,
I am back with more questions.
My Report was fine but new task is to insert SUBREPORT based on different query that has Product_Description field - the only one corresponding field to a Main Report.

Now my Main Report has all codes trimmed.
I am trying few hours now on how to link those 2 properly.

Output expected:

Product_Description TotalMain_OK Total_SUB_Spoiled
MNB-9-Spring 100 12
MNB-9-Fall 200 10
Total MNB-9 300 22
M-NN-8-Winter 1 0
M-NN-8-Summer 8 0
Total M-NN-8 9 0
AA-BB-NN-4-LA 10 5
AA-BB-NN-4-CA 11 5
Total AA-BB-NN-4 21 10


Also Grand Total in SUB should be shown in Main and I do not see how to make it shown.

Summary of my question is:
does CR required to link SUB to Main?
If fields of the Main are formula - do I have to make SUBs field as formula also and then link it or do I have to leave it alone and just link whole field?

Thanks

 

[lbass]

Place the subreport in the group header (or footer) for the product_description group (GH#2), and link the subreport to the main on this field. Then in the subreport, create a shared variable for the subreport report footer, like:

whileprintingrecords;
shared numbervar spoiled := sum({@spoiled});//substitute your summary

Then in the main report add these formulas:

//{@reset} to be placed in a section before the subreport executes, e.g., GH2a if the sub is in GH2b (you can suppress GH2a):
whileprintingrecords;
shared numbervar spoiled := 0;

//{@resetsubtot} to be placed in the GH#1 header:
whileprintingrecords;
numbervar subtotspoiled := 0;

//{@accum} to be placed in a section below the one in which the sub is executing, e.g, in GH2c:
whileprintingrecords;
shared numbervar spoiled;
numbervar subtotspoiled := subtotspoiled + spoiled;
numbervar grtotspoiled := grtotspoiled + spoiled;

//{@displaysubtot} to be placed in the Group #1 footer (GF1):
whileprintingrecords;
numbervar subtotspoiled;

//{@displaygrtot} to be placed in the report footer:
whileprintingrecords;
numbervar grtotspoiled;

-LB

 

[CrystaLv]

Hi,
thanks a lot for looking into this for me. I had not expected answer so fast and had been trying to resolve it on my own since this morning - no luck.

I've tried your solution just now - just a beginning
Place the subreport in the group header (or footer) for the product_description group (GH#2), and link the subreport to the main on this field. Then in the subreport, create a shared variable for the subreport report footer, like:

whileprintingrecords;
shared numbervar spoiled := sum({@spoiled});//substitute your summary

and when I run SUB it comes up with numbers in place of whileprintingrecords; formula.
I link it to the MAin by product_description field and SUB comes up blank when attached.

Trying to resolve it. Thanks

One thing
I have Trimmed product_description (as you advised) in GH1 and product_description in GH2.
When created SUB should I had done the same,
Trimmed product_description in GH1 and product_description in GH2?

I had never had a problem before to link SUB to Main Reports.
But I had never used trimmed fields. I suspect that problem is in the trimming.

Thanks so much

 

[lbass]

I don't know what you mean by "and when I run SUB it comes up with numbers in place of whileprintingrecords; formula."

You should be linking {table.description} in the main report to {table.description} in the subreport, not using any formulas. I didn't advise trimming anything earlier that I can see. The earlier formula was for your Group #1, and you do not need to link on that.

What is the summary you are using in the subreport that you want displayed in the main report per description? That is the summary that belongs in place of "sum({@spoiled});" in my formula.

-LB

 

[CrystaLv]

I was talking about this formula
left({table.proddesc},instrrev({table.proddesc},"-")-1)

But I am getting there i think. Thanks for all help

 

[CrystaLv]

lbass,
I went exactly as you recommended in your post with all the formulas and every formula is showing Zero.

My Totals for Sub are 3542 and 17. It displayed in preview.
So I am not sure if I've got first step correctly.
Was I suppose to create formula
whileprintingrecords;
shared numbervar spoiled := sum({@spoiled});//substitute your summary
and place it into GH2b? I did and formula shows 3542 and 17.

However the rest of the formulas based on it show Zero in Main Report.

Thanks,T

 

[lbass]

The shared variable formula you are showing in your last post belongs in the report footer of the subreport.

-LB

 

[CrystaLv]

Hi again,
I ended up having subreport calculating total and another subreport calculating Grand total.
However I love your solution and keep trying whenever I have time but it comes up as a zeros. Thanks

 

[lbass]

Not sure what your display is, or if this is the issue, but you cannot suppress the section that the subreport is in if you are trying to pass shared variables from the subreport.

-LB