FS 308846 ALS Excel Spreadsheet Report

From Calidus HUB
Revision as of 12:53, 9 May 2013 by Anw (talk | contribs) (v0.2 - Added more field mapping)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)





Aptean Logo.png







Air Link Systems

ALS Excel Spreadsheet Report

CALIDUS eSERV

9th May 2013 - 0.2
Reference: FS 308846











































Functional Overview

Client Requirement

Create a Service Excel report to match the current output

Solution Overview

Create a Service Excel report to match the current output, referenced in Appendix B

Scope

  • External to CALIDUS eSERV system


Set-up

Pre-requisites

  • The database must be directly accessible from the PC that's running the report.
  • Additionally, the data source must be set up as EPOD on the machine running the report.

Menu Structure

Data

Functional Description

Service Excel Report

Create a parameterised Service report (as can be seen in Appendix A).

Note Note: All related files can be found in P:\EPOD\Correspondence\Air Link.

Parameters required:

  • Job Group
  • Site
  • Date From (YYYYMMDD, based on EPL_ORDER_DATE)
  • Date To(YYYYMMDD, based on EPL_ORDER_DATE)

Columns on the report:

  • Client Name - ? (Job Group or customer field)
  • Site Name - ? (Customer Name?)
  • Store Number - ? (Another customer field?)
  • Order Number - EPOD_JOB.EPL_JOB_CODE
  • Job Ref - ? assumed - EPOD_JOB.EPL_JOB_ID
  • Product - EPOD_SERVICE.EPL_SERVICE_GROUP
  • System Type - EPOD_SERVICE.EPL_SYSTEM_TYPE
  • Other - ?
  • Priority Code - ? (Service Level?)
  • Date Logged - EPOD_JOB.EPL_ORDER_DATE
  • Time Logged - EPOD_JOB.EPL_ORDER_TIME (New field)
  • Date Required - EPOD_JOB.EPL_EXPIRY_DATE (New field)
  • Time Reqd - EPOD_JOB.EPL_EXPIRY_TIME (New Field)
  • Date Attended - EPOD_JOB.EPL_ARRIVAL_DATE
  • Time On Site - EPOD_JOB.EPL_ARRIVAL_TIME
  • Time Off Site - EPOD_JOB.EPL_END_ACTUAL_TIME
  • Eng - EPOD_USER.EPL_USER_NAME from EPOD_LOAD.EPL_USER_ID from EPOD_JOB.EPL_LOAD_ID
  • Status - ? two fields: - EPOD_JOB.EPL_STATUS or EPOD_JOB.EPL_JOB_STATUS
  • Rectification Required -?
  • Additional Text - EPOD_JOB.EPL_USER_NOTES (required to be enabled for the Job Group)
  • Materials Used - EPOD_SERVICE_PRODUCT.EPL_PRODUCT_CODE
  • Qty - EPOD_SERVICE_PRODUCT.EPL_QUANTITY
  • Customer Name - EPOD_JOB.EPL_CUST_SIGNATORY
  • Job Text - EPOD_JOB.EPL_JOB_INSTRUCTION
  • Completion Text - EPOD_SERVICE.EPL_DIAGNOSIS_NARRATIVE
  • Job Start Mileage - ?
  • Job Comp Mileage - ?

For the report requirement specifically for ALS:

  • A test spreadsheet has been created, connection EPOD, linking to the database EPOD_DEV, using the administrator user. You will need to set up a datasource called the same thing, using a defined reports user, connecting to the ALS database. The Service Query tab shows the report created, as well as a simple Job Query report.
  • It uses a view in the database (added in DEV called vwEPOD_JOB_SERVICE) as the links are too complex for Microsoft Query.
  • The WHERE clause is specified in the Connection SQL.

Further changes required are:

  • Add new fields in the appropriate place as seen below. Note Note: Awaiting confirmation of some fields.
  • Add selection based on Job Group.
  • Possibly change the query to link to customer via the job group to get the name/depot, if that's what's required.


Appendix A: Creating Parameterised Excel Reports

Prerequisistes

Note Note: The database must be directly accessible from the PC that's running the report. Additionally, the data source must be set up as EPOD on the machine running the report. You should ensure that a non-privileged database user is set up for these reports, that can read data only.

Create Report

In Microsoft Excel:

  • Create a new spreadsheet
  • Select Data/Get External DataFrom Other Sources/From Microsoft Query from the menu.
  • Choose an existing data source or create data source.
  • In the following wizard:
    • Choose any column from any table
    • Click Next, Next, Next, Select View Data in Microsoft Query, click Finish.

