Formula required for Attribute information

Tom Yates

Hi,



I am new to Crystal Reports and wondered if someone could help me? 



I am trying to run a sub report to group Attribute Benefits. 



I am running a report to see who has a benefit of 'Christmas Card' or 'Christmas E-Card' or Both. 



In my sub report I have created the following formula but it is not showing if a constituent has both benefits on their record. 



If {CnAttrCat_1.CnAttrCat_1_Description} = "Christmas Card" then "Xmas Card"

else if {CnAttrCat_1.CnAttrCat_1_Description} = "Christmas E-Card" then "Xmas E-Card"

else if {CnAttrCat_1.CnAttrCat_1_Description} = "Christmas Card" and {CnAttrCat_1.CnAttrCat_1_Description} = "Christmas E-Card" then "Both"



Any help would be appreciated.



Thanks, Ryan 

Alan French

Hi Ryan,



The reason it is not showing if a constituent has both attributes is because the formula is testing each attribute to see if it meets the criteria, and a single attribute cannot be equal to both "Christmas Card" and "Christmas E-Card".



The way I would do this would be to create 2 formulas to assign a value of 1 to each attribute:

If {CnAttrCat_1.CnAttrCat_1_Description} = "Christmas Card" then 1

and in a separate formula:

If {CnAttrCat_1.CnAttrCat_1_Description} = "Christmas E-Card" then 1



and then group your report by constituent ID and insert a summary to the group header for each of these formulas. If the constituent has both attributes then the group header will contain two 1s, otherwise they will have a 1 and a 0.

Tom Yates

Alan French:

Hi Ryan,



The reason it is not showing if a constituent has both attributes is because the formula is testing each attribute to see if it meets the criteria, and a single attribute cannot be equal to both "Christmas Card" and "Christmas E-Card".



The way I would do this would be to create 2 formulas to assign a value of 1 to each attribute:

If {CnAttrCat_1.CnAttrCat_1_Description} = "Christmas Card" then 1

and in a separate formula:

If {CnAttrCat_1.CnAttrCat_1_Description} = "Christmas E-Card" then 1



and then group your report by constituent ID and insert a summary to the group header for each of these formulas. If the constituent has both attributes then the group header will contain two 1s, otherwise they will have a 1 and a 0.

Hi Alan,



Thank you so much for replying, I really appreciate it. When I insert a summary would I group it by Count or Max?



I have created something similar but was asked whether I could get the information into one box instead of having each individual box for Christmas Card and E-Card. Do you think its impossible to achieve this type of request within one formula? 

Tom Yates

Hi Alan,



Thank you so much for replying, I really appreciate it. When I insert a summary would I group it by Count or Max?



I have created something similar but was asked whether I could get the information into one box instead of having each individual box for Christmas Card and E-Card. Do you think its impossible to achieve this type of request within one formula? 

Alan French

Use Maximum for the summary. Count would not distinguish between a 0 or a 1 as it is just testing to see if the field contains a number.



Yes it's possible to get the info into one field. If you create a third formula that looks at the values of the 2 summaries, then drop this into the group header, this should give you the result you're after (replace attribute1 and attribute2 with the names of your first 2 formulas):

 

if Maximum ({@attribute1}, {CnBio.CnBio_ID}) > 0 then

    if Maximum ({@attribute2}, {CnBio.CnBio_ID}) > 0 then

        "Both"

    else "Xmas Card"

else if Maximum ({@attribute2}, {CnBio.CnBio_ID}) > 0 then

    "Xmas E-Card"

Tom Yates

Alan,



Thank you so much, this worked perfectly. I really appreciate your help with this. 



Thanks again. 



Ryan 

Alan French

No problem, glad I could be of help

Tom Yates

Hi Alan,



Thank you so much for your help the other week with this question. I was wondering if the formula below is possible with the option of 3 benefits? 



So previously we was looking at Christmas Card and Christmas E-Card and you suggested the formula of - 



if Maximum ({@attribute1}, {CnBio.CnBio_ID}) > 0 then

    if Maximum ({@attribute2}, {CnBio.CnBio_ID}) > 0 then

        "Both"

    else "Xmas Card"

else if Maximum ({@attribute2}, {CnBio.CnBio_ID}) > 0 then

    "Xmas E-Card"



I have tried to amend the formula to include the options for 3 benefits but just can't seem to get it to work, can you help please? 



Thanks in advance. 



 

Alan French

There's probably a more elegant method to do this, but my ham-fisted approach is as follows.



If you create a new formula for the third benefit (I don't know what your third benefit is called, so you'll need to replace "Foo" with the actual description of the attribute):



If {CnAttrCat_1.CnAttrCat_1_Description} = "Foo" then 1



Then edit your formula that's in the group header to this:



if maximum({@attribute1},{CnBio.CnBio_ID}) > 0 then

    if maximum({@attribute2},{CnBio.CnBio_ID}) > 0 then

        if maximum({@attribute3},{CnBio.CnBio_ID}) > 0 then

            "Benefits 1, 2, 3"

        else

            "Benefits 1, 2"

    else

        if maximum({@attribute3},{CnBio.CnBio_ID}) > 0 then

            "Benefits 1, 3"

        else

            "Benefit 1"

else

    if maximum({@attribute2},{CnBio.CnBio_ID}) > 0 then

        if maximum({@attribute3},{CnBio.CnBio_ID}) > 0 then

            "Benefits 2, 3"

        else

            "Benefit 2"

    else

        if maximum({@attribute3},{CnBio.CnBio_ID}) > 0 then

            "Benefit 3"



Then you can change all the values in the quotes to reflect what the actual benefits are, e.g. "Christmas Card, Christmas E-Card, Christmas SMS" instead of "Benefits 1, 2, 3" 

Tom Yates

Hi Alan,



I really appreciate you helping me out with this, thank you so much. You have helped me out again big time!



Hope you have a nice weekend.



Thanks, Ryan