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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics