Hi All,
Me again with a conundrum regarding custom Flex Header Elements.
I've been trying to get a calculation of Age in years and months based on birthdate_1 attribute. I finally got my SQL to work in SSMS, but devastatingly when I put it into Tessitura the header is still giving me an error.
Anyone have this working in their database?
The forum is not letting me post my SQL code in this post. I'll try to follow up with it as a comment.
Thanks,
Kanani
Hi Kanani,
This is the SQL we use in one of our Flex Headers:
select convert(varchar(05),cast(datediff(DAY, cast(key_value as date), getDate() -1) / (365.23076923074) as int)) + 'y ' +convert(varchar(05),cast(datediff(MONTH, cast(key_value as date), getDate() -1) % (12) as int)) + 'm'from TX_CUST_KEYWORDwhere customer_no = @customer_no and keyword_no = 1
This returns the age as "19y 10m" for someone born on 25 Dec 2000.
Martin
Oh, Martin!
Thank you thank you thank you! You don't know how happy this makes me. Such a headache trying to figure this out last week. Happy Monday!
Can someone from the Network weigh in on why Kanani's function call wasn't working?
Hi Gawain, Kanani, and everyone! It's not always possible for us to troubleshoot custom SQL, and we can't see the full context in a forum post. But given everything that's been said in the thread, I would check the permissions on the function. All custom SQL objects need permissions granted to ImpUsers and tessitura_app to be called from anywhere in the application. In the case of scalar functions, it's the EXECUTE permission. This is the quickest way:
GRANT EXECUTE ON dbo.your_function_name TO ImpUsers, tessitura_app
If that doesn't work, feel free to open a support ticket. As I said, we can't troubleshoot custom code but perhaps there is some other environment factor we can help diagnose.
Based on this thread, I will include a recipe for an Age element in the forthcoming Flex Header Recipe Book.
Aha! I always forget about that.