Group Suppression 1

[MadCatmk2]

Hi

Vital stats first of all:
Crystal 8.5 connecting to SQL server via OLEDB connection.

I'm having a problem with suppressing groups within a report.

The report i have has a group relting to an ID field in the data base. Each ID has a name location and department associated with it, these are listed in the details section

The Request table is a one to many relation with the ClientDetails table joined on the ID field. ClientDetails links with a Style table.

Each record in the ClientDetails table has a StyleID which links to the Style table. If for example this ID was 2 then {Style.alpha1} takes on the value "name", if its 4 then it assumes the value "Location"

e.g.

[Group Header] {Request.ID}
[Details section]{Style.alpha1} {ClientDetails.Alpha1}

possible output

10000
name John Smith
location Hospital1
department CCU
10001
name Bob Jones
location Hospital2
department A&E

Given this output what i wuld like to do is suppress all of the groups with the location = Hospital2

i.e. {ClientDetails.alpha1} = "location"

Does anyone have any ideas how i can do this. If i try using the record selection and include the above criteria it shows all of the desired records but it omits the name and department fields as they are also {ClientDetails.alpha1} just that they have a different value due to the style ID.

This has been troubling me for a while now and i'm really not sure how this can be resolved if at all. Any help will be most appreciated. I hope i've explained the problem well enough.

Thanks in advance

 

[LidoDeJesolo]

How about using multiple details sections? One for each variable? Then set different suppression rules on each?

Lido
Development & Reporting
UK

 

[MadCatmk2]

Problem with that method is that there is only the one field {clientdetails.alpha1}. Usually there are three clientdetails records for each ID, and depending on the {ClientDetails.styleID}, alpha1 takes on the value name, department, or location.

Does that make sense?

Thanks for the quick reply.

 

[MadCatmk2]

Is there a way that i can suppress the group header and everything else encompassed by the group based on the criteria in the details section. E.g if one of the {ClientDetails.alpha1} = "Hospital1" then suppress all of the {request.ID} group containing the details.

Thanks

 

[LidoDeJesolo]

But you could repeat that one field in each detail section with different suppression rules on each?

Lido
Development & Reporting
UK

 

[MadCatmk2]

I'm not sure if i explained my initial problem quite as well as i thought. At present i can get name location and department to appear in the details section. By suggesting three details section with different suppresion rules i get exactly the same as i have already.

The problem i am having is when i come to suppress the groups where a certain criteria is met. in this case

{ClientDetails.alpha1} = "Hospital1".

where Hospital1 is a location

When using the record selection or the group selection it lists the correct groups the only problem is that it omits the {ClientDetails.alpha1} details that equal the name and department after the location is selected. I need to show all {ClientDetails.alpha1} details for those groups that meet the criteria as apposed to just location in this instance.

Does this make more sense or am i just digging a hole for myself here ?

Thanks

 

[LidoDeJesolo]

My apologies. I misunderstood your original question.

Your group is: [Group Header] {Request.ID}

So you need to suppress the group, not the detail but base the suppression of the group on the value of the field {ClientDetails.Alpha1} in the detail section. This will suppress all the fields in each record according to how you have grouped your records.

Are we getting close?


Lido
Development & Reporting
UK

 

[lbass]

Not real clear on how your fields work, but you could try creating a formula {@Hosp1}:

if {ClientDetails.alpha1} = "Hospital1" and
{Style.alpha1} = "4" then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@Hosp1}, {table.requestID}) = 0

This should give you the groups without Hospital1.

-LB

 

[MadCatmk2]

hi

Lido:

That is indeed my group header and you are correct when you say i want to suppress the group based on a value in the details section. I also want the details for that group suppressed. The problem when i try the group suppression is that it only suppresses the group header and not the entire group as i'd like. Using the group selection and searching for Hospital1 (location) it shows the groups containing the location hospital1 but it suppresses the rest of the details i.e. name and department because they also come from the value {ClientDetails.Alpha1}. I think were getting closer to what i'm looking for, hope this makes more sense to you.

LB:

I'll give your suggestion a go. To be honest it took me a bit to figure out how the fields in the database work as its not one of my databases. I'll let you know how i get on with that. If it doesn't work, i'll do my best to explain how the fields work in the database.

I appreciate the help, thanks.

 

[MadCatmk2]

LB:

Thank you so so much, that has solved the problem. I can now search on location. I'm going to have to try and allow the user to be able to select on more than just the location but that shouldn't be to difficult now that i know how to go about it.

Lido:

Thanks for your help as well and for persevering with my vague problem descriptions.

Many thanks

 

[MadCatmk2]

Sorry to ask yet another question. Wasn't sure whether to create a new post for this or not.

I have a parameter field now where the user enters the department and location they wish to search on. In the search criteria i am using the like operator as apposed to equals so that i can have a broader search.

i.e.

if ({CIID.Alpha1} like "*" + {?Location} + "*" and {CIID.StyleID} = 4)
then
1
else
0

If i set the parameter field to allow multiple values will the like command search all the entries stored in the parameter like an array?

thanks

 

[lbass]

Actually, I think you could change the detail level formula to:

if {ClientDetails.alpha1} = "Hospital1" then 1 else 0

...and then use the group select formula from my earlier post.

With regard to your new issue, it looks like you are actually planning to use two parameters, one for department and one for location. Because of your unusual database design, where the same field contains either department or location (or name) depending upon another field, I think you would have to do something like the following. First create two formulas for the detail section:

//{@location}:
if {CIID.Alpha1} like "*"+{?location}+"*" then 1 else 0

//{@department}:
if {CIID.Alpha1} like "*"+{?department}+"*"
then 1 else 0

As I understand it, each of your groups (RequestID) can only have one status code = 5 (department) and only one status code = 4 (location). Therefore, in order to select those groups with the location of your choice AND the department of your choice, you could use a group select like:

sum({@location},{table.requestID})+sum({@department},{table.requestID}) = 2

This assumes that each of your parameters {?location} and {?department} are discrete value (not multiple value) parameters. I have been unable to successfully use like with wildcards and a multiple value parameter. Maybe someone else can jump in if they know how to make that work...

-LB

 

[MadCatmk2]

Thanks for that. I got that part working myself, using a seperate formula for both location and department. Its exactly the problem you were having that i am experiencing where by i cannot get the like operator and wildcards working. I'll keep looking at it, maybe something will come up.

Thanks for all of your help, its been really helpful.