MKPanchal
asked on
How to use Parameter Field value in SQL Expression Field
Hi all,
I want to use the Parameter field value in my SQL expression. My Query is like below
SELECT count(support_incidents.ca
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.
I want to use the Parameter field value in my SQL expression. My Query is like below
SELECT count(support_incidents.ca
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.
ASKER
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."
ASKER
I have defined the Parameter Field "From Date" and "To Date". I want to use the same in SQL Expression Editor.
Can you save the expression?
Can you give them default values?
mlmcc
Can you give them default values?
mlmcc
ASKER
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.ca
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"
SELECT count(support_incidents.ca
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"
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
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
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
Display the field insertion popup
Click the PARAMETER tab
Right click one of the parameters
Click EDIT
Can you provide a default value?
mlmcc
What are you trying to do with the SQL expression?
mlmcc
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
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.
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
~Kurt
mlmcc