Activity Status Description

Weird. I just copied your code into my test environment and it ran successfully. Are there other elements in your output set that could be causing the issue instead?

Thanks everyone. I think Beth was right, that it was a problem somewhere else in my output set. But now I have developed a different problem.

The code I created makes one row for each status the person has ever put down times each event they have ever gone to. I know this is the exponential combinations problem but I can't figure out why I would be getting it since each event should be associated with the status number directly and so shouldn't the code I created just pull the correct status for that event?

Thanks so much for all your help so far!

You would need to create a parameter to link directly to narrow it down to the specific event, so your users would have to add the parameter in output set to avoid the one to many link.

Caryl

Thanks Caryl, that makes a lot of sense. Do you have any idea how I'd go about it for something like this, where there are a lot of events one could theoretically chose from?

Thanks Caryl, that makes a lot of sense. Do you have any idea how I'd go about it for something like this, where there are a lot of events one could theoretically chose from?

Hey Jacob,

I've made a custom output set element which might do what you want. Basically it pulls the activity date, status, and number of attendees for a specific special activity which you define in the query elements. It's based on an element the Output Set Cookbook.

TR_QUERY_ELEMENT

Description: ActivityDate_Status_Num_Notes
Category: Activities
Data select: convert(varchar(12), !.sp_act_dt, 103) + '_' + ISNULL(RTRIM(!.status_desc),'') + '_' + ISNULL(cast(!.num_attendees as varchar(10)),'') + '_' + ISNULL(!.notes, ' ')
Data from: (select c.customer_no, c.sp_act, c.sp_act_dt, c.notes, c.num_attendees, p.description as status_desc, c.status from VS_SPECIAL_ACTIVITY c join TR_SPECIAL_ACTIVITY_STATUS p on p.id = c.status)
Data where: !.sp_act=<<p##>> and !.sp_act_dt=<<p##>>
Single row / Primary Group default: unchecked

TR_QUERY_ELEMENT_PARAMETER

Description: Activity
Data type: Number
End of day/multiselect: unchecked
Ref tbl: vrs_special_activity
Red id: id
Ref desc: description
Red where: blank
Ref sort: description 

When you've added this use the ID in TR_QUERY_ELEMENT_PARAMETER of the Activity in the data where <<p##>> for !.sp_act_dt, and just reference the default start_dt for the second <<p##>> (which is most likely 1).

When you save to Excel you can use the text to column feature to separate out the data as required (it will be concatenated with an underscore between each element).

Any Q's, get in touch!

Cheers

dgh