USP_DATALIST_CAMPAIGNHIERARCHYFORSUMMARYREPORT

This datalist returns a campaign hierarchy for use in the campaign summary report.

Parameters

Parameter	Parameter Type	Mode	Description
@CAMPAIGNID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



                    create procedure dbo.USP_DATALIST_CAMPAIGNHIERARCHYFORSUMMARYREPORT
                    (
                        @CAMPAIGNID uniqueidentifier
                    )
                    as
                        set nocount on

                        declare @HIERARCHYPATH hierarchyid
                        select @HIERARCHYPATH = CAMPAIGN.HIERARCHYPATH from dbo.CAMPAIGN where CAMPAIGN.ID = @CAMPAIGNID

                        select
                            CAMPAIGN.ID,
                            (select PARENT.ID from dbo.CAMPAIGN as PARENT where PARENT.HIERARCHYPATH = CAMPAIGN.HIERARCHYPATH.GetAncestor(1)) PARENTCAMPAIGNID,
                            CAMPAIGN.USERID
                        from
                            dbo.CAMPAIGN
                        left outer join
                            dbo.CAMPAIGNTYPECODE on CAMPAIGN.CAMPAIGNTYPECODEID = CAMPAIGNTYPECODE.ID
                        where
                            CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
                        order by
                            CAMPAIGN.USERID