0

I have some SQL code that declares a parameter (multiple values as a string) which other variables work from. My code works beautifully in SQL MS but I need the parameter with specific values multi pick list, which I need then to pass through my SQL statement as a string rather than a list. This is the bit I'm struggling with

I've tried creating an additional parameter to pass the pick list output through using a join expression =Join(Parameters!ReportPeriod.Value, ",") but this only seems to work if you use an expression in the report not a parameter

The below sets all the variables for the reports I need, it's a bit crude but works well. This needs to work as 1 pick list for 3 datasets that produce 3 sets of results, one table for each reporting period

I need the first line to remain as a string when in the SSRS pick list

Declare @ReportingPeriod Varchar(100) SET @ReportingPeriod = ('2016,2017,2018')

Declare @Start Varchar(4) = Left(@ReportingPeriod,4)
Declare @Years int =        CASE WHEN len(@ReportingPeriod)=4 THEN 1
                                WHEN len(@ReportingPeriod)=9 THEN 2 ELSE 3 END
Declare @End Varchar(4) =   CASE WHEN @Years=1 THEN @Start+1 
                                WHEN @Years=2 THEN @Start+2 ELSE @Start+3 END -- 
Declare @StartDate date = Concat(@Start,'0501')
Declare @EndDate date =     CASE WHEN @Years=1 THEN Concat(@End,'0430')
                                WHEN @Years=2 THEN Concat(@End-1,'0430') 
                                WHEN @Years=3 THEN Concat(@End-2,'0430') END

Declare @StartDate2 date =  CASE WHEN @Years=1 THEN Null
                                WHEN @Years in (2,3) THEN Concat(@Start+1,'0501') END
Declare @EndDate2 date =    CASE WHEN @Years=1 THEN Null 
                                WHEN @Years=2 THEN Concat(@End,'0430')
                                WHEN @Years=3 THEN Concat(@End-1,'0430')END
Declare @StartDate3 date =  CASE WHEN @Years=1 THEN Null 
                                WHEN @Years=2 THEN Null ELSE Concat(@Start+2,'0501')END
Declare @EndDate3 date =    CASE WHEN @Years=1 THEN Null 
                                WHEN @Years=2 THEN Null ELSE Concat(@End,'0430')END
Alix
  • 51
  • 1
  • 3
  • Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) – jtate Apr 03 '19 at 14:47
  • You are halfway through a solution here and asking us how to complete the other half. I would recommed adding your original dataset and reporting requirements to your question as it seems like a much more elegant solution would be appropriate. – iamdave Apr 03 '19 at 15:18

0 Answers0