SSRS Parameter Function for Capitalization

Question

Post reply

SSRS Parameter Function for Capitalization

mcoleman15902

SSC Eights!

Points: 880
More actions
December 8, 2015 at 1:11 pm

#323163

Hi Masters,
SQL SSRS 2008. I have a simple parameter that displays on my report. Is there a way to setup the parameter where when someone enters the parameter, it will always capitalize it. For example, the parameter is for projects. Projects are 3 characters. If someone enters a project parameter "brf", can it be shown as "BRF" ?
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Easy. Note my solution and comments:

DECLARE @UCase bit;

DECLARE @project varchar(10);

-- When @UCase is 0

SELECT @project = 'abc', @UCase = 0;

-- Logic for your output

SELECT PROJECT = CASE @UCase WHEN 0 THEN @project ELSE UPPER(@project) END;

-- When @UCase is 1

SELECT @project = 'abc', @UCase = 1;

SELECT PROJECT = CASE @UCase WHEN 0 THEN @project ELSE UPPER(@project) END;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mcoleman15902 SSC Eights! Points: 880 More actions · Answer 2

Brilliant. Thanks so much. I have a stored proc that the report calls. How can I integrate that CASE code into the PROC below:

USE [WEBAPP_CP]

GO

/****** OBJECT: STOREDPROCEDURE [DBO].[RPT_VENDOR_ACTIVITY_BY_EEOC] SCRIPT DATE: 11/20/2015 8:46:17 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [DBO].[RPT_VENDOR_ACTIVITY_BY_EEOC]

@BEGINVDATE DATE,

@ENDINVDATE DATE,

@PROJECT VARCHAR(29)

AS

BEGIN

SELECT VCHR.VEND_ID

,P.PROJ_ID

--,A.ACCT_ID

,V.VEND_NAME

,CASE

WHEN V.S_CL_SM_BUS_CD = 'L' THEN 'Large'

WHEN V.S_CL_SM_BUS_CD = 'S' THEN 'Small'

WHEN V.S_CL_SM_BUS_CD = 'N' THEN 'Non-Profit'

WHEN V.S_CL_SM_BUS_CD = 'F' THEN 'Foreign/Other'

END AS 'DEFAULT SIZE'

,V.VEND_NAME_EXT

,SUM(LNHS.CST_AMT) AS CST_AMOUNT

--,CAST(VCHR.INVC_DT AS DATE) AS INVC_DT

--,VCHR.VCHR_NO

,V.CL_VET_FL

,V.CL_SD_VET_FL

,V.CL_DISADV_FL

,V.CL_WOM_OWN_FL

,V.CL_LAB_SRPL_FL

,V.CL_HIST_BL_CLG_FL

,V.CL_ANC_IT_FL

FROM WEBAPP_CP.DELTEK.V_VEND V

RIGHT JOIN WEBAPP_CP.DELTEK.V_VCHR_HDR_HS VCHR

ON V.VEND_ID = VCHR.VEND_ID

RIGHT JOIN WEBAPP_CP.DELTEK.VCHR_LN_ACCT_HS LNHS

ON VCHR.VCHR_KEY = LNHS.VCHR_KEY

JOIN WEBAPP_CP.DELTEK.ACCT A

ON LNHS.ACCT_ID = A.ACCT_ID

JOIN WEBAPP_CP.DELTEK.PROJ P

ON LNHS.PROJ_ID = P.PROJ_ID

WHERE P.PROJ_ID LIKE '%' + @PROJECT + '%' AND

VCHR.INVC_DT BETWEEN @BEGINVDATE AND @ENDINVDATE

GROUP BY V.VEND_NAME_EXT

,V.S_CL_SM_BUS_CD

,VCHR.VEND_ID

,V.CL_VET_FL

,V.CL_SD_VET_FL

,V.CL_ANC_IT_FL

,V.CL_DISADV_FL

,V.CL_WOM_OWN_FL

,V.CL_LAB_SRPL_FL

,V.CL_HIST_BL_CLG_FL

,V.CL_ANC_IT_FL

,V.VEND_NAME

,P.PROJ_ID

--,VCHR.VCHR_NO

--,A.ACCT_ID

--,VCHR.INVC_DT

Order by p.PROJ_ID

END

mcoleman15902 SSC Eights! Points: 880 More actions · Answer 3

Or is it easier to create the function in SSRS report parameter properties ?

pietlinden SSC Guru Points: 63434 More actions · Answer 4

If you just want to show the parameter on your report, but in uppercase, I would do it in SSRS.

Something like

=UCase(Parameters!ParamName.Value)

where ParamName is the name of your parameter.

For grins, this converts to proper case... I only include it because it puzzled me a little...

=StrConv(Parameters!Protocol.Value, VbStrConv.ProperCase)

mcoleman15902 SSC Eights! Points: 880 More actions · Answer 5

I setup the parameter default values with the expression below:

=UCase(Parameters!Project.Value)

I am getting the following error:

"The DefaultValue expression for the report parameter 'Project' contains and error: The expression references the parameter 'Project', which doesn't not exist in the Parameters collection. Letters in the names of parameters must use the correct case."

I also tried setting up the function under parameter available values and got the same message. Any thoughts?

Thanks,

pietlinden SSC Guru Points: 63434 More actions · Answer 6

Are you trying to force the parameter value being supplied by the user to be converted to upper case as they type?

mcoleman15902 SSC Eights! Points: 880 More actions · Answer 7

No. I mean that would be great but perhaps a little overkill. The parameter is just pasted on the report header so it shows the user what project they have selected. I would like it to always display as uppercase. Hope this is more clear.

pietlinden SSC Guru Points: 63434 More actions · Answer 8

Oh, that's much easier. If all you want is to show the parameter value in the header in upper case, put a textbox in the header, and set the value to =UCase(Parameters!Project.Value)

mcoleman15902 SSC Eights! Points: 880 More actions · Answer 9

Good to go. The properties in the report header parameter, rather than the parameter properties in the toolbox on the left. Brilliant. Thanks as always.

MC