Hi all - Having trouble adding Constituency (either short_desc or the ID #) as an option in TR_QUERY_ELEMENT for an output set. I found an older thread which had some suggestions, but they don't seem to be working for me.Has anyone successfully done this? Thanks!
-Lisa
Hi, Lisa:
The following will return a constituency (short_desc) if it exists for a particular patron on your list:
1. Insert a row into TR_QUERY_ELEMENT_PARAMETER with the following values:
Description: Constituency
Data Type: Number
End Of Day: [unchecked]
Multi Select: [unchecked]
Ref Tbl: VRS_CONSTITUENCY
Ref Id: id
Ref Desc: short_desc
Ref Where: [blank]
Ref Sort: short_desc
2. Take note of the id for this line in TR_QUERY_ELEMENT_PARAMETER.
3. Insert a row into TR_QUERY_ELEMENT with the following values:
Description: Customer_Constituency
Category: Constituent
Data Select: !.short_desc
Data From: (select distinct a.customer_no, b.id, b.short_desc from vxs_const_cust a join vrs_constituency b on b.id = a.constituency)
Data Where: id = <<p##>> [update ## with the id in your system from TR_QUERY_ELEMENT_PARAMETER]
Control Group: [as appropriate for your environment]
Single Row: [checked]
Caveat: This will return the constituency ONLY if it belongs to the actual constituent pulled in the query. If Mrs. Gotrocks is your board member and you are pulling the Gotrocks Household account, you won’t see the constituency.
Lucie
Thanks this works here.
The main challenge is that you can only add this once to an output set and it reports on just one constituency. I suspect that this will be helpful as is.
However, Has anyone worked out a clever method to report on multiple constituencies in output sets.
One of my thoughts would be to add this multiple times as:
Customer_Constituency_1
Customer_Constituency_2
Customer_Constituency_3
...
Customer_Constituency_N
Anyone have a more cleaver idea?
Hi kids
If you're happy with just a comma-separated list of short_desc's like the common header element, you could just do this:
------------------------------------------------------------
Description: Constituency List
Data Select: !.const_list
Data From: (select customer_no, dbo.fs_const_string_new(customer_no, 'Y') const_list from t_customer)
Data Where:
Control Group: [Whatevs]
---------------------------------------------------
The 'Y' parameter in the function in the Data From select will make it return (constituencies from primary affiliates) as well, so if you don't want that, you would need to make it 'N'
Ken
We haven't done multiple constituencies, but I did end up creating a view that has "Highest Ranked Constituency" which pulls through for certain Development reports. It's for those times when Mr X is a Board Member, a Council Member and a Major Donor. Which category should he be listed as? So I set up a view that picks out the highest ranked constituency for each customer_no and then built an Output Set value that puts the value into an output set.