Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: CR charting query Post Reply Post New Topic
Page  of 2 Next >>
Author Message
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Topic: CR charting query
    Posted: 30 Mar 2009 at 7:38am
Hi.

I have a database with the following fields:

'companyname' which contains the values:

company1
company2
company3
company4
company5
company6

'value' which contains the company values:

11
14
16
13
72
26

and a 'date' field.

I have created a CReport that displays information between the dates specified. I am displaying a bar chart with the company names on the x-axis and values on the y axis.

So i specify a 'from' and 'to' date to display the data and it returns a graph with data.

On the x-axis is company3, company4, company5. This then means that company1, company2 and company6 have a value of zero between the dates specified, thus the graph doesnt display those company names.

Im wondering how i can get it to display all the company names (company1 to company6) and for company 1, company2 and company6, just display a value of zero?

Please can you help me with this?

thank you.

J

J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Mar 2009 at 7:48am

by using a paramter to filter your data it will exclde the records and therfore not appear in the chart becasue the data is not available in the record set.

One possible option:
Instead of filtering by your parameter create an If-then formula field to "flag" the reocrds that fall in the date range.
Not sure what your paramters are but the formual will be almost the same as your select statement.
if "parameter criteria met here" then 1 else 0
now instead of counting the records do a sum of the formula.
This will return a total number of times that each record meets the conditions which should include the instances of 0.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Mar 2009 at 7:51am
If you want it to display the compny "value" instead of a count change the formula to be something like:
if "parameter criteria met here" then {table.companyvaluefield} else 0
You can also temporarily place this formula into your details to make sure it is working correctly and giving you the values and 0 results you need fo the chart.
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 30 Mar 2009 at 7:59am
Thank you for replying.
 
Unfortunately, I must use a 'date' parameter to specify the information from a certain date to a certain date as the report is intended to be used by many people so that they can just specify a 'from' and 'to' date and display the information for between those dates.
 
Can you confirm that if I use a 'to' and 'from' parameter in my report, that this will ultimately filter out every piece of data getting into my report apart from that data between those dates and thus, i will not be able to add ALL the company names?
 
thanks.
 
j
J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Mar 2009 at 8:18am
My suggestion still allows you to use a process that end users choose the date range (parameters) but the parameters would change your "flag" counts rather than filter the data istelf as it does in the select statement.
You use the paramaters in your fomula field rather than in your select statement.
Yes, whenever you use any criteria in the select statement the data that does not meet your criteria is excluded from the report.
You can also further use these to conditionally suppress data for viewing but still have them appear in your charts.
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 30 Mar 2009 at 8:25am
ok so i have 2 parameters already created called 'fromdate' and 'todate'. How do i get it to work that these are not used to 'filter' out the data, but rather are used for 'manually' filtering the data out myself. (ie by formulas)?
 
 
cheers
 
J
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 30 Mar 2009 at 8:26am
i apologise if my questions seem a bit 'silly', but i am rather new to CR.
J
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Mar 2009 at 8:29am
No problems.
Can you post your select statement that "worked' other not showing your zero values (this will help me with the formula)
and
are you displaying the companyvalue field in your chart or the number of times the company appears in the report (y axis)?
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 30 Mar 2009 at 8:42am

Hey thanks for your advise. I tried what you said and it worked a treat........ Now for the bad news haha. I have my bars set up on my chart as stacked. So each bar is split into 4 different colours which correspond to certain criteria which I specified. However, one of those criteria does not exist, i get a 0 on the bar along with the three other numbers which correspond to the three other colours on the stacked bar.

 
Can i further edit this report so that the zeros dont appear which relate to the section of the stacked bar?
 
 
thanks.
J
IP IP Logged
jknockler
Newbie
Newbie


Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
Quote jknockler Replybullet Posted: 30 Mar 2009 at 8:54am
another way of explaining this is that if i switch my stacked bar chart to a sidebyside bar chart, I will have 4 bars for each and every of my companies.
 
bar one could be 5, bar two could be 0, bar three could be 1 and bar four could be 0. 
 
What i want here is that just bar one and bar three are displayed since they are the only one's with values greater than 0.
J
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.