Can I write back to the database?

[Danster]

Gday all,

Does anyone know if its possible to write a value back to my database based on say, a CR parameter which might say prompt the user with something like "Do you want to write a value into your database" (something a bit more meaningful for my actual purposes of course). Depending on whether they repond with a Y or N, I just want the answer written to a field.
In my case, my underlying db is SQL Server.

cheers all
Danster

 

[IdoMillet]

In CR 9 you can construct your data source as a COMMAND with an UPDATE statement followed by a SELECT statement.

See:

http://support.businessobjects.com/library/kbase/articles/c2011921.asp

Alternatively, you can create a Stored Procedure and use it as the data source to achieve the same thing.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[mrees]

If you use a stored procedure with parameters as your data source, your CR can ask the user for the parameters, and then the stored proc can run on to update your tables, eg

CREATE PROC CRTest (@Answer char(1))
AS

//Send data to Crystal Report
SELECT
  item1,
  item2,
  item3
FROM
  mytable

//Decide to update table
IF @Answer = 'Y' 
  BEGIN
    UPDATE mytable
    SET updated = 'Y'
  END

 

[Danster]

Thanks both Ido and mrees,
Excellent answers...
I'm using the suggestion of a stored proc because I'm using CR 8.5.
If my report is already based on a few tables in the database (about 10 tables), is it possible to just call the stored proc from Crystal somehow without having it in the data source?
I've tried just SELECT LinkField, updateField FROM MainTable in my stored proc similar to mrees code, so at least I have a linking field from the CR source tables to the stored proc, but this gave an error "Invalid file link. Not an indexed field" when linking in CR.

I don't want to have to duplicate the whole table linking rigmarole in the stored proc.

Is there a way around this error or do I NEED to base my report on the stored proc SELECT statement?

cheers

Danster

 

[dgillz]

It is not required to use an SP as your data source in this instance, just add the 10 tables in question to your report, create the proper linking, and write the report.

However with 10 tables, an SP may be much more efficient.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Danster]

dgillz,
If a stored proc is not necessary, how do I update the database? I'm using CR 8.5.

cheers

danny

 

[dgillz]

oops,

Yes an SP would be necessary in that case.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[dgillz]

Hello Ido and Mrees.

I want a crystal report to update a status field in a certain table, so I created the following SP:

CREATE proc dbo.sp_create_UpdateOrderStatus as

SELECT Ord_type, Ord_no
FROM OEORDHDR_SQL
WHERE Ord_Type='O'

UPDATE OEORDHDR_SQL
SET status = '4'
GO

The problem is, my parameter is on the Crystal side and this SP is updating the entire database rather than just the order(s) I want.

I actually used the SP as a subreport, since I could not link the SP to my tables on my already written report. I was hoping my subreport link would limit the records that got updated in my database, but alas the entire database was updated.

Any ideas how to limit the execution of the WHERE clause of my SP to only those records I want? This would be for certain unique Ord_No records only.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[mrees]

dgillz,

How do you define which Ord_Nos you want to update?

mrees

 

[dgillz]

mrees,

Right now, with a parameter on the crystal report.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[vidru]

dgillz,

You could put together a comma separated list in the main report containing the updatable order numbers, alter the procedure to take a string parameter, and pass that to the subreport:
[tt]
ALTER proc dbo.sp_create_UpdateOrderStatus
@LIST VARCHAR(200) -- would come in as (1,2,3,4,5,6,7,8,9,etc.)
AS
DECLARE
@SQL VARCHAR(200)

SET @SQL = 'UPDATE OEORDHDR_SQL
SET status = 4
WHERE Ord_no IN '

IF (@List IS NOT NULL AND @List <> '')
EXEC(@SQL + @LIST)
GO
[/tt]

 

[mrees]

dgillz,

So when you run/view the report, you only update one Ord_No? Or like in vidru's suggestion you want to update many ord_no's

mrees

 

[dgillz]

I want to update the order numbers specified by my parameter in crystal. It could be one or many.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Danster]

Gday all,
Thanks for all answers & subsequent questions with answers & no doubt more to come...
I think the answer to dgillz question is to have a parameter in the SP which will prompt when Crystal is run.
Something like
CREATE proc dbo.sp_create_UpdateOrderStatus @OrdNums char(x) as

SELECT Ord_type, Ord_no
FROM OEORDHDR_SQL
WHERE Ord_Type='O' and Ord_no=@OrdNums

although I think this will only work if the parameter on accepts a single value.

BUT, as a secondary question, I have a formula field which I'd like to pass the value back to the stored proc. How can I do it without creating a stored proc parameter? Or if I do have to create a SP with a parameter, how can I get Crystal to write the value of the formula to the parameter, but without asking the user?

cheers

 

[chelseatech]

Here is a totally different approach I took to the problem.

I created a report to find some incorrect data, and created a formula to write an "UPDATE" statement to modify each record.

I ran the report and it display a bunch of Update statements which I then exported to text.

I then opened the text file, cut and pasted the text into SQL Query Analyzer and executed them. All my records were then updated.

Probably not something you would want in schedule production report, but great for a one off fix.

The other solution to your problem is a UFL, which displays a message box on each line, and asks for a new value for the record. It then updates the database. Messy, and it wouldn't work on a web server, but it would answer your question.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm