Hi All -
I have successfully built widgets that bring in customer attributes, but I found a bug that I haven't been able to sort out. I'm going to write this out as best I can to help understand.
I have brothers, call them Luke and Kirk. They are primary individuals in the same household.
Luke has an attribute on his account that denotes his 23-24 Academy rank is Level 8. Kirk has an attribute on his account that denotes his 23-24 Academy ranks is Level 1. If you go into the records you can confirm that this is the case and that the attributes display correctly on their accounts.
In TX_Analytics_Division_Element in have this:
Data Select - MAX(key_value)
Data From - dbo.V_CUSTOMER_WITH_PRIMARY_AFFILIATES q join dbo.TX_CUST_KEYWORD t on t.customer_no=q.expanded_customer_no
Data Where - t.keyword_no=528
But in my Analytics Widget, the younger student Kirk's rank keeps showing up as Level 8, which is his older brother's rank. But his attribute is correct on his record, just importing his brother's attribute into the Analytics widget. I have a suspicion this is due to the MAX key value, but I don't know which formula will bring me the correct attribute.
Any ideas out there? Thank you!
You might try this in SSMS:
declare @luke int = 20364846, @kirk int = 20364847, @attribute int = 271;select * from T_KEYWORD where keyword_no = @attribute;
select MAX(t.key_value)from dbo.V_CUSTOMER_WITH_PRIMARY_AFFILIATES q join dbo.TX_CUST_KEYWORD t on t.customer_no=q.expanded_customer_nowhere t.keyword_no = @attribute and t.customer_no = @kirk;
(replace the variables with the right numbers)
If this is how the query works then this is the behavior to expect, I'm not 100% sure how this actually joins in to the cube. Now, it looks like it works for me. Next question, what cube are you using this in? For things like Seats and Tickets you're going to have a problem where (likely) your transactions are all applied to the household, not an individual. In this case, if you put a household in (and that's what all this MAX/V_CUSTOMER_WITH_PRIMARY_AFFILIATES stuff is about) then it will find the highest value from all members of the household, the household included, and post that. Are you perhaps using Luke and Kirk as recipients, and that's how you're getting them individually on rows? If so, I think you'll still be stuck with the customization linking to the owner of the transaction.
Hi, thanks for the response. I am using the Seats and Tickets cube - for our academy, performance = class. Production season = Level for the 22-24 FY. I throw the attribute in their account as well just to make list-pulling easier (cannot pull unpaid or partially paid orders out of list manager, only ticket history, and most of our academy families use payment plans). So I put the attribute on their records to help ID people and pull level rosters that way.
The two boys are individuals in a household. The household is the owner of the transaction. Each boy has their own order, of which they are the initiator and the recipient. And the attribute is on the individual record for each.
Is there a table for initiators, or recipients, that I can have the system pull from?
I really appreciate your thoughts - thanks again,
I have never added a custom element to Analytics, so I don't know how exactly the element joins into the the data cube, but if there's a way to do it, I expect it to be there.