Crystal Reports Need help in formula !!

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Need help in formula !!

Page of 2 Next >>

Author

Message

karthizen
Newbie
Newbie

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17

Topic: Need help in formula !!
Posted: 23 Dec 2008 at 9:11am

Hi All,

This is karthik. I need a help in creating a formula. Please find below my question..Bit long :) ..... please bear with me...

I have a table with Product name and Product amt like the following.

Product 1	Product 1 Amt	Product 2	Product 2 Amt	Product 3	Product 3 Amt	Product 4	Product 4 Amt
Barbie	100	Barbiegirl	50	MickeyMouse	85	DonaldDuck	45
Barbie	100	MickeyMouse	85	DonaldDuck	45	Barbiegirl	50
Barbie	50	MickeyMouse	85	Barbiegirl	100	DonaldDuck	45

I need to look out for 'Barbie' in all the 4 products and to bring that under Product 1, also I need to sum the Amount under Barbie & have to display it in Product 1 amt. Also I need to move the Product name under Product3 to Product2 , product3 amt to product 2 amt and likewise.

I need the output in this format,

Product 1	Product 1 Amt	Product 2	Product 2 Amt	Product 3	Product 3 Amt	Product 4	Product 4 Amt
Barbie	150	MickeyMouse	85	DonaldDuck	45
Barbie	150	MickeyMouse	85	DonaldDuck	45
Barbie	150	MickeyMouse	85	DonaldDuck	45

I have written a formula to sum up the Amt like the following.

B2:

if isnull ({PRODUCT_INFORMATION.PRODUCT_DESC_2}) then 0

else if 'Barbie' in {PRODUCT_INFORMATION.PRODUCT_DESC_2} then

{@B22} else 0

B22:

if isnull ({PRODUCT_INFORMATION.PRODUCT_AMT_2}) then 0

else {PRODUCT_INFORMATION.PRODUCT_AMT_2}

B3:

if isnull ({PRODUCT_INFORMATION.PRODUCT_DESC_3}) then 0

else if 'Barbie' in {PRODUCT_INFORMATION.PRODUCT_DESC_3} then

{@B33} else 0

B33:

if isnull ({PRODUCT_INFORMATION.PRODUCT_AMT_3}) then 0

else {PRODUCT_INFORMATION.PRODUCT_AMT_3}

B4:

if isnull ({PRODUCT_INFORMATION.PRODUCT_DESC_4}) then 0

else if 'Barbie' in {PRODUCT_INFORMATION.PRODUCT_DESC_4} then

{@B44} else 0

B44:

if isnull ({PRODUCT_INFORMATION.PRODUCT_AMT_4}) then 0

else {PRODUCT_INFORMATION.PRODUCT_AMT_4}

{PRODUCT_INFORMATION.PRODUCT_AMT_1}+{@B2}+{@B3}+{@B4}

But I got no clue in moving Product name to the prior column.

can any one help me out in this?

Regards,
Karthik...

IP Logged

hilfy
Admin Group

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702

Posted: 23 Dec 2008 at 1:54pm

Are there only three products or, as I suspect, is the more complicated than your sample data? Your example is somewhat confusing...what question are you trying to answer with your report?

If you wanted everything in one column, you could do this fairly easily with a command that contains a union query.

I may have some other thoughts, but I need to know what you're really trying to do.

-Dell

Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics

IP Logged

DBlank
Moderator

Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053

Posted: 23 Dec 2008 at 2:20pm

FYI - question answered via a pm

IP Logged

karthizen
Newbie
Newbie

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17

Posted: 23 Dec 2008 at 3:09pm

Thanks for DBlank & Hilfy....

Lemme post the Question which I have asked DBlank & his reply....

Hi,

I need to look out for Barbie in each of the column for a particular row, if I found it then I need to add the amount of the product(barbie), by defualt Barbie is going to occupy first column and its amount is going to occupy second column.

Lets assume there are more than one barbie in a row.

column1	column2	column3	column4	column5	column6	column7	column8
Barbie	100	Mickeymouse	50	Barbie	85	Donaldduck	45

in the above example, col-1 and col-5 has barbie. I am going to add its amount 100+85 and I am going to place it in col-2

the next thing i want to do is to bring the product Donaldduck in col-7 to 5 and donaldduck's amt to col-6.

Like this

column1	column2	column3	column4	column5	column6	column7	column8
Barbie	185	Mickeymouse	50	Donaldduck	45

Hope I have explained my requirement....

I just need the formula...I have designed the report already.

Regards,
Karthik...

IP Logged

karthizen
Newbie
Newbie

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17

Posted: 23 Dec 2008 at 3:10pm

Sent by : DBlank
Sent : 23 Dec 2008 at 12:27pm

Basically you are trying to collapse all of the "barbie records into columns 1 and totals in column 2 and then shift your other data to the left one column if you have have "removed" a Barbie Column.

If that is correct the only way I can think to do this is to recreate each field conditionally amnd then use these new fields to replace the other ones. This is also assuming that you are only looking to change the way the data is displayed in a report and not the actually data in the table itself.

I am sure their are more elegant ways to do this but here is one way.

The below assumes you only have 8 total columns in your table and that Barbie is ALWAYS in column 1.

Create a formula field called "barbie count 2" with the code

"if {PRODUCT_INFORMATION.PRODUCT_DESC_2})="Barbie" then
{PRODUCT_INFORMATION.PRODUCT_AMT_2} else 0"

Create a formula field called "barbie count 3" with the code

"if {PRODUCT_INFORMATION.PRODUCT_DESC_3})="Barbie" then
{PRODUCT_INFORMATION.PRODUCT_AMT_3} else 0"

Create a formula field called "barbie count 4" with the code

"if {PRODUCT_INFORMATION.PRODUCT_DESC_4})="Barbie" then
{PRODUCT_INFORMATION.PRODUCT_AMT_4} else 0"

Create a formula field called "barbie total" with the code

"{PRODUCT_INFORMATION.PRODUCT_AMT_2} + {@Barbie count 2} + {@barbie count 3} + {@barbie count 4} "

In your report replace your coulmn2 field with this new field and it will show you the total sum of any all of the Barbie records in that row.

Create a formula field called "Description2" with the code

"if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then " " else if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" then {PRODUCT_INFORMATION.PRODUCT_DESC_4} else if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" then{PRODUCT_INFORMATION.PRODUCT_DESC_3} else {PRODUCT_INFORMATION.PRODUCT_DESC_2}"

Replace your colum 3 with this field.

Create a formula field called "Total2" with the code

"if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then 0 else if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" and {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" then {PRODUCT_INFORMATION.PRODUCT_AMT_4} else if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" then{PRODUCT_INFORMATION.PRODUCT_AMT_3} else {PRODUCT_INFORMATION.PRODUCT_AMT_2}" and replace your colum 4 with this new field.

Repeat this same concept for the last 4 fields shifting it over.

Does this make sense?

Regards,
Karthik...

IP Logged

karthizen
Newbie
Newbie

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17

Posted: 23 Dec 2008 at 3:14pm

Dear DBlank,

It works but there is an issue.....

This is the actual data,

column1	column2	column3	column4	column5	column6	column7	column8
Barbie	100	Mickeymouse	50	Barbie	85	Donaldduck	45

I am getting the output like the following,

column1	column2	column3	column4	column5	column6	column7	column8
Barbie	185	Mickeymouse	50	Donaldduck	45	Donaldduck	45

where as the output should look like,

column1	column2	column3	column4	column5	column6	column7	column8
Barbie	185	Mickeymouse	50	Donaldduck	45

Any suggestions?

Regards,
Karthik...

IP Logged

DBlank
Moderator

Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053

Posted: 23 Dec 2008 at 4:56pm

i did not take that last column into account.

for your replacement formula fields for columns 7 and 8 reverse check it and see if Barbi was in column 3, 5 or 7 and use an or statement instead of the and statements you used in the other formula fields.

like this:

"if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" or {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" or {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then " " else {PRODUCT_INFORMATION.PRODUCT_DESC_4}"

do the same thing for the count:

"if {PRODUCT_INFORMATION.PRODUCT_DESC_2}="Barbie" or {PRODUCT_INFORMATION.PRODUCT_DESC_3}="Barbie" or {PRODUCT_INFORMATION.PRODUCT_DESC_4}="Barbie" then 0 else {PRODUCT_INFORMATION.PRODUCT_AMT_4}"

Logically if any of these were barbie then the original data in column 7 and 8 already "shifted" left so the or statement will remove it from your displayed data in the report. if none were barbie then it will leave the data in for display.

My email server is going down for a few hours later tonight for maintenance so just post here and don't pm me as I won't get it.

FYI- if you don't want the zeros to display in the null columns do a conditional suppress on those formula fields where the formula field=0

IP Logged

hilfy
Admin Group

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702

Posted: 23 Dec 2008 at 6:47pm

Cool. Thanks for posting that karthizen.

DBlank, I'm glad you were able to answer the question - it's always good to see a new "face" in the forum answering questions. In the future, it would be great if you could post your answers to the forum. That way other folks can search for them if they have similar issues. I generally try to do that even if the poster pm's me.

-Dell

Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics

IP Logged

DBlank
Moderator

Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053

Posted: 23 Dec 2008 at 7:06pm

No problem. Will do in the future.

IP Logged

karthizen
Newbie
Newbie

Joined: 10 Dec 2008
Location: India
Online Status: Offline
Posts: 17

Posted: 24 Dec 2008 at 7:39am

Good Morning all...

its me again...

I guess I am missing out some where.....

column1	column2	column3	column4	column5	column6	column7	column8
Barbie	185	Mickeymouse	50			Donaldduck	45

The column is not shifting after finding barbie and its corresponding amt

Please help

Regards,
Karthik...

IP Logged

Page of 2 Next >>

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum