Data Extract General Information
The eLandings web application provides an interface to search for and retrieve finalized landing report and production report data from the eLandings database. Using this feature you can extract multiple Landings or Production Reports from eLandings at the same time. The data is retrieved by the Processor Code or the Federal Permit Number. The Report Extract page provides users with the ability to extract these reports in three different formats, XML, CSV, or EXCEL.
In addition to the report extract feature, users can download and customize fishery specific landing and production report templates, providing a consistent output of the information pertinent to the end users' needs.
Limited users are restricted to reports that they created and were the last person to update that report. Limited users will not see the complete data set, using this tool.
NOTE: This tool cannot be used as a method for submitting landing and production report data. The tool is designed to extract data housed within the eLandings database.
When you extract data as an Excel spreadsheet file, you may receive a file error message: FILE ERROR: DATA MAY BE LOST when you open the file with MS Excel. The eLandings team has never experienced any loss of data. This error message appears to be generated when MS Excel recognized that the file was created with a non-Microsoft product. Click OK and proceed.
The most important aspect of this feature is to remember that only data entered into the eLandings system can be extracted. If a portion of your landing reports are being documented on conventional paper ADF&G fish ticket forms, then these will not be available for extraction as they are not contained within the eLandings database.
How to Extract Landing and Production Reports
Access the Data Extract page, after login, by clicking on the "Data Extract" link.
By clicking the link you will navigate to the Report Extract web page, while will allow you to extract multiple landing or production reports.
The Report Extract web page includes instructions that will prove to be helpful as you learn to use this tool. This document will provide you with detailed instructions to create both a selection criteria and an output template.
All reports that you wish to extract must be in a finalized status. For more information on the status of landing reports, please reference https://elandings.alaska.gov/confluence/display/doc/The+eLandings+System+Reporting+Sequence
- Report Date:
- You can select a specific date range or use the drop down menu to select general time frames
- Processor Code (Owner):
- Select this option if you are trying to locate and extract landing report data
- Federal Permit Number:
- Select this option if you are trying to locate and extract production report information
- Report Type:
- At-Sea Production Report
- Shoreside Production Report
- Output Format:
- MS Excel
Data Extraction Selection Criteria
We provide several ways to select the data to extract. The fields that are required to be completed are highlighted in yellow and defaults are displayed in the illustration below.
The most important consideration when establishing the data extract criteria is the limitation of 5000 records. This limitation is in place to protect the eLandings system and its other users from experiencing degradation of service. If you try and request more than 5000 landing reports, you will get the following error:
ERROR – 1566: Search returned too many records to display. Refine search criteria to limit results.
You may choose the Report Date (the default) which is the date of landing – when the report was initially created within the eLandings system or the Last Update Date, the date of the last update of the landing report by an authorized user within your operation. An example of a Last Update is when grading and pricing of halibut is updated within the eLandings system several days after the delivery has occurred.
Once you have determined the date you wish to base your criteria upon, select the date range. You can enter a range such as or use the drop down, as illustrated above.
Again, remember that the Report Extract feature is designed to only return a maximum of 5000 records. So when you select your date range think about the volume of tickets that are generated by your operation. If a query request will return more than 1000 landing reports, you will get the following error:
ERROR – 1566: Search returned too many records to display. Refine search criteria to limit results.
If you get this error, simply narrow your date range so that it will return fewer than 5000 landing reports.
Selecting the Report Type also helps to limit the number of Landing Reports returned. If you do not select a specific fishery report type, groundfish, salmon, or crab landing reports, the Report Extract feature will return all landing reports. If you wish to have At-sea Production Reports or Shoreside Production Reports, you will need to specifically choose a production report type.
When you login to eLandings our user account is tied to your operation. If you are associated with multiple operations, you can have several Processor Codes and/or Federal Permit Numbers, which will display in the drop-down menu, indicated from the down arrow
You can also select landing reports by tender vessel and more specifically, by the tender batch for this tender vessel. If you uniquely name each tender batch you do not need to include the Tender ADFG Vessel Number.
Detail Type & Repeating Data
Landing Report data includes information on the landing characteristics, such as vessel name and ADFG number, CFEC permit, dates, areas fished, etc. Landing Report data also includes itemized catch. Frequently this itemized catch is multiple rows of data that is linked to the landing characteristics.
The Report Extract feature defaults to "Fill in repeating data" as illustrated below.
If you remove the check from the "Fill in repeating data" box, the CFEC permit is listed once and the itemized catch with grading and pricing are listed as separate rows.
When multiple species items and divisions under these species by grading and pricing occur, a new landing report record row is created. When multiple statistical areas and harvest by percentages are also documented on the landing report, the landing characteristics, such as vessel name and ADFG number remain identical thru all records for this landing, but the itemized catch is divided by statistical areas. Creating a report that incorporates both divisions creates unmanageable data for most end users. When using the Report Extract feature you will need to choose Grading/Pricing or Statistical Area in Detail Type. The application default is to fill in the data and Grading/Pricing.
Once you've made your selections, click on the Extract button.
A windows dialog box will pop-up asking you if you want to Save or Open the file.
NOTE: The Report Extract feature is designed to return a maximum of 1000 records.
This limitation is in place to protect the eLandings system and its users from experiencing degradation of service.
Data Extract Formats
Data can be extracted in XML, Microsoft Excel or CVS format. Each of these formats has specific benefits and your selection should be driven by your specific needs and familiarity with each of these formats. If you are planning to import the extracted reports into a business application, you should investigate the appropriate format by referencing the documentation associated with that application prior to selection.
The XML file can be imported into MS Excel or Access, but is primarily imported into specialized business applications.
Note that the XML file provides information on both the guess estimate of the number of records that should be received from the selection criteria and the final total of landing reports. These estimated counts are used internally by the system and are not meaningful to the end user.
Data Extract data using the Template
If you wish to extract landing or production report data on a regular basis with a consistent format, please explore the Reports Extract Template. This feature allows you to customize and save multiple report structures within an MS Excel or CSV format.
In preparation to create a report output template, you should determine the specific information you need. A landing report contains many data elements. Review a fish ticket landing report and highlight the information you want returned. As an example, you may want information on the Date of Landings, but not need Date Fish Began. The templates are fishery and report specific. You will establish a separate report template for groundfish, salmon, or crab landing reports and a separate report for production reports.
Once you have determined the information you want incorporated into your report, select the "Report Type" from the dropdown menu and then select "Download Template".
A File Download dialog box will appear, select "Open".
MS Excel will open and an odd file will be displayed. This file displays only the field or column names for each to the eLandings data elements. In this case, it is landing report data.
If you chose Shoreside Production Report, the fields in the template would be appropriate to that report type.
Creating the Template
To create the template, simply scroll left to right through the field headers and delete the columns that you do NOT want to included in your report. You can also change the order of columns.
Please note The column header names reference the eLandings database and cannot be modified. For example: date_fishing_ended.
Full documentation on each of the pre-defined Data Extract template are located at: Report Extract Spreadsheet Columns
The following fields were selected for a salmon report. Many columns were deleted and moved, but NO column names were modified. The template is saved using the Save As function. Each template you create needs to be uniquely named.
You can also add columns that get their values from formulas.
- Insert the column where you want it.
- Give the column a unique column name. If the column name is the same as one of the predefined columns the formula will be overwritten by the data for the column name.
- Create the formula and copy it to all cells in the column where you want it to appear. The report extract fills in the cells of the template, it does not copy rows to create new ones, so if the formula is not on a row you won't see data in the column for that row.
- For example, a new column labelled "Amount" could be created. The values for rows 2 to 1000 could be set a formula multiplying the sold_weight times the price.
Using the Template
To use your template complete the Selection Criteria for Dates, Processor Code or Federal Permit Number, and report type - en to download your data using the the template select "Browse".
Locate your template file, in this example "Salmon Tender Template" and open. The template is loaded and ready to extract.
When you click "Extract" your data will be loaded into your custom defined template and only the columns you chose to save will be loaded with data.