SQL Expression

[maas]

Hello All,

I have created one query and it is giving me the correct result. The query is below:

{SELECT sum(amount)
FROM gl_entry g,chart_acc c ,bustruct b
WHERE g.chart_acc_id=c.chart_acc_id
AND g.action_dt between '01-Jan-2009' AND '31-AUG-2009'
AND c.COA_CODE='4000'
AND g.entity_id=b.bustruct_id
AND b.name='Liquidity'
}

Now, I am trying to replace the dates with date parameters which the user can eter them while running the report.
Is it possible and how?

Thank you

 

[kat50]

I believe that you can remove the line
AND g.action_dt between '01-Jan-2009' AND '31-AUG-2009'
from your SQL. Then create a parameter field for the start date and the end date. Then go to the seleciton expert choose records and new, choose the date field, you will then choose formula down at the bottom and type in

{g.action_dt} >= {?Start Date} and
{g.action_dt} <= {?End Date}

this will produce a bubble when you hit refresh and you can enter the dates.

 

[lbass]

You can't use parameters in SQL expressions, and since SQL expressions directly access the database, they will not be affected by the main report's selection criteria.

You could instead create the subquery in a command where you CAN you use parameters. However, you should consider using the command as the entire datasource for your report, and build in the subquery as one item in the select statement. If instead link to a command, the linking will occur locally and slow your report.

-LB

 

[maas]

Hello All,

Thanks for your help.

I did one solution is to create a subreport and it gives the correct result.

Now, my question is: I placed the subreport in the group header #1 in the main report.

How can I show the formula @Sum(amounts) which is in the subreport in the main report and exactly at the same group header #1 in the main report? And I want to use it with the other formulas in the main report?

Could you please help.

Thanks