September 20, 2016 at 8:27 am
Having some trouble getting some formatting to look a certain way in SSRS. I'm doing a scatter chart. My Y-axis data is coming in as a 2 floating point numbers from the database table that needs to be converted into seconds.
Usually these numbers look something like 0.000335648146574385.
So I'm pulling these numbers into SSRS and doing a quick calculation on them to get seconds =int(Fields!TimeSecond.Value * 1440 * 60). Just having my Y-axis show up as a number works fine. Minimum of 0 up to 120 for 2 minutes. Data for the 2 points charts nicely across X and Y. But the data won't chart as a date type so getting the labels to look nice is a pain.
What I want is for the data's labels to show up in mm:ss format even though I'm just using an int value. I thought simply converting how the labels display would be easiest. So a value of 120 would show up as 02:00. 100 would show up as 01:40. Etc. Then I could have the Y-axis display 00:00 as the minimum and 02:00 as the maximum. This would probably be easier if the data was coming in as a datetime to begin with, but it isn't and I can't change that.
I got some help on the MSDN forums and it got me closer to what I wanted...
=iif(Sum(Fields!TimeSecond.Value)>0,iif(int(Sum(Fields!TimeSecond.Value)/60)<10,"0" & int(Sum(Fields!TimeSecond.Value)/60),int(Sum(Fields!TimeSecond.Value)/60)) & ":" & iif(Sum(Fields!TimeSecond.Value) mod 60<10,"0" & Sum(Fields!TimeSecond.Value) mod 60,Sum(Fields!TimeSecond.Value) mod 60),"")
I now get 00:00 format. But after 00:59 instead of going to 01:00 it goes to 00:60. Then 00:70, etc. The gentleman that answered me said it was converting properly for him so I can only assume I'm doing something wrong somewhere else. Anyone have any idea or run into a similar problem?
Thanks in advance!
September 20, 2016 at 9:07 am
You could convert it to an actual datetime from the year dot (01/01/0001 00:00) since you are only interested in the minutes and seconds parts.
I.e. =DateAdd(DateInterval.Second, 30, System.DateTime.MinValue)
Where 30 would be substituted for you seconds field. This way it would return an actual datetime you can format appropriately.
Edit:
I'd probably add this bit "=int(Fields!TimeSecond.Value * 1440 * 60)" as a calculated field on the dataset and use the calculated field in the above expression.
September 20, 2016 at 9:26 am
Even better - use the TimeSerial (it does the same as above) function and format the datetime it returns!
I.e. =TimeSerial(0,0,Fields!Seconds.Value)
You can pass any number of seconds in e.g. 120 and it will handle it.
September 20, 2016 at 9:43 am
Been tinkering with your solution. In my y-axis number formatting I have a custom format set as:
=Format(TimeSerial(0,0,Fields!SecondsValue.Value), "mm:ss")
Still giving me the same result though.
00:50
00:60
00:70
instead of
00:50
01:00
01:10
Frustrating 🙂
September 20, 2016 at 9:50 am
The custom format should just be a string e.g. mm:ss
You can adjust the intervals via the axis options.
September 20, 2016 at 10:00 am
Hang on, I think I see what you are getting at.
In a scatter plot the y-axis labels will always be determined by the value, you cannot set your labels as a different field to the series value.
However in this case the chart will treat a datetime value as a Scalar and plot the value as if it was a number. So you can use it as a value for the series.
September 20, 2016 at 1:35 pm
Yeah basically totally unformatted the values go from 0 to 130 seconds and display as those numbers at intervals of 10. All I want to do is literally just change the number to read in mm:ss format. It seems like it'd be such a simple conversion, but it just won't work for some reason.
September 21, 2016 at 2:22 am
Use the TimeSerial as the value for your Y series. You're not formatting, you're converting the Integer to a DateTime. The chart will interpret the DateTime as a Scalar, it will plot it correctly and it will also display the y-axis as DateTime values, which can be formatted like a DateTime value (mm:ss).
Change the axis interval to 10 and interval type to Seconds.
September 22, 2016 at 7:57 am
Very close to the final solution now!
My Y axis is now showing the appropriate "mm:ss" format and in the correct interval. In vertical axis properties I have a custom number formatting that is simply ="mm:ss". Min as 00:00:00/Max as 00:02:10/Interval as 10/Type as Seconds all set and it works nicely. My X axis is also showing the appropriate labels for each going record across as well.
However, the data isn't plotting onto the charts. So I think it's pretty clear I have an issue there of some kind. In chart properties, under Values, I have my value expression for my data points as: =TimeSerial(0,0,Fields!Seconds.Value) so those values should be returning the appropriate time value.
My guess is it has something to do with the date. Adding MM/dd/yyyy to the Y-axis shows it as plotting the current date there, whereas the values along the X-axis are being sorted by dates which are often older dates. Is there a way to get this to ignore the date and use only the time as the plot point? I'm passing in a date parameter (MM/dd/yyyy) to show data for specific dates.
September 22, 2016 at 8:25 am
This is to do with the interval min and max you set on your axis, the type it is expecting is a DateTime and when you enter 00:00:00 and omit the date it converts that to midnight of the current date. However the TimeSerial returns your time on the date of 01/01/0001, therefore your values are all falling outside of the range of your axis min max and not being plotted.
Change your interval min to 01/01/0001 00:00:00 and max to 01/01/0001 00:02:10 and try rerunning the chart.
September 22, 2016 at 8:27 am
You're the man. Total, complete, fantastic success!
Thank you so much!
-Rich
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply