Crystal Report problem
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10996
Printed Date: 27 Apr 2024 at 6:50am
Topic: Crystal Report problem
Posted By: ri88
Subject: Crystal Report problem
Date Posted: 30 Aug 2010 at 5:01pm
|
I want to create crostab table from the database. The row consist of date each year and the couloum consist of location of the warehouse. However, there are some user who have not fill the couloum (meaning the location was blank). the problem happened when i filter one location (for example KAKA). the result is not only KAKA coloum was filtered, but also the blank coloum was not displayed after i filtered KAKA coloum. Anybody can help? |
Replies:
Posted By: sukhveer
Date Posted: 30 Aug 2010 at 8:57pm
|
Easy way? Go to your database and update all your Null values for location to some default value like 'No location'... then you should be good.
Will let you know if it can be done from crystal end.
|
Posted By: ri88
Date Posted: 31 Aug 2010 at 4:26pm
| How to update the null value to no location? because the database is restricted to every one for updating. Can we update this in the crystal report if we cannot modify in the database? |
Posted By: Senthil Raj
Date Posted: 31 Aug 2010 at 8:02pm
|
if you are using a select query to retrieve data then you can handle in
query as isnull(field_name,'No location') or nvl(field_name,'No location') |
Posted By: ri88
Date Posted: 01 Sep 2010 at 9:14pm
|
How To create query in the crosstab table:
in the coloum: null value, X Location, Y Location
in the row : Year 2008, Year 2009
Because if we use filter, it also remove the null value in the coloum (the things that we also want to see)
|
Posted By: ri88
Date Posted: 02 Sep 2010 at 10:57pm
|
Crosstab table.
This table below is before i filter the X location.
|
Posted By: ri88
Date Posted: 20 Sep 2010 at 9:35pm
|
any body can help? |
Posted By: Senthil Raj
Date Posted: 21 Sep 2010 at 12:47am
|
use selection expert to use that query
------------- Live And Let Live... |
Posted By: ri88
Date Posted: 21 Sep 2010 at 4:07pm
| i already use select expert to exclude X coloum, however, the blank coloum near x also filtered. how to fix it? |
Posted By: DBlank
Date Posted: 22 Sep 2010 at 4:34am
| Are you using an outer join in this report? |
Posted By: ri88
Date Posted: 22 Sep 2010 at 3:16pm
| should we use outer join or not? |
Posted By: DBlank
Date Posted: 23 Sep 2010 at 3:45am
|
i do not know as i have never seen the data or table structure but if you added a select statement that may have turned the outer join into an inner join.
more likely you did something like a sleect statment of
field<>'X'
go back into the select expert and there is a setting (pick list) for NULLS
change it to use 'Default Values For Nulls'
|
Posted By: ri88
Date Posted: 27 Sep 2010 at 5:03am
|
How i know if i already have turned the outer join into an inner join.
because i already chose the outer join in the database link in database expert.
What does it mean
Change it to use 'Default Values For Nulls' in the select expert?
|
Posted By: DBlank
Date Posted: 27 Sep 2010 at 5:11am
|
Open the select expert
click on the show formula
click on the formula editor
In here there is the 'Expression Editor'
In the expression editor there is a pick list option that is likely defaulted to use 'Exceptions for Null'
change this to use 'Default values for Nulls'
|
Posted By: ri88
Date Posted: 27 Sep 2010 at 4:27pm
and this is just happen when i created the crostab table. anybody can help? |
new_reply_form.asp?PID=35691&PN=2&TR=14">
Posted: Today at 5:11amPosted By: DBlank
Date Posted: 28 Sep 2010 at 4:04am
|
That was whta i was trying tot tell you. IN your select statment you did not tellit how to handle nulls so it omits them. By changing that option it tells it what to do. YOu could also change your selecy statment if you want. isnull(field) or field<>'X'
|
Posted By: ri88
Date Posted: 04 Oct 2010 at 12:29am
|
I tried
If IsNull({YourField}) then "Empty"
else {YourField}
and it worked. Thanks for your help. |