Showing every day even when there is no data 1

[Stoffel24]

Hi. I have posted this in the crystal formulas section too! Sorry to those that lurk in both sections. I would like to group by the day of the month such that EVERY day of the month is shown regardless of whether there is data for this day or not.

Firstly, this report is run for a whole month only. (ie only Jan or only Feb etc) I have data reported for samples that are logged in on certain days. On some days, no sample is logged. Therefore, if I group on logged_date, I get gaps in my days. What I would like is for there to be no gaps in the days and when no sample was logged, for there to be no results reported. I have thought about doing the report using a formula returning 1 to 31 for Jan, March, May etc and 1 to 30 for April, June etc and then there's pesky February which I don't even want to start thinking about (Stupid Romans!) But I am not sure this is very elegant nor am I sure how to go about doing this even if I wanted to! How would this formula work? But perhaps there are some among you who have much better ideas...I hope!

Any help would be appreciated!

 

[IdoMillet]

The best approach is to aff a table to your database that has one record for each day. This type of "TIME dimension table" is very often used in data warehouses for this as well as other reasons (pre-compute formulas such as month, quarter, day of week, ...).

After you have this table, you can use an Outer Join from it to your data to ensure all days survive the join.

hth,
- Ido


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Stoffel24]

Thanks Ido. I was kinda hoping to avoid creating a new table but it seems it may be the best route to take. Now as a related question, what is the best way to create this table. I am thinking that we have a table for days with a days column and a month_grp column, so that we can have 1 to 31 for month_grp "Long", 1 to 30 for month_grp "Short" and 1 to 28 for month_grp "Feb". Then we have a separate table showing the months with columns Month and Month_grp so we can create a relationship between the 2 tables. Only problem is, what about the case of a leap year? Do we need a third column for leap_year or something?

Alternatively, I just create a days table with 1 to 31. Then in Crystal I write a formula along the lines of
If the month is April, June... then and days = 31 then suppress
Else if the month is Feb and days > 28 and year is blah blah then suppress

The second option is looking much better! I'll go with that for now but other ideas will be tolerated!

 

[vidru]

Check out this thread: thread183-523971. Might be more than you need, but the basic structure of a calendar table is there.

-dave

 

[kenhamady]

There are several approaches that are outlined in the following newsletter:

http://www.kenhamady.com/news0208.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[IdoMillet]

I think the easiest approach is to use a spreadsheet to build the data and then copy it into a table in your database.

Start by typing the earliest date in a cell. Then seelct and extend a range from that cell down...

Use the menu option of
Edit, Fill, Series
and use the 'Date' radio button to indicate this is a
date series. Excel than populates the dates for you.

You can then use spreadsheet functions such as Year(), Month(), Weekday() to add more columns to the table before you copy it over into the database. In your case, consider using the DAY() function to return the day of the month (1 to 31).

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[zforgetaboutit]

Stoffel24 (TechnicalUser) Oct 30, 2003
I have thought about doing the report using a formula returning 1 to 31 for Jan, March, May etc and 1 to 30 for April, June etc and then there's pesky February which I don't even want to start thinking about (Stupid Romans!)

Well ... how would you have arranged the calendar?

 

[tful282]

I recently had a similar problem- generating records for dates that weren't there, and the solution we came up with uses a pivot table, the idea is outlined here

http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html

Once the table and join are created you can restrict records based on the date i.e. MyDate>='01-Jan-2003' and MyDate<'01-Feb-2003' that way you don't have to worry about leap years, february etc.

 

[synapsevampire]

Use a period table, everything else is kinda kludgey and not long term.

Ken's article speaks of them, but his reasoning for suggesting they not be used is incorrect.

check out my FAQ:

Creating data for dates not in the data set with SQL Server SQL to create a Periods Table

faq767-4532

-k

 

[kenhamady]

Synapsevampire and I seem to get different results on this issue, but I commonly see reports where a criteria placed on the outer table cancels the effect of the outer join. It may be that this only happens when the SQL is generated by Crystal, and it may not happen in all flavors of SQL, but I find it a very common problem among my customers.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

Ken: Do you add in a isnull(field) OR <condition>?

That's the way you wouyld handle it in pure SQL, and I do so from within Crystal occasionally too (as you know I generally develop SQL on the databasse not in Crystal).

-k

 

[kenhamady]

I don't think adding an IsNull in the CR selection formula will help in this example:

I want every day in March to show, but only transaction records in the USA (outer table). There is only one record for the date of 3/15, but it is from Canada. So, the only 3/15 record would look like this in the data set:

Period Date Trans Date Country
----------------------------------------
3/15/2003 3/15/2003 Canada

This is neither NULL nor a USA record, so I don't see how this record could be included. You would lose the only 3/15 record and would have a hole in your date series.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

If you only want USA rows why would you want this row?

Perhaps you could demonstrate real data.

The dates should be in the periods table, and the record selection is against that table for the dates.

If you have child table criteria, try:

isnull({childtable.field})
or
{childtable.field} = &quot;USA&quot;

-k

 

[kenhamady]

You need this row because it is the ONLY row for 3/15. If you lose it, you lose one of your dates in the series. This record is neither Null nor USA.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[IdoMillet]

A UNION ALL statement that explicitly adds all the records from the Time Dimension table (with Nulls in the query columns from the other tables) solves this situation.

Just exercise care if you are counting anything to ensure you are counting a potentially null column (otherwise, you would get a count that is artificially inflated due to the added records).

Another solution is use a COMMAND or SQL query against a data source that allows you to include the extra condition as part of an &quot;ON&quot; rather than a &quot;WHERE&quot; clause.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[kenhamady]

k- here is a demo using the CR sample data. The Employee table will stand in for your periods table.

1) Create a new report with the Employee table, just ID and Name.
2) Add a Distinct Count of the Employee ID - you get 15 Employees.
3) LO join to the Orders table and add Order Date to the report. You should still have 15 different Employees.
4) Filter for Orders after May 1, 1998. This takes you down to 3 Employees.

5) Now the goal is to show all 15 employees (like your period table), but only include transactions in May (filter the outer table). Using:

IsNull ({Orders.Employee ID}) or {Orders.Order Date} > Date (1998,5,1)

as a selection formula gets you back to 12 employees, not 15. It includes 9 more employees that had NO orders ever (NULLs), but skips the 3 Employees whose order history ended in April. Their records are neither NULL nor in May.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

What connectivity was being used?

I just tested using the standard Extreme ODBC with your scenario in CR 9 (I don't have CR 8.5 at this contract) and it returned 15 distinct names.

Here's the SQL from the Database Show SQL Query:

SELECT `Employee`.`Employee ID`, `Employee`.`First Name`, `Orders`.`Order Date`, `Orders`.`Employee ID`
FROM `Employee` `Employee` LEFT OUTER JOIN `Orders` `Orders` ON `Employee`.`Employee ID`=`Orders`.`Employee ID`
WHERE (`Orders`.`Employee ID` IS NULL OR `Orders`.`Order Date`>=#1998-05-02 00:00:00#)
ORDER BY `Employee`.`Employee ID`

If you want a copy of it email me at crystalreports//@//hotmail.com (remove the slashes).

-k

 

[kenhamady]

If you use the sample data from v9 ALL employees have data after May of 1998, so you wont get the same result. However, if you change to May 1, 2002 and you will only get 12 Employees as I described above. The selecton formula will be:

IsNull ({Orders.Employee ID}) or
{Orders.Order Date} > Datetime (2002,5,1,0,0,0)

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

SV-

I just copied your SQL into my show SQL query for the Xtreme Database, and it returns 12 distinct employees and 13 records (EmployeeIDs 3,4, and 9 do not appear, and 7 appears twice). But also, these 13 records and 12 employeeIDs compare to a total of 2,201 records and 15 employeeIDs without the (`Orders`.`Employee ID` IS NULL OR `Orders`.`Order Date`>=#1998-05-02 00:00:00#) select. That is, there are 3 employeeIDs and 2,188 records omitted.

-LB

 

[lbass]

I'm using 8.0. Didn't know the Xtreme database differed between versions...

-LB