SSRS Question

[LeeSmith]

I am working on a report in SSRS. I have created a calculated field using the IIF function. I now want to group my data by the values returned from the if statement and use them as parameters. The grouping seems to work well, but when I create the parameter I end up with more than one value the same e.g. "Beauty Therapy" appears twice in the drop down list.

Below is the IIF Statement

=(iif((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=011),"Counselling",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=012),"Food Hygiene",
(iif((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=013),"Health and Safety",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=014),"First Aid",
(iif((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=015),"Beauty Therapy",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=021),"Science and Mathematics",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=031),"Agriculture, Horticulture & Animal Care",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=041),"Engineering and manufaturing technologies",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=051),"Planning and the built environment",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=061),"ICT",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=071),"Leisure travel & tourism",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=091),"Arts, media & Publishing",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=101),"History, philosophy & theology",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=111),"Social Services",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=121),"Languages",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=122),"BSL",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=144),"Family Learning",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=147),"SAL",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=131),"Teacher Training",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=141),"Esol",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=142),"EFL",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=143),"Skills For Life",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=146),"GCSE",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=148),"Access To Education",
(IIF((Right(Fields!QUAL_Mgmnt_Code_1.Value,3)=151),"Business","Other"))))))))))))))))))))))))))))))))))))))))))))))))))

Also if anyone could suggest a more efficient way of writing this formula, it would be much appreciated

Regards

Lee

 

[namliam]

Not a solution to your duplication and I really have no solution for that, but why not use a referencing table?? If there is ever a new category you are going to have a nightmare maintaining this... while if you have a table it is easy.

 

[SQL_Hell]

Yep, I agree...a lookup table is the best way to go here, then you can join into your main report query:

SELECT *
FROM maintable
INNER JOIN newLookuptable NL
ON RIGHT(QUAL_Mgmnt_Code_1,3) = newLookuptable.code.

 

[namliam]

@SQL Hell

Better make that a left join to cover the "Other" which wont have a PK in the lookup table, acting as a defeault value.

 

[SQL_Hell]

Well yeah that's true, but it was only really an example to show the 'on' clause not a solution

 

[LeeSmith]

Thanks for the advice, helped a treat