| Author |
Message |
morrisds
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 7
|
   Topic: Make Null values = 0
Posted: 05 Mar 2009 at 8:43am |
I just started working with Crystal this week......
I have a report that is limiting itself to only records that have a value in a certain field - that field is part of a calculation.
Some records have a value in that field and some are blank. I have tried every variation of if then statements that I can come up with, but the null valued records are still being exluded. I even tried a Case formula that I found online. They all seem to work fine for the records that have at least a 0 value in them, but all others just never appear.
Is there some code that would change null to 0, and also allow the actual values to exist too?
My field is called {IM2_InventoryItemWhseDetl.QtyOnHand}
Thanks,
DSM
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Mar 2009 at 9:15am |
First are you sure you are not excluding the NULL items from the report altogther? If they are there did you try a formula field as:
if isnull({IM2_InventoryItemWhseDetl.QtyOnHand}) then 0 else {IM2_InventoryItemWhseDetl.QtyOnHand}
Then use the formula field for your calculations.
This assumes the {IM2_InventoryItemWhseDetl.QtyOnHand} is number not text. You would have to convert it if it is not
If they are being excluded from the report, what type of DB are you using and how? Example if it is SQL are you using a stored proc or view or just a straight table or tables. f so how aer they joined. You may need an outer join instead of an inner join to get your null records to pull in...
Edited by DBlank - 05 Mar 2009 at 9:19am
|
|
IP Logged |
|
morrisds
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 7
|
Posted: 05 Mar 2009 at 9:24am |
That is the exact formula I have in there now.
It does seem to be excluding them all because there are records visible that have zeros, and numbers. I am thinking that the records with zeros actually have 0 in the on hand field in the main data.
I am doing it though an ODBC link to our companies planning/MRP software.
The records that are coming back are doing other calculations that are correct, so I am assuming it is not a text field. How would I check for sure?
Thanks,
DSM
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Mar 2009 at 9:32am |
Its a number or it would have spit an error on your formula.
My guess is that the export process you have is excluding the null values before it even gets to Crystal or do you have a select statement in Crystal that may account for the exclusion?.
What exactly is the process you have set for your export including joins and filters?
|
|
IP Logged |
|
morrisds
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 7
|
Posted: 05 Mar 2009 at 9:44am |
I am building this report off of an older report that would show the null values, but at that time they were not used in any calculations.
My business problem is that our total inventory sees inventory in an out of town warehouse, and assumes it is available to ship locally.
What I have done here is limit {IM2_InventoryItemWhseDetl.QtyOnHand} to exlude certain warehouses using the select expert. I was then subtracting that total out of the current on hands.
So, the strange thing is that I went in and removed the exclusions, and the report comes back with the same limited number of items. It is like it is only seeing those records because they have something in that field.
Is there a difference between blank and null sells when it comes to crystal?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Mar 2009 at 10:04am |
There are some differences and I cannot adequately explain them but it seems odd that if you remove the select statement it still excludes them when it was puling them before...
You can try and change your report options to "convert database NULL values to Default" as checked and see if that helps the situation.
ALso, if I remember correctly some versions of Crystal will convert outer joins to inner joins if you use a select statement which obviously can impact your data but that should have been fuixed when you removed the select statment.
Can you post the full select statement so we can see if there is anything with that?
|
|
IP Logged |
|
morrisds
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 7
|
Posted: 05 Mar 2009 at 11:12am |
I think my issue is that I am now pulling the data into the mix. Turns out that before it wasn't doinf anything but being displayed in a sub-report.
I am afraid that you lost me on the join & select statement stuff. That just went right over my head.
|
|
IP Logged |
|
morrisds
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 7
|
Posted: 05 Mar 2009 at 11:50am |
I think I figured it out. You were right. They are being blocked beofre they even got to cr.
I was limiting the input to certain warehouses. I went into the source tables, and the items that aren't showing up don't have those warehouses set up.
I was under the assumption that each warehsoue existed for all items, but would simply have zeros if there was nothing there.
I think I need to address from the source and come back to this afterwards.
Thanks for your help.
|
|
IP Logged |
|
morrisds
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 7
|
Posted: 06 Mar 2009 at 5:19am |
So, that is the case. Some items have no inventory in those specific warehouses, so when I pull it they don't show up because there is no warehouse listed.
I am OK with having 2 reports, but have one other issue now. When I sum the inventory in those 2 warehouses using a select .....
{IM2_InventoryItemWhseDetl.WhseCode} in ["CAL", "CIT", "VIS"]
it doubles the information since it is seeing 2 entries. Is there a way to sum the on hands before it gets to the point of going into the report?
Thanks
DSM
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Mar 2009 at 7:32am |
I am not familiar with the extraction process you are using so I cannot answer that one.
You might be able to use a conditional running total as a sum instead of a summary function to address this problem but without really seeing that data and the design I am not sure how to use to fix it.
If you have used running totals and conditions, try playing with that to see if you can find a condition to exclude the duplication.
|
|
IP Logged |
|
|
|
