| Author |
 Topic  |
|
|
wafw1971
Yak Posting Veteran
68 Posts |
 Posted - 03/07/2013 : 06:31:44
|
0down votefavorite
I want to be able to create a suite of parameters based on what the user selects (see below for example)
I currently have 5 Datasets - Main Dataset, Time Dataset, BDM List Dataset, Region List Dataset, Site List Dataset:
SELECT OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonth, SUM(OccupancyDetail.No_of_Nights) AS No_of_Nights,
SUM(OccupancyDetail.Capacity) AS Capacity, Site.Region, Site.BDM, Site.SiteName
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 = (@BDM)
AND Region = (@Region)
AND SiteName = (@SiteName)
GROUP BY OccupancyDetail.ReferenceDate, OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonth,Site.Region, Site.BDM, Site.SiteName
Example:
1st Parameter - ‘Reporting Level’ This is a drop down list to determine which part of the business needs to look at the report. Company (Total results) Region (Regional results broken down by Region) BDM (BDM results broken down by BDM) Site (Site results broken down by Site)
If ‘Company’ is selected then 3 parameter boxes appear, each with a dropdown option:
These parameters only appear once the 1st Parameter is selected.
2nd Parameter - ‘Time Grouping’
This will give you the choice of Year or Month
3rd Parameter - ‘Select Dates’
This is greyed out until the 2nd Parameter is completed.
If Year is selected in the ‘Time Grouping’ Parameter then the ‘Select Dates’ Parameter will give a multiple choice of which Years you want the report to produce.
•For Example you can select 2011 on its own or 2011, 2012 and 2013 to view comparisons
If Month is selected in the ‘Time Grouping’ Parameter then the ‘Select Dates’ Parameter will give a multiple choice of which Months you want the report to produce.
•For Example you can select Jan 2011 on its own or Jan 2011, Jan 2012 and Jan 2013 to view comparisons
4th Parameter ‘Report Type’
This will give a multiple choice of which type of report is required.
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/07/2013 : 06:50:11
|
you cant control the visibility property of parameters based on another parameter value in standard report viewer, atleast till ssrs2008. For that you might need to develop a custom page with parameter dropdowns.
You can set dependency among them to make them greyed out though until the dependent parameter gets selected.
You can also make them multivalue allowing multiple values to be selected.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 03/07/2013 : 07:00:30
|
Hi Visakh
I am working on Report Builder 3 does that make a difference?
Greyed out will be fine, how do I do the following:
When the report is run 1 parameter is available with a 5 selections
If the first selection is chosen the Time and Date Parameters are now available, these are multivalued
If the Second selection is made the BDM list will be available this is also a multivalued, once the choices are made the Time and Date Parameters are now available, these are multivalued.
Etc etc.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/07/2013 : 08:46:36
|
You can create a dataset to retrieve the values for first parameter and map it in parameters properties tab
For making Time and Date parameters dependent add first parameter as a parameter for dataset for these two parameters.
For making them multivalued you can check the checkbox for multivalue in parameter properties.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 03/07/2013 : 08:56:58
|
the First value does not exist in any table so how do I make a dataset from that.
Its called
Reporting Level: Drop Down option:
COMPANY - This does not exist either this is just a slection to get the grand total (Select All)
BDM - This is from a BDM Dataset
REGION - This is from a Region Dataset
SITE - This is from a Site Dataset
Does this make sense? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/07/2013 : 11:05:14
|
quote:
Originally posted by wafw1971
the First value does not exist in any table so how do I make a dataset from that.
Its called
Reporting Level: Drop Down option:
COMPANY - This does not exist either this is just a slection to get the grand total (Select All)
BDM - This is from a BDM Dataset
REGION - This is from a Region Dataset
SITE - This is from a Site Dataset
Does this make sense?
so will it always have single selectall option? if yes you can hardcode it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 03/07/2013 : 11:31:29
|
Hi Visakh
That is the first parameter, what you select there will open up the relevant parameters.
For example if you select BDM, the BDM Parameter is open and the Time and Date parameter are also open but the Region and Site are still unavailable. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/07/2013 : 14:59:51
|
ok..then create a dataset with hardcoded values for it
like
SELECT 'BDM' AS value,'BDM' AS Description
UNION ALL
...
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 03/08/2013 : 03:42:09
|
Good Morning Visakh
Do you meant to hardcode it into the main dataset or the BDM Dataset,
SELECT OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonth, SUM(OccupancyDetail.No_of_Nights) AS No_of_Nights,
SUM(OccupancyDetail.Capacity) AS Capacity, Site.Region, Site.BDM, Site.SiteName
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 = (@BDM)
UNION ALL
AND Region = (@Region)
AND SiteName = (@SiteName)
GROUP BY OccupancyDetail.ReferenceDate, OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonth,Site.Region, Site.BDM, Site.SiteName
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/08/2013 : 05:29:20
|
i mean hardcode it for dataset you use to populate the first dropdown
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 03/08/2013 : 07:21:14
|
Hi Visakh
What am I doing wrong, I just cant get it to work.
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 Site.BDM IN (@BDM)
AND Site.Region IN (@Region)
AND Site.SiteName IN (@SiteName)
GROUP BY OccupancyDetail.ReferenceDate, OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonth
Time Dataset
select DateChoice FROM
(select distinct CalendarYear, 1 as MonthNumber,CAST(CalendarYear as varchar(4)) as DateChoice from Time
where @Time = 'YEAR'
union all
select Distinct CalendarYear, MonthNumber,CalendarMonth + ' ' + CAST(CalendarYear as varchar(4)) as DateChoice from Time where @Time = 'MONTH') as QRYDATA
ORDER BY CalendarYear,MonthNumber
3rd Parameter Selection Dataset
SELECT SiteInfo FROM
((SELECT DISTINCT BDM, Region, SiteName As SiteInfo
FROM Site
WHERE @ReportingLevel = 'BDM'
union all
SELECT DISTINCT BDM, Region, SiteName As SiteInfo
FROM Site
WHERE @ReportingLevel = 'Region' )
union all
SELECT DISTINCT BDM, Region, SiteName As SiteInfo
FROM Site
WHERE @ReportingLevel = 'SiteName') AS QRY2
I just cant figure it out. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/08/2013 : 09:09:20
|
which one of this is used for populating first parameter?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 03/08/2013 : 09:18:17
|
| The 3rd one, the one that says @RepotingLevel |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 03/08/2013 : 09:52:10
|
Nope thats not correct. that itself contain @ReportLevel parameter. Then how do you use this to fill values for reportlevel parameter?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 03/08/2013 : 12:10:38
|
I found out I was doing it wrong and this is what I was meant to do (See Datasets below): But I have now got another problem when I run this report I am getting the grand totals whatever I select, I know that I need to change a Where clause but I don't know which one.
Do you?
My new Main dataset is:
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)
GROUP BY OccupancyDetail.ReferenceDate, OccupancyDetail.CalendarYear, OccupancyDetail.CalendarMonth
My Time Dataset is:
select DateChoice FROM
(select distinct CalendarYear, 1 as MonthNumber,CAST(CalendarYear as varchar(4)) as DateChoice from Time
where @Time = 'YEAR'
union all
select Distinct CalendarYear, MonthNumber,CalendarMonth + ' ' + CAST(CalendarYear as varchar(4)) as DateChoice from Time where @Time = 'MONTH') as QRYDATA
ORDER BY CalendarYear,MonthNumber
And Finally my last dataset:
SELECT DISTINCT BDM AS SiteInfo FROM Site
WHERE @ReportingLevel = 'BDM'
UNION ALL
SELECT DISTINCT Region FROM Site
WHERE @ReportingLevel = 'Region'
UNION ALL
SELECT DISTINCT SiteName FROM Site
WHERE @ReportingLevel = 'SiteName' |
|
|
| |
Topic |
|
Reporting Services Parameters
