How to do this in Query Generator?

[Former Member]

Dear Experts,

Please check my following query I've written in Query Generator.

/* select from dbo.OCRD t0 */
declare @BP nvarchar(20)
set @BP=/* t0.CardName */ '[%0]'

declare @Dt1 datetime
declare @Dt2 datetime

set @Dt1=/* Start Date */ [%1]
set Dt2=[%2]


select distinct t1.po, Max(t1.supplier) Supplier,Max(t1.process) Process, Max(t1.OrderNo) OrderNo,Max(t1.Date) Date,
isnull((select sum(t2.Quantity) from dbo.ir t2 where t2.po=t1.po and t2.TType<0),0) Issued,
isnull((select sum(t3.Quantity) from dbo.ir t3 where t3.po=t1.po and t3.TType>0),0) Received,
isnull((select sum(t2.Quantity) from dbo.ir t2 where t2.po=t1.po and t2.TType<0),0)-isnull((select sum(t3.Quantity) from dbo.ir t3 where t3.po=t1.po and t3.TType>0),0) Balance

from dbo.ir t1 where t1.Supplier=@BP and (t1.Date>=@Dt1 and t1.Date<=@Dt2) group by t1.po

The Query works well. But for the the lines

set @Dt1=/* Start Date */ [%1]
set Dt2=[%2]

it displays BP Code as the prompt next the text boxes meant for inputting dates. I would like to display 'Start Date' and 'End Date' respectively for Dt1 and Dt2.

Please explain me how to go about it.

Thanks in advance.

Regards

Anand

[Former Member]

It is probably due to your view. Try system table to see what you could bring up.

[Former Member]

Hi Gordon,

It works with system tables.

In my case I got to collect data from the view.

How to go about it?

Thanks

Anand

[Former Member]

That is probably the limitation from B1 query manager. I don't know any option off hand. Is the view mandatory?

[Former Member]

Dear Anand,

You may try this first:

declare @Dt1 datetime

set @Dt1=/* Start Date */ [%1\]

declare @Dt2 datetime

set @Dt2=/* End Date */ [%2\]

/* select from dbo.OCRD t0 */

declare @BP nvarchar(20)

set @BP=/* t0.CardName */ '[%0\]'

[Former Member]

 
declare @Dt1 datetime
set @Dt1=/* Start Date */ [%0]

declare @Dt2 datetime
set @Dt2=/* End Date */ [%1]


/* select from dbo.OCRD t0 */
declare @BP nvarchar(20)
set @BP=/* t0.CardName */ '[%2]'

I've changed. When I run the query the input box does not appear, the query shows only a blank table.

Thanks

Anand

[Former Member]

Try this way:

/* select from dbo.IR t1 */

declare @Dt1 datetime

set @Dt1=/* t1.Date */ [%0\]

[Former Member]

Dear Gordon,

Even this is not working.

Thanks

Anand