cancel
Showing results for 
Search instead for 
Did you mean: 

Write-back in a table with Crystal Reports

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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...

JWiseman
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Jamie

the work flow is really easy.

user has a button to submit and then the date/time, the username and a parameter will write in to a table.

Thanks,

Thierry

JWiseman
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Jamie

I see its not so hard but with an Oracle Server it will not work

there are several other thread's that user has some problems with Oracle and commands in Crystal

JWiseman
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

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...

Former Member
0 Kudos

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

JWiseman
Active Contributor
0 Kudos

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

Answers (2)

Answers (2)

Former Member
0 Kudos

There are also the WebElements:

"Develop an interface to provide additional interaction with data such as write-backs to the database and report annotations"

Have somebody ever done that?

Former Member
0 Kudos

Pls check this link. Hope it might be helpful.

http://www.tek-tips.com/viewthread.cfm?qid=823202