Infographic in SSRS

• dannnyhunter

  Valued Member

  Points: 66

  More actions

  July 9, 2018 at 4:30 pm

  #354178

  Hi everyone,

  I just got a little infographic working in SSRS. I recently got an infographic working in excel to display the amount of people that are secluded.

  https://imgur.com/a/fD1YrzK

  and i wanted to get it to work in SSRS so i made a stored procedure that creates a list from 1 to 100 with the x and y values to place them in the grid
  
  ;with nums as( 
select
   1 value
  union all 
select
   value + 1 value
  from nums 
where
   1=1
   and nums.value <= 99)
SELECT  
value id
  ,row_number() over (partition by (value-1)/10 order by value) x
  ,(value-1)/10 y
FROM nums   

  From here we can add this as a dataset to SSRS and create a scatter plot with the following settings:

  https://imgur.com/a/SDtsTvx
  we get a grid of points.

  then we can click on one of the markers to change the picture that is displayed for the marker. I used the following icons because aqua is sort of my companies colour:

  https://imgur.com/a/4KbkduI
  with the following expression to the value of Image

     =iif(Fields!value.Value <= sum(Fields!perc.value,"main_query"),"Aquaman","greyperson")   =iif(Fields!value.Value <= sum(Fields!perc.value,"main_query"),"Aquaman","greyperson")

  https://imgur.com/a/yVX3lLi
  main_query perc is a simple field in the dataset that is a percent. The expression checks the value of the number in the grid and if it is less than or equal to the percent then it chooses the Aquaman icon and if it is greater it chooses greyperson.

  which brings me to the final infrographic

  https://imgur.com/a/Z0h79Lc

  all images on imgur in one album: https://imgur.com/a/fD1YrzK

  I hope you all find this as exciting as I do, or at least as a little interesting.

  Cheers

• pharmkittie

  SSCertifiable

  Points: 5775

  More actions

  July 10, 2018 at 3:49 pm

  #1997080

  Thanks. I tried it. Nice.  Do you have some sample data for main_query?  I tried some test values and nothing I am coming up with causes any of the men to be other than all one color.  I tried various values like this:
  \value1    perc
  0.10    0.15
  2.00    3.00
  4.00    0.00
  0.00    1.00
  5.00    0.00
  0.00    2.00
  0.20    0.65
  0.50    0.20
  0.70    0.90
  0.85    0.25
  0.25    0.75
  0.30    0.40

• dannnyhunter

  Valued Member

  Points: 66

  More actions

  July 10, 2018 at 3:57 pm

  #1997081

  i used 46 as a test. when i was using a real query it calculated something like 0.09, so i had to multiply it by 100 to get the percentage as a whole number

• pharmkittie

  SSCertifiable

  Points: 5775

  More actions

  July 11, 2018 at 7:45 am

  #1997127

  dannnyhunter - Tuesday, July 10, 2018 3:57 PM

  i used 46 as a test. when i was using a real query it calculated something like 0.09, so i had to multiply it by 100 to get the percentage as a whole number

  Thanks but I'm afraid that I still don't understand.  Where do I put the 46?  If have a query like this for a second dataset that the image for the marker uses:
  SELECT   value1, perc FROM    testing
  with the table having data like this: 
  value1............................................................................perc

  46.00	0.15
  2.00	46.00
  4.00	0.00
  0.00	1.00
  5.00	0.00
  0.00	2.00
  0.20	0.65
  0.50	0.20
  0.70	0.90
  0.85	0.25
  0.25	0.75
  0.30	0.40
  The expression for the marker image is this:

  =iif(Sum(Fields!value1.Value, "main_query") >= sum(Fields!perc.value,"main_query"),"aquaman","greyman")

  What am I missing?
  Thanks.