Writing records

[HowardHammerman]

My client wants an invoice report. Not a problem. One page per invoice. However, he wants a preliminary report that checks to make sure that all the elements needed for the monthly invoice are in the tables in the correct format. Again not a problem. Those customers who fail the test need to be reported along with the errors. Can do. At the same time he wants a file produced with one record per customer with some additional ID information so that while staff members are correcting the invoices with errors, the remaining invoices can be run. That is, he wants Crystal to create a new table with one record per customer with errors so that the next report can check that table and if the id is found, skip the customer. Question: How can we get Crystal to write a record under certain conditions? Thanks in advance.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[CoSpringsGuy]

I think I would setup a parameter field called errors with possible values of Y or N

create a formula which tests for those records with errors
Create an additional details section with the fields required by staff members to make corrections.

conditionally suppress the new detail section based on the parameter and the results of the error formula
conditionally suppress all sections which comprise the Invoice report based on the parameter field


Run the report once selecting Y for correction output and either print or export that information to useful output format
Run the report again selecting N for invoice output

Once the corrections aremade int he original table I assume there is someway to determine what has and has not run already. If not you could export the error portion to an excel table to link back into your original table.

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[HowardHammerman]

That was my plan. The problem is the messiness of an excel table. Is it possible to export to an SQL table?

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[IanWaterman]

Never tried this myself.

But can you use a subreport or a SQL expression which actually executes a stored Procedure.

The SP could take Shared variable values from main report and these are used in a Update query embedded within the SP.

A final SubReport in the report footer could then read off that table.

Ian

 

[HowardHammerman]

THAT is what I was looking for. Does anyone have more info on executing a Stored Procedure from within a sub report?

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[IdoMillet]

One of the 3rd-party Crystal Reports schedulers listed at

http://kenhamady.com/bookmarks.html

allows you to export a Crystal report to an ODBC table and replace or append to the records in that table.

While Crystal alone can export to an ODBC table, it can do so ONLY if the table doesn't already exist. The scheduler mentioned above solves that problem.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IanWaterman]

Believe you will need to Create a static table, don't think you can use temp table. MS SQL will look something like

 CREATE TABLE #invoice_fails 
    (
		  PostingDate DATE
		, InvAmt Decimal(9,2)
		, ErrorDesc NVARCHAR(20)
		, Customer NVARCHAR(20)
		, TrxNo INT
		 )

Stored Proc

PROCEDURE [dbo].[usp_RPT_Invoice_errors] 
(	@PostingDate Date,
	, @InvAmt Decimal(9,2)
	, @ErrorDesc NVARCHAR(20)
	, @Customer NVARCHAR(20)
	, @TrxNo INT



AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

 	INSERT INTO #invoice_fails 
    (
		  PostingDate DATE
		, InvAmt Decimal(9,2)
		, ErrorDesc NVARCHAR(20)
		, Customer NVARCHAR(20)
		, TrxNo INT
		 )

Values 
(PostingDate, InvAmt, ErrorDesc, Customer, TrxNo)

END

Then in Crystal create a command based subreport.

Execute usp_RPT_Invoice_errors CmdParam1, CmdParam2, CmdParam3, CmdParam4, CmdParam

Then in Linking window link the Command Parameters to the fields in the main report that you want to add to Table.

Good luck

Ian

 

[IdoMillet]

You can also use one of the 3rd-party UFLs to trigger SQL INSERT statements from Crystal formulas.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[RTag]

Add all the logic to the stored procedure and base the report on it. You can use a command if you don't have rights to add a stored procedure.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[RTag]

Howard, as I said in my previous post you can create a stored procedure, which is going to process the records in the database, insert those with problems to a "bad records" table and return a list of the good records. For each record in the "good records" list you can run a subreport to print an invoice. If your goal is to send these invoices by e-mail you can use a bursting to process each subreport and send it by e-mail (I remember you were selling one tool that can do that).

There are 2 major problems with this approach:
- There should be a user interface to remove the records from the "Bad Records" table when they are fixed, which is going beyond a reporting solution
- There is no notification that some records require user interaction. In case you want to automate the process: you will schedule the report, it will run and create some records in the "Bad records". The problem is that somebody has to remember and check this table, otherwise the records will be never fixed and some customers will never receive their invoices.
Also, if you already have subreports in your invoice report - you will be not able to use this approach, you will need to separate the stored procedure for the printing procedure and to run them one by one.

Another way to handle this is using a 3rd party reporting tool (with a scheduler), which can retrieve the list of the “good records” on the fly and generate and distribute the invoices. Then it will send an email to the user with processed and not processed records, so the user will know what is going on and what should be done. Since this will be just one report and one job you will be able to find a free tool - let me know if you need more details about that.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.