Crystal Reports and SQL Expressions

Check this question with the solution by rhinok

https://www.experts-exchange.com/questions/28177316/SQL-Statment-to-SQL-Expression-in-Crystal-Reports.html

Please wait for rhinok to respond, I am sure he can add further details on what you can and can't do with SQL Expressions.

mlmcc

Are you using the subreport to return just one value ?

Original question -

Crystal Reports - Hide Footer based on Criteria
https://www.experts-exchange.com/questions/28349811/Crystal-Reports-Hide-Footer-based-on-Criteria.html

mlmcc

Vasto

Yes, I am returning only one value from the sub report.

The solution link mlmcc posted contains comprehensive information about how to use a SQL Expression.  Since you're using SQL Server, you can correlate data in your report to data in the expression.  The basic rules to follow are:

1)  Encapsulate the expression in parentheses
2)  Only return a single, distinct value. A SQL Expression is added as a database field to the SELECT clause that Crystal generates when you run the report
3)  To correlate the data, double-click or drag the database field down from the Field tree in the SQL Expression editor to the expression you're writing.  This ensures it'll be formatted properly.

Just to save you the effort of having to open another link, here's an example of a correlated SQL Expression using SQL Server:

(
SELECT DISTINCT
	Y.REF_BILL_CODE
FROM
	HPS_ACCT_DX_LIST X
	INNER JOIN CLARITY_EDG Y ON X.DX_ID = Y.DX_ID
WHERE
	X.HSP_ACCOUNT_ID = "HSP_ACCOUNT"."HSP_ACCOUNT_ID"
	X.LINE = 1
)

Select allOpen in new window

rhinok

Thanks I had a look at your post earlier for guidelines and got it to work using fixed variables.

I have 2 parameters, one which comes straight from the report, this feeds through fine, but my second is a selection parameter, being fiscal year, i.e. 2014. Which is selected at the time of running the report.

In a sub report, it is easy to pass this to the sub report, in the query, but I am stumped as to how to get it working in the SQL expression

@JacquesSmith

SQL Expressions only accept actual database field values.  You can't use either formulas or parameters within them.  That being said, your base data in the report is already filtered by the parameters, right?  So, if you pass in field values from the report to the SQL Expression then the expression will implicitly be limited based on the parameters you've selected.

As an example, if you have filtered your report based on a date range then the records in the report will fall within that range, which means there's a date field in your report.  Pass that field into your SQL Expression and you'll only get a record that matches that date field.

rhinok

Unfortunately the database on which the report is based is purely a stock Database with Stock Codes and Stats in it, there are no date fields.

The database that I want to run the SQL query on is a sales database.

So if I understand correctly, unless I have a field in the database of the report, I cannot pass it on to the Query, unless I hard code it into the query.

I suppose the best solution is to have multiple Sql Expression fields, and select the appropriate one via formula.

That was just an example.  What parameters are you trying to pass in?

One of the 3rd-party Crystal Reports UFLs (User Function Libraries) listed at http://kenhamady.com/bookmarks.html allows you to use a Crystal formula to dynamically construct an SQL statement and execute it against any ODBC or OLEDB data source.

In the case of SELECT statements, the formula can return either a single or delimited values.

rhinok

The first parameter is the Item Code, which is in the report, and this goes through perfectly, the Second Parameter, is a 4 digit year to select the fiscal year i.e. 2014 or 2015 etc.

This fiscal year parameter is selected when the report is run, and this is passed through to the Sub Report, I would like to pass it in some way to the expression if I could like I do with a sub Report.

In the Expression, I am summarizing a value field based on Stock Code and fiscal period, and passing back this single value to the report

Since a parameter value is established only after the report runs, it can't be used in a main report SQL expression.
See my post above for a solution that does work with parameter values.

rhinok

Thanks your solution does pass the Fiscal Period into the Query.

I have a fiscal table with the fiscal years in it, so it is inserting values, but random fiscal years.

The problem is, how do I get it to display the fiscal year from the fiscal table that I want, and have selected in the parameters selection

Can you please post both your record selection criteria and the SQL Expression code?

If you filter the data in the report to just records with FY 2014 and Item code then those values will get passed into the SQL Expression field for each record. Since a record can only have one distinct value, per field, you shouldn't be getting anything random.

I will post the selection criteria, SQL expression as well as my table linking in the morning .

When I use the fiscal year in the selection criteria it only displays the 2014, and this it passes on to the SQL expression, the problem is that all the records are not displayed from the items table any more, and I am missing records.

I gave a table of item no's and stats, this outer joins to the sales value table (this is the table that I run the expression on, summing the sales value on item no and fiscal year). The next table is the fiscal calendar table, which has been joined from sales value with a left outer join.

When I use the fiscal period in a select statement, it seems to change the left outer join  to a inner join.

I hope I have made sense, but it should be clear once I have posted the info.

rhinok,
I  did post an answer, with an attachment on the 31st, and checked that it was there, but it appears to be missing now, apologies.


Record Selection Formula:

{ICILOC.QTYONHAND}<>0 and {ICPRICP.PRICELIST}="STD" and {ICPRICP.DPRICETYPE}=1
and {CSFSC.FSCYEAR}="2014"

SQL Expression Code:

(
Select SUM(icstati.SALESQTY)
from  icstati
WHERE ICILOC.ITEMNO = "ICSTATI"."ITEMNO" and ICSTATI.YEAR = "CSFSC"."FSCYEAR"
)

The Problem is that when I use the Fscryear in the record selection Process, it changes the left outer Join to a Inner Join, and thus all the records that are selected from the iciloc table and icstati table are not displayed.

if I remove the value from the record selection formula, and replace the SQL expression with just the value of "2014" then it works
31-01-2014-07-41-24-PM.jpg

I would argue that the real problem is you're trying to apply a filter to a left joined table, not the SQL Expression itself.  You can try to get around this by checking for NULLs in the record selection, but results can be hit or miss. Alternately, you can use the left joined table in a subreport instead of in a main report, but this is overly complex and not particularly efficient.

The only truly consistent and efficient way to apply a filter to a left joined table is to do it in the join itself, which requires SQL.  Whenever I have a requirement like this, I use a SQL Command or View.   Also, if you used a SQL Command or View as your data source, it would eliminate the need for a SQL Expression, since you could  just build that field directly into the SQL.

Even though I did not get a proper working solution, the information and knowledge gained was excellent