In Microsoft Query:

  • Click SQL button
  • Paste in the required SQL e.g.
SELECT * FROM EPOD_DEV.dbo.EPOD_JOB
  • Select Criteria/Add Criteria from the menu.
  • From the popup, enter the criteria required.
    • e.g. EPL_SITE_ID, equals, []
  • Do this for each criteria required on the report
  • Click the Return Data button.

In Microsoft Excel:

  • Choose cell for the table to be placed. Note Note: Leave some room at the top for some parameter entry.
  • Enter the values for the parameters in the reserved cells.
  • Click on any cell in the table.
  • Select Data/Connections/Connections from the menu.
  • Select your query (typically Query from <datasource>
  • Click Properties
  • Click Definition tab
  • Click Parameters button
  • For each parameter:
    • Select 'Get value from following cell'
    • Click the entry box below.
    • Click on the data cell that contains the data you want to filter by on this parameter.
    • Tick 'Refresh automatically when cell value changes'
  • Click OK.

Formatting fields

  • Select the field and apply a format through right-click, Format Cells, Custom Format, as follows:
    • Dates - apply format of ####\\##\\##
    • Times - apply format of ##\:##\:##\.##

These formats should stick with the data even when refreshed, as long as the columns do not change. If they do, the formats may need to be reapplied.

Developer notes

  • Complex queries (i.e. procedures, SQL with sub-queries or multiple types of table join) cannot be parameterised. This then requires the complex SQL to be defined in a view or views within the database, then selected from the view in the report.
  • New fields added to the table will appear on the end. These can either be moved manually or a new table can be created using the same datasource, then the old table deleted.
  • Column names always default to the field names from the data source, so name them using the syntax '[my col name]' after the field declararion in the SELECT statement.

Appendix B: TEST PLAN

Test Script / Scenario ReferenceALS Excel Spreadsheet ReportCall Number(s): 308846
Test Script / Scenario Descriptiondescription of what is to be achievedPASS / ISSUES / FAIL
Menu AccessWhere on the menus the item can be found 
Pre-requisitesThe prerequisites of the testTested By:
 
Test ObjectiveThe details of what each group of tests is to achieveDate:
 



Step Action Result Remarks P/F
1 Area being tested in this cycle      
  Any notes or prerequisites for the tests following.      
1.01 The actions to follow The expected result    
1.02 The actions to follow The expected result    
1.03 The actions to follow The expected result    


Appendix C: Quote & Document References

Cost Details
Activity Estimate
No. of Days		 No. of Days Rate per Day (£) Cost (£ Exc. VAT)
Requirements 0.00 0.00 650 £0.00
Change Request Evaluation 0.25 0.25 650 £162.50
Functional Specification 0.25 0.25 650 £162.50
Technical Specification 0.00 0.00 650 £0.00
Development 1.25 1.25 650 £812.50
Testing and Release 0.25 0.25 650 £162.50
Implementation 0.50 0.50 650 £325.00
Project Management First argument to "number_format" must be a number. First argument to "number_format" must be a number. 650 £First argument to "number_format" must be a number.
 
TOTAL First argument to "number_format" must be a number. First argument to "number_format" must be a number.   £First argument to "number_format" must be a number.
Estimate excludes training, release to live and go live support.

C.1 References

Ref NoDocument Title & IDVersionDate
1APRIL 2013.xlsN/A04/2013
2EPOD Excel Reports v0.2.xlsx0.209/05/2013


C.2 Glossary

Term Definition
EPOD Electronic Proof of Delivery. The OBS EPOD system is CALIDUS ePOD.
CALIDUS eSERV The OBS mobile system to complete Service functionality in the field. This is part of the CALIDUS ePOD system.
PDA The mobile device on which the C-ePOD system will run in the field. This can be a Phone, EDA or industrial PDA, running Android.
DAL Data Access Layer. A mechanism for accessing data by the system that is removed from the application, allowing for simplified access and providing protection to the data, as only approved DAL methods can be used to modify it.
GPS Global Positioning System. A mechanism of retrieving accurate positioning information in the form of Latitude and Longitude (Lat-Long) co-ordinates from a device.
GPRS, 3G, HSDPA, Data Service All terms referring to mobile device network connectivity, and the speed at which the device connects to the internet.


C.3 Authorised By


Rev1
Rev1 Title		_____________________________
Retrieved from ‘https://calidusassist.adcservices.apteancloud.com/calidus-assist/OBS/index.php?title=FS_308846_ALS_Excel_Spreadsheet_Report&oldid=1076’