Q on formula using if isnull

[jack91]

hi,


I have 100 computers which need to show warranty info which I have done the following(this works for me)

if isnull({table.column})then
"computers having NO warranty INFO"
else if {table.column} <= CurrentDate then
"computers that have Warranty Expired"
else
"computers having Warranty NOT expired"

An additional requirement came up for which I need:

"Computers that are important = YES"

Computers that are important ---> field name and "YES" would be the default value.

Question is: can i add this new requirement to the original formula ?

Presently I am using one pie chart show three divisions:
1)computers having no warranty info
2)warranty expired
3)Warranty NOT expired.

Now I will need to display the same as above but now I will need ** TWO pie charts.

Important computers
1)computers having no warranty info
2)warranty expired
3)Warranty NOT expired.

UN-important computers:
1)computers having no warranty info
2)warranty expired
3)Warranty NOT expired.

**** can i add to the original formula:

({Computers that are important = YES})" and
if isnull({table.column})then
"computers having NO warranty INFO"
else if {table.column} <= CurrentDate then
"computers that have Warranty Expired"
else
"computers having Warranty NOT expired


********** Second related Requirement *********

For the computers that have warranty expired which of the computers have warranty expired within the next 30 days, 60 days and so on.
For this I am using:
if {column.warranty_end}>= currentdate and {v_asset1.warranty_end} <= currentdate+30 then
"Less than 30 days"
else ifcolumn.warranty_end} > currentdate+30 and {column.warranty_end}<= currentdate +60 then
"Between 30 - 60 days"
else if{column.warranty_end} > currentdate+60 and {column.warranty_end}<= currentdate +90 then
"Between 60 - 90 days"
else if{column.warranty_end} > currentdate+90 and{column.warranty_end}<= currentdate +365 then
"Between 90 days and 1 year"
else if{column.warranty_end} > currentdate+365 and {column.warranty_end}<= currentdate +730 then
"Between 1 - 2 years"
else if{column.warranty_end} > currentdate+730 then
"greater than 2 years"
else if isnull({column.warranty_end}) then
"no warranty"
else
"Warranty Expired"

** for this too I will need to break this up in to two column graphs one for those computers that are important and a second column graph for those that are "not important"

The grouping too in the details section of the reports would have to be similar with the first part of the details report showing important computers information followed by the second part showing UN-important computers.

Any advise is very much appreciated.
Regards
Jk

 

[Madawc]

Your code seems on the right lines. Try it, see what happens.

Note that when a value can be null, the test for null must come first, or the whole formula will stop.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You could do this with grouping, where you insert a group #1 on a formula that defines important or not important. Not sure whether the field can be null or whether it can only be Yes or No, but try a formula like this;

if isnull({table.field}) or
{table.field} = "No" then
"Not Important" else
"Important"

Insert a group #1 on this, and then insert a second group on the warranty status formula. You can then insert a pie chart based on warranty status in the Group #1 header or footer. Then add a pie chart to Group #2 that is based on the warranty expired in N days formula. Conditionally suppress the pie charts (or the section, if you don't otherwise need it) using a formula like this:

{table.warrantystatus} <> "computers having Warranty NOT expired"

-LB

 

[jack91]

Lb,

Regarding the first requirement:

Will I get ** 2 pie charts for?If i follow your suggestion)

Important computers --- ** pie chart 1
1)computers having no warranty info
2)warranty expired
3)Warranty NOT expired.

UN-important computers: --- ** pie chart 2
1)computers having no warranty info
2)warranty expired
3)Warranty NOT expired.

Thanks
Jk

 

[lbass]

IF you put a chart into a group section, it will execute once for each group instance. Why not try it?

-LB

 

[jack91]

How do i put a chart in a group section.

The way i am doing it is;

insert > chart > group or advanced ? > what selections do i make now?

Thanks
Jk

 

[lbass]

Why not experiment a little?

If you are using XI (you should always identify your version), once you have inserted the chart you are required to choose a section to place it in--Use either the group header or the group footer. Then in the chart expert, go to the type tab and choose pie. Then follow my previous instructions.

-LB

 

[jack91]

Hi Lb,

I am using Crystal version 10.

I had tried this but was not sure of the results. I used group header. I chose the advanced tab.

I will post back after i try again.

Thanks
Jk

 

[jack91]

