How do I display all 12 Months in a chart?

[Former Member]

Is there a way to display ALL 12 months (including future months) in a Crystal Reports chart, when the raw data does not include all 12 months. The issue I am running into is that I have a small data set and it only displays the months from the date field selected for the chart. The following is an example of the months displayed:

January

March

June

July

September

November

Is it possible to display the "missing" months on a Crystal Reports chart? If so, how do I force the missing month's values?

If it's not possible, would someone please reply to this post to validate my thoughts.

Thank you in advance for your help!

[Former Member · Accepted solution]

There is a way to do this - I have seen it posted here but I do not remember. Try searching for it. If I find it, I will be back

[Former Member · Accepted solution]

I just found Jason's and Jamie's solutions but Jason is here now... (Hi J)

[Generating Calendar Months for the year|]

[JWiseman · Accepted solution]

hello LuAnn, Debi, & Jason,

i have attached a sample report wherein the chart uses a set of formulae so that each month is populated and you don't need to change your database...although changing the database to have a set of good lookup tables is always best

you can copy the formulae to your existing report, change the formulae variables to match your fields, and then change your chart to match the sample.

you could also bring in this sample as a subreport to your existing report and then copy the chart itself over to the main report if you want.

please note that since you would be using a chart with multiple series, that the labels don't show up as desired...in the sample i have created another set of formulae that you can use for the labels.

cheers,

jamie

[Former Member · Accepted solution]

Excuse the interuption, but..

Jamie, how does one attach a document to a post???

[Former Member · Accepted solution]

Get a job at SAP.

[Former Member · Accepted solution]

LOL...

[JWiseman · Accepted solution]

hi Debi,

i just found out from the powers that be that look after sdn...you have to be a forum "moderator" in order to post attachments to forums. which is too bad as there are many people such as yourself who provide tons of valuable answers and assistance for the forums and a sample report can be worth much more than a thousand words. plus it's easier to put some code into a .txt object so that the forum post doesn't mangle it.

if you wish i can find out how you can become a moderator...the bad part of that is that a moderator has specific requirements for forum tasks.

jamie

[Former Member · Accepted solution]

>

> hi Debi,
> 
> i just found out from the powers that be that look after sdn...you have to be a forum "moderator" in order to post attachments to forums. which is too bad as there are many people such as yourself who provide tons of valuable answers and assistance for the forums and a sample report can be worth much more than a thousand words. plus it's easier to put some code into a .txt object so that the forum post doesn't mangle it.
> 
> if you wish i can find out how you can become a moderator...the bad part of that is that a moderator has specific requirements for forum tasks.
> 
> jamie

Thank you Jamie for the vote of confidence to think I could be a moderator. I'm not sure I have sufficient knowledge to do this, but if I had the time to dedicate to it, I would try.

[Former Member · Accepted solution]

Hi Jamie,

Apologies for the delayed response to the threads - I've been on vacation. I tried opening as well as saving the Crystal Report you attached - and either way the report won't open. I have CR 2008. Am I doing something wrong? Please advise. Thanks!!!!

[JWiseman · Accepted solution]

hi Luann,

i have attached the report again as a .zip file.

if you can't open it in your version of cr2008 please patch your cr2008 designer to the latest Service Pack level and then apply the latest Fix Pack level using the Download tab on your cr2008 start screen.

jamie

[Former Member · Accepted solution]

Hi Jamie,

I clicked on the link for the zip file and it appeared it was downloading. However when it was finished nothing popped up on my screen to extract the winzip file.

Would you mind pasting the formulas in the thread? I have the BO Client Tool Suite 3.1 on my machine and ran into several issues with the install. I am afraid if I download the latest CR 2008 patch that something will break in the client tools. Please let me know. Thanks again!!!!!

[JWiseman · Accepted solution]

formula 1

// set the 2 variables below to your fields

datetimevar dtv:= {Orders.Order Date};

numbervar nv:= tonumber({Orders.Order Amount});

// you don't need to change the code below

if month(dtv) = 1

then nv

formula 2

datetimevar dtv:= {Orders.Order Date};

numbervar nv:= tonumber({Orders.Order Amount});

if month(dtv) = 2

then nv

repeat for all other months

put all 12 in a new chart

manually create labels for the bottom of the chart.

jw

[Former Member · Accepted solution]

Hi Jamie,

I created 12 different formulas (changed the variables according to my report) for each month and called them DateJan, DateFeb, and so on. I inserted a new chart, on the Data tab I selected the database date field under "On Change Of", and under Show Values is where I listed the counts of DateJan, DateFeb, etc. and something isn't right. It's a bar chart, and there are several bars under each month, and it still does not display the missing months. Please advise. Thanks!

[JWiseman · Accepted solution]

hi LuAnn,

use the For All Records in the On Change Of...this eliminates any grouping in the X Axis. you will then have a chart with 12 values or at least spaces for 12 bars in the case of 0's.

jw

[Former Member · Accepted solution]

Hi Jamie,

In the chart expert I switched from "On Change Of" to "For All Records" and there are no bars on my graph? Sorry - I'm somewhat of a newbee when it comes to charts in Crystal. Not sure what I've done wrong. :o(

[Former Member · Accepted solution]

Hi Jamie,

I displayed the data point values and they are all the same number (12) across the chart - which is the total number of records the report. There are 11 bars that are the same value. Don't know if that helps. Please advise. Thanks again!!!!!!

[Former Member · Accepted solution]

Hi again,

I figured out the issue - it was with the data. I needed perform counts on ticket numbers by month. I wrote additonal formulas against the variable formulas you sent me and that worked!

Two final questions - how do I create manual labels at the bottom of the chart? Is that as simple as inserting a text box? When I changed from bar chart to a line chart I didn't see the line - only numbers and they were stacked - do you know why that is? Please let me know, and THANKS again for all your help!!!!!

[JWiseman · Accepted solution]

hi LuAnn,

you will indeed have to build the labels manually using text objects.

unfortunately you can't use a line chart as you have to use 12 different Show Values / Series in order to have the spacing for each bar when there's no data. each series has only 1 value and therefore no lines, just a single point for each series.

perhaps you can plead your case to your database administrator to add in a date lookup table. the best solution for report development is to always have a lookup table in the database that has all dates within the many years that you would report off of. you can then avoid annoying workarounds related to missing data like this one

cheers,

jw

[Former Member · Accepted solution]

Thanks Jamie! Do you know much about Webi?

[Former Member]

It all depends on what's available in your current data set. Does your data set have a month column?... or is it being calculated from a full date field?

Jason