report parameters to View or SP variable

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

Development Tools

Reporting Services Development

report parameters to View or SP variable

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-05-11 : 10:19:56

For the first time I have to have a SSRS report parameter (Start Date and End Date) that somehow ties to the SP. I have not done this before. The report I currently need this to work for have code such as (View):

SELECT

Yard,TruckNumber,
SUM(RevDay30)RevDay30,SUM(RevDay29)RevDay29,SUM(RevDay28)RevDay28,SUM(RevDay27)RevDay27,SUM(RevDay26)RevDay26,SUM(RevDay25)RevDay25,
SUM(RevDay24)RevDay24,SUM(RevDay23)RevDay23,SUM(RevDay22)RevDay22,SUM(RevDay21)RevDay21,SUM(RevDay20)RevDay20,SUM(RevDay19)RevDay19,
SUM(RevDay18)RevDay18,SUM(RevDay17)RevDay17,SUM(RevDay16)RevDay16,SUM(RevDay15)RevDay15,SUM(RevDay14)RevDay14,SUM(RevDay13)RevDay13,
SUM(RevDay12)RevDay12,SUM(RevDay11)RevDay11,SUM(RevDay10)RevDay10,SUM(RevDay9)RevDay9,SUM(RevDay8)RevDay8,SUM(RevDay7)RevDay7,
SUM(RevDay6)RevDay6,SUM(RevDay5)RevDay5,SUM(RevDay4)RevDay4,SUM(RevDay3)RevDay3,SUM(RevDay2)RevDay2,SUM(RevDay1)RevDay1

FROM(
SELECT
FTNumber,Yard,TruckNumber,

CASE WHEN FTDATE=DATEADD(DAY, - 30 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay30,
CASE WHEN FTDATE=DATEADD(DAY, - 29 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay29,
CASE WHEN FTDATE=DATEADD(DAY, - 28 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay28,
CASE WHEN FTDATE=DATEADD(DAY, - 27 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay27,
CASE WHEN FTDATE=DATEADD(DAY, - 26 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay26,
CASE WHEN FTDATE=DATEADD(DAY, - 25 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay25,
CASE WHEN FTDATE=DATEADD(DAY, - 24 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay24,
CASE WHEN FTDATE=DATEADD(DAY, - 23 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay23,
CASE WHEN FTDATE=DATEADD(DAY, - 22 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay22,
CASE WHEN FTDATE=DATEADD(DAY, - 21 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay21,
CASE WHEN FTDATE=DATEADD(DAY, - 20 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay20,
CASE WHEN FTDATE=DATEADD(DAY, - 19 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay19,
CASE WHEN FTDATE=DATEADD(DAY, - 18 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay18,
CASE WHEN FTDATE=DATEADD(DAY, - 17 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay17,
CASE WHEN FTDATE=DATEADD(DAY, - 16 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay16,
CASE WHEN FTDATE=DATEADD(DAY, - 15 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay15,
CASE WHEN FTDATE=DATEADD(DAY, - 14 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay14,
CASE WHEN FTDATE=DATEADD(DAY, - 13 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay13,
CASE WHEN FTDATE=DATEADD(DAY, - 12 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay12,
CASE WHEN FTDATE=DATEADD(DAY, - 11 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay11,
CASE WHEN FTDATE=DATEADD(DAY, - 10 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay10,
CASE WHEN FTDATE=DATEADD(DAY, - 09 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay9,
CASE WHEN FTDATE=DATEADD(DAY, - 08 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay8,
CASE WHEN FTDATE=DATEADD(DAY, - 07 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay7,
CASE WHEN FTDATE=DATEADD(DAY, - 06 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay6,
CASE WHEN FTDATE=DATEADD(DAY, - 05 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay5,
CASE WHEN FTDATE=DATEADD(DAY, - 04 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay4,
CASE WHEN FTDATE=DATEADD(DAY, - 03 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay3,
CASE WHEN FTDATE=DATEADD(DAY, - 02 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay2,
CASE WHEN FTDATE=DATEADD(DAY, - 01 + DATEDIFF(DAY, '19000101', GETDATE()), '19000101') THEN CONVERT(MONEY,TicketTotal) ELSE 0 END AS RevDay1

FROM(
SELECT FTDate, FTNumber, Yard, TruckNumber, MAX(TicketTotal)TicketTotal
FROM VW_Shale_STT_LoadPerformance1

GROUP BY FTDate, FTNumber, Yard, TruckNumber

) AS X

) AS Y
GROUP BY Yard,TruckNumber

I'm trying to figure out how I can accomplish getting a report parameter and then having a WHERE in my code to pull what I need. Any help in this would be greatly appreciated. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:25:08

just add your WHERE condition to above query and run this in data tab of your report. it automatically asks for parameter value and on entering it gives you resultset. then refresh the dataset and it automatically adds the field info to dataset and parameters to report. If you need to change properties of this parameters you can go to design tab click square on left top corner and select view parameters.

osupratt
Posting Yak Master

238 Posts

Posted - 2009-05-11 : 13:58:44

this is a view though. i have about 4 other views to give me rolling 30 days worth of revenue, utilization, etc. i then have my stored procedures that i have for my report. i would this work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 14:12:03

yup, it will work even if you're using views in query directly rather than in procedure.

osupratt
Posting Yak Master

238 Posts

Posted - 2009-05-11 : 16:36:42

so in the view if i change some of the code to something like:

case when Date between StartDate and EndDate AND Date = DATEADD(DAY,-0,EndDate) then Value else 0 END AS EndDateValue

???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-12 : 10:08:47

sorry didnt get that. can you elaborate?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources