Formatting within a crosstab

[Tatertot45]

I think i'm going insane! I have a very simple report that counts the number of orders entered in a day, and how manyu of those orders are shipped incomplete. I'm using CR XI, and a SQL datawarehouse. I summarize the info in a cross tab. I am counting order numbers, and have the count as use "Show as a % of" box to create a % of orders. The cross tab looks like this

Date incomplete Complete total
9/7/2007 8 36 44
22% 88% 100%


My problem is that the percents are rounded to the 1, so if i have .4% incomplete, it looks like 0%, but the complete is 99%. Also, the head boss-boss wants this to the nearest.01. However, i can't format the crosstab figures to .01! I have tried everything, right click, and choose the number format, going into the customize window and doing it there, and even changing the non% fields, to see if maybe the count fields are whole numbers, so the % are whole numbers. I can make the changes in the customize window, but they don't actually take effect. After i hit OK, everything stays the same. I have even tried changing the default report settings. Dioes anyone have any idea on how i can format these numbers to .01? I'm out of ideas...

Thanks,

 

[lbass]

You can follow the steps in thread782-1396810, but using variables for incomplete and complete instead of the jan and feb. Then in the display string area, use a formula like:

whileprintingrecords;
numbervar incomp; //your incomplete variable
numbervar comp; //your complete variable
totext(incomp % (incomp + compl),2)+"%" //2 for two decimals

-LB

 

[Tatertot45]

I think i have this figured out, but i can't get the last formula to work correctly.I get the message that a Paren is missing and it highlights the first Incomp.

totext(incomp % (incomp + compl),2)+"%" //2 for two decimals

Any ideas...

 

[lbass]

If you used the same variable names as I did, your formula should look like this (I corrected the misspelling of "comp"):

whileprintingrecords;
numbervar incomp;
numbervar comp;
totext(incomp % (incomp + comp),2)+"%"

-LB

 

[Tatertot45]

Got It!!! Sort of. I now have everything working, except that my final value has a "$" in front of it. Is there a way to trim that off? My final answer looks like this:

"$85.15%"

I'm ready to throw our DB out of the window, too bad i can't open the windows. I run into this issue with our web reporting tool as well.

Thanks for the great idea on how to get this reporting solve.

 

[lbass]

I'm not sure how you could have gotten the $ sign. Were you using currencyvar instead of numbervar? What version of CR are you using?

-LB

 

[Tatertot45]

I have not idea how i got that $ in there. I used Number Variables, just like you suggested. I'm using Crystal XI. I know that our DB is notorious for issues like this. We use a Progress DB, but we have a SQL Datawarehouse that this report is pulling from. I was thinking if there was a way to trim off the first character, I'd do that. I've looked everywhere and can't find it.

 

[Tatertot45]

Nevermind.... I fixed it. I went into "Options", and checked to see how the default Number formats were set up. The Currency ymbol was checked. I unchecked it, problem was solved.

Thanks again for your help.

 

[synapsevampire]

The original field is probably currency.

btw, you should post your formulas, I understand that you're frustrated, but you need to show us what you're using, not describe it.

Try using:

mid(YourVariable,2)

to eliminate the currency symbol.

-k