Syntax Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Syntax Error

Author

Topic

Trybbe
Starting Member

27 Posts

Posted - 2009-08-18 : 05:48:35

Hi may you please tell me what is wrong with this statement

=IIF(Parameters!Consolidate.Value = "ALL", "Retail HeadCount Report for Period: "& Parameters!EndPeriod.Value, 
Parameters!Consolidate.Value & " HeadCount Report for Period: "& Parameters!EndPeriod.Value)

When I run the report I get an error

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 06:44:12

Which error?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 06:45:57

Isn't this a simpler approach

=IIF(Parameters!Consolidate.Value = "ALL", "Retail", Parameters!Consolidate.Value) & " HeadCount Report for Period: "& Parameters!EndPeriod.Value

N 56°04'39.26"
E 12°55'05.63"

Trybbe
Starting Member

27 Posts

Posted - 2009-08-18 : 07:04:24

Sorry I did not provide enough information, I found the solution in another forum

 HI
I was having trouble replicating your error. Your provided info is not supplying enough to build a test report with. 

Offhand if you are using a Multi-value parameter for Consolidate, then that expression won't work, as well as, if your EndPeriod parameter is an integer then you should convert it to a string:

=IIF(Parameters!Consolidate.Value = "ALL", "Retail HeadCount Report for Period: "& Parameters!EndPeriod.Value.ToString ,
Parameters!Consolidate.Value & " HeadCount Report for Period: "& Parameters!EndPeriod.Value.ToString)

If you are using a multi-value parameter for Consolidate then you will have to use the JOIN function:

=IIF( Join(Parameters!Consolidate.Value,", ") = "ALL, ", "Retail HeadCount Report for Period: "& Parameters!EndPeriod.Value.ToString , Join(Parameters!Consolidate.Value,", ") & " HeadCount Report for Period: "& Parameters!EndPeriod.Value.ToString)

Thanks for the feedback

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources