Solved

Crystal Report Field Problem

Posted on 2011-03-17
64
435 Views
Last Modified: 2012-05-11

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
Comment
Question by:crystal_Tech
  • 40
  • 24
64 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What is the issue?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
Please attend to my  question asap
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility

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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
i only know a way to connect database using datafile
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I see you are using CR7, my idea won't work.


mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

my luck ....!
then what you suggest sir
0
 
LVL 100

Expert Comment

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

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
its asking me to select *.qry file
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You have to create one.  It is a separate program in the SEAGATE - Crystal program set.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

sir  any other way ?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I don't know of another way

It might be possible through a subreport .

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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


0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
That is exactly what the union query is doing.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Probably not.

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

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
sir one group already in Main Report, Name in Transname,
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
sir where must i put the sub report in main report
in the group header or detail section
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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

Author Comment

by:crystal_Tech
Comment Utility
hello........Sir ..!!
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You should be able to call it just like any other report.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What is the report source for the main report?

What is the source for the subreport?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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

Please assist me.


0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

OR

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

Expert Comment

by:mlmcc
Comment Utility
What do you mean call the .qry file from VB?

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

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
Before go to your suggestion i have to create crystal report Right ?
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
Now what must i do
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
One of the database options should be a query file.  I forget how that is shown.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
In the field explorer of Crystal is that the name?

Try using

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

Author Comment

by:crystal_Tech
Comment Utility
Compile error
Argument Not Optional
Highlighted on
.ReplaceSelectionFormula =
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
(TPT_Ledger.G_Name) field is in the Reports Detail Section
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Sorry mixing methods and properties. ReplaceSelectionFormula  is a method so try it as

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

mlmcc
 
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility
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
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

Sorry
It was Commented password line

Now its opening Report but still nothing in the report
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are there records for the name being passed?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
Comment Utility

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
 
LVL 1

Author Closing Comment

by:crystal_Tech
Comment Utility
Thanks good answer
But it took Little long  :-D
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now