SQL QUERY WITH PARAMETER DEPENDING ON EACH OTHER
HI
I am using sql server 2008 to execute query.I am working on sql query,which i have to use in
SSRS 2008 (Reporting)
I have a table and i want the output based on Hierarchy,i have 3 column 'Year''Quarter' and 'Month'
I want to create a SSRS reports from this query,so i need to parameter that i sould have a option to select
Year from Parameter,Ex 2001 or 2002 with a check box,so if could select Year from it.when i select a Year ex 2001 Then i should be able to see all the
Quarter related to it 2001 year,and if a select any Quarter from drop down example Q3 than i should be able to select
Related Months of it like JULY,AUGUST,SEPTEMBER
So how should i create a query with this type of Parameter..i am giving the example of data i have ..
year
Quarter
month
2001
Q1
jan
2001
Q1
feb
2001
Q1
mar
2001
Q2
apr
2001
Q2
may
2001
Q2
jun
2001
Q3
jul
2001
Q3
aug
2001
Q3
sep
2001
Q4
oct
2001
Q4
nov
2001
Q4
dec
2002
Q1
jan
2002
Q1
feb
2002
Q1
mar
2002
Q2
apr
2002
Q2
may
2002
Q2
jun
2002
Q3
jul
2002
Q3
aug
2002
Q3
sep
2002
Q4
oct
what sort of query should i write with the parameter so that i get Year ,quarter ,month as the output COLUMN in MY SSRS reports based on the the value selected in Parameter....
ON YEAR PAPAMETER I SHOULD BE ABLE TO SEE 2001,2002,
IF I SELECT ANY YEAR I SHOULD BE ABLE TO SEE ITS RELATED QUARTER LIKE Q1,Q2,Q3,Q4
AND IF I SELECT ANY QUARTER I SHOULD BE ABLE TO SEE ITS MONTH AND ACCORDING TO THIS I SHOULD BE ABLE TO SEE MY qUERY OUTPUT OR OUTPUT IN SSRS...
PLEASE HELP ME
December 15th, 2010 11:48pm
Hello,
You should have three parameters namely @Year, @Quarter and @Month. All the three parmeters will be loaded from a dataset.
Parameter @Year:
=============
SELECT YearColumn AS Label, YearColumn AS Value
FROM TableName
GROUP BY YearColumn
Parameter @Quarter:
===============
SELECT QuarterColumn AS Label, QuarterColumn AS Value
FROM TableName
WHERE YearColumn = @Year
GROUP BY QuarterColumn
Parameter @Month:
==============
SELECT MonthColumn AS Label, MonthColumn AS Value
FROM TableName
WHERE YearColumn = @Year
AND QuarterColumn = @Quarter
GROUP BY MonthColumn
Main Query:
=========
SELECT *
FROM TableName
WHERE YearColumn = @Year
AND QuarterColumn = @Quarter
AND MonthColumn = @Month
In the report body place a table and assign the main Query dataset to that table and design the report as you wanted.
Hope its clear & helpful....Pavan Kokkula Tata Consultancy Services.
Free Windows Admin Tool Kit Click here and download it now
December 16th, 2010 1:32am
HI Pavan,Thax for your reply
Hi got the way you helped me,but my Third Parameter is MONTH where i have to select multiple Month to see the reports ,example Year parameter i select 2001,Quarter Parameter i select Q1,and in MOnth parameter i need to select 1,2,3(jan.Feb,Marc).but if i select
multiple value then my report does not genetrate giving me an error cannot convert nvarchar to numeric
My stored Proc
create procedure time(@yearid numberic,@quarterid numeric,@monthid nvarchar(2500))
select * from TimeTable
where Yearid=@yearid and quarterid= @quarterid and monthid in (@monthid)
My Year Dataset in SSRS
select year id,Year from TimeTable
My Quarter Dataset in SSRS
select Quarterid,Quarter from TimeTAble where Yearid=@yearid
My MonthDataset in SSRS
select Monthid,MOnth from timetable where Quarterid=@quarterid
mY REPORTS RUN FIINE IF I SELECT SINGLE VALUE FROM ALL THE PARAMETER,BUT IF A SELECT MULTIPLE VALUES FROM MONTH PARAMETER,THEN IT FAILS ..I HAVE ALSO SELECT MULTIVAULE OPTION IN PARAMETER pARAMETER PROPERTIES OF MONTH PARAMETER,
WHAT SHOULD I DO
I TRIED 1 MORE OPTION ,IN MY MAIN dATASET PROPERTIES IN PARAMETER TAB IN PLACE OF
=Parameters!MONTH.Value
I HAVE USED =join(Parameters!MONTH.Value," ',' ")
........BUT IT DIDNT WORK TO SELECT MULTIPLE VALUE 1,2,3 IN MONTH PARAMETER,and i am using yearid,quarterid and month id in Value field and year,quarter and month respectively in Label field in AVAILABLE VALUE TAB OF REPORTS PARAMETER PROPERTIES
..BUT NOTHING IS WORKING
December 16th, 2010 4:08am
Hi Mushtaq308,
Since the values stored in a mutli-value parameter is in an array, it is not able to pass in a stored procedure directly.
The solutions to solve the issue are that:
Jion the values into string in SQL Server Reporting Services(SSRS), and then in the stored procedure use dynamic SQL statement to execute it
Jion the values into string in SQL Server Reporting Services(SSRS), and then in the stored procedure use user function to splict the string into values again.
For solution 1, please change the stored procedure to be like the following one:
create procedure time(@yearid numberic,@quarterid numeric,@monthid nvarchar(2500))
DECLARE @sql varchar(200)
SET @sql = 'select * from TimeTable where Yearid= ' + @yearid + ' and quarterid= ' + @quarterid + 'and monthid in (' + @monthid + ')'
EXECUTE @sql
For solution 2, please see the following blog for more information:
http://businessintelligencechronicles.blogspot.com/2009/01/another-take-on-splitting-reporting.html
If you have any more questions, please feel free to ask.
Thanks,
Jin ChenJin Chen - MSFT
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 1:19am