Bar Chart with count bars for 7 days even if count = 0

[Former Member]

Hi, (I use CR 10)

I created a bar chart showing counts for orders (based on their creation date), for Order I selected "in ascending order" and "for each day". Now the chart only creates bars for days on which I have a data record created (which makes sense). I need bars for all days of the current week though, even if they show 0 entries. To get only the last 7 days I created a filter selection "{entries.creation_date} > DateAdd ("d", -7, CurrentDate)"

I already got a hint that I shall link a table in database expert which owns data records for every day. I tried this but still do not see Charts with a 0-Value for days without orders.

regards,

Jan

[nikhil_sabnis2 · Accepted solution]

Hi Jan

What you are trying to achieve is only possible if you have the dates to be shown on X-axis. Since you already have the dates in a database table, we are done 50% here.

If you are calculating the count of orders for each day at report level then follow the steps below:

1. Create a formula to get the count of orders per day. Name it "OrdrCnt":

Count({order.order_id},{order.each_Day});

2. Create another formula named "FinalOrdrCnt". Call "OrdrCnt" in this formula as below:

If IsNull({@OrdrCnt}) Then 0 Else {@OrdrCnt}

3. Now use "FinalOrdrCnt" in the Chart Expert as the "Show Value(s)". Use "Do Not Summarize" option.

Let me know if this works.

Regards

Nikhil Sabnis

[Former Member · Accepted solution]

Hi Nikhil,

ok, sounds fine. In "Count({order.order_id},{order.each_Day});" - what is the order.each_day? A field from my database? My field with entries for every day is a column in a different table than "order".

regards,

Jan

[nikhil_sabnis2 · Accepted solution]

Hi Jan

Here I assumed that you have a group in the report based on your every day field. each_day field is the field in your which has the every day data.

The line you are referring to is getting count of orders for each day.

Regards

Nikhil

[Former Member · Accepted solution]

Hi Nikhil,

I tried that: Just to check if the OrdrCnt formula works fine I put it in the report page details. It returns the overall sum of orders for in every order record - this way it can't work, can it?. I think I linked the datetime field table incorrectly to the order table - as there is no key which is connecting both I just linked them on the datetime attribute and put a left join on it. Thinking about that it can't work cause this way all dates which are not in an order are not included.


Using an outer join would return too many entries at the same time, I can't figure out how to set this up.

regards,

Jan

[abhilash_kumar · Accepted solution]

Hi Jan,

Please go through these steps below:

1) You would need to create a look-up table in Excel/Any other database with all the dates. I guess you've already figured this part

2) In the database links tab, go ahead and join both the Date Fields. The join type should be Left Outer Join with the Look-up table being on the left

3) Create a group on the Date field from the Look-up table

4) Go to Group Expert > Options > Select 'For Each Day' for the option 'The Section will be printed'

5) Insert a count at the Group Level. Go to Insert > Summary > Do a Count on the Order field and place it at the Group Footer level

6) Now, go to File > Report Options > make sure 'Convert Database NULL values to default' and 'Convert Other Null Values to default' is UNCHECKED. This is important otherwise Crystal Reports will replace Null Orders with zeroes resulting in the count being 1 as zero is also counted.

7) Go to Insert Chart > Place it on the Report Footer. It will automatically create a Group Chart for you.

I've attached a sample report for your reference. Please have a look at that too.

Once you download the file, extract the zip file and change the extension to .rpt.

Hope this helps!

-Abhilash

Chart.txt.zip

[JWiseman · Accepted solution]

hi Jan,

there are a couple of options at your disposal. one is to create a lookup table like Abhilash suggested. this is a really great method to use if you want to ensure that you have one line on your report for every date.

if you only want to show a bar on each chart (or a space for each bar) for each day of the week then you can follow these steps:

1) create one formula for each day...i.e. create a new formula called Day1 with syntax similar to

if {entries.creation_date} = DateAdd ("d", -7, CurrentDate)

then {the field that you want to summarize}

Day2 would be

if {entries.creation_date} = DateAdd ("d", -6, CurrentDate)

then {the field that you want to summarize}

2) put all 7 formulae into the Show Value of the chart.

3) change the On Change Of to All Records.

now you'll have a bar for each date. you're not quite done though as you probably want a label for each bar...create a formula  for each day with syntax similar to

DateAdd ("d", -7, CurrentDate)

put these labels below your chart and format them to what you want.

i hope this helps. for an example on showing charts for days and times that don't exist in your database please see here.

[nikhil_sabnis2 · Accepted solution]

Hi Jan

You can create a SQL Command withing Crystal Reports as a data source which will give you the list of dates as well as the count of orders for each date, using your tables.

Let's say your table structure is like this:

table1:

Each_Date

table2:

Each_Date

Order_No

Now the query would look like this:

Select table1.Each_Date, NVL(Count(table2.Order_No),0) as OrderCnt

from

table1 Left Outer Join table2

on (table1.Each_Date=table2.Each_Date);

The syntax used above is for Oracle DB. It might be different for other databases. This way you will always have all the dates and the count for each day in place and there would not be a need to count the orders on the report. This should work.

Let me know if this helps.

Regards

Nikhil Sabnis

[Former Member · Accepted solution]

Thanks everybody for the detailed help!

I got it somehow, to get it finally work I had to use a right outer join... my database link goes from the order table to the dates lookup table (arrow direction) and then I chose right outer join. I always thought this is exactly the same as if the arrow goes from lookup to orders and left outer join, but appearently it has another result ?!

Anyway... my biggest mistake in the beginning was that I used a datetime field - a join on this just returns 0 matches (makes sense)

Still not ideal are these two side effects:

1. The applciation I build the reports on is only capable of datetime-fields. Is there a way to build this on a datetime field? I though I could create a formular which converts the datetime to a date, but I have no clue how to build the join on the formula (guess just not possible), would need to build a db trigger then to do the same.

2. I want to create a filter selection so only the last 7 days are taken, therefor I use:

{lookuptable.date} > DateAdd ("d", -6, CurrentDate) which does not work as it requires a datetime format. So I guess I build another formula to convert the date to a datetime and filter on this?

regards,

Jan

[abhilash_kumar · Accepted solution]

Hi Jan,

Glad you've got somewhere with this!

Anyway, why deosn't the field in the Lookup table and the field in the table from the original database have the same datatype in the first place? This way you can do join (Left or right should not be a problem in my opinion).

And,most importantly if you feel that the report returns results Only when you do a right outer join, could you make sure the Join is being enforced when the other join type is used?

So, what you can do is, have the look-up table on the left and enforce the join type to 'Enforce From'. This way you can be sure that the join will work if the report only contains records from the left table alone.

1) You certainly cannot create a join using formulas. Again, I'll ask you this, why don't both fields have the same datatype in the first place?

2) The formula doesn't work because the field is not datetime. Well, change the field to datetime, easy!

Or, use this formula instead:

cdatetime(date(lookuptable.date), time(0,0,0)) > DateAdd ("d", -6, CurrentDate)

Hope this helps!

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

well, the original table just doesnt own any date-only stamp, only datetimes. I can't influence, it's a 3rd party product my customer uses. Now I could have created the lookup table with a datetime column also, but then the join wouldnt work, would it? If I join on datetime then the database will not find any match? It will compare the exact values down to seconds...

You are right, if the join can match the datetime fields based only on the date my two questions are solved.

regards,

Jan

[abhilash_kumar · Accepted solution]

Oh I see what you're saying now!

Yes, you'll never get an exact match because of the time-stamp.

How about you 'cast' both the fields to char and extract just the date parts using a 'Command Object'. This way you should be able to join the fields.

Or, you said the right-outer join works fine for some reason isn't it? Let it be that way and instead use this formula in the report to filter records:

cdatetime(date(lookuptable.date), time(0,0,0)) > DateAdd ("d", -6, CurrentDate).

Let me know how this goes!

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

yes, I did something similar to the casting to char, I created a trigger with copied the timestamp to a date-only field and use this for the join. Just need to re-convert this back to datetime in Crystal for nice filtering, but that's ok. Thought there might be a better way.

regards,

Jan

[Former Member · Accepted solution]

Hi Jamie,

I don't really get this:

if {entries.creation_date} = DateAdd ("d", -7, CurrentDate)

then {the field that you want to summarize}

ok, I understand the first line, just checking if the orders matches with one of the last 7 days. But what shall I put after the "then"? How can I count all orders which got the <is 5 days old> - flag then?

[JWiseman · Accepted solution]

hi Jan,

if you only want to get a count of the orders then you'd use something like

if {entries.creation_date} = DateAdd ("d", -7, CurrentDate)

then 1

if you put this formula in your chart, you'd get one bar representing the number of order entries that are 7 days old....assuming that there is one details line per order.

for 5 days you'd use

if {entries.creation_date} = DateAdd ("d", -5, CurrentDate)

then 1

using 7 formulae in your chart will ensure that you always have 7 bars or at least a space in your chart where there are zero values.

using this method also ensures that you don't have to use a date lookup table to populate missing dates. note though that this only populates the chart...it doesn't actually add details lines. however, if it's only the chart you wish to add, then this method is easy.

cheers,

jamie