Create View with parameters???

  • Thread starter Thread starter Everaldo Ricardo
  • Start date Start date
E

Everaldo Ricardo

views doesn´t accept parameters, I suggest you to do a stored procedure,
creating a temporary table

Everaldo Ricardo - Brazil
 
or do table-valued functions.. right?

these take parameters; but you can still treat them like more like
views

-aaron
 
Hello!
I have 2 tables: The first is ExportProducts (CheckID, Date, ProductID,
Quantity, Price, Total), and the second table Payments(ID, Date, CheckID,
PaySum) to store every all the payments for one check in ExportProducts (
the CheckID to pay, The date of payment, and the paysum)
For example:
ExportProducts
CheckID Date ProductID Quantity
Price Total
1 06/05/2005 PR0001 100
150.00 150000.00
2 06/21/2005 PR0004 200
120.00 240000.00

And the table Paymetns
id Date CheckID PaySum
1 06/07/2005 1 5000.00
2 06/13/2005 1 7000.00
3 06/16/2005 1 3000.00
4 06/24/2005 2 10000.00

And now I must get a report All the Checks before a concrete date and thier
payments and remain sum.
If I don't need notice to the Date, I can do it like that
Create A View:
PaymentView:
SELECT CheckID, SUM(PaySum) AS PaySum
FROM dbo.Payments
GROUP BY CheckID


And the Second View:

SELECT dbo.ExportProducts.CheckID, dbo.ExportProducts.[Date],
dbo.ExportProducts.ProductID, dbo.ExportProducts.Quantity,
dbo.ExportProducts.Total, dbo.PaymentView.PaySum
FROM dbo.ExportProducts LEFT OUTER JOIN
dbo.PaymentView ON dbo.ExportProducts.CheckID =
dbo.PaymentView.CheckID

- Now I create a Crystal Report with the Command like Second View.

It works OK, but my Task is getting report with a concrete date. so I must
assign somewhere a parameter of date....I don't know how to do it.
Pls Help me!
Thanks in advance!
 
well, actually I always make report using Access, but I think that Crystal
is able to open Stored Procedure

Everaldo Ricardo

Duong Nguyen said:
Hello!
Thank you very much!
OK, I have created a Store Procedure like you told, But please help me how
to make a Crystal Report with a datasource = StoreProcedure?
I am waiting for your help!
Thanks again!
Regards!





Everaldo Ricardo said:
views doesn´t accept parameters, I suggest you to do a stored procedure,
creating a temporary table

Everaldo Ricardo - Brazil



Duong Nguyen said:
Hello!
I have 2 tables: The first is ExportProducts (CheckID, Date, ProductID,
Quantity, Price, Total), and the second table Payments(ID, Date, CheckID,
PaySum) to store every all the payments for one check in
ExportProducts (
the CheckID to pay, The date of payment, and the paysum)
For example:
ExportProducts
CheckID Date ProductID Quantity
Price Total
1 06/05/2005 PR0001 100
150.00 150000.00
2 06/21/2005 PR0004 200
120.00 240000.00

And the table Paymetns
id Date CheckID PaySum
1 06/07/2005 1 5000.00
2 06/13/2005 1 7000.00
3 06/16/2005 1 3000.00
4 06/24/2005 2 10000.00

And now I must get a report All the Checks before a concrete date and
thier
payments and remain sum.
If I don't need notice to the Date, I can do it like that
Create A View:
PaymentView:
SELECT CheckID, SUM(PaySum) AS PaySum
FROM dbo.Payments
GROUP BY CheckID


And the Second View:

SELECT dbo.ExportProducts.CheckID, dbo.ExportProducts.[Date],
dbo.ExportProducts.ProductID, dbo.ExportProducts.Quantity,
dbo.ExportProducts.Total, dbo.PaymentView.PaySum
FROM dbo.ExportProducts LEFT OUTER JOIN
dbo.PaymentView ON dbo.ExportProducts.CheckID =
dbo.PaymentView.CheckID

- Now I create a Crystal Report with the Command like Second View.

It works OK, but my Task is getting report with a concrete date. so I must
assign somewhere a parameter of date....I don't know how to do it.
Pls Help me!
Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Hello!
Thank you very much!
OK, I have created a Store Procedure like you told, But please help me how
to make a Crystal Report with a datasource = StoreProcedure?
I am waiting for your help!
Thanks again!
Regards!





Everaldo Ricardo said:
views doesn´t accept parameters, I suggest you to do a stored procedure,
creating a temporary table

Everaldo Ricardo - Brazil



Duong Nguyen said:
Hello!
I have 2 tables: The first is ExportProducts (CheckID, Date, ProductID,
Quantity, Price, Total), and the second table Payments(ID, Date, CheckID,
PaySum) to store every all the payments for one check in ExportProducts (
the CheckID to pay, The date of payment, and the paysum)
For example:
ExportProducts
CheckID Date ProductID Quantity
Price Total
1 06/05/2005 PR0001 100
150.00 150000.00
2 06/21/2005 PR0004 200
120.00 240000.00

And the table Paymetns
id Date CheckID PaySum
1 06/07/2005 1 5000.00
2 06/13/2005 1 7000.00
3 06/16/2005 1 3000.00
4 06/24/2005 2 10000.00

And now I must get a report All the Checks before a concrete date and
thier
payments and remain sum.
If I don't need notice to the Date, I can do it like that
Create A View:
PaymentView:
SELECT CheckID, SUM(PaySum) AS PaySum
FROM dbo.Payments
GROUP BY CheckID


And the Second View:

SELECT dbo.ExportProducts.CheckID, dbo.ExportProducts.[Date],
dbo.ExportProducts.ProductID, dbo.ExportProducts.Quantity,
dbo.ExportProducts.Total, dbo.PaymentView.PaySum
FROM dbo.ExportProducts LEFT OUTER JOIN
dbo.PaymentView ON dbo.ExportProducts.CheckID =
dbo.PaymentView.CheckID

- Now I create a Crystal Report with the Command like Second View.

It works OK, but my Task is getting report with a concrete date. so I must
assign somewhere a parameter of date....I don't know how to do it.
Pls Help me!
Thanks in advance!
Click to expand...
Click to expand...
 
uh whats crystal reports?

you've got ADP reports no?

I've used Crystal a bunch i just hate it since it's not as powerful..
I mean.. VBA!!! it rocks
 
how can I make a table-valued funtion ?? what sintaxe using Access projetc ?

Everaldo Ricardo
 
how can I make a table-valued funtion ?? what sintaxe using Access projetc ?

Everaldo Ricardo
 
if you're usig Access 2000 upgrade.

I'm sorry.. i called the wrong function type.

it's called an 'inline function'

start a new 'query' and the first option in ADP is 'Design inline
function'
the caption for this option is 'Design a view that allows parameters
and order-by clauses'
 
Back
Top