November 12, 2015 at 8:29 am
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
November 12, 2015 at 8:39 am
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=&', '');
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 12, 2015 at 9:00 am
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.
November 12, 2015 at 9:13 am
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)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 12, 2015 at 11:53 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply