SQL Command and Parameter in Main Report

[ricolame]

hi,

is there a way i could link my sub sql query (by adding command) to my formula variable in my main report ?

Below is my query frm my analyser which works well:
SELECT customer_id,part_id,default_unit_price,qty_on_hand,udf_layout_id
FROM CUSTOMER_PRICE CP,PART P
WHERE ( CP.PART_ID NOT IN
(SELECT DISTINCT(PART_ID)
FROM customer_order CO,CUST_ORDER_LINE COL
where CO.ID=COL.CUST_ORDER_ID AND CO.ORDER_DATE>01/01/2005)) AND P.ID=CP.PART_ID AND P.QTY_ON_HAND>0

ORDER BY UDF_LAYOUT_ID;


i would like to change the order_date>01/01/2005 now by a
parameter linked to thatin my main report.

Is that possible?

With regards,
CR10

 

[ricolame]

just to add a further clarification

i have a {@minDate} that determines the lower date range bound of a period. Period is also determined at runtime where user will choose if it is for past 6 months or past 1 year.

now i would just need to get this @minDate into my SQL query
to form another 'view' i need.

Is that all possible?

Any help here? Thanks a million !

 

[bdreed35]

Absolutely.
From the Database Expert, right click the Command and choose Edit.
On the the right side of the dialog, click the Create Parameter button.
Name the parameter, and give it a datatype of date.
Click OK.
Back in the Command Dialog, delete 01/01/2005, then double click the parameter name to add it in place of the deleted value.
Click OK to save the Command.

~Brian

 

[ricolame]

hi, i think i need some guidance on how to do that.

Okay say i create a parameter call myDate of data type Date in the Comand Dialog.

I replaced my SQL query now with ?

CO.ORDER_DATE>01/01/2005 now to CO.ORDER_DATE>{?myDate}

??

But this ?myDate depends on what period paramter i had chosen, and has to be linked to a variable call {@minDate}

Effectively i would like it to be >{@minDate} where @minDate is a formula in my Main report.

 

[bdreed35]

You won't be able to pull the data in based upon a variable in your report.
What does the mindate formula do? Can you post the formula?

~Brian

 

[ricolame]

oh no..

@minDate
minimum(PastXMonths({?Period}))

and PastXMonths is basically a function that determine the date range of the period i have chosen say 'Past 6 Months' ie.

So if i select a 'Past 6 months' , the minDate i obtain today is 01/07/2005. Thus I need this minDate to get into the sql subquery.

Possible no?

 

[ricolame]

or any other possibilities?

 

[BobSuruncle]

Could you do this in SQL?
What database are you using?

Here's a very simple example using Access as the database:

 SELECT `Customer`.`Customer Name`, 
`Orders`.`Order Amount`, 
`Orders`.`Order Date`
FROM   `Customer` `Customer` INNER JOIN `Orders` `Orders` 
	ON `Customer`.`Customer ID`=`Orders`.`Customer ID`

where `Orders`.`Order Date` > DateAdd('m',-{?month},Now) and  `Orders`.`Order Date` <=now

You'd create your query using the "Command" method, and while in the "Modify Command" dialog, you'd create a numeric parameter.(I called mine Month)
To add the parameter to the query, you'd position your cursor where you want the parameter to be, and double click the parameter name from the "Parameter List" area of the dialog.


Bob Suruncle

 

[ricolame]

hi,

cos my main report has a parameter Period which defines exactly what number to plug in your {?month} variable

that's was the problem

i tried another method by creating a subreport again, using the sql command i've written. This works well, yup, but am slowed down by the processing..

3 subreports..