3 Replies - 4831 Views - Last Post: 15 June 2011 - 12:27 PM Rate Topic: -----

#1 griffinfujioka  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 32
  • Joined: 26-October 08

SQL Reporting Services - nested iif or CASE in calculated field?

Posted 14 June 2011 - 02:33 PM

I'm working in SQL Reporting Services 2005 to generate information on gender distribution by age. I have age as an integer as one of my fields, however I'd like to create a calculated field DisplayAge which will adjust the ages to display a range of ages. For example:
Age DisplayAge
0 0-9
1 10-19
2 20-29
3 30-39
4 40-49
5 50-59
Etc...


This is my first experience in SQL. I've been doing OK with the queries but I'm having a hard time with the syntax in a calculated field?

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Reporting Services - nested iif or CASE in calculated field?

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: SQL Reporting Services - nested iif or CASE in calculated field?

Posted 15 June 2011 - 01:40 AM

I suggest you write a custom function that can be called from the query. The age field would be input argument and the range returned.

Age 5 falls within 50-59?
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5777
  • View blog
  • Posts: 12,591
  • Joined: 16-October 07

Re: SQL Reporting Services - nested iif or CASE in calculated field?

Posted 15 June 2011 - 03:57 AM

There is no iif in MSSQL. That's an evil access thing. So, now it's a case statement, which is a SQL standard. The example in the docs should be clear enough to follow. Take a stab at it and see how far you get.


If I'm misunderstanding and you're going from 0-9 to 0, 10-19 to 1, then just divide by 10.
Was This Post Helpful? 0
  • +
  • -

#4 griffinfujioka  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 32
  • Joined: 26-October 08

Re: SQL Reporting Services - nested iif or CASE in calculated field?

Posted 15 June 2011 - 12:27 PM

I figured it out. I wrote a long SWITCH statement in my calculated field expression.

Something along the lines of:

DisplayAge = SWITCH(fields!age.value=0, "0-9", fields!age=1,"10-19".....etc)

Thanks for the replies
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1