Daily Graphs of activity

I

i. Wiin

I've got a daily based crystal report that doesn't show 0's for days where
no activity occurred. How do I get 0 values to show up in the report?
 
L

Lucas Tam

I've got a daily based crystal report that doesn't show 0's for days
where no activity occurred. How do I get 0 values to show up in the
report?

You need to pad the report with data - you'll need to generate 0 data for
the missing days.
 
I

i. Wiin

How do I generate 0 data for missing days? SQL clause that builds the
DataSet that the report is based off of is "SELECT * FROM table WHERE
table.daterecorded BETWEEN '1/1/2002' AND '6/30/2002'" then Crystal breaks
up the groups by day and displays a count of rows for that day. How Do I
Create A 0 Row For A Day If The Criteria Is Count Of Rows Per Day?
 
I

i. Wiin

Help. Anyone?


i. Wiin said:
How do I generate 0 data for missing days? SQL clause that builds the
DataSet that the report is based off of is "SELECT * FROM table WHERE
table.daterecorded BETWEEN '1/1/2002' AND '6/30/2002'" then Crystal breaks
up the groups by day and displays a count of rows for that day. How Do I
Create A 0 Row For A Day If The Criteria Is Count Of Rows Per Day?
 
L

Lucas Tam

How do I generate 0 data for missing days? SQL clause that builds the
DataSet that the report is based off of is "SELECT * FROM table WHERE
table.daterecorded BETWEEN '1/1/2002' AND '6/30/2002'" then Crystal
breaks up the groups by day and displays a count of rows for that day.
How Do I Create A 0 Row For A Day If The Criteria Is Count Of Rows
Per Day?

You'll need to write a SQL while loop to insert blank data. Time for you to
learn T-SQL!

OR... if you're binding a dataset to the report, you can loop through the
dataset and pad with 0-data.

Try microsoft.public.sqlserver.programming for more info.
 
I

i. Wiin

OK, let's get technical.

Here's the table structure

"ID" int 4, key
"DateRecorded" DateTime
"Location" VarChar 64

The report is counting "hits per day" regardless of location.

Here's some possible data
7683 12/10/2003 4:10:20 Kitchen
7684 12/10/2003 7:55:12 Laundry Room
7685 12/10/2003 14:34:02 Patio
7686 12/11/2003 5:02:56 Kitchen
7687 12/13/2003 9:00:49 Patio
7688 12/14/2003 12:30:00 Back Door

Notice there's no data collected for 12/12/2003. How does 0-data go in?
How will the report be smart enough to make it a 0 for 12/12? If I enter a
row like "7689 12/12/2003 00:00:01 0", it's going to report a "hit" (1) for
the 12th when there shouldn't be anything. It's the Crystal Report that is
grouping by day a count, not any t-sql.

There must be a setting in CR, that tells it to "pad" days with 0 counts.
 
I

i. Wiin

Seriously, How do I put 0-data in my table to get it to work?

Or is there a setting in CR, when grouped by day, to show "missing" days?


i. Wiin said:
OK, let's get technical.

Here's the table structure

"ID" int 4, key
"DateRecorded" DateTime
"Location" VarChar 64

The report is counting "hits per day" regardless of location.

Here's some possible data
7683 12/10/2003 4:10:20 Kitchen
7684 12/10/2003 7:55:12 Laundry Room
7685 12/10/2003 14:34:02 Patio
7686 12/11/2003 5:02:56 Kitchen
7687 12/13/2003 9:00:49 Patio
7688 12/14/2003 12:30:00 Back Door

Notice there's no data collected for 12/12/2003. How does 0-data go in?
How will the report be smart enough to make it a 0 for 12/12? If I enter a
row like "7689 12/12/2003 00:00:01 0", it's going to report a "hit" (1) for
the 12th when there shouldn't be anything. It's the Crystal Report that is
grouping by day a count, not any t-sql.

There must be a setting in CR, that tells it to "pad" days with 0 counts.
 
L

Lucas Tam

Seriously, How do I put 0-data in my table to get it to work?

Or is there a setting in CR, when grouped by day, to show "missing" days?

I TOLD YOU. YOU NEED TO PAD THE DATA.

The bundled version does not have the ability to fill in data. I'm not sure
if the full version does either...
 
I

i. Wiin

I'm still not sure how to "fill with 0 data".

Did you see the structure of the database I posted? Can you please show me
how to pad a 0 into it without Crystal Reports counting it as a "hit"?

I'm beginngin to think you have NO idea what you're talking about. Either
show how to pad with 0 as I have requested.

I've gone as far as posting the SQL query I use to fill the dataset that is
passed to the Crystal Report. I've given you the datastructure of the
table. I've given you sample data. And yet all you can say is to fill with
0 data. I've told you that if I place a new record into the table with a
date, then Crystal Reports counts it as a "hit" and shows a 1. I jsut want
CR to show a 0 for any date that has no data that is inside the date range.
 
L

Lucas Tam

I'm still not sure how to "fill with 0 data".

Did you see the structure of the database I posted? Can you please
show me how to pad a 0 into it without Crystal Reports counting it as
a "hit"?
"ID" int 4, key
"DateRecorded" DateTime
"Location" VarChar 64

ID as your ID or Null
DateRecord - Date of missing record
Location as Null

When you out in Crystal, Location will be null, thus the date will be
display but a blank entry will be shown.

When doing a Select Count(Location), Null locations will be ignored due
to the way Count works (unless you want to include with with Select
Count All.

If Crystal is counting null entries (i.e. you're using Crystal's count
methods), write a custom count function it ignore all locations or IDs =
null.

I'm beginngin to think you have NO idea what you're talking about.
Either show how to pad with 0 as I have requested.

Gee thanks.
 
L

Lucas Tam

When you out in Crystal, Location will be null, thus the date will be
display but a blank entry will be shown.

That should be when you output in Crystal.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top