SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Reporting Services Parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 03/07/2013 :  06:31:44  Show Profile  Reply with Quote

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
52309 Posts

Posted - 03/07/2013 :  06:50:11  Show Profile  Reply with Quote
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/

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 03/07/2013 :  07:00:30  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/07/2013 :  08:46:36  Show Profile  Reply with Quote
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/

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 03/07/2013 :  08:56:58  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/07/2013 :  11:05:14  Show Profile  Reply with Quote
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/

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 03/07/2013 :  11:31:29  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/07/2013 :  14:59:51  Show Profile  Reply with Quote
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/

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 03/08/2013 :  03:42:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/08/2013 :  05:29:20  Show Profile  Reply with Quote
i mean hardcode it for dataset you use to populate the first dropdown

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

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 03/08/2013 :  07:21:14  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/08/2013 :  09:09:20  Show Profile  Reply with Quote
which one of this is used for populating first parameter?

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

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 03/08/2013 :  09:18:17  Show Profile  Reply with Quote
The 3rd one, the one that says @RepotingLevel
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/08/2013 :  09:52:10  Show Profile  Reply with Quote
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/

Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 03/08/2013 :  12:10:38  Show Profile  Reply with Quote
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'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000