views:

26

answers:

1

Hi

I have a Crystal Report set up to run with my windows forms application.

I am passing a string to the SelectionFormula of the report.

What I am doing is:

I have 3 tables - StockFile, BranchFile and Supplier

A Stock item is created when that item is added to branch stock, an item can be moved around branches until it is sold, so can have a number of records in the stockfile table.

What I want to do is create a report which pulls back:

[BranchFile].[BranchName], [BranchFile}.[BranchName], [Supplier].[SupplierName], (RTRIM([StockFile].[DESCRIPTION_LINE_1]) + '/' + RTRIM([StockFile].[DESCRIPTION_LINE_2]) + '/' + RTRIM([StockFile].[USER_ENTERED_DATA])) AS DESCRIPTION, [StockFile].[StockCode], [StockFile].[Size], [StockFile].[Weight], [StockFile].[CreationDate], [StockFile].[RetailPrice], [StockFile].[AssignedBranchQty]

I want this data returned for the following criteria:

(min([StockFile].[CreationDate]) <= '2009-08-01') AND [StockFile].[CreationDate] <= '2010-08-23' AND [StockFile].[AssignedBranchQty] <> 0

I can manage everything apart from the min() part.

Please can someone help, Can I achieve this using the SelectionFormula???

Thank You

+2  A: 

In general, try to minimize the amount of SQL work you do in Crystal and your Winforms app.

Create a query or view on your back end doing the SQL work, use your Winforms app for the data entry of date parameters or whatever other limiting criteria you need to apply to the query, and apply the criteria within Crystal.

It will be much easier to work with SQL on the back end compared to within the UI or reporting tool.

Beth
Cheers Beth, Much appreciated!
Richard
Good point. Crystal is a reporting tool, made for advanced formatting. It has some data filtering capabilities, but it's not a SQL engine.
PowerUser
I am now having much more success by using views to gather together the data and then creating the report based upon that view.I have also found this better as I can make changes to the view without having to re-deploy my windows forms application to update the report (where possible)
Richard