Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is it possible to have multiple parameters within a Report Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 3:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 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
Posted Wednesday, March 27, 2013 1:02 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 612, Visits: 2,146
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 »

Add to briefcase

Permissions Expand / Collapse