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
STill not letting me... here's a screen shot of it
Is this what you are using in TR_FLEX_HEADER_ELEMENT? I've never seen variables declared there. Try creating a local view in SSMS (customer_no and age as your columns) and write a simple select statement from that, instead.
Hi Kevin,
Thanks for your response. After some research, I learned that you can't declare variables in views, but functions are the way to go. I've never created either, but I was able to stumble through creating a function with some help from SQL for dummies and Google. However, it's only returning age in years, whereas the query I wrote before was giving me what I want which is "x years x months".
Are you familiar with functions, or know what might be going wrong for me? I appreciate your help.
I think you need to define a character length for your varchar age (i.e. @age varchar(255)): otherwise it defaults to 1.
Also, I think this may be a bit easier to maintain:
Woohoo! Thanks, Gawain! adding (255) did the trick.
Arg! I spoke too soon. I don't know how to write the select statement in TR_Flex_header_element so that it will give me the age. Putting @customer_no where I had a hard constituent id for testing is giving me error.
What does your syntax look like?
Also might want to add an "ISNULL" statement to display some text in case the birthdate isn't available.
I tried a few different things
I'm just going off of other examples...I don't actually know how this works.
I would expect the latter to work...but maybe drop the "as 'age'" part?
All of the examples in my Flex Headers of using a function use the former format, but that is because they are table functions (so returning a value for every customer and then you pull out the value you want with the comparison to @customer_no).