Crystal Reports- Count of Data in Field 1

[oxygen101]

Crystal Reports 9

I am evaulating how many days a work order is open for. I have a field of data called Days Open. In Days Open there are a number of values that can be possible(ie. 1-2 Days, 3-7 Days, 8-15 Days and etc.) I need to be able to count each of these instances. I tried using Count(Days_Open) but of course it give me the total amount and not the individual counts.

Anyone have any ideas?

Thanks much.

 

[dgillz]

Please post sample data and desired results.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[IanWaterman]

YOu could create a series of Running Totals for each type, that count these fields.

eg #1-2Days

In the Evaluate, check the formula box and enter

{Days_Open} = '1-2 Days'

Repeat for other day ranges.

Ian

 

[lbass]

Please show us how Days_Open displays if placed in the detail section, and also tell us whether it is a number or a string, by running your mouse over the field and noting what the tooltip text says or by right clicking on and browsing the field.

-LB

 

[oxygen101]

*******I tried this but I get a False result when I put the formula in the Group Footer.

{RPT_TT_DTPARA.DAYS_OPEN} = 'Under 1 Day' and
{RPT_TT_DTPARA.DAYS_OPEN} = '1-2 Days' and
{RPT_TT_DTPARA.DAYS_OPEN} = '3-7 Days' and
{RPT_TT_DTPARA.DAYS_OPEN} = '8-15 Days' and
{RPT_TT_DTPARA.DAYS_OPEN} = 'Over 16 Days'

********Here is some Sample Data

Created DateTime(DateTime)
4/7/2009 10:46
4/7/2009 10:22
4/7/2009 10:19
4/6/2009 15:44
4/6/2009 10:51
4/6/2009 9:41
4/6/2009 9:17
4/6/2009 8:34
4/6/2009 8:34
4/6/2009 8:11
4/3/2009 16:20
4/3/2009 16:02

Work Order Number/Issue(String)
TT-11623-1
TT-11622-1
TT-11621-1
TT-11620-1
TT-11619-1
TT-11618-1
TT-11617-1
TT-11616-1
TT-11615-1
TT-11614-1
TT-11613-1
TT-11612-1


Days Open(string)
Under 1 Day
Under 1 Day
Under 1 Day
1-2 Days
1-2 Days
1-2 Days
1-2 Days
1-2 Days
1-2 Days
1-2 Days
3-7 Days
3-7 Days


I want to see the count for the work orders listed above like this.

Under 1 Day 3
1-2 Days 7
3-7 Days 2

I am planning on showing all the days_open,work order number, created datetime detail for one report and not for the other one(summary report). For the report that I want to show all the detail- I have it in the Details section. For the summary report, I really just want to show the count of the Days Open like above.

Thanks again!!

 

[lbass]

If you are looking for a summary report in the report header or footer, insert a crosstab and add the days_open field as the row, and count of workorder as the summary field.

-LB

 

[oxygen101]

I believe this is exactly what I need. Thank you all so much. I may have more questions but so far so good.

 

[oxygen101]

Soo.....now- I had to add another field in the Days Open field called Pending Resolution. In the view, I had to change the logic of Days Open to compare the Cleared Date to Created Datetime. Since this field accounts for work orders that are still open, it shows that there are some work orders with a null cleared date. When I run the view through the database it shows me everything I need to see. See a few examples from database output(there are actually a total of 147 orders listed for the month of March).

WO_NUMBER CREATED_DATETIME DAYS_OPEN CLEARED_DATE

TT-11554-1 3/17/2009 16:13 1-2 Days 3/18/2009 23:32
TT-11494-1 3/3/2009 8:23 8-15 Days 3/11/2009 13:35
TT-11585-1 3/26/2009 16:19 Pending
Resolution
TT-11504-1 3/6/2009 9:21 Under 1 Day 3/6/2009 15:00

However now that implemented it into Crystal using Cross Tabs, it is not showing the Pending Resolution field in the rows. See below.

Total
1-2 Days 43
3-7 Days 6
8-15 Days 1
Under 1 Day 64
Total 114

*****I need to show this-

Total
1-2 Days 43
3-7 Days 6
8-15 Days 1
Under 1 Day 64
Pending Resolution 33
Total 147


I started to think that because there is a null value that it may not pull it in but the logic is already set up in the view that I am using to create the report. Does that matter?

I am inches away from being completed(I think anyway) and I hit this snag. :|

Any suggestions?

Thanks again.

 

[lbass]

Did you verify the database after changing the view?

-LB

 

[oxygen101]

I sure did- that's why I figured that it would show up fine. The database hasn't been changed and the view appears to be correct(it gives me everything I want to see). I started off with a new view, validated the code, ran the view(that's how I got the example data), and created a new Crystal Report from the new view. I am not sure why that field specifically does not show up.

 

[oxygen101]

Here is an example of the logic used in the view for Days Open.

CASE
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) < 1
THEN 'Under 1 Day'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) >= 1
AND ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) <= 2
THEN '1-2 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) >= 3
AND ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) <= 7
THEN '3-7 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) >= 8
AND ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) <= 15
THEN '8-15 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) >= 16
THEN 'Over 16 Days'
WHEN wo.CLEARED_DATETIME is null THEN 'Pending Resolution'
END days_open,

Not sure if it's going to be useful.

 

[oxygen101]

I just noticed that in the Browse Data button in the Cross Tab Expert it shows all the fields in Days Open but I am just not sure why its not showing it when it gets to the report. I know that March has Pending Resolution in it. AHHH...

 

[lbass]

Try changing the view to use:

CASE
WHEN wo.CLEARED_DATETIME is null THEN 'Pending Resolution'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) < 1
THEN 'Under 1 Day'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) >= 1
AND ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) <= 2
THEN '1-2 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) >= 3
AND ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) <= 7
THEN '3-7 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) >= 8
AND ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) <= 15
THEN '8-15 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.created_datetime, 0) >= 16
THEN 'Over 16 Days'

END days_open,

Null checks have to be done first in Crystal, so I imagine it would hold true for the view also.

-LB

 

[oxygen101]

Thanks. I tried this and I still get the same result. I also tried not to have the Cross Tab show up in the Group 1 Footer where I need it and allowed it to show up in the Report Footer. It all shows up but of course I need it to be for the group and not for everything. I have it grouped by Created_Datetime, monthly. I also saw something yesterday while I was looking for more answers. See below.

Right Click the summary field, select "Format Field", go to "Common" Tab.

You can find X-2 for "Display" String option. Click that button and write the following formula

If IsNull(CurrentFieldValue) = True then
0.00
else
CurrentFieldValue

**I modified this to be:
If IsNull({RPT_TT_TT_DAYSOPENED.DAYS_OPEN}) = True then
""
else
{RPT_TT_TT_DAYSOPENED.DAYS_OPEN}

**Then I tried this:
If IsNull({RPT_TT_TT_DAYSOPENED.DAYS_OPEN}) = True then
"Pending Resolution"
else
{RPT_TT_TT_DAYSOPENED.DAYS_OPEN}

This didn't help because it changed the count values to something else- actually it listed Under 1 Day for the count values.

Then I realized Days_Open does not have a null value..It is the value 'Pending Resolution' within Days Open that has a null value. How do you account for that?

 

[lbass]

Are you saying that Pending Resolution is showing up in the report footer?

-LB

 

[oxygen101]

I am sure am but it only shows up with a fresh report(without groups, parameter dates, etc.--(Of course the items I need have to are not allowing it from showing up.)) See below. These amounts are for every work order- I need to be able to group by Creation Date and have date parameters so my users can pull this info for a particular month.
Total
1-2 Days 2,626
3-7 Days 3,362
8-15 Days 2,061
Over 16 Days 3,012
Pending Resolution 106
Under 1 Day 1,853
Total 13,020

It appears that null values automaticlly get excluded from Cross Tabs and there are some workarounds but they are just not working for me. Frustrating in deed. Maybe I am just not applying them correctly who knows.

 

[lbass]

You can't display data that isn't there in a crosstab or in groups. You could just create running totals that do a distinctcount of workorder, evaluate using a formula, e.g.:

{view.days_open} = "1-2 Days"

Reset never.

Create one running total for each possible days_open result. Place these in the report footer with a text label.

-LB

 

[oxygen101]

Hmm..I tried this before but I put the formula in the Group Footer before. See above. I tried this now in the report footer and it shows False. That's also what happened before. I tried an individual formula's for one of the totals that I need and it still shows up False. I also used the same formula that is above. I just don't get it. Thanks again for your help.

 

[oxygen101]

I wonder if you can modify the view to add a value to the specific Days Open field "Pending Resolution" but not change the database?? I tried this but I am getting an inconsistent datatype error.

Case
when wo.cleared_datetime is null
then '0'
else wo.cleared_datetime
end nullClearedDate,
CASE
WHEN wo.CLEARED_DATETIME is null THEN 'Pending Resolution'
WHEN ROUND (wo.CLEARED_DATETIME - wo.creation_datetime, 0) < 1
THEN 'Under 1 Day'
WHEN ROUND (wo.CLEARED_DATETIME - wo.creation_datetime, 0) >= 1
AND ROUND (wo.CLEARED_DATETIME - wo.creation_datetime, 0) <= 2
THEN '1-2 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.creation_datetime, 0) >= 3
AND ROUND (wo.CLEARED_DATETIME - wo.creation_datetime, 0) <= 7
THEN '3-7 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.creation_datetime, 0) >= 8
AND ROUND (wo.CLEARED_DATETIME - wo.creation_datetime, 0) <= 15
THEN '8-15 Days'
WHEN ROUND (wo.CLEARED_DATETIME - wo.creation_datetime, 0) >= 16
THEN 'Over 16 Days'
END days_open,

Any thoughts?

 

[lbass]

You need to go to the field explorer->running total->new->
distinctcount of workorder_>evaluate using a formula->
{view.days_open} = "1-2 Days"->Reset never.

Place this in the report footer. There is no way this would ever display "false".

-LB