report writing
Download
Skip this Video
Download Presentation
Report writing

Loading in 2 Seconds...

play fullscreen
1 / 38

Report writing - PowerPoint PPT Presentation


  • 114 Views
  • Uploaded on

Report writing. Building NASIS Reports. Purpose of Reports. To get data from the database for presentation to users and customers. Reports generally query the selected set Permanent data can be accessed

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Report writing' - dragon


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
purpose of reports
Purpose of Reports
  • To get data from the database for presentation to users and customers.
  • Reports generally query the selected set
  • Permanent data can be accessed
  • Query portion of the report selects specified data elements to be used in generation of the output.
reports
Query

Data manipulation

Output

Reports
query
Query
  • SQL required structure
    • EXEC SQL
    • SELECT columns statement
    • FROM tablestatement

Example:

EXEC SQL SELECT compname

FROM component;.

parts of report scripts
Parts of Report Scripts
  • Optional parts
    • WHERE clause
      • table join statements
      • conditional clauses
    • Sort statement
    • Aggregation statements
    • Data manipulation
    • Templates
    • Section and Column formatting
    • Page formatting
parts of scripts
Parts of Scripts
  • Data manipulation
    • Multiple queries
    • DERIVE statements
    • DEFINE statements
    • INTERPRET statements
    • PARAMETER statements
overview ways to visualize the report process
Overview:Ways to visualize the report process
  • Report process flow chart
  • Report styles
  • Data transformations
slide9
NASIS

Reports Flow Chart

report styles
Report Styles
  • Tabular
    • headings
    • columns
  • Narrative
    • bullets
    • paragraphs
  • Export
    • delimited text
creating a nasis report
Open the Reports Explorer

On menu or toolbar choose to open new report

Enter report name

Enter report format

Creating a NASIS Report
creating a list of map units
Creating a list of map units
  • Purpose: Demonstrates default report format.
  • Problem: List each national mapunit symbol and mapunit name.
  • Tables: mapunit.
  • References:
    • EXEC SQL (p. 22)
    • table structure report
map units
EXEC SQL

SELECT nationalmusym, muname

FROM mapunit;.

Report SQL begins with EXEC SQL and ends with semicolon and period

Selects the columns needed for the report

Identifies the table they columns reside

Map units
adding conditions
EXEC SQL

SELECT nationalmusym, muname

FROM mapunit

WHERE muname matches ‘Harney silt loam, 0 to 1 percent slopes’;.

Adding conditions

Need to understand the data types and comparisons

Adding conditions
adding sorts
EXEC SQL

SELECT nationalmusym, muname

FROM mapunit

WHERE muname matches ‘Harney *’;

SORT BY muname.

Notice the semicolon and period

Adding sorts
adding tables
EXEC SQL

SELECT nationalmusym, muname, compname

FROM mapunit

INNER JOIN correlation by default

INNER JOIN datamapunit by default

INNER JOIN component by default

WHERE muname matches ‘Harney *’;

SORT BY nationalmusym.

Adding tables
exercise rpt 1
Based on what you know, create a default format report that shows the area symbol, map unit symbol, national mapunit symbol, mapunit name, and corresponding data mapunit description for mapunits in the survey area where you reside. Sort the report by mapunit symbol.

Email to [email protected]

Exercise Rpt 1
using outer joins
Using outer joins
  • Explained in queries
  • INNER JOIN 1:1 join
  • OUTER JOIN
    • LEFT
    • RIGHT
    • FULL
exercise 2
Exercise 2
  • Add to exercise 1 and add to the report all components in the survey area and include the component restriction records that are available.
  • Send me a screen shot of the results in both txt and html format
exercise 2 answer
EXEC SQL

SELECT areasymbol, nationalmusym, muname, compname, reskind

FROM area

INNER JOIN legend by default

INNER JOIN lmapunit by default

INNER JOIN mapunit by default

INNER JOIN correlation by default

INNER JOIN datamapunit by default

INNER JOIN component by default

LEFT OUTER JOIN corestrictions by default;

SORT BY nationalmusym.

Exercise 2 answer
aggregation
Aggregation
  • Aggregation can be done in two methods
    • Group by
    • Aggregate
  • Use the previous query and use the Group By to count the number of reskind for each map unit.
