Formula required for Attribute information

Tom Yates · October 2015

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 · October 2015

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 · October 2015

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 · October 2015

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 · October 2015

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 · October 2015

Alan,

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

Thanks again.

Ryan

Alan French · October 2015

No problem, glad I could be of help

Tom Yates · November 2015

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 · November 2015

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 · November 2015

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

Formula required for Attribute information

Comments

Categories