Hi Lb,

if isnull({table.field}) or
{table.field} = "No" then
"Not Important" else
"Important"

Insert a group #1 on this,

----> done.

and then insert a second group on the warranty status formula.

-----> done.
My second group is:
if isnull((warrany end date field})
then
"computers having NO warranty INFO"
else if (warrany end date field} <= CurrentDate then
"computers that have Warranty Expired"
else
"computers having Warranty NOT expired"

There is a third group for which I am using:

if {column.warranty_end}>= currentdate and {v_asset1.warranty_end} <= currentdate+30 then
"Less than 30 days"
else ifcolumn.warranty_end} > currentdate+30 and {column.warranty_end}<= currentdate +60 then
"Between 30 - 60 days"
else if{column.warranty_end} > currentdate+60 and {column.warranty_end}<= currentdate +90 then
"Between 60 - 90 days"
else if{column.warranty_end} > currentdate+90 and{column.warranty_end}<= currentdate +365 then
"Between 90 days and 1 year"
else if{column.warranty_end} > currentdate+365 and {column.warranty_end}<= currentdate +730 then
"Between 1 - 2 years"
else if{column.warranty_end} > currentdate+730 then
"greater than 2 years"
else if isnull({column.warranty_end}) then
"no warranty"
else
"Warranty Expired"

But If i figure out the pie charts for the first 2 groups properly then I will worry about the third group.

So, you then said,

You can then insert a pie chart based on warranty status in the Group #1 header or footer. Then add a pie chart to Group #2 that is based on the warranty expired in N days formula.

----> For this I went to:

Placement: chosen: Header , other choices in the drop down were, importance flag, warranty expired.

Layout:
Insert > Chart > advanced Tab >

Chosen: (also available for each record/ for all records)
On change of: important flag
show value: count of @warranty expired.

Doing the above gives me a 3 bar chart. This is wrong , I need 2 pie charts.

One for Important computers with 3 divisions in the pie chart
simillarly for non-important computers.

Please advise.
Thanks
Jk

 

[lbass]

Please reread my last post.

-LB

 

[jack91]

Lb,

Its partially working.

When i insert the pie chart in group #1 (important/non-important), I get:
GH1 a: pie chart
GH1 a: identical pie chart

important computers: computers having no warranty info
(what's missing is the other 2 sections like -- see formula)

non-important computers: computers having no warranty info
(what's missing is the other 2 sections like -- see formula)

it seems to be evaluating the first part only. And i don't have a clue why the pie chart in Gh 1 a and Gh1 b are the same.

if isnull({computers.warranty_end})
then
"computers having NO warranty INFO"
else if {computers.warranty_end} <= CurrentDate then
"computers that have Warranty Expired"
else
"computershaving Warranty NOT expired"

any suggestions?
THANKS
Jk

 

[jack91]

as an alternative I tried to * hardcode the "important computers" into the warranty expiration formula like this;

if isnull({computer.warranty_end}) and {computer.important}= "Y" // and use "N" for non-important computers.
then
"Assets having NO warranty INFO"
else if {computer.warranty_end} <= CurrentDate then
"Assets that have Warranty Expired"
else
"Assets having Warranty NOT expired"

seems to work but I like LB' suggestion -- I will give it a shot again with a clear head.

Thanks
Jk

 

[jack91]

Hi Lb,

you said earlier ---> You can then insert a pie chart based on warranty status in the Group #1 header

This works but I want both the pie charts(critical/non-critical) right next to each other in the first page of the report.

The way I have it working now is to have the critical computer graph first followed by details.
This is followed by the non-critical graph followed by the non-critical computer details.

This works but I want both the pie charts(critical/non-critical) right next to each other in the first page of the report. This can then be followed by the details section of critical and non-critical computer details

Please advise

Thanks
Jk

 

[jack91]

LB, would you please have a look at my last comment.

RH a --> cross tab
GH 1a --> graph for critical/non-critical computers having warranty info.
GH 1b --> Future Warranty Expiration of the critcal/non-critical computers
GH 1b --> OLD Warranty Expiration of the critcal/non-critical computers

Please use this info along with my last comment. Everything works fine but I need one last thing.

I would like:

graph for critical/non-critical computers having warranty info to be next to each other so when a user looks at the page they have all the info together.


TIA
Jk