USP_REPORT_DESIGNATIONPROGRESSSUMMARY

            CREATE procedure dbo.USP_REPORT_DESIGNATIONPROGRESSSUMMARY
                (
                    @DESIGNATIONLEVELID uniqueidentifier = null,
                    @GOALID uniqueidentifier = null,
                    @REPORTCODE1ID uniqueidentifier = null,
                    @REPORTCODE2ID uniqueidentifier = null,
                    @CATEGORYID uniqueidentifier = null,
                    @TYPE uniqueidentifier = null,
                    @GROUPBY tinyint = null,
                    @CURRENCYCODE tinyint = null
                )
            as

                declare @SELECTEDCURRENCYID uniqueidentifier;

                if @CURRENCYCODE = 0
                    -- use each DESIGNATIONS's base currency

                    set @SELECTEDCURRENCYID = null;
                else
                begin
                    -- use org currency

                    set @CURRENCYCODE = 1;
                    set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                end

                select 
                    'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + CONVERT(nvarchar(36),DESIGNATIONPROGRESS.DESIGNATIONID) as [DESIGNATIONID],
                    DESIGNATIONPROGRESS.LEVEL1NAME,
                    case when DESIGNATIONPROGRESS.LEVEL2NAME is null then DESIGNATIONPROGRESS.LEVEL1NAME
                         when DESIGNATIONPROGRESS.LEVEL3NAME is null then DESIGNATIONPROGRESS.LEVEL1NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL2NAME
                         when DESIGNATIONPROGRESS.LEVEL4NAME is null then DESIGNATIONPROGRESS.LEVEL1NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL2NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL3NAME
                         when DESIGNATIONPROGRESS.LEVEL5NAME is null then DESIGNATIONPROGRESS.LEVEL1NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL2NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL3NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL4NAME
                         else DESIGNATIONPROGRESS.LEVEL1NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL2NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL3NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL4NAME + ' \ ' + DESIGNATIONPROGRESS.LEVEL5NAME
                         end DESIGNATIONNAME,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL1ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL2ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL3ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL4ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVEL5ID,
                    DESIGNATIONPROGRESS.DESIGNATIONLEVELTYPE,
                    DESIGNATIONPROGRESS.REPORTCODE1,
                    DESIGNATIONPROGRESS.REPORTCODE2,
                    coalesce(DESIGNATIONPROGRESS.DESIGNATIONGOAL,0) as DESIGNATIONGOAL,
                    coalesce(DESIGNATIONPROGRESS.OVERALLGOAL,0) as OVERALLGOAL,
                    DESIGNATIONPROGRESS.GOALNAME,
                    DESIGNATIONPROGRESS.STARTDATE,
                    DESIGNATIONPROGRESS.ENDDATE,
                    coalesce(DESIGNATIONPROGRESS.TOTALEXPECTED,0) as TOTALEXPECTED,
                    coalesce(DESIGNATIONPROGRESS.TOTALRECEIVED,0) as TOTALRECEIVED,
                    coalesce(DESIGNATIONPROGRESS.TOTALPLANNEDGIFT,0) as TOTALPLANNEDGIFT,
                    coalesce(DESIGNATIONPROGRESS.TOTALGIFTS,0) as TOTALGIFTS,
                    coalesce(DESIGNATIONPROGRESS.TOTALDONORS,0) as TOTALDONORS,
                    DESIGNATIONPROGRESS.APPEALNAME,
                    coalesce(DESIGNATIONPROGRESS.APPEALRECEIVED,0) as APPEALRECEIVED,
                    coalesce(DESIGNATIONPROGRESS.APPEALEXPECTED,0) as APPEALEXPECTED,
                    coalesce(DESIGNATIONPROGRESS.APPEALNUMGIFTS,0) as APPEALNUMGIFTS,
                    coalesce(DESIGNATIONPROGRESS.APPEALNUMDONORS,0) as APPEALNUMDONORS,
                    coalesce(DESIGNATIONPROGRESS.APPEALMAXGIFT,0) as APPEALMAXGIFT,
                    coalesce(DESIGNATIONPROGRESS.APPEALPLANNEDGIFT,0) as APPEALPLANNEDGIFT,
                    DESIGNATIONPROGRESS.APPEALCURRENCYID,
                    DESIGNATIONPROGRESS.APPEALCURRENCYISO,
                    DESIGNATIONPROGRESS.APPEALCURRENCYDECIMALDIGITS,
                    DESIGNATIONPROGRESS.APPEALCURRENCYSYMBOL,
                    DESIGNATIONPROGRESS.APPEALCURRENCYSYMBOLDISPLAYSETTINGCODE,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYID,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYISO,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYDECIMALDIGITS,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYSYMBOL,
                    DESIGNATIONPROGRESS.DESIGNATIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
                    DESIGNATIONPROGRESS.ISACTIVE
                from 
                    dbo.UFN_DESIGNATION_GETPROGRESSINCURRENCY(@DESIGNATIONLEVELID, @GOALID, @REPORTCODE1ID, @REPORTCODE2ID, 1, @GROUPBY, @SELECTEDCURRENCYID) DESIGNATIONPROGRESS
                    /*#IDSETEXTENSION*/
                where
                    (DESIGNATIONPROGRESS.LASTCATEGORYCODEID = @CATEGORYID or @CATEGORYID is null) and
                    (DESIGNATIONPROGRESS.LASTTYPEID = @TYPE or @TYPE is null);