• Replies 17 replies
  • Subscribers 338 subscribers
  • Views 278 views
  • Users 0 members are here
Related

Age in Flex Header

Kanani Reichlin
$organization

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

Parents
  • $organization

    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_KEYWORD
    where customer_no = @customer_no and keyword_no = 1

    This returns the age as "19y 10m" for someone born on 25 Dec 2000.

    Martin

  • $organization in reply to Martin Keen (Past Member)

    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!

  • $organization in reply to Kanani Reichlin

    Can someone from the Network weigh in on why Kanani's function call wasn't working?

  • $organization in reply to Gawain Lavers

    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. 

Reply
  • $organization in reply to Gawain Lavers

    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. 

Children