You need to sign in to do that

Don't have an account?

craigsteger

# Merging rows in a report using custom summary formulas?

I've created a report that shows my opportunity pipeline by probability but I'm trying to merge a couple of the rows together and am looking for some help on how to do it. I'd like to combine the 10% and 20% opportunities into a single row of data so the total monthly and grand totals are combined.

Somehow the last row of date would show the following:

10-20% $3,000 $468,686.35 $602,427.38 $1,074,113.73

Any help that can be provided would be great. Thanks!

JakesterYou could create a custom formula field that basically says "combine 10-20%" and show the rest of the percentages normally, and then you could group on that. If you need to continue to show 10 and 20% separately as well as group them together, then you could group by Probability the way you are now and then group by the custom formula field, too. It would repeat a little, but it shouldn't be too bad. craigstegerThanks for the quick reply Jakester. What I'm struggling with is how to write the formula to combine the 10% and 20%. Any help you could provide there? Jakester

Untested, I think it would be something like this:

case( Probability, <.21,"10-20%" <.31,"30%" <.41,"40%","50%" )

Craig StegerI've tried this formula a couple of times but I keep getting a syntax error. Do you have any ideas on what might be causing that? Really appreciate the help in troubleshooting this issue I have. JakesterWhat's the syntax error say? craigsteger

The error I receive is:

Error:Syntax error. Found '<'Jakester

You'd obviously repeat this pattern all the way to 100%, instead of just to 50%

I think you can't actually use "<" with a case function, so maybe something like

if(Probability <.21,"10-20%", if(Probability <.31,"30%", if(Probability <.41,"40%",50% )

craigsteger

This is where I am now and am getting a different error.

if(PROBABILITY <0.21,"10-20%",

if(PROBABILITY <0.31,"25-30%",

if(PROBABILITY <0.51,"40-50%",

if(PROBABILITY <0.81,"75%",

if(PROBABILITY <0.91,"90%",

if(PROBABILITY <1.00,"100%"))))))

Error:Incorrect parameter for function <(). Expected Object, received NumberJakesterI can't read it because you didn't use the Code button on the toolbar. Can you repost using that? craigstegerif(PROBABILITY <0.21,"10-20%", if(PROBABILITY <0.31,"25-30%", if(PROBABILITY <0.51,"40-50%", if(PROBABILITY <0.81,"75%", if(PROBABILITY <0.91,"90%", if(PROBABILITY <1.00,"100%"))))))

Sorry about that. Here's the code.

Jakester

Try

if(PROBABILITY <0.21,"10-20%", if(PROBABILITY <0.31,"25-30%", if(PROBABILITY <0.51,"40-50%", if(PROBABILITY <0.81,"75%", if(PROBABILITY <0.91,"90%",)))),100%)

craigsteger

I just tried that and now I'm getting this error. Also, should the 100% be in quotes as well?

Error:Syntax error. Found ')'JakesterYes, it should be in quotes - I'm sorry, but I don't think I'm going to be able to give you the perfect formula. Now that you have the idea, start simple. Try just doing one If() function, and build on that, testing along the way.