Reporting Services Parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

Reporting Services Parameters

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-03-07 : 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

52326 Posts

Posted - 2013-03-07 : 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

75 Posts

Posted - 2013-03-07 : 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

52326 Posts

Posted - 2013-03-07 : 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

75 Posts

Posted - 2013-03-07 : 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

52326 Posts

Posted - 2013-03-07 : 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

75 Posts

Posted - 2013-03-07 : 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

52326 Posts

Posted - 2013-03-07 : 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

75 Posts

Posted - 2013-03-08 : 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

52326 Posts

Posted - 2013-03-08 : 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

75 Posts

Posted - 2013-03-08 : 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

52326 Posts

Posted - 2013-03-08 : 09:09:20

which one of this is used for populating first parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-03-08 : 09:18:17

The 3rd one, the one that says @RepotingLevel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 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

75 Posts

Posted - 2013-03-08 : 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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources