Solved

Crystal Report Database Query

Posted on 2003-12-10
15
585 Views
Last Modified: 2007-12-19
I would like to be able to change the query of a Crystal Report on the fly.

I have the query built into a command in the designer at the moment.

Can any one help me?
0
Comment
Question by:Terry Rogers
15 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9913049
I'm not sure what you mean by 'built into a command'

However, in Visual Basic, add references to the Crystal libraries (Project/References), that will get you started. It allows you to change various properties of a crystal report and then run it.
0
 
LVL 1

Author Comment

by:Terry Rogers
ID: 9913180
ok, Im using a Crystal Report designer within my VB app.

When I click the designer it shows me the design of the report.

On the left it says "Database Fields" and under this is "Command" with the Fields from the "Command" SQL Statement coming off of it.

If I go into the database expert I can see on the right the server address and under that another item called command which I created with my SQL query in. To edit the sql query in that command I right click on it and select edit command.

I need to be able to edit this command from within my VB code and not the designer.

Hope ive made things a little more clear.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9913497
Ah, OK, I am using the full version of Crystal rather than the VB version at the moment so I got a bit confused, sorry.



This will work in theory but I avoid the data environment so I haven't used it much.

DataEnvironmantName.Commands("CommandName").CommandText = "New SQL Query"

Make sure the query returns the same number of fields and ensure they have the same names.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9913557
Oh yeah, now that I have had a hack, the command has to be a SQL statement rather than a table, otherwise that will just change the table name rather than the whole SQL statement.
0
 
LVL 1

Author Comment

by:Terry Rogers
ID: 9914453
This is good except im not using a data environment, so how can I change the command in the report?

My designer is called CrystalReport,

And I dont have a CrystalReport.Commands options, I have a CrystalReport.Database but I still cant find the command thing your referring to.
0
 
LVL 1

Author Comment

by:Terry Rogers
ID: 9914522
I have found that CrystalReport.SQLQueryString is holding my query but when I try to change it it doesn't change.
0
 
LVL 1

Author Comment

by:Terry Rogers
ID: 9914543
Also im using Crystal Reports 9 if thats of any help.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Expert Comment

by:mlmcc
ID: 9917663
What you are trying to do may require the Crystal Reports 9 Designer Design and Run Time library (CRAXDDRT.dll).  This may also incur a requirement for you to purchase runtime licenses for all users ($199 per license).  

In general you cannot change the select part of the query.  It may be possible with the CRAXDDRT library.

mlmcc
0
 
LVL 1

Author Comment

by:Terry Rogers
ID: 9918820
This has come from CrystalDevHelp.chm.

Property: SQLQueryString
Description: String. Gets or sets SQL query string.
Read/Write: Read/Write
Restriction in event handler: Can be written only when formatting idle.

It seems this is what I need to change, and it is a read/write property. But how can I make "formatting idle"?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9918910
Formatting Idle sounds lke it means you can only change it when the report is not running, which is what you are doing anyway.

Sorry I will have to bow out as I only have version 7, and I can't find the crystal reports designer option in VB.

There is one other option and that is to base the report on a view (A SQL Server view or a MS Access query) then change the view in the database. It's a bit hairy though and really you shouldn't have to if Crystal help says you can change the source query.

Good Luck!

0
 
LVL 1

Expert Comment

by:vbnewby
ID: 9919443
This may not be the correct answer to your question, but it is the way I have got around it.

I first created a report table in the database, had my SQL statment populate the report table, then had crystal report look at that table



0
 
LVL 1

Author Comment

by:Terry Rogers
ID: 9919988
vbnewby, this seems like a solution but it seems a bit long winded.

Anyone, anyone at all who can come up with a good solution to this problem would be great.

getting more and more desperate by the minute. :-\
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9920215
Like I said there is the view option but it is also a little bit long winded.
0
 

Accepted Solution

by:
SerCouWisMOD earned 0 total points
ID: 9966975
A request has been made to close this question and refund the points;
http://www.experts-exchange.com/Community_Support/Q_20829593.html
If there are no objections within the next 72 hours, the request will be granted.

EXPERTS: Please leave your thoughts on the request here.

SerCouWisMOD
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba find the last empty column 10 89
Access 2016 VB code 9 103
passing a value with stream reader AFTER a ";" 3 59
Spell Check in VB6 13 99
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

864 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

23 Experts available now in Live!

Get 1:1 Help Now