| Author |
Message |
jknockler
Newbie
Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
jknockler
Newbie
Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
jknockler
Newbie
Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
|
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 Logged |
|
jknockler
Newbie
Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
jknockler
Newbie
Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
|
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 Logged |
|
jknockler
Newbie
Joined: 05 Mar 2009
Location: Ireland
Online Status: Offline
Posts: 32
|
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 Logged |
|
|
|