g roup by example
EXEC SQL

SELECT areasymbol, nationalmusym, reskind, count(*) as rowcount

FROM area

INNER JOIN legend by default

INNER JOIN lmapunit by default

INNER JOIN mapunit by default

INNER JOIN correlation by default

INNER JOIN datamapunit by default

INNER JOIN component by default

LEFT OUTER JOIN corestrictions by default

GROUP BY areasymbol, nationalmusym, reskind;.

Group by example
aggregate
AGGREGATE
  • More functionality than GROUP BY
  • Aggregation by column
  • Allows Crosstab formatting
  • Aggregate by ROWS and by COLUMN
  • Row aggregation allowed in first SQL only
  • Sum, Average, First, Last, Min, Max, None, Unique and List
aggregate example
EXEC SQL select musym, muname, areaname,

lmuaoverlap.areaovacres acres

from mapunit, lmapunit, lmuaoverlap, laoverlap, area

where join area to laoverlap

and join laoverlap to lmuaoverlap

and join lmapunit to lmuaoverlap

and join lmapunit to mapunit;

SORT BY musym SYMBOL, areaname

AGGREGATE ROWS BY musym

COLUMN muname UNIQUE, acres SUM

CROSSTAB areaname CELLS acres.

Aggregate example
exercise 3
Using a default html report, create a report that will provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit nameExercise 3
example 3 answer
EXEC SQL

SELECT nationalmusym, muname, compname, comppct_r, repdmu

FROM mapunit

LEFT OUTER JOIN correlation by default

LEFT OUTER JOIN datamapunit by default

LEFT OUTER JOIN component by default;

SORT BY nationalmusym SYM, comppct_r DESC

AGGREGATE ROWS BY nationalmusym COLUMN compname NONE.

Example 3 answer
example 4
Example 4
  • Purpose: Demonstrates the use of Templates, Sections and Column formats.
  • Problem: Change default report output from 3a to formatted output using these features.
  • Tables: mapunit, correlation, datamapunit, component.
  • References:
    • TEMPLATE statement (p. 58)
    • SECTION statement (p. 43)
    • Column Layout specs (p. 53)
example 5
Example 5
  • Purpose: Demonstrates the use of Aggregation.
  • Problem: You wish to eliminate duplicated data in columns, using script from Example 4.
  • Tables:mapunit, correlation, datamapunit, component.
  • References:
    • Aggregation specs (p.26)
exercise 5a
Exercise 5a
  • Purpose: Use knowledge from previous examples.
  • Problem: Using 3a script, create a new report with formatted columns and headings that show mapunit symbols, mapunit names, component names and percentages.
    • Sort by mapunit symbol
    • show dominant components first
    • eliminate duplicate symbols and mu names
example 6
Example 6
  • Purpose: Demonstrates the use of Section Conditional statements.
  • Problem: Organize previous output in different fashion.
  • Tables: mapunit, correlation, datamapunit, component.
  • References:
    • SECTION statement (p. 43)
example 7
Example 7
  • Purpose: Demonstrates the use of multiple queries and defining variables.
  • Problem: The list of components and associated crops are on different db paths.
  • Tables: mapunit, correlation, datamapunit, component.
  • References:
    • BASE TABLE statement (p. 4)
    • DEFINE statement (p. 5)
    • NMCASE statement (p.10)
example 8
Example 8
  • Purpose: Demonstrates use of Property output.
  • Problem: You want to produce a listing of total AWC for each component in each map unit. Sort by musym, and rep comp percent
  • Tables: mapunit, correlation, datamapunit, component.
  • References:
    • DERIVE statement (p. 21)
exercise 8a
Exercise 8a
  • Purpose: to use knowledge learned to create a data export format report.
  • Problem: Develop a data export report in comma-delimited format showing mapunit symbol, component name, and minimum representative depth to seasonal high water table for the dominant component in each mu. Sort by mapunit symbol.
example 10 11
Example 10/11
  • Purpose: Demonstrates the use of headers to improve appearance of report.
  • Problem: Add page header to each page and a report title. List mapunit symbols and names. Show ssa name in report title.
  • References:
    • Header (p. 32)
    • Line specs (p. 47)
ad