FishKiller
IS-IT--Management
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.
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.