How to build string from column

• texasphill

  SSC-Addicted

  Points: 476

  More actions

  November 12, 2015 at 8:29 am

  #305008

  I am trying to default a multi-select parameter in and SSRS report to be what the user selected the last time they ran a report. I query the ExecutionLogStorage table to get the row of the last run report for that user. The Parameters field contains this:

  &StartRow=1&EndRow=1000&LoginID=40&ProjectAssessment=16&ShowColumns=Asset&ShowColumns=Category&ShowColumns=IOL&ShowColumns=Vulnerability%20Exposed&AssetDescription=&

  ShowColumns is the multi-select parameter. My issue is how to build a string from that to return "Asset, Category, IOL, Vulnerability%20Exposed"

  This is what I've tried so far for ParameterValue:

  SELECT TOP 1 ParameterValue

  FROM (

  SELECT

  TimeEnd = els.TimeEnd

  --, ParameterValue = IIF(CHARINDEX('&' + @ParameterName + '=', ParsString) = 0, null,

  --SUBSTRING(ParsString

  -- , StartIndex

  -- , CHARINDEX('&', ParsString, StartIndex) - StartIndex))

  --, ParameterValue = IIF(CHARINDEX('&' + @ParameterName + '=', ParsString) = 0, null,

  --REPLACE(SUBSTRING(ParsString

  -- , StartIndex

  -- , LastIndex),('&' + @ParameterName + '='),', '))

  , ParameterValue = IIF(CHARINDEX('&' + @ParameterName + '=', ParsString) = 0, null,

  SUBSTRING(ParsString

  , StartIndex

  , LastIndex))

  FROM (SELECT ReportID

  , TimeEnd

  , ParsString = '&' + CONVERT(VARCHAR(MAX), Parameters) + '&'

  , StartIndex = CHARINDEX('&' + @ParameterName + '=', '&' + CONVERT(VARCHAR(MAX), Parameters) + '&') + LEN('&' + @ParameterName + '=')

  , LastIndex = LEN(CAST(Parameters as varchar(8000))) - CHARINDEX(@ParameterName, REVERSE(CAST(Parameters as varchar(8000)))) + 1

  FROM [ReportServer$SQLEXPRESS].[dbo].[ExecutionLogStorage]

  WHERE CHARINDEX('&LoginID' + '=' + @LoginID, CONVERT(VARCHAR(MAX), Parameters)) > 0

  ) AS els

  INNER JOIN [ReportServer$SQLEXPRESS].[dbo].[Catalog] AS c ON c.ItemID = els.ReportID

  WHERE c.Name = @ReportName

  UNION ALL

  SELECT TimeEnd = CAST('1900-01-01' AS DateTime)

  , ParameterValue = null

  ) i

  ORDER BY TimeEnd DESC

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  November 12, 2015 at 8:39 am

  #1839404

  You could try this. Might not work in all circumstances though:

  declare @x varchar(500) = '&StartRow=1&EndRow=1000&LoginID=40&ProjectAssessment=16&ShowColumns=Asset&ShowColumns=Category&ShowColumns=IOL&ShowColumns=Vulnerability%20Exposed&AssetDescription=&';

  select @x

  ,replace(replace(right(@x, len(@x) - charindex('&ShowColumns=', @x) - 12), '&ShowColumns=', ','), '&AssetDescription=&', '');

  ---

• texasphill

  SSC-Addicted

  Points: 476

  More actions

  November 12, 2015 at 9:00 am

  #1839414

  Thanks for the reply. I cannot hard code the last column because it is also variable. SSRS adds the last value that was not selected...kind of strange. In this case there are actually 15 columns (options) in the parameter. The others aren't in there because they too were not selected.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  November 12, 2015 at 9:13 am

  #1839422

  OK, version 2.

  declare @x varchar(500) = '&StartRow=1&EndRow=1000&LoginID=40&ProjectAssessment=16&ShowColumns=Asset&ShowColumns=Category&ShowColumns=IOL&ShowColumns=Vulnerability%20Exposed&AssetDescription=&';

  select @x

  ,left(replace(right(@x, len(@x) - charindex('&ShowColumns=', @x) - 12), '&ShowColumns=', ','),charindex('&',replace(right(@x, len(@x) - charindex('&ShowColumns=', @x) - 12), '&ShowColumns=', ','))-1)

  ---

• texasphill

  SSC-Addicted

  Points: 476

  More actions

  November 12, 2015 at 11:53 am

  #1839475

  Version 2 worked! Thanks. Unfortunately I cannot use a delimited string to set the default of a multi-select parameter like I thought. I'm thinking I need to split the string into a table.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  November 13, 2015 at 12:32 am

  #1839532

  That very process was recently described in this thread.

  ---