SQL String Parameter

[ShawnDT]

Good Afternoon,

I am having a little trouble trying to figure how I write this query to accept User Input. I want the user to be able to type in the in year (2008, 2009 etc). The query does the search and it is brings back the data. I am using MSSql 2005 reporting Service
<Code>
select 'Phase 3 Proposal' As Activity, PlanOrigin, [1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12] as [Total Sent],
[1] as 'Jan', [2] as 'Feb', [3] as 'Mar', [4] as 'Apr', [5] as 'May', [6] as 'Jun',
[7] as 'Jul', [8] as 'Aug', [9] as 'Sep', [10] as 'Oct', [11] as 'Nov', [12] as 'Dec'
from (select 'Phase 3 Proposal' AS Activity, PlanOrigin, Month(DateToPresale) as MonthNumber from vwSales
where Year(DateToPresale)=2009) as MyTempTable
pivot (count(MonthNumber) for MonthNumber in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as MyPivotTable
<Code>

I have 2009 in there as a default to return the data.

 

[genomon]

Where does the user "type in the year"? Do you have a frontend app, or are you expecting the user to use Query Analyzer?

Ever notice how fast Windows runs? Me neither.

 

[ShawnDT]

The front end app is Reporting Services. They should be able to in a text box type in the parameter and then hit view report.

 

[genomon]

D'oh! I dont use reporting services. If it were VB or VBA you would capture the contents of the text box in a variable,and use the variable in your SQL string. Sorry for my unfamiliarity with MSSql 2005 reporting Service!

Ever notice how fast Windows runs? Me neither.