Visual Basic :: Crystal: Sort Using A Formula Field

Crystal Report Sort Formula Field!
Please find attached a screen shot of my report....

I'd like to sort this report by the percentage column that i've circled in red. Is there a way to do this????

This value in red is obtained as a formula field

% answered wrongly(RED) = {#RTotal0} % (Count ({Consolidated_Audit_Questions.SubectID}, {Consolidated_Audit_Questions.SubectID}))

Rtotal0 = No of times answered wrongly

PLEASE HELP!!!

I'm using Crystal reports.NET

Crystal Formula Field
Hello All

I am trying to get a formula field to work in Crystal Reports V7

The formula lookes like this:

Code:
'Batch Number ' + Totext({Route_ttx.Batch ID})
The problem I have is that {Route_ttx.Batch ID} is numeric and no matter how much I try I cannot get it to format correctly within the formula.

I get a resulting field 'Batch Number 1,234.00'

What I really need is 'Batch Number 1234'

I cannot seem ot be able to format the number correctly before it is converted into a string and appended to the 'Batch Number ' text.

Any ideas?

Crystal Reports Formula Field
Hi
I got a database column which has number values like
480, 495 , 615, 630
which is equivalent to
480 = 8am (which is like 480/60=8)
495 = 8.15am
615 = 10.00am
630 = 10.15 am
i get this values from ctDEdit control, which gives numbers for time.
Now i have to show this number values as time in crystal reports. so i thought of having a formula field.
its easy to get hours and minutes from this number:
its goes like this
(615/60)=10.25
so 10 is hours
(25*60)=1500, here 15 is minutes.
can any one please help me to do this in a formula field, ie to show the time as 10.15am for number 615.
i hope im clear.
thanks
kumaarrv

Using Formula Field Crystal Report 8.5
hi all
i want to use forumula fied in Crystal report 8.5 and i also want to use condition in formula field
any one help me
thanx in Advance
Farhaj Qazi

Sum A Formula Field At Crystal Report
Hi Dear Programmer
How are you all. I am facing a problem, that is summesion of a formula field. Here are the details

I have a database of MS access format. First I got two time there. By calculating the time I got the different. The calculation is done at Crystal Report. Now I need the sum of that field. But I can't do it by CR. Anybody here to help me by giving the formula code or other process. I try with Sub total command but failed. Giving a sample attachment.
Thanks.
Mamun

Crystal Report Formula Field
how can i return a value using crystal report formula field

Not Able To Sum The Field In Crystal Reports 9 Formula Editor
Friends,

The below formula is not working. Please point me where iam making mistake.

=========================================
whileprintingrecords;
global numbervar tottrd;
numbervar cnttottrd;
if
(
{Admin.OTC}="001"
or
{Admin.COEN} in
["Chargeable Labour + Chargable Spares",
"Chargeable Labour + Free Spares",
"Chargeable Spares + Free Labour"]
)
then
cnttottrd := {PAYMENTS.PAIDAMT}
else
cnttottrd := 0;
tottrd := tottrd + cnttottrd

=========================================

if i change
cnttottrd := {PAYMENTS.PAIDAMT}
to
cnttottrd := 1
then its working.......

Please treat this as urgent.

Thanks & Regards
Sathyguy

Crystal Reports - Sorting A Formula Field
Hi,

I've got a formula field on my report: -

Code:
//@SumQty
Sum({SALES_VIEW.SUM_QTY}, {SALES_VIEW.AR_DESCRIPTION})
I want to sort the report using this field, but can't seem to find a way.

Is it possible to do this?

Regards

Crystal Reports Formula Field Charting
I am not a Crystal expert, I normally would be using VB but this project requires a report written in Crystal and I have no access to VB. Any suggestions would be great.

I have a report with a field: {StartTime} that is being pulled from a subreport: ASubrpt.rpt.

I the main report I have a formula field: {RoundedTime} that rounds the time from {StartTime} in ASubrpt to the nearest minute by fives. (example 7:22 would be 7:20).

Now, I need to graph and cross tab the count of records on the change of {RoundedTime}.

Please advise. I have even tried using the Charting on Print-Time Formulas pdf from Business Objects.

If there was a way to format the StartTime field in ASubrpt to be rounded and then create the chart in the subreport then I could insert it into the main report, but I do now know if you can format the time like that w/o a formula field. And then would it even be chartable?

Thanks in advance!

Crystal Reports 9: Formula Field Alignment
I have the following Formula Field in the detail section of a report…
If IsNull({ado.AMOUNT}) = False Then
   Formula = {ado.amount}
Else
   Formula = 0.00
End if

The Horizontal Alignment for the field is set to “Right”. For some reason when ado.AMOUNT is Null the zero value of the formula is displayed to the right but it is off by one position to the left.
See Example
AMOUNT
      $0.00
      $10.00

Anyone know why this is happen?

Sort Field In Crystal Report
Hi,

When I execute the statement (written at VB program) below :
PrintIt.SortFields(0) = "+{PRODUCT.Item}"

to sort my crystal report records according to 'Item', I encounter an error => Invalid field name.

'Item' is a text field of 150 chars in my PRODUCT Table.
I have no problem in sorting other fields.
I also realised that the 'Item field' do not have "Browse Field Data" option.

Can anyone help ? Thanks in advance.

Crystal Report Help - Print On Multiple Lines Property In Formula Field
Hi Crystal report experts,

I want to print formula field in multiple lines. Print on multiple lines property is ignored in formula field. Please let me know the alternates if any.

Thanks in advance

With Regards
Anees Ahamed.N.

Edited by - AneesAhamed on 12/1/2003 5:50:46 PM

Passing A Variable Containing Single Qutation Mark As Formula Field To Crystal Repor
can any one tell what to do when a single quation mark comes in a variabe that has to be passed as a formula field to crystal report from VB

Finding Excel Filename To Input Into Sort Formula
Hi,

I have data in a spreadsheet which needs to be sorted in 2 columns say col A first & then by B. How can I amend the VB code to enable the sort to be carried on a different worksheet automatically & not just on ther "21519999-Jan 08 GL Download" file ? I have copied the code below:

ActiveWorkbook.Worksheets("21519999-Jan 08 GL Download").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("21519999-Jan 08 GL Download").Sort.SortFields.Add _
Key:=Range("D4 "), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("21519999-Jan 08 GL Download").Sort.SortFields.Add _
Key:=Range("J4:J1274"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("21519999-Jan 08 GL Download").Sort
.SetRange Range("A4:L1274")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Many Thanks

Reggeadog

Formula Field
I have a table in Access with fields as follows:

EmpCode
Month_Year
Percentage of Availability (PA)

I have another table called StaffMaster which has the following fields:

EmpCode
EmpName
Dept

In the report, I wish to retrieve and display records as follows:

Month1 Month2 Month3 Month4

EmpCode EmpName PA PA2 PA3 PA4

Can this be accomplished? Please help. Urgent!

Thanks

A Memo Field Cannot Be Used In A Formula
i use crystal reports 8.5 and SQL server i dynamic create a report in my vb programe, i used TextObject ,and there is a column is wrong, it is ntext what 's the problem ,how can i do it
when i meet then problem "a memo field cannot be used in a formula"
thans a lot

Field Value Based On Some Formula
Hi

Please take a look at the following table.

Invoice: (Invoice is a table with or without records.)
======================================================================
InvoiveNo InvoiceDate CustomerID OrderID Cess SalesTax Dummy .... etc.
======================================================================
- - - - - - - ......
- - - - - - - ......
- - - - - - - ......
- - - - - - - ......
======================================================================

In my current project user can create user defined fields for any
table(Cess, SalesTax, Dummy are defined by the user). Now what i want
is when any user create a field he should be able to specify formula
for that perticular field. Formula may be anything, say:

Dummy = 10/100 * SalesTax or something..something..

Now the problem is that when i add record to Invoice table how can i
calculate the value for the user defined fields. What i mean is

VB Code:
rsInvoice.Fields("InvoiceNo") = txtInvoice.TextrsInvoice.Fields("InvoiceDate") = txtInvoiceDate.TextrsInvoice.Fields("CustomerID") = cboCusomer.ItemData(cboCustomer.ListIndex)rsInvoice.Fields("OrderID") = cboOrder.ItemData(cboOrder.ListIndex)rsInvoice.Fields("UDF") = ? (formula)rsInvoice.Fields("UDF") = ? (formula)rsInvoice.Fields("UDF") = ? (formula)

How can i calculate udf's values. One UDF's value may depend on other
UDF.

I hope you understand what i mean. Can anybody please help on this
issue.

If there is something that is not cleared by me please let me know.

Insert A Formula Field In Crystalreport-1
Let say my original field data is "20010401", how to switch it become like this "01/04/2001"

but in crystalreport would not work in this way:-
Right({Table1.test},2) + "/" + Mid({Table1.test},5,2) + "/" + left
({Table1.test},4)

Insert A Formula Field In Crystalreport
Let say my original field data is "20010401", how to switch it become like this "01/04/2001"

Line Break In Formula Field
i m using two fields from different tables and i want line break between these fields
eg:
{empmain.empname_u} + " here i want break line command" +{desig.name_u}

Field With Formula In Access Table
Hi all, im using Access and in my table i need a field that using formula just like in SQL Server.

Here we go...

Inside my table i have ..

Field Name Data Type
--------------------------
M1 Currency
M2 Currency
M3 Currency
: (to) :
M12 Currency
Y2D Currency ---> in this field i need to have a formula which is (M1+M2+M3+...+M12)

im using the default value to add in this formula but i get error massege "Data Type Mismatch" and different kind of error as well. SO, how can i add in this formula into this Y2D field??

Thanks.. and appreciate your help

Passing Formula Field Values At Runtime
I am using Crystal Report 8.5 & VB6.

I designed a report with a sub report. I am able to pass formula field values to main report but failed to pass to sub report. I am using formule field values to set my report header. Following is code for main report which is working nice for main report

Code:
Report.FormulaFields.GetItemByName("d" & Trim(Str(rsMonthTransaction.Fields("reportseq")))).Text = "'" & rsMonthTransaction.Fields("payheadname") & "'"

I need how do I perform this.

Thanks

SQL Server : Ceate New Field With Formula Through Query
Hi

How to create a new field 'Field_TRY' with formula something like '(Field1+Field2)-Field3' with the help of query. I think ALTER TABLE query will do this, but i don't know the syntax.

Please Help .

EVALUATING A FORMULA FIELD IN A DATAGRID EXCEL STYLE

I HAVE A DATAGRID WITH THE FOLLOWING COLUMNS

DIAMETER (D) , THICKNESS (T) FORMULA WEIGHT

THE USER CAN ENTER A FORMULA LIKE 3.14 * D * T IN THE FORMULA COLUMN AND THE WEIGHT COLUMN SHOULD SHOW THIS EVALUATION. THIS IS LIKE THE EXCEL SHEET FORMULAS WE KNOW OF.

CAN YOU KINDLY HELP ?

REGARDS

SANJISH

Formula Field - String Concatination - Does Not Work If Null.
Please review the following compiled in formula editor. It does not output anything if even 1 address is null. Please help me how to resolve this problem.

if LENGTH(TRIM({JSROC_N.ADD1}))=0 then "." else TRIM({JSROC_N.ADD1})
+ "," + if LENGTH(TRIM({JSROC_N.ADD2}))=0 then "." else TRIM({JSROC_N.ADD2})
+ "," + if LENGTH(TRIM({JSROC_N.ADD3}))=0 then "." else TRIM({JSROC_N.ADD3}) + ","
+ if LENGTH(TRIM({JSROC_N.ADD4}))=0 then "." else TRIM({JSROC_N.ADD4})

.

.........
When you are laughing, you are in a state of meditation.
b'caz laughing and thinking cannot be done together.

Crystal Reports: Highlight Field Based On Value Of Other Field
Is this possible in Crystal Reports? I'd like to colour the values in {ado.NetWt} red where the value in {ado.LBound} > 0....a forecolor property or something?

Crystal Formula
What is wrong with the following???

if {?Gender} = "Male or Female" then formula =
{Customer.Interest1} = {?PM_Interest} or
{Customer.Interest2} = {?PM_Interest} or
{Customer.Interest3} = {?PM_Interest}
Else formula =
{Customer.Gender} = {?Gender} and
{Customer.Interest1} = {?PM_Interest} or
{Customer.Gender} = {?Gender} and
{Customer.Interest2} = {?PM_Interest} or
{Customer.Gender} = {?Gender} and
{Customer.Interest3} = {?PM_Interest}
End if

Thanks

Crystal Formula
Check this.

a) I have two several tables on crystal report (8.0)
b) I have the same table (periodo_publicidad) twice one with the real name and the other one with and alias (periodo_publicidad_2)

c)From VB I am passing a formula to Crystal like this

sqls = "{Periodo_publicidad.pep_Fecha_desde} <= # " & txtfecha & " # " _
& " and # " & txtfecha & " # <= {Periodo_publicidad.Pep_fecha_hasta} " _
& "and {Periodo_publicidad.pep_tipo_rango} = 'I'" _
& "and {contrato.ren_renglon} <> 'C' "
'& "and exists (select 'x' " _
& " from periodo_publicidad as periodo_publicidad_2)"

If this possible?
What is wrong?

Crystal Formula With SQL
I am using sql2000 and i am passing this as a formula to crystalreport.

dfecha = CDate(txtfecha)
sqls = "{periodo_publicidad.pep_fecha_desde} >= " & dfecha & "" _
& " AND {periodo_publicidad.pep_fecha_hasta} <= " & dfecha & "" _
& " AND {periodo_publicidad.pep_tipo_rango} = '" & sI & "'"

but it says that a date is required. Why?

How To Sort A Database By Field
Isthere a way to sort out a database recordset using VB?

Sort On A Memo Field
Dear VB users,

I want to sort on a memo field but VB gives an error:

Error 3117: Can’t sort on Memo.

Can someone give me a solution?

Nice regards,

Michelle.

How To Sort The Recordset By Field
I need some direction. I have a DAO connection to an Access database where I get data. Each field in the Access database is displayed separately in a textbox in my program. I would like to sort the main field name (called "Name") in ascending order. Currently is doesn't do this. How is this done?

I was thinking that "data1.recordset.sort" would provide an option for the field name with an option to select either ASC or DESC, but I can't find this. Where should I go?

DataReport - How To Create A Formula Field Or Insert Value To A Textbox In Detail Section
Hi All,

In the Command Button click event i am showing the Datareport. the below is the code of command button

i have a variable dtn i will get the number of days in that variable. I need to show the TotalSales divided by the Number of Days. So that i can calulate the RunRateOf the Product.

SoldQty/dtn is the one i have to use

Private Sub Command1_Click()
dtn = DateDiff("d", txtFromdt.Text, txtTodt.Text)

rst.Open "select stk.SC01001 as ItemCode,stk.SC01002 as Description,
sum(stk.SC01045) as Qtyonord,sum(stk.SC01042) as StkQty," & _
"(select sum(ST030100.ST03020) From ST030100
Where ST030100.ST03017 = stk.SC01001 " & _
"And ST030100.ST03015 >='" & txtFromdt.Text & "' And ST030100.ST03015 <='" & txtTodt.Text & "' And ST03018 ='" & txtPrd.Text & "'" & _
") as SoldQty from SC010100 stk where stk.SC01037 ='" & txtPrd.Text & "' Group By stk.SC01001,stk.SC01002 order by stk.SC01001", cnn, adOpenStatic, adLockReadOnly, adCmdText

Set RunRateReports.DataSource = rst.DataSource
RunRateReports.Orientation = rptOrientLandscape
RunRateReports.Show
RunRateReports.WindowState = 2
RunRateReports.Sections(2).Controls("Label6").Caption = Format(txtFromdt.Text, "DD/MM/YYYY")
RunRateReports.Sections(2).Controls("Label7").Caption = Format(txtTodt.Text, "DD/MM/YYYY")

RunRateReports.Show
RunRateReports.Orientation = rptOrientLandscape
End Sub

i tried by adding the below part in the above query
SoldQty, SoldQty/dtn as Avgsales from...

and in place a textbox in the DataReport and given its DataField to Avgsales but i didn't get suceeded.

What's the correct way to solve this problem.

Thank you very much,
Chock.

Crystal Reports Formula
Hi!! I'm new to CR & finding it very difficult to Get into
formulas. I wud be really glad if someone could give an idea
on how to incorporate the following SQL statement on the report.

Code:
SELECT count(students.bno)
FROM Batch, Students
WHERE Batch.Bno=Students.Bno
GROUP BY Batch.bno;
I have grouped my report by Batches.
It looks like this,

Quote:

Batch Name

Student No | Student Name | ...
......
.......
......
.......

Total Student in this batch %

The formula shud come to %. I wud really appreciate any help as it
is urgent!..

Thanks!!
Mur.

Crystal Report Formula Help
i dont know where to begin... i have created this report and would like to have when the user inputs a begdate and enddate and state to show $totals of that criteria.
the report does show the beg date and end date and the state , but the only thing wrong it calculated the total cost of all the states income in the grand total of the report, i only want to have the state to show the total of that kind
ie if user inputs begdate = 1/1/03 and end date (today) and select state CA i want the report to show the totalcost income of that state between those dates, it only shows the grand totalcost of all states right now....
i am using win xp, vb6 and crstal report 4.6? that is on the vb cd

i dont know if i need to code in vb or put a formula in crystal???? HELP please

StartDate = InputBox("Enter Starting Date To Print:", "Date Entry", Date)
EndDate = InputBox("Enter Ending Date To Print:", "Date Entry", Date)
State = InputBox("Enter Code of State To Print:", "State Entry")
If StartDate = "" Then
Else
If EndDate = "" Then
Else
If State = "" Then
Else
CrystalReport1.ReportFileName = "\Server1salestx.rpt"
CrystalReport1.GroupSelectionFormula = "{OrderStatus.Date} >= " & "Date(" + Format$(StartDate, "yyyy,mm,dd") + ") and {OrderStatus.Date} <= " & "Date(" + Format$(EndDate, "yyyy,mm,dd") + ") and {OrderStatus.Order_State} = " & "'" + (State) + "'"
CrystalReport1.Action = 1
End If
End If
End If
End If

Crystal Report 8.5 Formula
greetings,
I work with VB6 sp5, SQL Server2000, Crystal Reports 8.5
Trying to create a certain report, I need to check the values of some table fields and accordingly display some notes.
my table contains, among all others, two int-type fields called HoursIn and MinutesIn. I would like to create a formula (i guess) to check these values.
If both are 0 then I would like to display "CLOSED", otherwise diplay the HoursIn and MinutesIn values.

can someone help me with this please?
should I have an empty text field and feel it accordingly? or insert the formula field and have it feeled? how can it be done? I tried but with no success.
thank you in return.

Crystal Reports Formula
Hi All,

I want to create formula in Crystal report.I am very new to Crystal reports.Can Any one guide me how to Create formulas.

I want to use replace function .

Any Ideas??

Formula In Crystal Report
Hi All,

I want to create formula in Crystal report.I am very new to Crystal reports.Can Any one guide me how to Create formulas.

I want to use replace function .

Any Ideas??

Formula In Crystal Reports
In crystal report I am using sql server database, there is a value i want an another value from that fields. what i did in crystal report in formula fields i made a variable name "goldpur" the following are the command used in the formula editor

(whileprintingrecords;

shared numbervar goldpur;

goldpur := {view_invo.gld_nwt} * {view_invo.kt}/24/0.995)

view_invo.gld_nwt,view_invo.kt are both field name of the database

and the error is "A number,currency amount is required here"

Thank

Regards

Very urgent

Simple Formula In Crystal
Here is my formula

WhilePrintingRecords;
numberVar x;

if {LOAN.CLOSED} = -1 then
x = x + 1;

"Total loans closed:" & x

The "closed" field in the "loan" table contains 0 or -1, when I refresh my report it returns a 0.00.

On the report it shows about 6 records with a -1.

Anyone know?

Chng In Crystal Formula From VB
hi,
I am using crystal 8.5 & vb6
I have stored one formula XyesNo in crystal which is having value true.

Now Want to Ask one question in VB and accordingly want to change the value of XYesNo, say from True to False.

I hv come to know that we can change this by
---
CrystalReport1.Formulas(0) = "XYesNo= 'True'" Or

CrystalReport1.Formulas(0) = "XYesNo= 'False'" Or
---

but in crystal report 8.5 there is no formulas, the only available are
formulasyntax & formulafields. The member who gv me the solution is using crystal 7.0,

Tell me how to do

Thanks

Error On Formula (crystal)
hi,
I want to print city and pincode,
if pincode exist then city and pincode else print city only.
the following the code of the formula I wrote in crystal but
the problem is it prints city and pincode only when pincode is
not blank, it should also print city name even if pincode is blank,
Pls check the code

---
if {custmaster.pincode1} = "" then
{custmaster.city1}
else
{custmaster.city1} + "-" + {custmaster.pincode1}
---

thanks & regards

Crystal Report 8 Formula?
I'm having problems grouping a formula field. I have been told that you cannot create a group if the formula has the WhilePrintingRecords within the code. I have 4 formulas that help me get the subtotal(so to speak) for the Total Time(text object). Not a field with in database.

Formula1: WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal := 0
Formula2: WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal := RunningTotal + {@TimeDiff}
Formula3: WhilePrintingRecords;
NumberVar RunningTotal;
RunningTotal;
The 4th formula is just attaching the word minutes to formula3 total. Now is there another way to get a total for this field without using the WhilePrintingRecords, so that I can make a group out to the total field?

Thanks for any help jeffro!

Crystal Report Formula..??
I hope someone can help me out. I'm trying to write a formula for the top ten in industrial. The fields I'm using are repairtime, setuptime, and pmtime, which are used to do the totaling and that will be used to find the top total of the machineno. Also I'm using the machineno, and division, the division field is used to look up just the industrial equipment.

Can anyone help out??

Aja

Crystal Report Formula
Hi Guys,

I am using Crystal reports 8.5. I am writing reports for another packaged software which takes custom crystal reports. I have written a report and it work fine. But I have a small problem in Record selection. I have a field on the report depending on this field starting characters another field must be selected from another table. I was wondering if a formula can do this. ie using a query in a formula.

I would check like this

if Left({field1},1) = "S" then
here I need to select another field from another table with different condition
else
{field2}

Could you guys help me out on this.

thanks

Crystal Report 9 Formula
where can i get more tutorials or resources or good ideas for effective high level formula writing.

Crystal Report Formula
Hi friends,
I am using crystal reports 8 . while writing the formulas the all the fields are not displayed in report fields(in formula box).
for E.g I am trying to hide/supress one of the fields which is blank using the formula for that particular field. but cannot see that fields in report fields in the formula box.
Any help will be highly appreciated.

Noorali.