Don’t worry, I could have come up with a more complex title for this piece, just looks at the mess I ended up creating!
So what on earth is this all about? Well, I had this crazy idea one night sitting in a hotel room whilst I was waiting for an SAP Mentor meet-up – or it might have been a SAP SuccessFactors Confidant meet-up (I have to say the Confidant group have recently had the better outfits! – Here I am modelling one of the last ever pieces of SuccessFactors kit to officially be allowed to use the ole heart logo…
It’s a terrible selfie – but I wanted to show all the Northern Hemisphere people that it wasn’t autumn/fall everywhere in the world!
Anyway, I digress. The point was I was sat up lateish in the evening and I had an idea…
What if?
What if I could help the People team at the company I was implementing SAP SuccessFactors Timesheet for, have simple report that could allow them a direct link to all the unsubmitted/unapproved timesheets in the system? Or even a link that opened the ones that were submitted, but had some strange details, like more than 8hours in a day across multiple assignments? But the main point, give them a link so they don’t have to go the the person’s profile, choose the correct assignment, open the time section, navigate to timesheets, then find the week that the timesheet corresponded to!
That would be cool!
Here’s the Doco
Enter SAPSF quick links:
- KBA – 2089418 – Quick Links and Deep-Linking within the SuccessFactors Application
- SAP Help Portal – List of SAP SuccessFactors Deep Links
So SAPSF has a quick link/deep link to timesheets:
Basically,
<sapsf_url>/sf/timesheet?selected_user=<userId>&selected_date=<yyyy-mm-dd>
the selected date is the start date of the timesheet.
The great bit is that all of this info is available in Stories – I can get the start date of the timesheet – super cool!
The bad bit is that I need to format the date of the start of the timesheet in ISO standard YYYY-MM-DD, so 2024-12-16. At least they didn’t ask us to use the insane US MM/DD/YYYY format, but, still, surely it shouldn’t be too hard to format a date into whatever format I need…
Wrong!
There is no “Date” -> “formatted string” function in Stories. You’d think that this would be a very basic functionality of the calculated field logic, but you’d be disappointed.
Start Hacking
Okay then, so what functions do I have for dates? Start at the beginning – I need YYYY, is that doable? Well there is a YEAR function! Awesome! But it returns a number and I need to concatenate this together into a string. No worries mate!
We have the number to text function! TOTEXT( <number>). So we can easily get the year. And we can concatenate it with a hyphen
CONCAT(CONCAT( TOTEXT(YEAR([Time Management#Employee Time Sheet#Start Date] )) ,"-" )
This will give us “2024-“. Also known as a great start!
Now, on to the month… Again, we have a function “MONTH()” which gives a numeric month from a given date, so (dealing with the concatenate function only taking two string inputs) we get:
CONCAT( TOTEXT(YEAR([Time Management#Employee Time Sheet#Start Date] )) , CONCAT("-" , CONCAT(TOTEXT(MONTH([Time Management#Employee Time Sheet#Start Date] )) ,"-" )
Which gives us “2024-12-“. Nice!
So now on to the date! There is a DAY function! Yeah! Brilliant… Ah, but it returns the day of the week… of 1 through to 7. Useful, I guess if I wanted to know if a given date was a Monday or a Sunday, but a fat lot of good if I need to know the day of the month.
Out of the box thinking.
This is where things got “fun”. So looking at all the remaining functions available that worked with dates – the was one “DAYS_BETWEEN” that could be useful. If we had a date that was the first day of the month, then we could work out the days between that date and the timesheet date, and the day of the month would be the number of days in between plus 1. Okay – how to do that? Well, there is a “TODATE” function that takes a string and turns it into a date given a particular date format (oh for the love of things less complex, why couldn’t we have a “TOSTRING” function that worked on Dates!) , and we just worked out how to get a string of month and year… so! To generate the date of the first day of the month in which our timesheet exists:
TODATE( CONCAT("01" , CONCAT( TOTEXT( MONTH([Time Management#Employee Time Sheet#Start Date] ) ),TOTEXT( YEAR([Time Management#Employee Time Sheet#Start Date] ) ) ) ), "DDMMYYYY" )
This gives us a date of 01/12/2025 (and yes I’m using the right date format I mean why would you put a month before a day?)
So, with this, we can now calculate the number of days between the start of the month and the timesheet start date and then make that number a text value!
TOTEXT( DAYS_BETWEEN( TODATE( CONCAT("01" , CONCAT( TOTEXT( MONTH([Time Management#Employee Time Sheet#Start Date] ) ), TOTEXT( YEAR([Time Management#Employee Time Sheet#Start Date] ) ) ) ), "DDMMYYYY" ), [Time Management#Employee Time Sheet#Start Date] ) + 1 )
I have my elusive 16! And now can construct the ISO format date of 2024-12-16. Win!
Well, it was a win right up until the next month when at there were timesheets that started on the 7th of the month… unfortunately, nothing to allow us to easily pad the day of the month with a leading zero if it was only one character. Fortunately there was an IF function and a LENGTH function that allowed for testing the length of the day of the month value and then adding an additional zero.
Which is how we got to the final code (which thinking about it, is going to fail in January when the months have less than 2 digits too. Oh well, will be a fun challenge to give to one of the team and see if they can follow the logic and solve for the month bit!)
Using the standard ways of getting a hyperlink into a story report, then allows for this to be embedded into a table and a shown in the Story:
Now – some useful hints here – you’ll notice that I have two columns – This is because for some almost as strange reason as not being able to just do DATE_TO_STRING(), the hyperlink function does not allow you to just have a field value as a hyperlink, it must start with either http:// or https://
But if you want the user to be able to download the report to Excel and then just click on the link there – well you need to content to actually have the URL in it, hence two columns.
In conclusion
Anyway – I hope you found this helpful and somewhat entertaining and perhaps if your name is Søren and you happen to read this, you’ll remember the conversation we had at SuccessConnect, and you’ll think about asking one of your team to just make a “DATE_TO_FORMATTED_STRING( <date>, <date format>)” function that we can use in the calculated fields of Stories?
Have a good day all!