Solved

Crystal Reports and SQL Expressions

Posted on 2014-01-28
20
2,366 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +2
20 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 39815592
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
ID: 39815616
Are you using the subreport to return just one value ?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39815636
Original question -

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

mlmcc
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:JacquesSmith
ID: 39815657
Vasto

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

Expert Comment

by:Kurt Reinhardt
ID: 39815821
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
ID: 39815868
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
ID: 39815931
@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
ID: 39815993
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
ID: 39818170
That was just an example.  What parameters are you trying to pass in?
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 39818216
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
 

Author Comment

by:JacquesSmith
ID: 39818335
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 23

Expert Comment

by:Ido Millet
ID: 39818351
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
ID: 39818465
@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
ID: 39821562
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
ID: 39822412
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
ID: 39822496
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
ID: 39832135
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 101

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
ID: 39832500
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
ID: 39832770
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
ID: 39862700
Even though I did not get a proper working solution, the information and knowledge gained was excellent
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

729 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