July 9, 2018 at 4:30 pm
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.
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
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
July 10, 2018 at 3:49 pm
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
July 10, 2018 at 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
July 11, 2018 at 7:45 am
dannnyhunter - Tuesday, July 10, 2018 3:57 PMi 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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply