Subreports - question on formating 1

[HJM1234]

I have a database that someone setup and I have a problem with formating it. I'm just starting to learn Crystal Reports and the problem is as follows;


Field ID|AnotherID | Title | Variable 1| Var 2| Var3
1 123 Factor 00.000 00.250 00.350
2 123 Amt water 00.000 50.500 70.300
3 123 Concent 00.000 25.000 50.000
4 123 Pounds 00.000 12.500 25.000


I would like it to look like

Field ID|AnotherID | Title | Variable 1| Var 2| Var3
1 123 Factor 0.000 0.250 0.350
2 123 Amt water 0.000 50.5 70.3
3 123 Concent 0.000 25 50
4 123 Pounds 0.000 12 25


Basically, I have 3 different format types that are grouped together in a table and I want to format them differently. Field 1 would be x.xxx Field 2 would be xx.x
Field 3 and 4 would be xxxx

Any help would be appreciate. I was told that it basically could not be done. If it is possible, I would like to format the report properly because right now it does not look right. Also, I do not have the authority to change the tables, if I could I would not be writing this.

Thank you.

 

[lbass]

Select the fields you want conditionally formatted->right click->format field->number->customize->decimals->x+2 and enter:

select {field1}
case 1 : 3
case 2 : 1
case 3,4 : 0
default : 0

-LB

 

[HJM1234]

LB

Thank you for your help, it almost completely worked. The number for case one worked, I got 3 decimal places, Everything else got the default at zero decimal places. It appears that what I did effects the first varialbe (I tried several numbers and the first one always worked). The other numbers were always the default decinmal places. (Again I tried several different set of numbers). Tonight I will try to run some loops for the field name. Again Thank you, the program that was working on this project before he quit said that it was impossible. The project manager was hedging for something that I would have to live with. Right now it looks a lot better, I just need the second line to have one decimal place, with the default at zero.

 

[lbass]

This formula depends upon the value of the Field ID. I thought there were only 4 values, 1-4. This formula would work if this were true, but I suspect you were simplifying things. How about showing samples of real data along with how you would want them to display.

-LB

 

[HJM1234]

I tried to make an attachment and failed

UC_Bill | UC_Bill_S | Desc | Cool | Domestic | Process
1741071 1231322 DFC 0 1 0
1741072 1231322 Water 0 408.2 0
1741073 1231322 BOD Con 0 310 0
1741073 1231322 TSS Con 0 370 0
1741073 1231322 BOD lbs 0 1055 0
1741073 1231322 TSS lbs 0 1260 0

This is how the table actually looks when I print it out in a query. This is also how the data should be printed out in Crystal Reports, except for the commas. The first line is for percentages between 0 and 1. In the above case the facility is being billed at 100% of its water consumption. The flow is the number of gallons in 1,000 gallons. The number can be zero int the millions of fallons. The BOD and TSS concentrations in this example are the default values, and although these numbers are correct they can change, but all of the calculation from mg/l to pounds is already calculated in the table. So basically, all I need it to print the numbers from the table the way they basically appear. The way it was done was to set everything to a default of 5.
It looked something like this, (The screen makes it look worse then it is, the columns do line up)

UC_Bill | UC_Bill_S | Desc | Cool | Domestic | Process
1741071 1231322 DFC 0.0000 1.00000 0.00000
1741072 1231322 Water 0.0000 408.20000 0.00000
1741073 1231322 BOD Con 0.0000 310.00000 0.00000
1741073 1231322 TSS Con 0.0000 370.00000 0.00000
1741073 1231322 BOD lbs 0.0000 1,055.00000 0.00000
1741073 1231322 TSS lbs 0.0000 1,260.00000 0.00000

I did not like it but there was little I could do. At least with what you told me it looks a lot better.

Thanks again.

 

[lbass]

So you are saying that the number of decimals does not depend upon the sequence of the records? Are you saying that you just want to trim off excess zeros on the right?

-LB

 

[HJM1234]

The first record is a percentage and I would like it to be x.xxx. (In Fortran F5.3). The second record is a flow and I would like it to be xxxxxxx.x (F7.1) The last 4 records would be whole numbers XXX,XXX. Sorry if I made it more confusing then it already is. I've taken several programing classes, however I have never seen something like this for a table. It might be the right way but right now I don't see the benefits. I purchased a book called Crystal Reports Encyclopedia to get a better understanding of Crystal Reports, hopefully I'll get better at understanding what I'm working with.

Thank you for your help.

 

[lbass]

So are you using the last integer in the UC_Bill field to determine the number of decimals? THen use:

select right({table.UC_Bill},1)
case 1 : 3
case 2 : 1
case 3,4 : 0
default : 0

-LB

 

[HJM1234]

I entered the following


select right ({UC_BILL_FACTORS.DOMESTIC_AMOUNT},1)
case 1 : 3
case 2 : 1
case 3 : 0
case 4 : 0
case 5 : 0
case 6 : 0
default : 0


When I save it I get an error, the error highlights

{UC_BILL_FACTORS.DOMESTIC_AMOUNT}

It's a Crystal Report popup. IT states " A String is required here"

 

[lbass]

What you are trying to do is capture the last digit in the first column of your sample above--is that called {UC_BILL_FACTORS.DOMESTIC_AMOUNT}??? I would be surprised if it were. You still did not confirm whether that is the value that determines the sequence and therefore the number of decimals. Anyway, if the first column is a number, then use:

select right (totext({UC_BILL_FACTORS.DOMESTIC_AMOUNT},0,""),1)
case 1 : 3
case 2 : 1
default: 0

-LB

 

[HJM1234]

LB

Thanks again, I talked to someone at work today, and we made one change to what you told me to make it work. I added a record field, first record 3 decimal places, second record 1 decimal place, all the other records zero decimal places. He looked at everything and he said that I confused you with my example sorry, thanks for all of your help

select RecordNumber
case 1 : 3
case 2 : 1
default : 0


HJM