| Author |
Message |
atminnie
Newbie
Joined: 19 Jul 2010
Online Status: Offline
Posts: 9
|
  
Topic: Including Null data in Charts
Posted: 09 Sep 2010 at 3:57am |
Hello,
I have a chart in Crystal 10.0.5 listing YTD sales qty by InvoiceDate (per month).
I would like the chart to show the entire year (including the months Oct-Dec) across the bottom of the graph even though they have no data, no records.
How can I tell Crystal to include empty groups in the chart?
|
 IP Logged |
|
|
|
redbar0n11
Newbie
Joined: 29 Jul 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 09 Sep 2010 at 1:28pm |
|
If you're using select expert to select the criteria, and something about it not showing the nulls, then you need to tell it to include nulls. Edit the formula in select expert and use the IsNull() formula -- enter it after w/e else you have in there and make sure to use "AND":
**existing formulas** AND
IsNull({table.month})
Also is that doesn't work, try adding =TRUE after it jsut in case. I could'a swore that fixed one of my IsNull one time, but I could be mistaken. Either way, mine worked with it, and without... sooo yeah. :) Give it a shot.
|
| IP Logged |
|
atminnie
Newbie
Joined: 19 Jul 2010
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2010 at 3:24am |
Thanks for your response. I tried that and it came up blank, no data at all.
The exact formula I'm using for Select Expert right now is this:
{AR_InvoiceHistoryHeader.InvoiceDate} in YearToDate and
{AR_InvoiceHistoryDetail.ItemCode} in ["FP GOTHIC", "FPGABLE"]
I tried putting in AND IsNull({AR_InvoiceHistoryHeader.InvoiceDate}) but that made an empty report. Do you think I need a specifically month field instead of a general date field? I'd have to make a formula for that.
Thanks for the input. Edited by atminnie - 10 Sep 2010 at 4:14am
|
| IP Logged |
|
redbar0n11
Newbie
Joined: 29 Jul 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2010 at 6:18am |
|
Looks like you're working in MAS 90. Looks like you're trying to pull all invoices for 2010? But you also want the remaining months to appear also? If that's the case, I'd say you need to remove the YearToDate. That's only gonna pull data from Jan 1 - "Today's Date". Make the date range absolute by telling it you want InvoiceDate Between 1/1/2010 and 12/31/2010. Give that a shot.
|
| IP Logged |
|
atminnie
Newbie
Joined: 19 Jul 2010
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2010 at 7:59am |
I'm using MAS200. and yes pull all 2010 data and show blanks for the future months with no data yet.
I changed date criteria, sitll no luck on showing months without data; I get the same data as originally.
Her is what my Select Expert formula is now:
{AR_InvoiceHistoryHeader.InvoiceDate} in Date (2010, 01, 01) to Date (2010, 12, 31) and
(IsNull({AR_InvoiceHistoryHeader.InvoiceDate})=TRUE or
IsNull({AR_InvoiceHistoryHeader.InvoiceDate})=FALSE) and
{AR_InvoiceHistoryDetail.ItemCode} in ["FP GOTHIC", "FPGABLE"]
Would it work better to use a Cross-set maybe? Edited by atminnie - 10 Sep 2010 at 8:00am
|
| IP Logged |
|
redbar0n11
Newbie
Joined: 29 Jul 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2010 at 8:18am |
|
Ah, alright. Yeah, you must be using Version 4.3 cuz prior versions used ARO_ and ARN_ Plus there are numbers everywhere in the tables... Very annoying.
Actually, thinking about it now... we're not talking about NULL values. If for some reason the invoices were in the database, and there wasn't a date in that field, THEN we'd be talking about NULLs -- I went in the wrong direction at the beginning not exactly understanding the situation. I don't quite know the solution for ya. But it might lie in the chart properties. I'd try Googling it, but you might have already done that.
|
| IP Logged |
|
atminnie
Newbie
Joined: 19 Jul 2010
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2010 at 9:53am |
Well thanks,
Yeah I'm trying to go for showing group headings where there are no corresponding records.
I couldn't see anything in the chart properties, and tried using a specified order in the Group Expert. No luck.
I'll post back here if I make any progress. Edited by atminnie - 10 Sep 2010 at 9:53am
|
| IP Logged |
|
redbar0n11
Newbie
Joined: 29 Jul 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2010 at 10:56am |
|
Might wanna try creating another table and joining... There are a few functions to try and look up how to properly use in help or online: Left Join, Right Join, Left Outer Join, Right Outer Join. Basically it joins values from 2 tables where values match exactly, and would allow for blank/null records (such as months with no data) -- I don't know man. Maybe if that doesn't work, try combining with IsNulls. Here's where I found this: http://www.pcreview.co.uk/forums/thread-3709458.php
Also, if you're trying to do this for the Grouping, which sounds correct, then this will prolly need to be formulated in the group formula -- or in select expert in the GROUP SELECTION area not the RECORD SELECTION area.
Edited by redbar0n11 - 10 Sep 2010 at 10:59am
|
| IP Logged |
|
redbar0n11
Newbie
Joined: 29 Jul 2010
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2010 at 11:02am |
|
Also try:
Open the report. Go to File-> Report Options. In the Report Options page, Check the following options:
1)Convert Database NULL Values To Default
2)Convert Other NULL Values to Default
|
| IP Logged |
|
|
|
