?
Solved

How to Create Crystal Report

Posted on 2012-08-31
13
Medium Priority
?
1,145 Views
Last Modified: 2012-09-10
Hi
I have created a couples of Crystal reports all reports are connected with MS Access DB.

Now i have switched from ms access to SQL SERVER2008
Ans how all this report convert from access to SQL

and honestly i dont know how to connect with sql server.

Please explain me step by step

I am using Crystal report 7.0 (Old version)
and Database = SQL SERVER 2008 R2
0
Comment
Question by:crystal_Tech
  • 8
  • 4
13 Comments
 
LVL 12

Expert Comment

by:Seaton007
ID: 38354825
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38355059
I have my doubts that CR 7 will connect with SQL Server 2008 R2

I know there were some updates requred to connect with SQL Server 2000

You probably need to create an ODBC connection to SQL Server
Be sure you use the 32-bit ODBC connection wizard.  CR7 is 32-bit only.

Once created you should be able to choose it when you create a report as the data source under ODBC connections

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38355482
Thanks for Quick Reply.

I tried following thing

Creating New Report using  CR7
In Standard Report Expert..
There is
Data File
Query
SQL/ODBC
Dictionary
OLEDB
etc.....

Clicked on SQL/ODBC
windows displayed

Log on Server

server Type i have choosed
Active Data (ADO)

Clicked on OK
Window displayed - Select Data Source

There is Four Options
1- ODBC(ADO)
2- ADO and OLEDB
3- DAO (This option is disable)
4- Data Definition (This option is disable)

Selected First option ODBC(ADO)
Clicked on NEW
window displayed
Create New Data Source
Selected First Option
File Datasourse (Machine Independent)
Clicked on NEXT
Selected Driver
SQL Server
Clicked on NEXT
Typed Name of DSN (MyServerPrint)
Clicked on NEXT
typed name of My SqlServer in server box
clicked on NEXT
Authenticity of the login ID..?
Selected Second option (with SQL Server Authentication)
Login Id = myloginid
Password = myPassword
clicked on NEXT
Changed default database to MyDatabase
clicked on NEXT
clicked on NEXT
clicked on FINISH
Clicked on TEST DATA SOURCE
TESTS COMPLETED SUCCESSFULLY
OK
OK
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Now Whats the next step..?

How can i drag and drop fields from sql table to report
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:crystal_Tech
ID: 38355831
ok now i got it ..
but now problem is
i am getting

Runtime error  '20599'
Can Not Open SQL Server

Using following code

    
' My Connection string is
    CON.Open _
            "Provider = sqloledb;" & _
            "Data Source=MYSERVER;" & _
            "Initial Catalog=MYDB;" & _
            "User ID=MYID;" & _
            "Password=MYPASS;"

if CON.State =  1 
                  '''Do Nothineg
else
 CON.Open
end if

CrystalReport1.ReportFileName = App.Path & "\CUST.rpt"
CrystalReport1.Connect = CON
    CrystalReport1.DiscardSavedData = True
    CrystalReport1.RetrieveDataFiles
CrystalReport1.SelectionFormula = "{SQLTABLE.Field1} = " & Text1.Text & ""
    CrystalReport1.Formulas(1) = "Field2= '" & Text1.Text & "'"

    CrystalReport1.Destination = crptToWindow
    CrystalReport1.Action = 1

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38355973
Have you built a report?

Try moving the discard saved data line up 1 so it is just after you open the report

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38356019
<< Have you built a report? >>
Yes and its working fine from CRYSTAL it self.

But NOT from my code.

You mean like this..?
if yes then i tried .. it still same error.


CrystalReport1.ReportFileName = App.Path & "\CUST.rpt"
CrystalReport1.DiscardSavedData = True
CrystalReport1.Connect = CON
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38356479
Tried this one .. but no luck

CrystalReport1.Connect = "DSN=MyPrintCon;UID:MyID;PWD:Mpwd;DSQ=MyDatabase;"

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38356539
Your version is old enough that I don't recall how to connect that way.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38357217
is there any other way .. because more than 30 reports allredy created.. and need to waiting for switch into SQL Server 2008
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38357334
Tried following code
But still same error i am getting.

from crystal environment its working fine.


CrystalReport1.ReportFileName = App.Path & "\Cust.rpt"
CrystalReport1.Connect = "Provider=SQLOLEDB.1;Password=*****;Persist Security Info=True;User ID=****;Initial Catalog=CustDetail;Data Source=ServerName"
CrystalReport1.RetrieveDataFiles
CrystalReport1.Destination = crptToWindow
CrystalReport1.DiscardSavedData = True
CrystalReport1.Action = 1

Open in new window

0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 38357381
Are you building the reports against the production database?
If so there is no need to change the database path.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38357673
i think problem is my DSN
i have created dsn.. i checked and configured it.. it wasn't work
and also it was not in the ODBC registry even not in the system DSN

so i have registered DSN through regedit

and now it is working fine..
like charm

over all my code is
   
CrystalReport1.ReportFileName = App.Path & "\Cust.rpt"
CrystalReport1.Connect = "DSN=MyCon;UID=xxxxx;PWD=xxxx;DSQ=MyDatabase" 

CrystalReport1.RetrieveDataFiles
CrystalReport1.Destination = crptToWindow
CrystalReport1.DiscardSavedData = True
CrystalReport1.Action = 1

Open in new window

0
 
LVL 1

Author Closing Comment

by:crystal_Tech
ID: 38385694
Thanks.
Problem was DSN
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

840 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