Anw: v0.2 - Added more field mapping

2013-05-09T10:53:06Z

v0.2 - Added more field mapping

← Older revision		Revision as of 12:53, 9 May 2013
Line 58:		Line 58:

	Columns on the report:		Columns on the report:
	* Client Name - ?		* Client Name - ? (Job Group or customer field)
	* Site Name - ?		* Site Name - ? (Customer Name?)
	* Store Number - ?		* Store Number - ? (Another customer field?)
	* Order Number - EPOD_JOB.EPL_JOB_CODE		* Order Number - EPOD_JOB.EPL_JOB_CODE
	* Job Ref - ? assumed - EPOD_JOB.EPL_JOB_ID		* Job Ref - ? assumed - EPOD_JOB.EPL_JOB_ID
Line 66:		Line 66:
	* System Type - EPOD_SERVICE.EPL_SYSTEM_TYPE		* System Type - EPOD_SERVICE.EPL_SYSTEM_TYPE
	* Other - ?		* Other - ?
	* Priority Code -?		* Priority Code - ? (Service Level?)
	* Date Logged - EPOD_JOB.EPL_ORDER_DATE		* Date Logged - EPOD_JOB.EPL_ORDER_DATE
	* Time Logged - EPOD_JOB.EPL_ORDER_TIME (New field)		* Time Logged - EPOD_JOB.EPL_ORDER_TIME (New field)

Anw: v0.1 - Initial Creation

2013-05-09T10:42:38Z

v0.1 - Initial Creation

New page

<div class="noprint">
{{#vardefine:Client|ALS}}
{{#vardefine:ClientName|Air Link Systems}}
{{#vardefine:System|''CALIDUS'' eSERV}}
{{#vardefine:Doc_Title|ALS Excel Spreadsheet Report}}
{{#vardefine:Version|0.2}}
{{#vardefine:Date|9th May 2013}}
{{#vardefine:Reference|308846}}
{{#vardefine:Year|2013}}
</div>
{{Doc_Title
|Client={{#var:ClientName}}
|System={{#var:System}}
|Title={{#var:Doc_Title}}
|Reference=FS {{#var:Reference}}
|Version={{#var:Version}}
|Date={{#var:Date}}
|Year={{#var:Year}}
}}


<div class="noprint">
= 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 {{#var:System}} 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}} 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 - ?
* Site Name - ?
* Store Number - ?
* 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 -?
* 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}} 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}} 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}} 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 =

{{TestPlan_Header
|Title={{#var:Doc_Title}}
|Log={{#var:Reference}}
|Description=description of what is to be achieved
|MenuAccess=Where on the menus the item can be found
|Prerequisites=The prerequisites of the test
|Objective=The details of what each group of tests is to achieve
}}
{{ #vardefine: Cycle | 0 }}{{ #vardefine: SubCycle | 0 }}
{{TestPlan_CycleHeader
|Cycle={{ #vardefineecho: Cycle | {{ #expr: {{ #var: Cycle }} + 1 }} }}{{ #vardefine: SubCycle | {{ #var: Cycle }} }}
|Title=Area being tested in this cycle
|Notes=Any notes or prerequisites for the tests following.
}}  {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=The actions to follow
|Result=The expected result
|Remarks=
|PassFail=
}} {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=The actions to follow
|Result=The expected result
|Remarks=
|PassFail=
}} {{TestPlan_Test
|Test={{ #vardefineecho: SubCycle | {{ #expr: {{ #var: SubCycle }} + 0.01 }} }}
|Action=The actions to follow
|Result=The expected result
|Remarks=
|PassFail=
}} {{TestPlan_CycleFooter}}


{{Doc_Appendix
|Appendix=C
|Estimate=Y
|Glossary=EPOD
|Ref1=APRIL 2013.xls
|RefV1=N/A
|RefDate1=04/2013
|Ref2=EPOD Excel Reports v0.2.xlsx
|RefV2=0.2
|RefDate2=09/05/2013
|REQ=0
|EST=0.25
|FS=0.25
|TS=0
|DEV=1.25
|ST=0.25
|IMP=0.5
|Client={{#var:Client}}
|Year={{#var:Year}}
|FSEST=Y
|Rev1=Rev1
|Rev1Title=Rev1 Title
}}</div>
[[Category:{{#var:Client}} FS]]

FS 308846 ALS Excel Spreadsheet Report - Revision history

Anw: v0.2 - Added more field mapping

Anw: v0.1 - Initial Creation