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

Parameters appear only when selected from previous parameter Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 7:19 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
Below is my Dataset, what I would like to be able to do is only have a parameter showing if certain criteria is met can you help:

This is my process:

When the report is run the user is faced with one parameter, this is the Reporting Level Parameter this is not a multi value choice this a either/or option.

The choice is either/or ‘Company or BDM or Region or Site’

Once this is selected depending on choice the following parameters will unhide:

Company  Time  Date  Report Type

BDM  BDM Distinct List (This can be Multivalued)  Time (This can be Multivalued)  Date (This can be Multivalued)  Report Type (Either/Or)

Region  Region Distinct List (This can be Multivalued)  Time (This can be Multivalued)  Date (This can be Multivalued)  Report Type (Either/Or)

Site  Site Distinct List (This can be Multivalued)  Time (This can be Multivalued)  Date (This can be Multivalued)  Report Type (Either/Or)




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)
AND SiteName IN (@SiteName)


GROUP BY OccupancyDetail.ReferenceDate, OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonth

Thanks

Wayne
Post #1427967
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse