=COUNT(SWITCH Query

• DarthMJH

  Grasshopper

  Points: 22

  More actions

  May 6, 2015 at 10:03 am

  #398415

  Hi,

  I have created a report using Report Builder 3.0 that has a Matrix.

  I am trying to count all the Operating Systems that have a certain version of IE installed. I have created the expression below, however I am getting some strange results.

  The first and third results (Win 7 & 2008 SP2) give me the correct figures but the second and forth (2008R2 & 2012) are incorrect. If I swap Win7 with Win2012 R2 (first and forth) then Win7 is incorrect and 2012 is then correct. So it must be to do with my syntax but I cannot see where.

  =COUNT(SWITCH(Fields!Operating_System.Value = "Microsoft Windows 7 Workstation" and Fields!Version.Value = "11",

  Fields!Operating_System.Value = "Microsoft Windows Server 2008 R2" and Fields!Version.Value = "11",

  Fields!Operating_System.Value = "Microsoft Windows Server 2008 SP2" and Fields!Version.Value = "9",

  Fields!Operating_System.Value = "Microsoft Windows Server 2012 R2" and Fields!Version.Value = "11",0,1))

  I've spent hours on this and cannot see what is wrong.

  Thanks for looking.

• Mohamed I.

  SSCrazy

  Points: 2774

  More actions

  July 6, 2015 at 5:13 am

  #1810394

  Hi

  The Switch syntax is

  Switch(Expression1, Value1, Expression2, Value2,...,Default Value)

  I think you missed the value after each of your expressions.

  :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

• FunkyDexter

  Hall of Fame

  Points: 3907

  More actions

  July 10, 2015 at 4:35 am

  #1811547

  Also count just counts all non null values. Unless one of the values returned from your switch is a Nothing you're just going to get a count of all records, regardless of OS. I think you might be looking for a Sum.

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  July 10, 2015 at 6:46 am

  #1811592

  =Sum(Switch(

  Fields!Operating_System.Value = "Microsoft Windows 7 Workstation" and Fields!Version.Value = "11",1,

  Fields!Operating_System.Value = "Microsoft Windows Server 2008 R2" and Fields!Version.Value = "11",1,

  Fields!Operating_System.Value = "Microsoft Windows Server 2008 SP2" and Fields!Version.Value = "9",1,

  Fields!Operating_System.Value = "Microsoft Windows Server 2012 R2" and Fields!Version.Value = "11",1,

  True,0)

  =Sum(IIf(

  (Fields!Operating_System.Value = "Microsoft Windows 7 Workstation" and Fields!Version.Value = "11") OR

  (Fields!Operating_System.Value = "Microsoft Windows Server 2008 R2" and Fields!Version.Value = "11") OR

  (Fields!Operating_System.Value = "Microsoft Windows Server 2008 SP2" and Fields!Version.Value = "9") OR

  (Fields!Operating_System.Value = "Microsoft Windows Server 2012 R2" and Fields!Version.Value = "11")

  1,0)

  Both these will give the number of rows where any of the 4 conditions is true

  Far away is close at hand in the images of elsewhere.
  Anon.

• DarthMJH

  Grasshopper

  Points: 22

  More actions

  July 10, 2015 at 7:15 am

  #1811603

  This is what I did in the end:

  =COUNT(SWITCH

  (Fields!Operating_System.Value = "Microsoft Windows 7 Workstation" and Fields!Version.Value = "11",0,

  Fields!Operating_System.Value = "Microsoft Windows Server 2008 R2" and Fields!Version.Value = "11",0,

  Fields!Operating_System.Value = "Microsoft Windows Server 2008 SP2" and Fields!Version.Value = "9",0,

  Fields!Operating_System.Value = "Microsoft Windows Server 2012 R2" and Fields!Version.Value = "11",0,

  False,1))

  Thank for all your responses.