How can i group by Variable parms selected.

[ronbconfused]

I am on version 10 of crystal. I have a sales report that i have to convert to crystal and i am having trouble. Here's an example of what i need to do:
We will allow variable categories selected from the parms drop down and they will be printed as a Primary, Secondary and Tertiary group with totals. The 3 groups will have the same items to choose from which are Sales Closer, Sales Manager, Price Code and Sale type. As you can see the only 2 that have anything in common is the first 2. If the user selects the first 3 as the primary, secondary and tertiary then the report will show the number of sales by price codes for the sales manager assigned to the closer.

I really have no idea how to do this. i do have a formula set up like this but i'm not sure what to do with it:

Switch (
{?PM-Primary}="Closer",{PersonnelTrans.Title},
{?PM-Primary}="Salesperson",{PersonnelTrans.Title},
{?PM-Primary}="Price Code",ToText ({SalesInventory.Week}),
)

i also have a record select based on each parm but again i am missing something. i have only been working in crystal for less than 2 months. if i had to do this on the as/400 no problem but this has me stumped. i hope i have been at least somewhat clear on this.

thanks for any help you folks can come up with.

 

[ronbconfused]

I forgot to add that the formula for the parm selections which i showed a sample of above is not displaying any values which must mean the formula isn't working. i placed the 3 parms in the detail line to see what values they have and they do not have any values

 

[lbass]

You are clearly using a subreport. Please explain your overall group structure, where the subreport is located, and explain how the parameters should work in relation to the subreport. Please note that you cannot group a main report by subreport values.

-LB

 

[ronbconfused]

no i'm not using a subreport. How would i be able to? the report is way too flexible or can i? Here's what the report needs to look like, bear with me as i go from group3 to 1:

Tertiary group tours sold Percentage
Secondary Group
Primary Group

this is a simplistic version but that's the idea. I have a formula for each group containing the switch sample and the group name is Group 3: @primaygroup(the formula) etc

 

[lbass]

I'm not suggesting you use a subreport. It's just that parameters only appear like {?pm-parameter} when using a subreport--as far as I know. Unless you are creating the parameters in another application?

Anyway, create three formulas like your first switch formula, and insert groups on them, so that the primary parameter is used in group 1, the secondary in group 2, etc.

I'm not sure you should use your parameters in the record selection formula. Try removing that, and just using them in your grouping. The formulas should appear in the field explorer like {?primary}, {?secondary},{?tertiary}. Or you can paste your record selection formula here. However, the main point of the parameters appears to be creating the correct groups.

-LB

 

[ronbconfused]

i have the parms up front so the users can select which category to sort by in the groups. i created the formulas as shown and am grouping on those formulas. i took the record select out since that made no sense and i still get nothing. i think the problem is the formulas since i have the formulas in the detail section which will be surpressed and the formulas show nothing. i have the groups in the group footer section. i know this is hard for you to imagine and i can't tell you how much i appreciate your helping me. i'm not being too clear huh.if you want i can email you the report

 

[lbass]

Please share the contents of your formulas of your three group formulas.

-LB

 

[ronbconfused]

Sure. I have 3 formulas called Primary, secondary and tertiary.

Primary = Switch (
{?PM-Primary}="Lease",{Roles.Role},
{?PM-Primary}="Broker",{Roles.Role},
{?PM-Primary}="TGEN",{Roles.Role},
{?PM-Primary}="Closer",{PersonnelTrans.Title},
{?PM-Primary}="Closer - Split",{PersonnelTrans.Title},
{?PM-Primary}="Manager",{PersonnelTrans.Title},
{?PM-Primary}="Salesperson",{PersonnelTrans.Title},
{?PM-Primary}="TO",{PersonnelTrans.Title},
{?PM-Primary}="Price Code",ToText ({SalesInventory.Week}),
{?PM-Primary}="Contract Week",ToText ({SoldInventory.Week}),
{?PM-Primary}="Sale Type",{Contract.ContractType},
{?PM-Primary}="Source",{Campaign.CampaignName}
)

Secondary - Switch (
{?PM-Secondary}="Broker",{Roles.Role},
{?PM-Secondary}="Lease",{Roles.Role},
{?PM-Secondary}="TGEN",{Roles.Role},
{?PM-Secondary}="Closer",{PersonnelTrans.Title},
{?PM-Secondary}="Closer - Split",{PersonnelTrans.Title},
{?PM-Secondary}="Manager",{PersonnelTrans.Title},
{?PM-Secondary}="Salesperson",{PersonnelTrans.Title},
{?PM-Secondary}="TO",{PersonnelTrans.Title},
{?PM-Secondary}="Price Code",ToText ({SalesInventory.Week}),
{?PM-Secondary}="Contract Week",ToText ({SoldInventory.Week}),
{?PM-Secondary}="Sale Type",{Contract.ContractType},
{?PM-Secondary}="Source",{Campaign.CampaignName}
)
Tertiary -Switch (
{?PM-Tertiary}="Lease",{Roles.Role},
{?PM-Tertiary}="Broker",{Roles.Role},
{?PM-Tertiary}="TGEN",{Roles.Role},
{?PM-Tertiary}="Closer",{PersonnelTrans.Title},
{?PM-Tertiary}="Closer - Split",{PersonnelTrans.Title},
{?PM-Tertiary}="Manager",{PersonnelTrans.Title},
{?PM-Tertiary}="Salesperson",{PersonnelTrans.Title},
{?PM-Tertiary}="TO",{PersonnelTrans.Title},
{?PM-Tertiary}="Price Code",ToText ({SalesInventory.Week}),
{?PM-Tertiary}="Contract Week",ToText ({SoldInventory.Week}),
{?PM-Tertiary}="Sale Type",{Contract.ContractType},
{?PM-Tertiary}="Source",{Campaign.CampaignName}
)

