passing parameter from View to Report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

passing parameter from View to Report

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-05-30 : 09:52:28

I have a view that has some code as below that gives me data on a rolling 30 day basis (only 3 days shown).

SELECT
Yard,
SUM(TicketTotalDay3)TicketTotalDay3,
SUM(TicketTotalDay2)TicketTotalDay2,
SUM(TicketTotalDay1)TicketTotalDay1
FROM(
SELECT
Yard,
CASE WHEN Date=DATEADD(DAY, - 03 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS TicketTotalDay3,
CASE WHEN Date=DATEADD(DAY, - 02 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS TicketTotalDay2,
CASE WHEN Date=DATEADD(DAY, - 01 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS TicketTotalDay1
FROM VW_Shale_STT_PreLoadSummaryBlock
) AS X
GROUP BY Yard

I then join this View with another one in a Stored Proc. This Stored Proc is passed to my report. I would like to know how to pass a Date Parameter to my report. So instead of 'Date' in my CASE statements above it would be @Date. I haven't done this so I am lost. I tried a Declare but it said I had to SET a value. I don't know if I just put the @Date in a WHERE criteria in my View or in my Stored Proc? If anyone could help in this, I'd appreciate it. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 13:51:57

what you need is to add a date parameter to your procedure and then use it in query above. on creating a dataset in report with this stored procedure as source and refreshing in data tab of report, it will automatically add the new parameter to report for you.

osupratt
Posting Yak Master

238 Posts

Posted - 2009-05-30 : 14:44:12

so how do i do this in my Procedure? if i add @Date in my View it says i need to specify the value?

could you please be a bit more specific? thanks for your help.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-30 : 22:39:04

--exec Myproc @Date = '05/30/2009'

create proc Myproc
@Date datetime
as
SELECT
Yard,
SUM(TicketTotalDay3)TicketTotalDay3,
SUM(TicketTotalDay2)TicketTotalDay2,
SUM(TicketTotalDay1)TicketTotalDay1
FROM(
SELECT
Yard,
CASE WHEN @Date=DATEADD(DAY, - 03 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS TicketTotalDay3,
CASE WHEN @Date=DATEADD(DAY, - 02 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS TicketTotalDay2,
CASE WHEN @Date=DATEADD(DAY, - 01 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS TicketTotalDay1
FROM VW_Shale_STT_PreLoadSummaryBlock
) AS X
GROUP BY Yard

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 06:45:02

quote:
Originally posted by osupratt

so how do i do this in my Procedure? if i add @Date in my View it says i need to specify the value?

could you please be a bit more specific? thanks for your help.

you should be adding this to procedure and not to view. see last suggestion on how to do this

nalnait
Starting Member

14 Posts

Posted - 2009-05-31 : 09:08:24

you can use function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 14:13:53

quote:
Originally posted by nalnait

you can use function

depends on what functionality you do inside.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources