Report writing
This presentation is the property of its rightful owner.
Sponsored Links
1 / 38

Report writing PowerPoint PPT Presentation


  • 72 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Report writing

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


Report writing

Report writing


Building nasis reports

Building NASIS Reports


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


Report writing

NASIS

Reports Flow Chart


Report styles

Report Styles

  • Tabular

    • headings

    • columns

  • Narrative

    • bullets

    • paragraphs

  • Export

    • delimited text


Data transformations structure of sql output

Data TransformationsStructure of SQL output


Data transformations structure of report variables

Data TransformationsStructure of report variables


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


Aggregation1

aggregation


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 name

Exercise 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


Exercise 5a answer

Exercise 5a answer


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)


  • Login