在给定时间段内每天计算开放票-Crystal Reports 11.0.0.895 [英] Counting open tickets for each day for a given period - Crystal Reports 11.0.0.895

问题描述

我的任务是创建一个报告，该报告统计过去365天每天的开放式服务票。如果没有完成日期，或者完成日期大于所评估的日期并且所创建的日期小于或等于所评估的日期，则票证在任何给定的日期被分类为开放票。我的问题是我没有要在报告中分组的日期字段，因为考虑到我正在计算未结故障单并且在给定的一天中可能没有创建或完成的故障单，因此无法在已创建或已解决的日期字段中进行分组。

I am tasked with creating a report which counts open service tickets each day for the last 365 days. A ticket is classified as open for any given day if there is no completed date, or the completed date is greater than the day being evaluated and the created date is less than or equal to the day being evaluated. My problem is that I have no date field to group on in the report as I cannot group on the created or resolved date fields given that I am counting open tickets and potentially no ticket was created or completed on a given day.

我尝试创建一个每天列出的命令，然后不加任何连接将其添加到报表中，这当然会创建笛卡尔积，但这会炸毁报表并导致长时间运行。如何在给定期间（在本例中为过去的365天）的每一天中每天创建一个组，在该组中可以放置公式来计算未结票据？

I have tried creating a command which lists everyday and then adding it to the report with no join which of course creates a Cartesian product but this blows the report up and takes way to long to run. How can I create a group for each day for a given period (in this case the last 365 days) in which I can place my formula to count open tickets?

是有解决此问题的方法，对您的帮助非常感谢。我正在使用Crystal Report版本11.0.0.895

Is there a solution to this problem, any help greatly appreciated. I am using Crystal reports version 11.0.0.895

推荐答案

这可能是您最好在数据库（或查询/ Crystal Reports中的命令）。作为草图：

This is a task you probably best solve in the database (or a query/command in Crystal Reports). As a sketch:

1. 每天要查看的行都有（也许使用日期表）


2. 每个从该表的第二天中选择符合您的条件的票证编号（ count ）

1. Have a row for each day you want to look at (maybe use a date table)
2. For each day from that table select the number (count) of tickets that match your criteria

这样，您将拥有一个始终包含365行的结果集，因此您的报告中不会出现任何问题。如果当天没有门票，则其中某些行可能包含0。

This way you will have a resultset that always contains 365 rows, so there will be no problems in your report. Some of the rows may contain 0 if there are no tickets for that given day.

下面是一个示例。它使用日期表。它显示了两种获取给定日期的机票信息的方法：

Below is an example. It uses a datetable. It shows two ways to get your ticket information to a given date:

• a子选择：这提供了每个日期的未售票数，没有多不少


• 门票已加入该日期。通过这种方式，您的结果集中所有票证信息都可以/可用。计数和分组可以在SQL中或在您的报告中完成（按严重性，已分配用户，...分组打开故障单）

已查看where / join条件（已打开< =日期和（closed为null或已关闭> =日期））它们是否完全代表您想要的内容（如果将在当天关闭的票证算作当天的开放，那么在同一日期打开和关闭的票证将算作已打开，....

Have a look at the where/join conditions (opened <= adate and (closed is null or closed >= adate)) whether they represent exactly what you want (should a ticket closed on date be counted as open for that day, will a ticket open and closed on the same date ever count as open, ....

create table #dates (adate date);
insert into #dates (adate) values 
('2017-06-23'),
('2017-06-22'),
('2017-06-21'),
('2017-06-20'),
('2017-06-19')


create table #tickets (id int, opened date, closed date);
insert into #tickets (id, opened, closed) values
(1, '20170620', null),
(2, '20170620', '20170622'),
(3, '20170621', '20170622'),
(4, '20170624', null)

-- just the open tickets per day
select 
    adate 
    ,(select count(id) from #tickets where opened <= adate and (closed is null or closed >= adate)) open_tickets
from #dates
left outer join #tickets on opened <= adate and (closed is null or closed >= adate)

-- tickets joined to date, all ticket information available
select 
    adate 
    ,#tickets.*
from #dates
left outer join #tickets on opened <= adate and (closed is null or closed >= adate)

drop table #tickets
drop table #dates

这篇关于在给定时间段内每天计算开放票-Crystal Reports 11.0.0.895的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！