Passing Parameter to Query

[Former Member]

I have to create the report for sales based upon 30 , 60,90,120,150,180 days. It should be come from From date + 30 days sales , From date+ 60 etc..

Input is From Date and To date.

The following query is using for above sales.In this query I have directly mentioned the date .

My Doubt is.

I have creaed the From date and To date Parameter using Parameter fields.

But How do i pass the Parameter value (From Date ) to bellow query using SAP.

select T0.ItemCode,

isNUll((select sum(Quantity) from INV1 where DocDate between '2011-09-01' and DATEADD(dd,30, '2011-09-01') and INV1.ItemCode =T0.ItemCode ),0) as first30,

isNull((select sum(Quantity) from INV1 where DocDate between '2011-09-01' and DATEADD(dd,60, '2011-09-01')and INV1.ItemCode =T0.ItemCode ),0) as first60,

isNull((select sum(Quantity) from INV1 where DocDate between '2011-09-01' and DATEADD(dd,90, '2011-09-01')and INV1.ItemCode =T0.ItemCode ),0) as first90,

isnull((select sum(Quantity) from INV1 where DocDate between '2011-09-01' and DATEADD(dd,120, '2011-09-01')and INV1.ItemCode =T0.ItemCode ),0) as first120,

isnull((select sum(Quantity) from INV1 where DocDate between '2011-09-01' and DATEADD(dd,150, '2011-09-01')and INV1.ItemCode =T0.ItemCode ),0) as first150,

isnull((select sum(Quantity) from INV1 where DocDate between '2011-09-01' and DATEADD(dd,180, '2011-09-01')and INV1.ItemCode =T0.ItemCode ),0) as first180

from INV1 T0 inner join OINV T1 on T0.DocEntry =T1.DocEntry group by ItemCode

How do I pass From date value as paramter to query.

Please help me.

[abhilash_kumar]

Hi,

You would need to add this query as a command object in the report and also add a command level parameter for the input.

After the connection to the database has been made from the Database Expert, just below the connection name and just above the Tables, Stored Procedures, Views, etc trees, there should also be an option called 'Add Command'.

Select this option to bring up the Command Editor and paste the query here. There should also be a 'Create' button on the top right; this lets you create command level parameters.

Click the button, create the From date parameter and you should see it in the parameter list in the Command Editor. Replace all the dates with the parameter name:

E.g:

select sum(Quantity) from INV1 where DocDate between {?From_parameter} and DATEADD(dd,30, {?From_parameter}) and INV1.ItemCode =T0.ItemCode

If the command throws a syntax error then try placing quotes around the date parameter or try explicitly converting it to a date using the to_date function : to_date({?From_parameter}).

Hope this helps!

-Abhilash