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!

 

[kenhamady]

The primary change to the Xtreme.MDB made with v9 was that they moved all the order dates to be 4 years later, and some of the dates now have time values as well.

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]

If you run Verify Database with a report that was created with the 8.5 "Xtreme sample database" against the newer "Xtreme Sample Database 9" (2 different ODBC DSNs) you would notice that the process finds some table changes.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

Ahhh, I see your complaint, this is the same thread we had before.

You are explicitly stating that you do NOT want those 3 dates, the record selection (SQL) does preserve the left outer just fine (which was your old statement), and without having looked at the data first, I thought that your problem was that the Left Outer wasn't preserved.

What you are saying is that you want a data element altered to a suppressed or nulled state if it does (or not) match some criteria, which is NOT how it works. The Left outer has passed correctly as evidenced by the null rows.

Your record selection criteria should be used to only filter rows in this instance, not expected to ignore valid data and what you explicitly requested, otherwise you'd have bigger complaints as a result. If you had 2 rows for emp 9, one of which was 12/1/2003 that row would be returned, honoring your criteria.

LB: Don't copy and paste SQL and expect consistent results.

-k

 

[lbass]

I only copied your SQL in this instance to show that I was using the same select--it's not my general practice.

I believe a left join is ordinarily used when the goal is to return ALL records in the left table, and I think when someone then tries (mistakenly) to put a select on the right table, it is usually with the goal of displaying selected values from that table, while maintaining all of the values in the left table.

While there might be some cases when you want to see only a subset of the records in the left table with nulls and some selected values from the right, the logic for doing this is less clear--although I have used this approach myself when I thought the nulls might represent cases which would have met my other criteria, had data been entered--but then my goal was not to return all records either.

I think your definition for a left join is based on whether nulls from the right-hand table appear, even if only partial records appear from the left table, while I would say a left join is truly a left join only if all records from the left table appear. Maybe they could better be distinguished as partial or complete left joins.

-LB

 

[kenhamady]

-k,

I think you missed the point. The goal is to is to show all groups in the report. I am showing you why your periods table technique will NOT keep all groups in the report if you put any criteria on the outer table. In my example we lost employees. In your example you would lose dates. The behavior will be the same.

As I stated in my article, using a periods table to generate the missing groups will ONLY work if you don't put selection criteria on the outer table. Any time you need to add criteria on the outer table your LO Join is not going to be able to keep all groups in the report. Adding IsNull with the LO Join only adds groups that have NO records at all. It does not guarantee that ALL groups will show up.

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]

I suggest you take this up in a SQL forum as I've been through this topic many times here (and in data warehousing and database forums), and some people just don't get it.

You state that you want all rows but you want to limit the rows sometimes, sort of...and the results aren't what I want, this time...sort of.

Try using a LO and NOT using a child table filtering criteria because that's NOT what you want, it's a mistake in design of SQL, not a problem with SQL, or use a SQL Expression to alter the date, or use real SQL (View) and a subquery, all of which resolve this issue you have with how you think SQL might be made more usable.

The Period table is sound, it's part of every major data warehouse, but you'll need to use SQL properly to take advantage of it.

If you can't use SQL for whatever reason, then I can understand Ken's prefered methods, but if you want good performance and a slick way to do things, use it, and don't worry over the child table filtering, just design intelligently and you'll be fine.

-k

 

[synapsevampire]

BTW, I receive more emails thanking me for this tip than any other here. Many of which reference that this replaced various hard coded methods, and solved other problems as well.

If you don't like the industry standard, than continue to hard code everything, but I urge you to try it with a bit more sophisticated SQL, you'll be better served in the long run.

-k

 

[IdoMillet]

SV, I don't believe Ken is arguing against the "Time Dimension" approach. He is simply pointing out that if users apply selection criteria on the right hand side of the join they may find that some rows from the Left Hand side are missing. This is an issue when the user wants all rows from the time dimension to appear in the result set.

My point was that this issue can be addressed either via a UNION ALL statement or by moving the criteria into the ON clause rather than the WHERE clause of the SQL statement.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[synapsevampire]

Good points, Ido.

Although if you read Ken's article on the topic, he promotes hard coding this type of solution, whereas the problem he states isn't really a problem, it's SQL.

You can code around it several ways, from Crystal the simplest is to NOT use a Where criteria at all, since you don't really want one. The problem here is in not understanding that a LO does not mean that it overides all other criteria, if it did, you'd be in BIG trouble on the other side of the coin.

The On is a reasonable solution too if you're directly modifying the SQL and don't require a dynamic solution.

Consider using a View for the child data set and an SP with a parameter (or parameters) which can be used exclusively by the child tables only.

-k

 

[kenhamady]

-k,

I still think my point in the article is correct, and I think you mislead people when you say the article is not correct. the article doesn't say that periods tables aren't useful. It reminds people that they can't have it both ways. I think you would be surprised how many folks try to do exactly that, and think that simply setting the LO in CR solves the problem, or that adding IsNull solves the problem.

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: This is what I took exception to:

"However, there is a weakness to this approach. If you put ANY criteria on the transaction fields, you cancel the effect of the outer join."

Which is wrong, it should state that if you limit the rows in the child to not allow those from the LO main table you will not return the Main table rows, further the coders need to understand understand how the LO will work with child criteria and that it's in accordance with your requirements.

Adding isnull() will preserve the LO join, but many people will then add a criteria limiting main table rows thinking that it will miraculously convert data rather than honor the criteria - bad guess

<shrug>

Guess we beat this subject to death (again)

-k

 

[kenhamady]

I still disagree. Look again at my example. My main table was employees and I didn't add any criteria to the main table. I only added one criteria to the child (outer) table, a date range. I can no longer do anything (in CR) that maintains all employees.

Adding that one criteria on the child table (date range on orders table) made us lose most of the employees, despite using an outer join. Then, even after adding the IsNull, I am still missing 3 employees.

Now, if my employees table was your periods table, and my criteria on the orders table were something like &quot;only show FEDEX orders&quot;, I would have the same problem and would not &quot;Show every day&quot; like the title of this thread.



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: You state &quot;If you put ANY criteria on the transaction fields&quot;, which is wrong, not all criteria will do this.

The fact that you shouldn't be limiting the child rows given this data and your requirements for what's returned is what eludes you, and the fact that you say any condition will cause this to happen is also wrong.

Hence my complaint.

You might suggest that if the coder believes that adding a criteria to the child will cause SQL to ignore the criteria because there's a LO, they'd be incorrect.

The point is, don't add the criteria if you want all of the main rows. You might use a SQL Expression to conditionally change dates to nulls, use a subquery, etc.

-k

 

[Reebo99]

OK boys, break it up.......

It's been fun watching, but I feel I must put a stop to it now......

To solve this do a right outer join, and then you can specify in the record selection :

Isnull({Data.Name}) or {Data.Name} = &quot;Whatever&quot;

This makes the dates table the child, but it will show all records.....

Reebo
UK

&quot;Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.&quot;
- Albert Einstein (1879-1955)

 

[kenhamady]

Reebo, sorry but if you don't start with my Employee table there is NO way to get all Employees (or to get all of k's dates in the period table).

k, here is a better illustration - just like the one that started this thread:

Using the Orders table in Extreme. Customer wants a bar chart of Fedex orders for April, by Day. He wants to see every day in April in the chart, so you go to a periods table with all dates in April. Adding the criteria for Fedex (in Crystal), or any other criteria that doesn't have every day represented in the data, will prevent your periods table from working. The periods table, even with the IsNull, can't get the days that have no Fedex records. You would have to do something in SQL. However, if you DON'T add the Fedex Criteria (in CR) then the LO Join alone will show all dates.


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]

Reebo: Break what up, Ken and I aren't fighting, we're having a civilized discussion, and any other intelligent input is appreciated.

And I do appreciate your input, but as Ken pointed out, you're not even close to understanding the issue, much less resolving it.

Ken: You're correct in your example, and I wasn't arguing your example as being capable of returning dates that you've filtered out, the point is you should NOT be adding a filter criteria at that level of the query, SQL will filter the rows since you asked it to. If there's more than
one row, and the other row is within the criteria, than it obviously will return that row.

This is a common misunderstanding of how it works, not a bug.

You can return the rows in other ways, but the point is, you DO want all rows, you just want certain dates to display as null, not rows greater than a certain date.

-k

 

[kenhamady]

You wrote:

&quot;you should NOT be adding a filter criteria at that level of the query&quot;

I agree. But anytime a user (in Crystal) adds a filter on the outer table, CRYSTAL puts the filter in at that level, just like in my examples. I see users do this all the time and they wonder why the complete list of dates goes away. That is why my articles says that they can't do this from Crystal. I know it isn't a bug in SQL, and I know that it is a common misunderstanding.

Again, my article is pointing out that the periods table technique (when used within Crystal) has the limitation shown in my example. A period table works well within Crystal if you don't need to filter the outer table. But it can't be used from Crystal if the report requirements include any filtering on the outer table. When that is part of the requirement you can't simply add a periods table to the report and use a LO join. So, I think my reasoning is right on. The fact that there are other solutions doesn't make my article incorrect.

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