Report writing
Download
1 / 38

Report writing - PowerPoint PPT Presentation


  • 107 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


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 shows the

  • Explained in queries

  • INNER JOIN 1:1 join

  • OUTER JOIN

    • LEFT

    • RIGHT

    • FULL


Exercise 2
Exercise shows the 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 shows the

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 shows the

  • 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 shows the

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 shows the

  • 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 shows the 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 shows the


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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

  • 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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

  • 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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

  • 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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name


Example 6
Example 6 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

  • 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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

  • 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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

  • 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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

  • 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 provide the national map unit symbol, map unit name and list of components without duplicating the national map unit symbol and map unit name

  • 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