Solved

Crystal Reports and SQL Expressions

Posted on 2014-01-28
20
2,181 Views
Last Modified: 2014-02-16
Hi

I am wanting to know if it is in any way possible to pass values from a record in a crystal reports to the SQL Expression Query as you can with a sub report.

I would like to eliminate the use of a Sub Report and speed up the reporting process.
0
Comment
Question by:JacquesSmith
  • 8
  • 6
  • 3
  • +2
20 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Check this question with the solution by rhinok

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_28177316.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
0
 
LVL 18

Expert Comment

by:vasto
Comment Utility
Are you using the subreport to return just one value ?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Original question -

Crystal Reports - Hide Footer based on Criteria
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_28349811.html

mlmcc
0
 

Author Comment

by:JacquesSmith
Comment Utility
Vasto

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

Expert Comment

by:Kurt Reinhardt
Comment Utility
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
)

Open in new window

0
 

Author Comment

by:JacquesSmith
Comment Utility
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
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
@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.
0
 

Author Comment

by:JacquesSmith
Comment Utility
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.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
That was just an example.  What parameters are you trying to pass in?
0
 
LVL 22

Expert Comment

by:Ido Millet
Comment Utility
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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:JacquesSmith
Comment Utility
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
0
 
LVL 22

Expert Comment

by:Ido Millet
Comment Utility
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.
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 400 total points
Comment Utility
@JacquesSmith

Since you have a parameter for a fiscal year it stands to reason there's a database field for Fiscal Year, right?  So, you should have a fiscal year field you can pass through to the SQL Expression just like the item code.  Since you're correlating data in the SQL Expression to what is in the report--which was limited based on parameter selection--you don't need to pass in parameters directly to the SQL Expression (which you couldn't do anyway).

So, your expression would look something like this:

(
SELECT 
	SUM(X.VALUEFIELD)
FROM
	TABLE X
WHERE
	X.ITEMCODEFIELD = "TABLE"."ITEMCODEFIELD"--THIS VALUE IS DRAGGED IN FROM THE FIELD TREE AND WILL BE FORMATTED AUTOMATICALLY
	AND X.FISCALYEARFIELD = "TABLE"."FISCALYEARFIELD"
)

Open in new window


There's no reason to use a third-party software that I can see.
0
 

Author Comment

by:JacquesSmith
Comment Utility
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
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
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.
0
 

Author Comment

by:JacquesSmith
Comment Utility
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.
0
 

Author Comment

by:JacquesSmith
Comment Utility
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
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
Comment Utility
The problem is, you can't pass a NULL value to the SQL Expression.  WHen it is a left outer join there can be cases when the record won't have a value for FSCYEAR.

I don't know if this will work but you can try

SQL Expression Code:

If IsNull({CSFSC.FSCYEAR}) then
(
Select SUM(icstati.SALESQTY)
from  icstati
WHERE ICILOC.ITEMNO = "ICSTATI"."ITEMNO" and ICSTATI.YEAR = 2014
)

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

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
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.
0
 

Author Closing Comment

by:JacquesSmith
Comment Utility
Even though I did not get a proper working solution, the information and knowledge gained was excellent
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now