SQLTeam.com | Weblogs | Forums

Dynamically specifying fields to select based on parameter


#1

Good Evening. I have a flat table that contains forecast data. and year, month quarter information as follows.

Forecast version,
Project,
Year
Month
Quarter
Amount.

What I am attempting to do is to somehow add a parameter to it to include or exclude month from the select portion of the statement to limit number of rows returned to the user.

So if the Month is selection set to true the select would look like

select Forecast version,
Year
Month
Quarter,
Project
sum(Amount)
from table
group by year,month,quarter

and if the month parameter is false it regenerates itself to
select
Project
Forecast version, '
Year,
Quarter,
sum(amount)
from table
Group by......

There are 700 projects and 20 years in the table so excluding month from select if user doesn't need it would reduce data set from
168K rows down to 56K rows.

Ideally I would want to connect excel to the query or a proc based on the query and dump data directly to excel vs going SSRS route although I suspect I would have same issue in SSRS as well. Thank you for your help.