Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

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
0
crystal_Tech
Asked:
crystal_Tech
  • 40
  • 24
1 Solution
 
mlmccCommented:
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
0
 
crystal_TechAuthor Commented:
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 =
0
 
mlmccCommented:
What is the issue?

mlmcc
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
crystal_TechAuthor Commented:

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    
0
 
crystal_TechAuthor Commented:
Please attend to my  question asap
0
 
mlmccCommented:
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
0
 
crystal_TechAuthor Commented:

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,

0
 
mlmccCommented:
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
0
 
mlmccCommented:

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

0
 
crystal_TechAuthor Commented:

Please Just give me few minutes, let me try your suggestion.
0
 
crystal_TechAuthor Commented:

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



0
 
mlmccCommented:
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
0
 
crystal_TechAuthor Commented:

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.....)
0
 
crystal_TechAuthor Commented:
i only know a way to connect database using datafile
0
 
mlmccCommented:
I see you are using CR7, my idea won't work.


mlmcc
0
 
crystal_TechAuthor Commented:

my luck ....!
then what you suggest sir
0
 
mlmccCommented:
You could try using a Crystal QUery.  I never used that option so I don't really know how to do it.

mlmcc
0
 
crystal_TechAuthor Commented:
its asking me to select *.qry file
0
 
mlmccCommented:
You have to create one.  It is a separate program in the SEAGATE - Crystal program set.

mlmcc
0
 
crystal_TechAuthor Commented:

sir  any other way ?
0
 
mlmccCommented:
I don't know of another way

It might be possible through a subreport .

mlmcc
0
 
crystal_TechAuthor Commented:

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


0
 
crystal_TechAuthor Commented:

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..?
0
 
mlmccCommented:
That is exactly what the union query is doing.

mlmcc
0
 
crystal_TechAuthor Commented:

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 ?
0
 
mlmccCommented:
Probably not.

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

mlmcc
0
 
crystal_TechAuthor Commented:

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
0
 
mlmccCommented:
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
0
 
crystal_TechAuthor Commented:
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.

0
 
crystal_TechAuthor Commented:

ok sir i have designed another report
(SubLedgerRcpt.rpt)
Now...?
0
 
mlmccCommented:
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
0
 
crystal_TechAuthor Commented:
sir one group already in Main Report, Name in Transname,
0
 
crystal_TechAuthor Commented:
sir where must i put the sub report in main report
in the group header or detail section
0
 
crystal_TechAuthor Commented:

Sir i am totally Confused .. ?
Can You please do this if i will give you teamviewer.?
0
 
crystal_TechAuthor Commented:
hello........Sir ..!!
0
 
mlmccCommented:
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
0
 
crystal_TechAuthor Commented:
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

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

mlmcc
0
 
crystal_TechAuthor Commented:
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

0
 
crystal_TechAuthor Commented:

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


0
 
mlmccCommented:
What is the report source for the main report?

What is the source for the subreport?

mlmcc
0
 
crystal_TechAuthor Commented:
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)

0
 
crystal_TechAuthor Commented:

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

Please assist me.


0
 
crystal_TechAuthor Commented:

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
0
 
crystal_TechAuthor Commented:

OR

I have created  Ldgr.qry from Crystal SQL Designer,
How to call this Ldgr.qry from VB from
0
 
mlmccCommented:
What do you mean call the .qry file from VB?

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

mlmcc
0
 
crystal_TechAuthor Commented:

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
0
 
mlmccCommented:
You can use this part in the QRY file
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 " ORDER BY M_Date", con, adOpenKeyset, adLockOptimistic


The where clause is added through the SELECT EXPERT and you can use parameters and pass the text fields from the application

mlmcc
0
 
crystal_TechAuthor Commented:
Before go to your suggestion i have to create crystal report Right ?
0
 
crystal_TechAuthor Commented:
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,
0
 
crystal_TechAuthor Commented:
Now what must i do
0
 
mlmccCommented:
One of the database options should be a query file.  I forget how that is shown.

mlmcc
0
 
crystal_TechAuthor Commented:
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 ?
0
 
mlmccCommented:
In the report is TPT_Ledger.G_Name the name of the field?

Are you reporting against the same database as the query?

mlmcc
0
 
crystal_TechAuthor Commented:

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

0
 
mlmccCommented:
In the field explorer of Crystal is that the name?

Try using

CrystalReport1.ReplaceSelectionFormula = "{TPT_Ledger.G_Name} = '" & Text1.text & "'"
0
 
crystal_TechAuthor Commented:
Compile error
Argument Not Optional
Highlighted on
.ReplaceSelectionFormula =
0
 
crystal_TechAuthor Commented:
(TPT_Ledger.G_Name) field is in the Reports Detail Section
0
 
mlmccCommented:
Sorry mixing methods and properties. ReplaceSelectionFormula  is a method so try it as

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

mlmcc
 
0
 
crystal_TechAuthor Commented:
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.
0
 
crystal_TechAuthor Commented:

Sorry
It was Commented password line

Now its opening Report but still nothing in the report
0
 
mlmccCommented:
Are there records for the name being passed?

mlmcc
0
 
crystal_TechAuthor Commented:

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,

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26908189.html

i was stuck and your help run me out

Thanks


0
 
crystal_TechAuthor Commented:
Thanks good answer
But it took Little long  :-D
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 40
  • 24
Tackle projects and never again get stuck behind a technical roadblock.
Join Now