Is it possible to have multiple parameters within a Report

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services 2008 Development » Is it possible to have multiple parameters...

Is it possible to have multiple parameters within a Report

Rate Topic

Display Mode

Topic Options

Author

Message

wafw1971

Posted Friday, March 08, 2013 3:07 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:30 AM
Points: 62, Visits: 155

I have a parameter called Reporting Level in my Report where you can choose from 4 options:

Company BDM Region Site

What I want to be able to do is this:

Select BDM and this opens another Parameter call BDM List (This is based on Get Values from a query), but I want the other Parameters to stay greyed out. Once a selection is made from the BDM List then the Time and Date Parameter becomes available.

Is this possible?

These are my Datasets for the BDM List and Region List:

SELECT DISTINCT BDM
FROM Site
WHERE @ReportingLevel = 'BDM'

SELECT DISTINCT Region
FROM Site
WHERE @ReportingLevel = 'Region'This is my main Dataset:

SELECT OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonth, SUM(OccupancyDetail.No_of_Nights) AS No_of_Nights,
SUM(OccupancyDetail.Capacity) AS Capacity
FROM OccupancyDetail INNER JOIN
Site ON OccupancyDetail.Site_Skey = Site.Site_Skey

WHERE (OccupancyDetail.ReferenceDate = convert(Date,getdate()))
AND CASE WHEN @Time = 'YEAR' THEN CAST(CalendarYear as varchar(4)) else CalendarMonth + ' ' + CAST(CalendarYear as varchar(4)) end in (@Dates)
AND BDM IN (@BDM)
AND Region in (@Region)

GROUP BY OccupancyDetail.ReferenceDate, OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonthI hope you can help this is my 3rd day working on this.

Thanks

Wayne

Post #1428473

Steven.Howes

Posted Wednesday, March 27, 2013 1:02 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 12:49 PM
Points: 370, Visits: 1,657

If you make your parameters dependant on another parameter then you should be able to greythe others out.

if you make your @Year or @Dates dependent on @ReportingLevel they'll be greyed out until you pick a ReportingLevel.

If your @Reportingperiod isn't a query but a list of options you could make it a query by doing

Select 'Year' as ReportingPeriod
Union
Select 'Month
Where @ReportingPeriod = @ReportingPeriod

This will make the report wait until reportingperiod has been selected.

Post #1436120

« Prev Topic | Next Topic »

Permissions