Help with displaying multiple totals or counts on same report 1

[FishKiller]

Help with displaying multiple totals on same report

Software Version = Crystal Reports Professional 9
Database connector = ODBC
Database type = SQL2000
Database Field = SQL View called “VR_COUNT”

I have created a SQL View to help simplify the task/goal outlined below. Inside the view are the following fields:

“EXAM_COUNT” type = NUMBER – “Total number of exams performed based on the true conditions being meet on the remaining fields listed below”
“LOC_NAME” type = String – “Currently returns only one location name”
“EXAM_ABBREV” type=String – “Returns exam number that was completed”
“RES_NM” type=String – “Returns resource utilized for EXAM_ABBREV”
“Exam_Month” type=NUMBER – “month number 1-12”
“Exam_Day” type=NUMBER – “day number 1-31”
“Exam_Year” type=NUMBER – “Year number 2000-2005”

GOAL: Automate current Volume Report which is manual and in Excel using a Crystal Report and move away from Excel. This report cannot be a crosstab due to it’s limitation of being dynamic (if I am wrong please correct) and needs to maintain the same format as I attempted to illustrate below.

A B C D E F G
1 DAY Location Title (LOC_NAME)
2 TOTAL (RES_NM=MRI) (RES_NM=CT)
3 1 11 6 5
4 2 210 110 100
5 3 50 1 49
… …
31 31 172 72 100


Column Definitions

B = Day number static 1 thru 31
C = Day total from all (RES_NM) that match (Exam_YEAR, Exam_Month, Exam_Day)
F = (RES_NM) Resource Group that contains defined (EXAM_ABBREV) VALUES SUCH AS: [“70540S”,”70336”,”70549”]

Formula that I tried is as follows:
If {VR_COUNT.EXAM_YEAR}=2005 and {VR_COUNT.EXAM_MONTH}=9 and {VR_COUNT.EXAM_DAY}=1 and (VR_COUNT.RES_NM} = “MR2” and {VR_COUNT.EXAM_ABBREV} in [“70540S”,”70336”,”70549”]
Then {VR_COUNT.EXAM_COUNT}

* If I try to leave the EXAM_ABBREV to a single selection the result is blank probably due to my lack of programming skills

I am also wondering if I need to create 31 different formula’s for each resource representing each day. In our case we have over 30 resources OUCH.

Any additional information needed please post and I will gladly post. Any ideas or help will gladly be accepted and appreciated.

 

[synapsevampire]

If you're constructing maunual fields to be used for aggregates, remember to put in an ELSE clause of:

ELSE
0

That way you won't get blanks.

I think that you do want a manual crosstab, however grouping by the date field will eliminate the need to specify the If {VR_COUNT.EXAM_YEAR}=2005 and {VR_COUNT.EXAM_MONTH}=9 and {VR_COUNT.EXAM_DAY}=1 portion, as the date will already be grouped, just create the formulas required, place it in the details, right click it and select insert->summary->sum, and have it do so by the date grouping, and then suppress the details.

It's unclear whether some criteria is date specific, but the formulas should look something like:

(VR_COUNT.RES_NM} = “MR2” and {VR_COUNT.EXAM_ABBREV} in [“70540S”,”70336”,”70549”] Then
{VR_COUNT.EXAM_COUNT}
else
0

-k

 

[FishKiller]

Thank you for the quick reply. If I am tracking you correctly, I can make a manual cross tab report. I am not even sure how to accomplish this. Do you have any pointers how this can be done and how to have "static" field entries?

 

[synapsevampire]

Group by the date.

Create the appropriate formulas, such as:

(VR_COUNT.RES_NM} = “MR2” and {VR_COUNT.EXAM_ABBREV} in [“70540S”,”70336”,”70549”] Then
{VR_COUNT.EXAM_COUNT}
else
0

Place them in the details and use the summary method as defined above, and suppressing the details.

I don't know what you mean as a staic field entry. Crystal doesn't allow for entering values into databases, adn if you mean a static text, use a text object. If you mean conditional values for each formula, the above is an example of that.

-k

 

[lbass]

Before trying a manual crosstab, I would see if an inserted crosstab might work. It looks to me like you could insert a crosstab with the following rows, in this order:

Exam_Year
Exam_Month
Exam_Day

For the column, you would use RES_NM, and for the summary, sum of Exam_Count.

You should have a group on location and then place this crosstab in the location group header or footer. I'm not sure how Exam_Abbrev fits in. If you only want a subset of of options from this field, you could use a formula that groups the options and then use this as a second column field. The formula could look something like:

if {VR_COUNT.EXAM_ABBREV} in [“70540S”,”70336”,”70549”] then 1 else
if {VR_COUNT.EXAM_ABBREV} in [“Other value1”,”Othervalue2”] then 2 else 3

-LB