on 01-12-2010 12:19 PM
Hi,
Is it possible to write some data back in to a table with a button in Crystal Reports?
Somebody has experience with something like that?
Thanks,
Thierry
Edited by: Thierry Rieder on Jan 12, 2010 1:33 PM
Here is another option. It is possible, at least I have been able to do this in some environments, to use create/update queries as part of a SQL Command when creating a custom data source, as long as the query returns a row of data as a final result.
You could use that concept to create a dynamic sub-report that is executed only upon clicking on a hyperlink it the main report (your button). In the SQL Command, create parameters for the data you wish to update through the SET clause as well as any conditional values to include in the WHERE clause. Then link source values in the main report to the parameters in the sub-report.
The idea is that you click on the link in the main report and the sub-report executes the SQL Update query on demand using the values passed to it by the main report.
Fuskie
Who acknowledges this might not be the most graceful solution, and he can't say for sure it would work, but short of exporting back to the database via ODBC, it is an option he would probably try if the goal was to provide functionality through the report and not the design tool...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thierry,
what exactly is the work flow that you are looking for?
for example, is it an annotation...where the end user types in some stuff in a text area and then presses submit to write the text to the database & report?
or is it that the end user wants to change a specific value on a report?
jamie
in that case you can do the workflow with an on demand subreport. webelements are not really necessary for a simple update, but can provide a user interface for more complex database interactions.
steps to do this
while in design mode in the cr designer....
1) create a new On Demand subreport off of your data connection where the write back is being done
2) the data source will be a command...the following is for sql server...consult your database manual on the syntax for your database...the ? pieces below represent your prompts in the command
INSERT INTO yourtable
VALUES
(
'{?YourNameParameter}',
'{?YourOtherParameter}',
{?YourDateParameter}
)
SELECT 1
3) link the subreport from the main report to pass values via formulae to the 3 command prompts
now when the end user clicks on the link for the on demand subreport, the above 3 values will be written in to the table.
notes:
1) the SELECT piece at the end of the above command is to avoid errors...a command object in crystal must return some form of dataset
2) you can put an opendocument link in the subreport to go back to the main report
cheers,
jamie
i am looking into this. seems the same issue with access & mysql as well. it is supposed to work though as command objects support updates / inserts / anything that the odbc layer will support.
this issue with oracle was around before and then addressed in a patch in an xir2 version i believe. with some of the database a semi-colon was required to have the 2 statements in the command. as you most likely noticed an error comes up with or without the semi-colon now.
i will keep you in the loop on the findings.
Actually its not supposed to work but it can. The SQL Command feature in Crystal was intended as a unidirectional query - to create custom SELECT queries. There is no question that using it to write back to a database is a kludge, but if you can get away with it, then why not use it.
Fuskie
Who notes that for many DBAs, it is best practices to give the Crystal Reports login to database read only permissions which would prevent report developers who have been denied read-write access from trying this backdoor approach...
Thierry,
I don't know about Oracle... I don't use it, but the process that Fuskie is describing works like a charm in MS SQL Server.
If there is an issue specific to Oracle that is preventing a write back, you should be able to get around it by using a stored procedure within the database itself.
HTH,
Jason
hi Thierry,
just letting you know that product group is indeed looking at this issue. they believe that it may have something to do with the requirement from oracle for a semi-colon between the 2 statements.
this is supposed to work. command objects were to support full sql in v9 as per requirements gathering between one of the product managers for crystal and a group of customer DBA's. it is not just supposed to be uni-directional. there does seem to be a lot of misunderstanding around this though. customer assurance will not provide assistance/support in writing commands as these are database specific, but the commands themselves are supposed to support full sql.
i will keep you posted on the result regarding a fix.
for the meantime, here's a suggestion from one of the developers...
_________________________
there is a registry key you can try if it solves the problem.
Change the value to SQL_MODE_READ_WRITE if needed.
Registry Key: accessMode
Default value : SQL_MODE_READ_ONLY
Description: SQL_ATTR_ACCESS_MODE (ODBC 1.0)
An SQLUINTEGER value. SQL_MODE_READ_ONLY is used by the driver or data source as an indicator that the connection is not required to support SQL statements that cause updates to occur. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source. The driver is not required to prevent such statements from being submitted to the data source. The behavior of the driver and data source when asked to process SQL statements that are not read-only during a read-only connection is implementation-defined.
_________________________
cheers,
jamie
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Pls check this link. Hope it might be helpful.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.