as you can see not all the fields in the parm set are from the same file. For the grouping i am grouping by these 3 formilaus. I can't get anything to show in these 3 formulas. that has got to be one of my problems. this is frustrating. whats new right

 

[lbass]

Where are you getting the parameters? If you created them within CR, they would appear like {?Primary}, not {?PM-primary}.

-LB

 

[ronbconfused]

I named them PM-Primary? could that be a problem? The others here want to have a naming convention so that's why i name them that way. I created the parms for the user to select from a list which corresponds to the formula

 

[ronbconfused]

i just renamed the parms and i still get the same non result

 

[ronbconfused]

Here's another thing that confuses me. if i select 'Broker' under what i have now how will only people with 'Broker' in the title field appear without a record select? also why am i not getting any values in the formulas when i can see them in the parms which i list on the report

 

[lbass]

Okay, now I see. I think your naming convention will be confusing if you ever need to add a subreport. Otherwise, it doesn't matter.

Are you getting ANY data in the detail section of the report? It look like you are using lots of tables--are you sure your linking is correct? If you use equal joins among all the tables, you will only get records when there is a matching record in every table.

I would start by getting data to show first. Then you can add the record selection formula, e.g,

(
if "Broker" in [{?PM-Primary},{?PM-Secondary},{?PM-Tertiary}] then
{Roles.Role} = "Broker"
) and
if //etc.

But, note that this will be complicated, because multiple parameter selections result in the same field selection. You will have to be careful about using "or" and "and" in the right places.

-LB

 

[ronbconfused]

I am doing left joins. that was one of my concerns. When i place a field from one of the tables in the detail section i am getting data. Now if i select 'Broker', 'Lease' and 'TGEN' for example i see all the different types of sales people. Like you said there are other areas that they want to use such as 'Price Code' which is not a sales person. Week number is in another table. i really don't know how to do this let alone make the selection as dynamic as they want it to be. i was wondering if a subprocedure would help. that's is also beyond me right now.

 

[lbass]

I am not sure what the problem is anymore.Are you getting data in the detail section, after inserting groups on the three formulas? If so, and the record selection formula is the remaining problem, try setting it up like this:

(
if {?PM-Primary} in ["Broker","Lease","TGEN"] then
{Roles.Role} = {?PM-Primary} else //assuming that field options for
//{roles.role} match the parameter text
if {?PM-Primary} in ["Closer","Closer-Split","Manager","Salesperson"] then
{Roles.Role} = {?PM-Primary} else
if //etc.
) and
(
if {?PM-Secondary} in ["Broker","Lease","TGEN"] then
{Roles.Role} = {?PM-Secondary} else
if {?PM-Secondary} in ["Closer","Closer-Split","Manager","Salesperson"] then
{Roles.Role} = {?PM-Secondary} else
if //etc.
)

-LB

 

[ronbconfused]

Now you're feeling my pain. I've been on this for about 6 weeks so i am quit lost. Some are telling me i don't need a record select but i can't see that. How else would i limit the reocords to what i want. The formulas are my problem. also i am writing a line in the group footer. i added the detail for debugging purposes. i think if the formulas worked i'd be further along. i am grouping on the 3 formulas i posted earlier. what you posted above does that go in to the record select? if so how would i do that with such a dynamic select? the select could be based on 3 different files.


thanks so much for your time. i'm sure it's not what you want to be doing.

 

[lbass]

You are not being specific in describing what is wrong. Please try to respond directly to my questions. I can't tell what is not working. Start by removing the record selection. Place your grouping formulas in the detail section. Are any of them populated? Also, can any of the fields be null? You might want to go to file->report options-> and check "Convert nulls to default values".

-LB

 

[ronbconfused]

i know i'm not being too clear. i have all 3 formulas in the detail section and none of them have data in them. to verify that i'm getting data i created a formular called name shich is the first and last names of a salesperson. i am seeing data in that formula.

Toanswer your question about nulls, no they cannot. If i take the sql from crystal and place it in query analyzer i get records.

 

[ronbconfused]

Would i be better off doing a store procedure for this? i want to stay as simple as possible for futher changes

 

[lbass]

Did you remove the record selection formula before testing to see if the formulas returned data?

-LB