Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Time Calculations/Query Expand / Collapse
Author
Message
Posted Sunday, December 01, 2013 2:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 14, 2014 1:21 PM
Points: 21, Visits: 50
Hi I'm working on a report in SSRS. I have a dataset that uses query 1 below and works fine. The problem I run into is when I expand the where clause, as is shown in the 2nd query, SSRS stops displaying the time calculation for ATA correctly. I've tried using combinations of "case when" and isnull in the denominator of the ATA calculation to deal with dividing by 0 or empty data, but the calculation continues to be incorrect in SSRS for the 2nd query. Any tips would be greatly appreciated. I've included sample data below. The correct ATA value for Query 2, for Oct 13 should be 01:24.


-----Query 1-------


select

a.[MonthID],a.[VDN], g.[QVDN],q.[Row],q.[Month],q.[FK_VDN],


cast(convert(datetime,(sum(cast(convert(datetime,g.[Avg Abandoned Time]) as float)*convert(int,g. [Abandoned Calls])))

/sum(convert(int,g.[Abandoned Calls]))) as time(0)) as "ATA"



from [VisaNA_CallVol].[dbo].[SigTableHeaders] as q



Full Outer join

[VisaNA_CallVol].[dbo].[VisaNAProdMonthID2] as g

on q.[MonthID] = g.[MonthID] and q.[FK_VDN] = cast(g.[QVDN] as float)


Full Outer join


[VisaNA_CallVol].[dbo].[VisaMonthCallVol] as a

on q.[FK_VDN]=a.[VDN] and q.[MonthID]=a.[MonthID]

WHERE q.[FK_VDN] IN ('2014572','2014552','2014580','2014635','2014607','2014608')

Group By a.[MonthID],a.[VDN], g.[QVDN],q.[Row],q.[Month],q.[FK_VDN]





----------Query 2 Expanded Where Clause-------


select

a.[MonthID],a.[VDN], g.[QVDN],q.[Row],q.[Month],q.[FK_VDN],


cast(convert(datetime,(sum(cast(convert(datetime,g.[Avg Abandoned Time]) as float)*convert(int,g.[Abandoned Calls])))

/sum(convert(int,g.[Abandoned Calls]))) as time(0)) as "ATA"



from [VisaNA_CallVol].[dbo].[SigTableHeaders] as q



Full Outer join

[VisaNA_CallVol].[dbo].[VisaNAProdMonthID2] as g

on q.[MonthID] = g.[MonthID] and q.[FK_VDN] = cast(g.[QVDN] as float)


Full Outer join


[VisaNA_CallVol].[dbo].[VisaMonthCallVol] as a

on q.[FK_VDN]=a.[VDN] and q.[MonthID]=a.[MonthID]

WHERE q.[FK_VDN] IN ('2014579','2014568','2014571','2014566','2014551','2014600','2014633','2014634','2014572','2014552','2014580','2014635','2014607','2014608','2014583','2014575','2014555','2014585','2014577','2014557','2014611','2014586','2014578','2014558','2014613','2014584','2014576','2014556','2014581','2014573','2014553','2014609','2014640','2014641','2014582','2014574','2014554')




Group By a.[MonthID],a.[VDN], g.[QVDN],q.[Row],q.[Month],q.[FK_VDN]


-------SigTableHeaders ddl------

CREATE TABLE [dbo].[SigTableHeaders](
[Row] [nvarchar](255) NULL,
[Month] [float] NULL,
[MonthID] [float] NULL,
[FK_VDN] [float] NULL
) ON [PRIMARY]



--------SigTableHeaders Sample Data-------

SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL
SELECT'Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL
SELECT'All Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL
SELECT'Calls Offered to VDN 800-953-7392','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN 888-877-1021','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN 630-350-4551','41548','1','2.01455e+006',UNION ALL
SELECT'Calls Offered to VDN 800-831-2651','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN 630-350-4552','41548','1','2.01455e+006',UNION ALL
SELECT'Calls Offered to VDN 888-427-5056','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN 630-350-4553','41548','1','2.01455e+006',UNION ALL
SELECT'Calls Offered to VDN 877-257-8087','41548','1','2.01457e+006',UNION ALL
SELECT'Calls Offered to VDN 630-350-4554','41548','1','2.01455e+006',UNION ALL
SELECT'Calls Offered to VDN 877-252-4268','41548','1','2.01458e+006',UNION ALL
SELECT'Calls Offered to VDN 630-350-4555','41548','1','2.01456e+006',UNION ALL
SELECT'Calls Offered to VDN 877-252-4279','41548','1','2.01458e+006',UNION ALL
SELECT'Calls Offered to VDN 630-350-4556','41548','1','2.01456e+006',UNION ALL
SELECT'Calls Offered to VDN 800-990-9601','41548','1','2.01458e+006',UNION ALL
SELECT'Calls Offered to VDN 630-350-4557','41548','1','2.01456e+006',UNION ALL
SELECT'Calls Offered to VDN 866-807-3981','41548','1','2.01458e+006',UNION ALL
SELECT'Calls Offered to VDN 630-350-4558','41548','1','2.01456e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01455e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01455e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01455e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01455e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01458e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01456e+006',UNION ALL
SELECT'Hangups During Prompter','41548','1','2.01458e+006',



-------VisaNAProdMonthID2 ddl---------


CREATE TABLE [dbo].[VisaNAProdMonthID2](
[MonthID] [varchar](50) NULL
[QVDN] [varchar](50) NULL,
[Abandoned Calls] [varchar](50) NULL,
[Avg Abandoned Time] [varchar](50) NULL,
) ON [PRIMARY]



------VisaNAProdMonthID2 Sample Data------


SELECT'1','','183','0:01:22',UNION ALL
SELECT'1','','28','0:01:10',UNION ALL
SELECT'1','2014587','28','0:01:10',UNION ALL
SELECT'1','','3','0:00:04',UNION ALL
SELECT'1','2014603','3','0:00:04',UNION ALL
SELECT'1','','3','0:01:08',UNION ALL
SELECT'1','2014594','3','0:01:08',UNION ALL
SELECT'1','','141','0:01:24',UNION ALL
SELECT'1','2014579','134','0:01:26',UNION ALL
SELECT'1','2014580','6','0:01:02',UNION ALL
SELECT'1','2014581','1','0:00:02',UNION ALL
SELECT'1','2014585','0','0:00:00',UNION ALL
SELECT'1','2014586','0','0:00:00',UNION ALL
SELECT'1','','8','0:01:55',UNION ALL
SELECT'1','2018133','4','0:01:06',UNION ALL
SELECT'1','2018134','4','0:02:44',UNION ALL
SELECT'1','','0','0:00:00',UNION ALL
SELECT'1','2014649','0','0:00:00',UNION ALL
SELECT'1','','0','0:00:00',UNION ALL
SELECT'1','2018185','0','0:00:00',


------VisaMonthCallVol ddl----

CREATE TABLE [dbo].[VisaMonthCallVol](
[MonthID] [varchar](50) NULL,
[VDN] [varchar](50) NULL,

) ON [PRIMARY]


------VisaMonthCallVol Sample Data--------


SELECT'1','2014551',UNION ALL
SELECT'1','2014566',UNION ALL
SELECT'1','2014571',UNION ALL
SELECT'1','2014572',UNION ALL
SELECT'1','2014573',UNION ALL
SELECT'1','2014607',UNION ALL
SELECT'1','2014608',UNION ALL
SELECT'1','2014609',UNION ALL
SELECT'1','2014633',UNION ALL
SELECT'1','2014634',UNION ALL
SELECT'1','2014635',UNION ALL
SELECT'1','2014557',UNION ALL
SELECT'1','2014558',UNION ALL
SELECT'1','2014574',UNION ALL
SELECT'1','2014575',UNION ALL
SELECT'1','2014576',UNION ALL
SELECT'1','2014577',UNION ALL
SELECT'1','2014578',UNION ALL
SELECT'1','2014613',UNION ALL
SELECT'1','2014640',UNION ALL
SELECT'1','2014641',
Post #1518687
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse