none
Counting issue SSRS

    Question

  • I am building a report that uses counts and sums in a different way than usual.

    You can have several transactions (checks or invoices) on the same day or within the same reporting period.  Each check is evaluated using a case statement to properly bucket the revenue. So far, so good.

    Here's the twist: We only can record a count of one for the first instance of revenue occurring, but ALL revenue is to be counted.

    For example:      check date          amount

                              110/10/2010        100.00

                               10/10/2010         400.00

    This should display as a count of 1 and amount of 500.00

    The revenue is processing correctly based on the following CTE, but we are still getting duplicate counts if more than one item exists.

    FYI: The DistinctCount table contains a historical record of when an order number has been processed previously so that it is not counted again. The problem with this approach is tha, while it restricts orders from counting if they were previously loaded into the distinct count table, they WILL be counted more than once if processed at the same time.

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    alter procedure [dbo].[reporting_closedGP_Rev] @cdate datetime,@regid int
    as
    
    set nocount on
    declare @today datetime 
    set @today=utility.dbo.fn_stripdate(@Cdate)
    
    if datepart(dw,@today)=2
    	select @today=@today-3
    else
    	select @today=@today-1
    
    declare @firstofmonth datetime=utility.dbo.fn_monthfirst(@today)
    declare @lastmonthtoday datetime=utility.dbo.fn_lmsameworkday(@today)
    declare @firstoflastmonth datetime=dateadd(m,-1,@firstofmonth)
    declare @lastofthismonth datetime=dateadd(D,-1,dateadd(m,1,@firstofmonth))
    
    /* if @today is a holiday, go back to previous day */
    declare @t datetime,@w int,@tgt int
    select @t=@firstofmonth,@w=0,@tgt=utility.dbo.fn_workingdays(@firstofmonth,@today)
    if utility.dbo.fn_workingdays(@today,@today)=0
    begin
    	while utility.dbo.fn_workingdays(@firstofmonth,@t)<@tgt
    	begin
    		select @t+=1
    	end
    	select @today=@t
    end
    
    --select @today='20121231'
    
    /* detect monthend */
    if utility.dbo.fn_workingdays(@firstofmonth,@today)=utility.dbo.fn_workingdays(@firstofmonth,@lastofthismonth)
    /* monthend */
    begin
    	select @lastmonthtoday=dateadd(d,-1,@firstofmonth)
    end
    
    --declare @reportcode varchar(15)
    --select @reportcode= case @regid when 3 then 'SCA' when 98 then 'NCA' else null end
    --declare @branches table (branch varchar(15), regid int)
    --select @regid=case when @regid=98 then 3 else @regid end
    --insert into @branches
    --select branch,regid from dbo.BranchDef where regid=@regid
    --			and isnull(reportcode,'')=coalesce(@reportcode,isnull(reportcode,''))
    
    create table #temp (escrowNumber varchar(20),fee_date datetime,titlefee money,escrowfee money, other money, acctcode int,  id integer identity(1,1) primary key clustered)
    
    insert into #temp(escrownumber, fee_date, titlefee, escrowfee, other, acctcode)
    
    select et.gfno
    		,oc.checkdate
    		,sum(case when od.acctcode in (100,200,400)  then od.amount else 0 end)  as titlefee
    		,sum(case when od.acctcode=500  then od.amount else 0 end)   as escrowfee			
    		,sum(case when od.acctcode in (300,350)  then od.amount else 0 end)  as other
    		,case
    				when oc.checkdate is not null
    				then od.acctcode
    		else
    			0
    		end as acctcode
    		from dbo.orders	et 
    			inner join dbo.checks oc  on oc.gfno=et.gfno
    			inner join dbo.checkln od  on oc.checkid=od.checkid
    			--inner join @branches b on et.branch=b.branch and et.regid=b.regid
    			--inner join dbo.ValidPayees vp on et.EscrowBank=vp.Bank
    						--and isnull(stuff(oc.combinedid,1,1,''),'')=vp.AgentNum
    						
     where oc.checkdate between @firstoflastmonth and @today and oc.voiddate is null
    and (oc.payee like 'wfg%' or oc.Payee like 'transfer to file [A-z]%')
    and isnull(stuff(oc.combinedid,1,1,''),'')<>'1085'
    and et.regid=@regid
    and (od.amount between 0 and 10000)       
    and et.GFNo not like '%[A-z]%'
    group by et.gfno,oc.checkdate,od.acctcode
    
    --select * from #temp
    
    insert into #temp(escrownumber, fee_date, titlefee, escrowfee, other,acctcode)
    select et.gfno
    		,im.invoicedate
    		,sum(case when il.acctcode in (100,200,400) then il.amount else 0 end)  as titlefee
    		,sum(case when il.acctcode=500   then il.amount else 0 end)  as escrowfee		
    		,sum(case when il.acctcode in (300,350)  then il.amount else 0 end)  as other
    		,case
    				when im.invoicedate is not null
    				then il.acctcode
    		else
    			0
    		end as AcctCode
    		from dbo.orders et  
    			inner join dbo.invoice im  on et.gfno=im.gfno
    			inner join dbo.invoiceln il  on im.idnum=il.idnum	
    			--inner join @branches b on et.branch=b.branch and et.regid=b.regid		
    			where im.invoicedate between @firstoflastmonth and @today					
    				and et.regid=@regid				
    				and et.gfno not in (select t.escrownumber from #temp t)
    				and il.Amount>0
    				and et.GFNo not like '%[A-z]%'
    			    and ISNULL(im.Initials,'')<>'EST'
    group by et.gfno,im.invoicedate, il.acctcode
    
    /*GHK: 8/3/12 Eliminate SV orders from list */
    --delete from #temp where escrowNumber 
    --			in (select o3.gfno from dbo.orders o3 inner join dbo.orders o2 on o3.OtherFileNum=o2.gfno and o3.regid=3 and o2.regid=2)
    
    --select * from #temp
    
    ;With cte As
    (select	@today as today
    		,utility.dbo.fn_workingdays(@firstofmonth,@today) as workday
    		,case when t.fee_date=@today and  t.escrowNumber not in (select uc.gfno from DistinctCount uc) and (o.kindoforder='T' or af.single1 is not null)
    							then o.gfno else null end as titleonly_today_closed
    		,case when t.fee_date=@today and  t.escrowNumber not in (select uc.gfno from DistinctCount uc) and o.kindoforder='B' and af.single1 is null and af.single2 is null then o.gfno else null end as both_today_closed
    		,case when t.fee_date=@today and  t.escrowNumber not in (select uc.gfno from DistinctCount uc)  and (o.kindoforder='E' or af.single2 is not null) then o.gfno else null end as escrowonly_today_closed
    		,case when t.fee_date=@today and  t.escrowNumber not in (select uc.gfno from DistinctCount uc)  and (t.acctcode in (300,350)) then o.gfno else null end as other_today_closed
    		,case when t.fee_date between @firstofmonth and @today  and  t.escrowNumber not in (select uc.gfno from DistinctCount uc) and (o.kindoforder='T' or af.single1 is not null) then o.gfno else null end as titleonly_mtd_closed
    		,case when t.fee_date between @firstofmonth and @today  and  t.escrowNumber not in (select uc.gfno from DistinctCount uc)and o.kindoforder='B' and af.single1 is null and af.single2 is null
    				then o.gfno else null end as both_mtd_closed
    		,case when t.fee_date between @firstofmonth and @today and  t.escrowNumber not in (select uc.gfno from DistinctCount uc) and (o.kindoforder='E' or af.single2 is not null) then o.gfno else null end as escrowonly_mtd_closed
    		,case when t.fee_date between @firstofmonth and @today and  t.escrowNumber not in (select uc.gfno from DistinctCount uc)  and (t.acctcode in (300,350) ) then o.gfno else null end as other_mtd_closed
    		,case when t.fee_date between @firstoflastmonth  and @lastmonthtoday and  t.escrowNumber not in (select uc.gfno from DistinctCount uc) and (o.kindoforder='T' or af.single1 is not null) then o.gfno else null end as titleonly_lmtd_closed
    		,case when t.fee_date between @firstoflastmonth and @lastmonthtoday and  t.escrowNumber not in (select uc.gfno from DistinctCount uc) and o.kindoforder='B' and af.single1 is null and af.single2 is null then o.gfno else null end as both_lmtd_closed
    		,case when t.fee_date between @firstoflastmonth and @lastmonthtoday  and  t.escrowNumber not in (select uc.gfno from DistinctCount uc) and (o.kindoforder='E' or af.single2 is not null) then o.gfno else null end as escrowonly_lmtd_closed
    		,case when t.fee_date between @firstoflastmonth and @lastmonthtoday  and  t.escrowNumber not in (select uc.gfno from DistinctCount uc) and (t.acctcode in (300,350) ) then o.gfno else null end as other_lmtd_closed
    		,case when t.fee_date=@today 		
    				then t.titlefee else 0 end as title_today_revenue
    		,case when t.fee_date=@today  
    				then t.escrowfee else 0 end as escrow_today_revenue
    		,case when t.fee_date=@today  
    				then t.other else 0 end as other_today_revenue
    		,case when t.fee_date between @firstofmonth and @today 		
    				then t.titlefee else 0 end as title_mtd_revenue
    		,case when t.fee_date between @firstofmonth and @today 		
    				then t.escrowfee else 0 end as escrow_mtd_revenue
    		,case when t.fee_date between @firstofmonth and @today 		
    				then t.other else 0 end as other_mtd_revenue
    		,case when t.fee_date between @firstoflastmonth and @lastmonthtoday then t.titlefee else 0 end as title_lmtd_revenue
    		,case when t.fee_date between @firstoflastmonth and @lastmonthtoday then t.escrowfee else 0 end as escrow_lmtd_revenue
    		,case when t.fee_date between @firstoflastmonth and @lastmonthtoday then t.other else 0 end as other_lmtd_revenue
    		from orders o inner join #temp t on o.gfno=t.escrownumber	
    		left join adfields af   on o.gfno=af.gfno
    		left join DistinctCount uc on o.gfno = uc.gfno
    		--where t.escrownumber not in (uc.gfno)
    		)
    select	today
    		,workday
    		,count (distinct titleonly_today_closed) as titleonly_today_closed
    		,count (distinct both_today_closed) as both_today_closed
    		,count (distinct escrowonly_today_closed) as escrowonly_today_closed
    		,count (distinct other_today_closed) as other_today_closed
    		,count (distinct titleonly_mtd_closed) as titleonly_mtd_closed
    		,count (distinct both_mtd_closed) as both_mtd_closed
    		,count (distinct escrowonly_mtd_closed) as escrowonly_mtd_closed
    		,count (distinct other_mtd_closed) as other_mtd_closed
    		,count (distinct titleonly_lmtd_closed) as titleonly_lmtd_closed
    		,count (distinct both_lmtd_closed) as both_lmtd_closed
    		,count (distinct escrowonly_lmtd_closed) as escrowonly_lmtd_closed
    		,count (distinct other_lmtd_closed) as other_lmtd_closed
    		,sum (title_today_revenue) as title_today_revenue
    		,sum (escrow_today_revenue) as escrow_today_revenue
    		,sum (other_today_revenue) as other_today_revenue
    		,sum (title_mtd_revenue) as title_mtd_revenue
    		,sum (escrow_mtd_revenue) as escrow_mtd_revenue
    		,sum (other_mtd_revenue) as other_mtd_revenue
    		,sum (title_lmtd_revenue) as title_lmtd_revenue
    		,sum (escrow_lmtd_revenue) as escrow_lmtd_revenue
    		,sum (other_lmtd_revenue) as other_lmtd_revenue
    from cte
     GROUP BY today, workday
    
    
    
    		drop table #temp      
    
    Any suggestions?

    Monday, November 11, 2013 10:59 PM

Answers

  • Hi GaryT_Musicman,

    In SQL Server Reporting Services (SSRS), if we want to count field value without duplicate we can use CountDistinct() function to calculate the value. In order to get a Sum value, we can add a total value in the report.

    Suppose we have a dataset with two fields: CheckDate, Amount. Please refer to the steps below:
    1. Add a table in the report body. Add a parent group [CheckDate] in the table.
    2. Add Amount field in the table.
    3. Right click CheckDate in the Row Group dialog box, select Add Total -> After.
    4. Below [CheckDate] field add the below expression:
    5. =CountDistinct(Fields!CheckDate.Value)
    Please refer to the screenshot below:

     
    If you want to verify above query code, I recommend you post the issue to T-SQL forum.

    If there are any misunderstanding, please feel free to let me know.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by GaryT_Musicman Wednesday, November 13, 2013 5:18 PM
    Tuesday, November 12, 2013 11:04 AM
    Moderator

All replies

  • Hi GaryT_Musicman,

    In SQL Server Reporting Services (SSRS), if we want to count field value without duplicate we can use CountDistinct() function to calculate the value. In order to get a Sum value, we can add a total value in the report.

    Suppose we have a dataset with two fields: CheckDate, Amount. Please refer to the steps below:
    1. Add a table in the report body. Add a parent group [CheckDate] in the table.
    2. Add Amount field in the table.
    3. Right click CheckDate in the Row Group dialog box, select Add Total -> After.
    4. Below [CheckDate] field add the below expression:
    5. =CountDistinct(Fields!CheckDate.Value)
    Please refer to the screenshot below:

     
    If you want to verify above query code, I recommend you post the issue to T-SQL forum.

    If there are any misunderstanding, please feel free to let me know.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by GaryT_Musicman Wednesday, November 13, 2013 5:18 PM
    Tuesday, November 12, 2013 11:04 AM
    Moderator
  • I found a solution that works for my requirement.

    I crfeated a temp table to hold previously counted values from the database.

    For a situation where a current order may have multiple checks and invoices, I implemented a Row_Number() based on the ordernumber and ordered by the accounting codes that define bucketing.

    I then only count the first instance that meets the selection criteria for the report. I did not include this code in the revenue area as I want to caount all revenue, not just the first instance of revenue.

    In the counts area I also included this lookup for month to date accounting numbers. Since there is a filter performed initiall if an order has been previously counted, this just covers the orders I'm adding into the month to date total from today.

    Wednesday, November 13, 2013 5:18 PM