• Replies 1 reply
  • Subscribers 336 subscribers
  • Views 182 views
  • Users 0 members are here
Related

TR_QUERY_ELEMENT (and parameters)

Doug Jones
$organization

Having difficulty working with Output Sets and their relation to the TR_QUERY_ELEMENT & _PARAMETER tables.  I have created a custom view at the ticket level called LV_ORDER_TICKETS. 

I have created an output set using 3 custom elements all from the same LV_ORDER_TICKETS (two of these elements make use of a tr_query_element_parameter)

When running the report "Execute an Output Set" for the above output set the system builds this SQL select which works but not quite the way I expect:

Select   work.customer_no, 
  'CUST-Ticket Price Type Grp' = a.price_type_group, 
  'CUST-Ticket Perf Name' = b.production, 
  'CUST-Ticket Season' = c.season 
From #work1 work
JOIN t_customer customer ON work.customer_no = customer.customer_no  
Left Outer Join LV_ORDER_TICKETS a ON work.customer_no = a.customer_no 
Left Outer Join LV_ORDER_TICKETS b ON work.customer_no = b.customer_no and b.lv_tkt_perf_code in ('CM0723E10','CM0724E10') 
Left Outer Join LV_ORDER_TICKETS c ON work.customer_no = c.customer_no and isnull(c.season,0) = (34) 

Is it possible to create the TR_QUERY_ELEMENTS in a way where only one join to the LV_ORDER_TICKETS view will occur.    We want each ticket row of the view to be filtered against all of our parameters. Is this possible to do this in the same way that List Builder elements operate?

Thanks.

-doug

Parents
  • $organization

    Hi Doug

     

    What you are seeing is because all the Elements used in an Output Set act independently of eachother. One thing to make sure is that you have the same parameters used for all of the elements, but I suspect this will still result in three joins.

     

    A solution might be to use one element in the output set, but have it output multiple information using concatenation and  use the pipe (|) as the delimiter. Then Output it as Tab delimited text and when you open it in excel saying that tab and pipe are the delimiters - voila you have your extra columns of data, ready for merging and what have you. Also in the naming of your element treat it like it will become the column headings. Yes that is a downside as it means the descriptions is not too descriptive.

     

     So using your example:

     

    Description: PriceGroup|Prod|Season

    Data_select: !.pricetype_category + ‘|’ + !.production + ‘|’ + !.season

    Note: I have made the assumption that these are all varchar values, if not cast them as varchar so you can do the concatenation

     

    Hope this is of some help.

     

    Cheers

    Sandra

Reply
  • $organization

    Hi Doug

     

    What you are seeing is because all the Elements used in an Output Set act independently of eachother. One thing to make sure is that you have the same parameters used for all of the elements, but I suspect this will still result in three joins.

     

    A solution might be to use one element in the output set, but have it output multiple information using concatenation and  use the pipe (|) as the delimiter. Then Output it as Tab delimited text and when you open it in excel saying that tab and pipe are the delimiters - voila you have your extra columns of data, ready for merging and what have you. Also in the naming of your element treat it like it will become the column headings. Yes that is a downside as it means the descriptions is not too descriptive.

     

     So using your example:

     

    Description: PriceGroup|Prod|Season

    Data_select: !.pricetype_category + ‘|’ + !.production + ‘|’ + !.season

    Note: I have made the assumption that these are all varchar values, if not cast them as varchar so you can do the concatenation

     

    Hope this is of some help.

     

    Cheers

    Sandra

Children
No Data