Public Function GetFiscalYearMonthList() as String

	dim yr_cnt as integer, mth_cnt as integer
	dim returnstring as string

	yr_cnt = 0
	mth_cnt = 0
	returnstring = ""

	for yr_cnt = 0 to (Report.Parameters!FiscalYear.Count() - 1)

		'fetch each month value
		for mth_cnt = 0 to (Report.Parameters!FiscalMonth.Count() - 1)
			
			returnstring = returnstring & Report.Parameters!FiscalYear.Value(yr_cnt) & Report.Parameters!FiscalMonth.Value(mth_cnt)

			if mth_cnt < (Report.Parameters!FiscalMonth.Count() - 1) then
				returnstring = returnstring & ", "
			end if

		next mth_cnt

		if yr_cnt < (Report.Parameters!FiscalYear.Count() - 1) then
			
			'add the comma for the next value
			returnstring = returnstring & ", "		

		End if

	Next yr_cnt

	Return returnstring

End Function

quote:

---

Originally posted by nicklarge

Hello SQL Server Forums.

In my report I have 2 multi-valued parameters. One is Year, and the other is Month. The Year runs as 2010, 2011, 2012 ... 2015, and the month parameter runs as 01, 02, 03 ... 12. The report itself is for parallel periods so the user could select 2010, 2011 and 2012 in the year dropdown, then 05, 06 and 07 in the month drop down. In my query I have the SQL as an expression, which, at some point needs to state (in the case of the values selected in this description) :

AND [Process Date Fiscal Year Month] IN (201005, 201006, 201007, 201105, 201106, 201107, 201205, 201206, 201207)

The reason that I want to do it this way is because my table is partitioned on Process Date Fiscal Year, and so then I could gain the performance benefit of partition elimination through this method.

How do I take the 2 multivalued parameters and write an expression that concatenates the items so they appear as shown?

Any suggestions please?

Regards, Nick.

---