 ShowAll Questionssorted byDate Posted 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! Jakester
You 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. craigsteger
Thanks 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 Steger
I'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. Jakester
What's the syntax error say? craigsteger

Error: Syntax error. Found '<' Jakester

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% )

You'd obviously repeat this pattern all the way to 100%, instead of just to 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 Number Jakester
I can't read it because you didn't use the Code button on the toolbar. Can you repost using that? craigsteger
` `

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%"))))))

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 ')' Jakester
Yes, 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.