Solved

Count function in Crystal

Posted on 2008-10-23
10
1,142 Views
Last Modified: 2011-10-19
Hi.  I need to do a simple record count in CR.
Fields: an integer, a datetime stamp.
I need a count by day/date/year of events each day:
5813      10/24/2008     7
5813       10/25/2008    6

SQL is a cinch:

SELECT     didnumber,LEFT(CONVERT(varchar(11), starttime, 101),11) as start
into temp1
FROM         CallLog
WHERE     (DIDNumber IN (5290, 5813))

select didnumber,start,count(*) total
from temp1
group by didnumber,start
ORDER BY DIDNumber, Start

How is this done in CR Design?
Thanks.
0
Comment
Question by:michaelheffernan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 22792069
You can group the report by date 3 times
For the first select the timeframe as YEAR
Second group timeframe is month
third timeframe is day

Then use summaries at the group level to count the events.

mlmcc
0
 

Author Comment

by:michaelheffernan
ID: 22797940
Hmmmmm.... lemme think about this 'un...
0
 

Author Comment

by:michaelheffernan
ID: 22800211
Hi.  Well, I kinda get the logic.  I don't see how to parse the year/month/day out of this datetime field for each grouping.  CR wants to group the field, period.

How do I parse out each element of the datetime field?
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 101

Expert Comment

by:mlmcc
ID: 22800615
When you create the group, since it is a date field you get an extra option for the interval to group on.

mlmcc
GroupingByDate.jpg
0
 

Author Comment

by:michaelheffernan
ID: 22817498
Ah, different version.
 
I do not have such options.  I so rarely have to do something like this that I have never upgraded CR thru its ownership changes.
This may not be possible with this version.

1.jpg
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 125 total points
ID: 22819068
You need to choose a DATE field.

mlmcc
0
 

Author Comment

by:michaelheffernan
ID: 22827200
Ah, oops, yes.  Okay, lemme try it...
0
 

Author Comment

by:michaelheffernan
ID: 22827213
Ah, I see, I see, sayeth the blind man.  Okay, thanks, I believe I can sort this out; thank you.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22854400
WHy accept that comment as the answer when the comment from the expert is the answer.  

mlmcc
0
 

Author Closing Comment

by:michaelheffernan
ID: 31509479
Thank you.  I probably clicked the wrong link or wrong comment to accept as a solution.
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question