Grouping parameter value

I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.

VT01

VT02

VT03

VN01

VN02

VN03

ST01

ST02

ST03

instead of listing everything, i want the viewers to see this

VT

VN

ST

or an alias for each of these like this:

Vet Tasks

Vet National

Survey Tasks

Survey National

any ideas, here's my current code, which is pullin up anything with the added substring part

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions]

(@.Region_Key int=null,@.QuestionCode char(5))

AS

BEGIN

SELECT dbo.Qry_Questions.Territory,

dbo.Qry_Questions.SalesResponsible,

dbo.Qry_Questions.Customer,

dbo.Qry_Questions.Date,

dbo.Qry_Questions.StoreName,

dbo.Qry_Questions.PostCode,

dbo.Qry_Questions.Address2,

dbo.Qry_Questions.[Question Code],

dbo.Qry_Questions.Question,

dbo.Qry_Questions.[Response Type],

dbo.Qry_Questions.response,

dbo.Qry_Questions.sales_person_code,

dbo.Qry_Sales_Group.Region_Key,

dbo.Qry_Sales_Group.Region

FROM dbo.Qry_Questions

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

WHERE REGION_KEY=@.Region_Key

AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@.QuestionCode

END

SET NOCOUNT OFF

You might try using the follwing as the grouping expression:

Code Snippet

=Left(Fields!<your_field>.Value, 2)

From there you could either set up a CASE statement or code for your aliases.

Hope this helps!

Scott

|||

I have the report working, i just want to be able to group the choices into 6 different choices. I know there is a way to do this in the report parameters properties box. I created my own non queried values that look like this:

Label Value

Survey national =IIF(Left(Fields!Question_Code.Value, 2)="SN",Fields!Question_Code.Value,nothing)

Survey vet =IIF(Left(Fields!Question_Code.Value, 2)="SV",Fields!Question_Code.Value,nothing)

Survey independent =IIF(Left(Fields!Question_Code.Value, 2)="SI",Fields!Question_Code.Value,nothing)

and so on...

But i keep getting an error :

A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field.

what am i doing wrong?

|||

I believe that if you are populating values for a parameter, you can't use the same dataset used in the report. I vaguely remember running into the same problem when I fist began using parameters. We use separate datasets for the parameters in our reports.

|||

Create a second dataset with the following query:

Select Distinct Left(Question_Code, 2)

FROM dbo.Qry_Questions

Group by Question_Code

Order by Question_Code

Change your parameter to query and point it at this new dataset. Then in your main dataset query add the following to your where statement:

Where Question_Code IN(@.question_code_parm)

|||Thanks that worked beautifully!! And i was able to hard code and rename the Question codes that were group for report parameters.