Start Free Trial

asked on

Crystal Report Field Problem

I am designing a report few fields i am dragging from Tabel-A and some fields from Table-B,

The Detail i am getting from Table - A is ok, but from Table - B data is create dublicate

Like

in table A fields are
SrNo. - Date - LcNo - Amt

and Table B files are
DateB - LcNoB - AmtB

and Report getting like this

SrNo. - Date - LcNo - Amt - DateB - LcNoB - AmtB
1 01-01-11 12345 500 05-01-11 44444 1000
2 02-01-11 4156 1000 05-01-11 44444 1000
3 03-01-11 4450 800 05-01-11 44444 1000

and report expecting like this
SrNo. - Date - LcNo - Amt - DateB - LcNoB - AmtB
1 01-01-11 12345 500
2 02-01-11 4156 1000
3 03-01-11 4450 800
05-01-11 44444 1000

Why would you expect it to look like th esecond.

If you drag the fields into the same section they will appear in that section.

How are the tables related?

Are they linked sorrectly?

mlmcc

ASKER

Sir
I am using Crystal Report 7.0 Designer version

Directly from 32 Bit Crystal Report Designer , and Creating Reports as per requirement
Then calling this report in vb project. like this

CrystalReport1.ReportFileName = App.Path & "\BalanceDetail.rpt"
CrystalReport1.Connect = con
CrystalReport1.DiscardSavedData = True
CrystalReport1.RetrieveDataFiles
CrystalReport1.SelectionFormula =

What is the issue?

mlmcc

ASKER

something i am missing

because report i am getting like this
1 01-01-11 12345 500 05-01-11 44444 1000
2 02-01-11 4156 1000 05-01-11 44444 1000
3 03-01-11 4450 800 05-01-11 44444 1000

It supposed to be

1 01-01-11 12345 500.00
2 02-01-11 4156 1000.00
3 03-01-11 4450 800.00
05-01-11 44444 1000.00
4 05-01-11 1000 5000.00
5 06-01-11 1211 3500.00
6 06-01-11 56987 4500.00
7 07-01-11 1248 6500.00

ASKER

Please attend to my question asap

When you link tables they become a single table to Crystal and the records fromthe second table are included in each record.

How are the tables linked or related?

Are lines 1-3 related in some way?
How do you know where the data from table B should appear?

mlmcc

ASKER

Tables not linked

i am not very familiar to crystal reports

just directly adding record into Crystal

i can not linked both tables by primary key field, because there is totally different field

Like in table A Primery field is LCPT_No ( this is running 7 digit number)
and in table B Primery field is Rcpt_No (this is running 3 digit number)

little bit i know if i am going to linked both table by primery field then might work, but current situation is totally different,

Then how can you tell when the table B data should be on the report.

I just noticed, do you want them in date order?

mlmcc

Try a command as

SELECT 'A' as source, SrNoField, DateField, LcNoField, AmtField FROM A
UNION ALL
SELECT 'B' as source, DateBField, LcNoBField, AmtBField FROM B

You can then use formulas for display
ADate
If {SOurce} = 'A' then
{DateField}

BDate
If {SOurce} = 'B' then
{DateBField}

You should be able to then put the formulas on the report where you want them and sort by the datefield

mlmcc

ASKER

Please Just give me few minutes, let me try your suggestion.

ASKER

Sir Where must i put Select statment

becuase i am using Selection formula to get report

Private Sub Command1_Click()

CrystalReport1.ReportFileName = App.Path & "\LedgerPort.rpt"
CrystalReport1.Connect = con
CrystalReport1.DiscardSavedData = True
CrystalReport1.RetrieveDataFiles

CrystalReport1.SelectionFormula = "{BDl.G_Name} = '" & Text1.text & "'"

CrystalReport1.Password = Chr(10) & "*******"
CrystalReport1.Destination = crptToWindow
CrystalReport1.Action = 1

You use the select statement as the source for the report.

When you create the report you can select to use a COMMAND. You enter it there.

Even better would be to use a dataset to run the report then you could build it in the application

What version of Crystal?

mlmc

ASKER

version i am using is 7.0

when i am creating new report, COMMAND option is not in there , There is
( Datafile, Query, SQL/ODBC, Dictionery,
Exchange, FileSystem, MS IIS Log, OLE DB, Outlook 97, Web Log, Etc.....)

ASKER

i only know a way to connect database using datafile

I see you are using CR7, my idea won't work.

mlmcc

ASKER

my luck ....!
then what you suggest sir

You could try using a Crystal QUery. I never used that option so I don't really know how to do it.

mlmcc

ASKER

its asking me to select *.qry file

You have to create one. It is a separate program in the SEAGATE - Crystal program set.

mlmcc

ASKER

sir any other way ?

I don't know of another way

It might be possible through a subreport .

mlmcc

ASKER

please show me , i am ready to take any chance, or any procedure.

ASKER

its just an idea,
if get all data into a temp table between date, from table A and from Table B, and sorting them date wise and then call desire data from temp table to Report.

is it possible..?

That is exactly what the union query is doing.

mlmcc

ASKER

Yes But i am talking about to get data using first recordset and then using second recordset insert data from table A to temp table, and same thing for table B,

But its taking long time and lengthy process..
and each and every time command buttone pressed it will process.

Sir Is it Good Idea ?

Probably not.

I probably could get this to work with a subreport but I suspect the performance would be very poor.

mlmcc

ASKER

lets work with subreport and lets see how it will work and performance,
But i never ever used subreport so
please assist me step by step how to use it

A subreport is just another report

Build a report for table B that shows what you want.

Modify the original report to just use TableA by removing all the fields from TableB
Also insert a group on the date field

When you get that done I'll explain how to insert the subreport and link it properly.

mlmcc

ASKER

ok just give 35 to 45 minutes because its going to be load shedding, i will be back to you after words
just wait for me please.

ASKER

ok sir i have designed another report
(SubLedgerRcpt.rpt)
Now...?

You willl need to insert a group into the report on the date field
You can then insert the report into the group header

Add a formula to the main report header
Name - DeclLastDate;
Global DateVar LastDate := Date(2000,1,1);
""

Add another formula
Name - DispLastDate
Global DateVar LastDate

You can link the subreport to the main report on 2 fields
Right click the subreport
Click CHANGE SUBREPORT L(INKS
In the main report box choose @DispLastDate
In the subreport box choose the datefield

In the main report box choose the datefield
In the subreport box choose the datefield

Click OK

Double click the subreport
It should open so you can edit it
CLick REPORT --> SELECT --> FORMULA
Edit the formula so that it is
{?pm-DispLastDate} <= {DateField}
AND
{?pm-DateField} > {DateField}

mlmcc

ASKER

sir one group already in Main Report, Name in Transname,

ASKER

sir where must i put the sub report in main report
in the group header or detail section

ASKER

Sir i am totally Confused .. ?
Can You please do this if i will give you teamviewer.?

ASKER

hello........Sir ..!!

Please understand we are all volunteers and work questions when we can. There will be times when no one is around for several hours.

I cannot do it for you because I don't have a copy of Crystal that will save in CR7 format.

You insert it in the group header.

mlmcc

ASKER

when i am refreshing main report There is two option There
1 - Use Current Parameter value
2 - Prompt for New Parameter Values

when i am selecting use current parameter value then Database Error displayed Error in Field links

Warning Fields in the report from the file "Receipt_Tport" Can not be linked
but data is displaying
another thing is how to call this reports through vb

CrystalReport1.ReportFileName = App.Path & "\LedgerTPort.rpt"

CrystalReport1.Connect = con
CrystalReport1.DiscardSavedData = True
CrystalReport1.RetrieveDataFiles
CrystalReport1.SelectionFormula = "{BDl.G_Name} = '" & Text1.text & "' and {Receipt_Tport.Transporter} = '" & Text1.text & "'"
CrystalReport1.Password = Chr(10) & "*******"
CrystalReport1.Destination = crptToWindow
CrystalReport1.Action = 1

You should be able to call it just like any other report.

mlmcc

ASKER

Sir i am using following code to call report
But its says
error in file links
Warning: fields in the report from the file Receipt_Tport Can not be linked

Sir, Receipt_Tport is second table

CrystalReport1.ReportFileName = App.Path & "\LedgerTPort.rpt"

CrystalReport1.Connect = con
CrystalReport1.DiscardSavedData = True
CrystalReport1.RetrieveDataFiles
CrystalReport1.SelectionFormula = "{Bilty_Detail.G_Name} = '" & Text1.text & "'"

CrystalReport1.Password = Chr(10) & "***********"
CrystalReport1.Destination = crptToWindow
CrystalReport1.Action = 1

ASKER

Sir ,
To make this easy, its fine if you want me to create another table.. just let me know the field name and datatype

What is the report source for the main report?

What is the source for the subreport?

mlmcc

ASKER

Sir

Bilty_Detail is Source for the main report ( i can not change anything in this table )

Receipt_Tport Source for the subreport ( i can change anything in this table or new table also i can create)

ASKER

Sir Now i got desired data into datagrid
Is it possible to put them into crystal report

Please assist me.

ASKER

Sir
Now i got desired data into datagrid
Is it possible to put them into crystal report

OR

To make this easy, its fine if you want me to create another table.. just let me know the field name and datatype

ASKER

OR

I have created Ldgr.qry from Crystal SQL Designer,
How to call this Ldgr.qry from VB from

What do you mean call the .qry file from VB?

The crystal .qry file is for use only by the report.

mlmcc

ASKER

i just want to use following query and whatever result is i want to see in crystal report.

But How ....????

RsldgrQ.Open "Select Bilty_Detail.CST as Memo, Bilty_Detail.GRDate as M_Date, Bilty_Detail.BiltyNo as LR_No, Bilty_Detail.GRNo as Cros_No, " & _
"Bilty_Detail.Total as Vasuli, Bilty_Detail.pf as Crossing,Bilty_Detail.Total - Bilty_Detail.pf as Balance, 0 as Amt_Rcvd " & _
"from Bilty_Detail Where Bilty_Detail.G_Name = '" & Text1.text & "' UNION ALL " & _
"SELECT 0 as RcptNo, Receipt_Tport.Rcpt_Date, " & _
"0 as Amt_Rcvd, Receipt_Tport.RcptNo, 0 as Total, 0 as pf, 0 as Balance, Receipt_Tport.Amt_Rcvd " & _
"from Receipt_Tport Where Receipt_Tport.G_Name = '" & Text1.text & "' ORDER BY M_Date", con, adOpenKeyset, adLockOptimistic

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Before go to your suggestion i have to create crystal report Right ?

ASKER

Now
i am creating crystal report
its asking me to select database ( Selected )
then
its showing me all the tables which are in database,

now actual problem is start from here

i want data from query not from table,

ASKER

Now what must i do

One of the database options should be a query file. I forget how that is shown.

mlmcc

ASKER

Ok What i did is

Created a New Query in Access Database and in this New Query i put the following Select Statement

Select Bilty_Detail.CST as MemoNo, Bilty_Detail.GRDate as M_Date, Bilty_Detail.BiltyNo as LR_No, Bilty_Detail.GRNo as Cros_No, Bilty_Detail.Total as Vasuli, Bilty_Detail.pf as Crossing, Bilty_Detail.Total - Bilty_Detail.pf as Balance, 0 as Amt_Rcvd, Bilty_Detail.G_Name from Bilty_Detail Where Bilty_Detail.G_Name = '" & Text1.text & "' UNION ALL SELECT 0 as RcptNo, Receipt_Tport.Rcpt_Date, 0 as Amt_Rcvd, Receipt_Tport.RcptNo, 0 as Total, 0 as pf, 0 as Balance, Receipt_Tport.Amt_Rcvd, Receipt_Tport.G_Name from Receipt_Tport Where Receipt_Tport.G_Name = '" & Text1.text & "'
ORDER BY M_Date;

Saved this query as TPT_Ledger

then Created a new crystal report > Data File > and in the list, i get that query (TPT_Ledger), Select all the fields form (TPT_Ledger) and drag and drop to the detail section,

Now for the access this report i am using following code from vb form

CrystalReport1.ReportFileName = App.Path & "\TPTLedger.rpt"
CrystalReport1.Connect = con
CrystalReport1.DiscardSavedData = True
CrystalReport1.RetrieveDataFiles
CrystalReport1.SelectionFormula = "{TPT_Ledger.G_Name} = '" & Text1.text & "'"
CrystalReport1.Password = Chr(10) & "************"
CrystalReport1.Destination = crptToWindow
CrystalReport1.Action = 1

(TPTLedger.rpt) Report is opening but Nothing in the report

Another thing is
using same query getting data in datagrid1

so

whats i am doing wrong ?

In the report is TPT_Ledger.G_Name the name of the field?

Are you reporting against the same database as the query?

mlmcc

ASKER

Name of the field is G_Name and TPT_Ledger is a query name

>>>Are you reporting against the same database as the query?
I dont understand your question, But please check attached code.

Dim Rsldgr As New ADODB.Recordset

If Rsldgr.State = 1 Then Rsldgr.Close

Rsldgr.Open "Select Bilty_Detail.CST as MemoNo, Bilty_Detail.GRDate as M_Date, " & _
"Bilty_Detail.BiltyNo as LR_No, Bilty_Detail.GRNo as Cros_No, " & _
"Bilty_Detail.Total as Vasuli, Bilty_Detail.pf as Crossing, " & _
"Bilty_Detail.Total - Bilty_Detail.pf as Balance, 0 as Amt_Rcvd, Bilty_Detail.G_Name " & _
"from Bilty_Detail " & _
"Where Bilty_Detail.G_Name = '" & Text1.text & "' " & _
"UNION ALL SELECT 0 as RcptNo, Receipt_Tport.Rcpt_Date, 0 as Amt_Rcvd, " & _
"Receipt_Tport.RcptNo, 0 as Total, 0 as pf, 0 as Balance, Receipt_Tport.Amt_Rcvd, " & _
"Receipt_Tport.G_Name " & _
"from Receipt_Tport " & _
"Where Receipt_Tport.G_Name = '" & Text1.text & "' ORDER BY M_Date", con, adOpenKeyset, adLockOptimistic

    If Rsldgr.RecordCount > 0 Then
        Set DataGrid1.DataSource = Rsldgr
    End If

Open in new window

In the field explorer of Crystal is that the name?

Try using

CrystalReport1.ReplaceSelectionFormula = "{TPT_Ledger.G_Name} = '" & Text1.text & "'"

ASKER

Compile error
Argument Not Optional
Highlighted on
.ReplaceSelectionFormula =

ASKER

(TPT_Ledger.G_Name) field is in the Reports Detail Section

Sorry mixing methods and properties. ReplaceSelectionFormula is a method so try it as

CrystalReport1.ReplaceSelectionFormula "{TPT_Ledger.G_Name} = '" & Text1.text & "'"

mlmcc

ASKER

First Error

Seagate Crystal Reports : Database Error
Error Opening File
File Could not be opened: "TPT_Ledger" at File Location: "TPT_Ledger"

Second Erro
Run Time Error 20535
Unable to connect : Incorrect Session Parameters.

ASKER

Sorry
It was Commented password line

Now its opening Report but still nothing in the report

Are there records for the name being passed?

mlmcc

ASKER

Oh i got it,
In the database query i put following syntax
Receipt_Tport.G_Name = '" & Text1.text & "'

i just removed it and its working fine

Of-course You earned point, But My another question is related to this question so please attend and reply me,

https://www.experts-exchange.com/questions/26908189/Previous-Value-in-Formula.html

i was stuck and your help run me out

Thanks

ASKER

Thanks good answer
But it took Little long :-D