Solved

How to use Parameter Field value in SQL Expression Field

Posted on 2006-11-25
12
2,709 Views
Last Modified: 2007-11-27
Hi all,
I want to use the Parameter field value in my SQL expression. My Query is like below
SELECT count(support_incidents.case_id)
From support_incidents where Entry_Date >= {?From Date} and Entry_Date <= {?To Date} and city = "Customers"."CITY" and status = "Closed";
I want to call the above SQL expression Cross-Tab, my cross tab like below

Row Field
Type of Compalint
Column Field
City Name
Closed Complaint

So in first column i got the data count of total compalint for that city. I want to also print from that total how much closed.

Please help me to get the data.
0
Comment
Question by:MKPanchal
[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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 18012428
What is wrong with the expression you posted?

mlmcc
0
 

Author Comment

by:MKPanchal
ID: 18013362
I cant use the Users input parameter value in the SQL Expression Editor. When i click on check then give me error message "No value given for one or more required parameters."
0
 

Author Comment

by:MKPanchal
ID: 18013371
I have defined the Parameter Field "From Date" and "To Date". I want to use the same in SQL Expression Editor.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 100

Expert Comment

by:mlmcc
ID: 18013460
Can you save the expression?

Can you give them default values?

mlmcc
0
 

Author Comment

by:MKPanchal
ID: 18013562
I can give the default value like below and it work. But i need the user input value From Date and To date.

SELECT count(support_incidents.case_id)
From support_incidents where Entry_Date >= '01-Jan-2005' and Entry_Date <= '31-Dec-2006' and city = "Customers"."CITY" and status = "Closed";

I cant save the expression because give me error "No value given for one or more required parameters"
0
 
LVL 22

Expert Comment

by:Ido Millet
ID: 18014073
If this can't be done with standard SQL expressions, you could use a subreport to achieve the same thing.

Alternatively, since you need the result to be input for a CrossTab, consider a 3rd-party tool (see list at: http://www.kenhamady.com/bookmarks.html).  My "CUT Light" User Function Library (UFL) provides 3 different functions that allow you to use a Crystal formula to build a SQL statement (action query, single-value select query, or concatenated multi-row select): http://www.milletsoftware.com/CUT_Light.htm

Cheers,
- Ido
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18014317
When I said default value I meant when you define the parameter.

Display the field insertion popup
Click the PARAMETER tab
Right click one of the parameters
Click EDIT
Can you provide a default value?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 18014325
What are you trying to do with the SQL expression?

mlmcc
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 18014456
For the sake of clarification,  you CAN'T reference parameters or formulas within a SQL Expression.  SQL Expression fields can ONLY reference the true database fields that appear in the Field Tree within the SQL Expression Editor.

You've obviously discovered this with your experimentation.  The reason that you can't reference these fields is because the SQL Expression that you create is passed to the database for processing as correlated or uncorrelated subquery field in the SELECT clause of the SQL that Crystal creates.  This is done during pass 1 to the database.  Basically, the SQL Expression is treated as if it were a true database field.

That being said, there might be a way around this, but you'd have to limit the data in your main report by the same date range criteria.  If you're trying to create a truly uncorrelated subquery (pulling an aggregate value from an unrelated table, for example), then you won't be able to accomplish your goal with a SQL Expression.

This may be an example where it is better for you to use either a SQL Command Object or a Stored Procedure with Input Parameters.  Of course, you would use this as the data source for your entire report, not just this field.

~Kurt
0
 

Author Comment

by:MKPanchal
ID: 18017006
I have not defined the default value in the parameter, user will input the Date.
With SQL Expression i want to cout or sum the value with range as per date, city etc. I am using this in Cross-tab, here my column in the cross tab is city so if user defined the city range then column will be more in cross tab i am calculating total of the customer complaint received but at the same time i want to get the how much complain closed from the received. So i need to use the cross tab here.
0
 

Author Comment

by:MKPanchal
ID: 18017027
Hi kurt
In my report i am calculating two counts at same time which i explain above. So i need to use the SQL expression or function or stored procedure. But in that i need to pass the parameter value like date which i explained.

Otherwise i need to create the two cross-tab with sub-report and its look not good.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 18019331
MKPanchal, if you're not going to filter the entire report by a date range then you need to create a stored procedure or SQL Command Object.

~Kurt
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

763 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