Skip to main content

Writing HTML files

In these examples you will learn how to generate HTML reports based on your data.

Start by importing scalaql:

import scalaql._

// Docs classes
import scalaql.docs.DocUtils._

// Imports for examples
import java.nio.file.Paths

We'll use the following CSV dataset:

import scalaql.docs.CsvData._

val dir = Paths.get("docs/src/main/resources/annual-enterprise-survey-2020/")
// dir: java.nio.file.Path = docs/src/main/resources/annual-enterprise-survey-2020

val csvInput =         
  csv
   .read[EnterpriseSurvey]
   // In this case it's required to provide correct naming.
   .option(Naming.SnakeCase)
   .directory(dir, globPattern = "**/*.csv")
// csvInput: Iterable[EnterpriseSurvey] = Vector(
//   EnterpriseSurvey(
//     year = 2018,
//     industryCodeAnzsic = "O",
//     industryNameAnzsic = "Public Administration and Safety",
//     rmeSizeGrp = "d_10-19",
//     variable = "Sales, government funding, grants and subsidies",
//     value = "78",
//     unit = "DOLLARS(millions)"
//   ),
//   EnterpriseSurvey(
//     year = 2018,
//     industryCodeAnzsic = "O",
//     industryNameAnzsic = "Public Administration and Safety",
//     rmeSizeGrp = "d_10-19",
//     variable = "Total income",
//     value = "78",
//     unit = "DOLLARS(millions)"
//   ),
//   EnterpriseSurvey(
//     year = 2018,
//     industryCodeAnzsic = "O",
//     industryNameAnzsic = "Public Administration and Safety",
//     rmeSizeGrp = "d_10-19",
//     variable = "Total expenditure",
//     value = "72",
//     unit = "DOLLARS(millions)"
//   ),
//   EnterpriseSurvey(
//     year = 2018,
//     industryCodeAnzsic = "O",
//     industryNameAnzsic = "Public Administration and Safety",
//     rmeSizeGrp = "d_10-19",
//     variable = "Operating profit before tax",
//     value = "6",
//     unit = "DOLLARS(millions)"
//   ),
//   EnterpriseSurvey(
//     year = 2018,
//     industryCodeAnzsic = "O",
//     industryNameAnzsic = "Public Administration and Safety",
//     rmeSizeGrp = "d_10-19",
//     variable = "Total assets",
//     value = "39",
//     unit = "DOLLARS(millions)"
//   ),
//   EnterpriseSurvey(
//     year = 2018,
//     industryCodeAnzsic = "O",
// ...

And then generate the following report:

case class ByIndustry(
  industry:              String,
  totalProfitByIndustry: BigDecimal,
  avgProfitByIndustry:   BigDecimal)

case class ByYear(
  year:               Int,
  totalProfitPerYear: BigDecimal,
  avgProfitPerYear:   BigDecimal,
  byIndustry:         List[ByIndustry])

val reportData: Query[From[EnterpriseSurvey], ByYear] =
  select[EnterpriseSurvey]
    .groupBy(_.year)
    .aggregate { (year, data) =>
      val byIndustry = data.report(_.industryNameAnzsic) { (industry, data) =>
         (
          data.sumBy(_.decimalValue) &&
          data.avgBy(_.decimalValue)
        ).map { case (total, avg) => ByIndustry(industry, total, avg) }
      }
       
      (
        data.sumBy(_.decimalValue) &&
        data.avgBy(_.decimalValue) &&
        byIndustry
      ).map { case (total, avg, byIndustry) =>
        ByYear(year, total, avg, byIndustry)
      }
    }
// reportData: Query[From[EnterpriseSurvey], ByYear] = FROM(CsvData::EnterpriseSurvey) -> GROUP BY(Int) -> AGGREGATE(MdocSession::App::ByYear)

This is how it could be written as HTML file:

reportData
  .foreach(
    html
      .write[ByYear]
      .file(Paths.get("docs/target/report.html"))
  )
  .run(
    from(csvInput)
  )

It will produce the following report:

yeartotalProfitPerYearavgProfitPerYearindustrytotalProfitByIndustryavgProfitByIndustry
201417617937.011381.09625322997416020671834625323
Rental, Hiring and Real Estate Services966078.011926.88888888888888888888888888889
Mining127488.01573.925925925925925925925925925926
Other Services230631.02847.296296296296296296296296296296
Administrative and Support Services299912.03702.617283950617283950617283950617
Health Care and Social Assistance459585.05673.888888888888888888888888888889
Manufacturing1328028.016395.40740740740740740740740740741
Transport, Postal and Warehousing446371.05510.753086419753086419753086419753
Agriculture, Forestry and Fishing1019889.012591.22222222222222222222222222222
Electricity, Gas, Water and Waste Services342154.04224.123456790123456790123456790123
Retail Trade873235.010780.67901234567901234567901234568
Public Administration and Safety34866.0430.4444444444444444444444444444444
Financial and Insurance Services2444200.030175.30864197530864197530864197531
Accommodation and Food Services392687.04847.987654320987654320987654320988
Information Media and Telecommunications224878.02776.271604938271604938271604938272
Professional, Scientific and Technical Services679487.08388.728395061728395061728395061728
All Industries5897233.0655248.1111111111111111111111111111
Education and Training134125.01655.864197530864197530864197530864
Construction692033.08543.617283950617283950617283950617
Wholesale Trade868073.010716.95061728395061728395061728395
Arts and Recreation Services156984.01938.074074074074074074074074074074
202022770336.014709.51937984496124031007751937984
Rental, Hiring and Real Estate Services1465873.018097.19753086419753086419753086420
Mining92843.01146.209876543209876543209876543210
Other Services298262.03682.246913580246913580246913580247
Administrative and Support Services404787.04997.370370370370370370370370370370
Health Care and Social Assistance578302.07139.530864197530864197530864197531
Manufacturing1540334.019016.46913580246913580246913580247
Transport, Postal and Warehousing579407.07153.172839506172839506172839506173
Agriculture, Forestry and Fishing1230707.015193.91358024691358024691358024691
Electricity, Gas, Water and Waste Services442509.05463.074074074074074074074074074074
Retail Trade1081675.013354.01234567901234567901234567901
Public Administration and Safety50871.0628.0370370370370370370370370370370
Financial and Insurance Services3187409.039350.72839506172839506172839506173
Accommodation and Food Services553217.06829.839506172839506172839506172840
Information Media and Telecommunications224084.02766.469135802469135802469135802469
Professional, Scientific and Technical Services915891.011307.29629629629629629629629629630
All Industries7607349.0845261.0
Education and Training171708.02119.851851851851851851851851851852
Construction1071439.013227.64197530864197530864197530864
Wholesale Trade1092697.013490.08641975308641975308641975309
Arts and Recreation Services180972.02234.222222222222222222222222222222
201719971573.012901.53294573643410852713178294574
Rental, Hiring and Real Estate Services1195456.014758.71604938271604938271604938272
Mining81828.01010.222222222222222222222222222222
Other Services264072.03260.148148148148148148148148148148
Administrative and Support Services350933.04332.506172839506172839506172839506
Health Care and Social Assistance518648.06403.061728395061728395061728395062
Manufacturing1375371.016979.88888888888888888888888888889
Transport, Postal and Warehousing507208.06261.827160493827160493827160493827
Agriculture, Forestry and Fishing1120211.013829.76543209876543209876543209877
Electricity, Gas, Water and Waste Services365535.04512.777777777777777777777777777778
Retail Trade979185.012088.70370370370370370370370370370
Public Administration and Safety45004.0555.6049382716049382716049382716049
Financial and Insurance Services2825184.034878.81481481481481481481481481481
Accommodation and Food Services493103.06087.691358024691358024691358024691
Information Media and Telecommunications206974.02555.234567901234567901234567901235
Professional, Scientific and Technical Services784626.09686.740740740740740740740740740741
All Industries6689013.0743223.6666666666666666666666666667
Education and Training156564.01932.888888888888888888888888888889
Construction876201.010817.29629629629629629629629629630
Wholesale Trade962118.011878.0
Arts and Recreation Services174339.02152.333333333333333333333333333333
201116606991.010728.03036175710594315245478036176
Rental, Hiring and Real Estate Services875506.010808.71604938271604938271604938272
Mining117128.01446.024691358024691358024691358025
Other Services219366.02708.222222222222222222222222222222
Administrative and Support Services276281.03410.876543209876543209876543209877
Health Care and Social Assistance433505.05351.913580246913580246913580246914
Manufacturing1274588.015735.65432098765432098765432098765
Transport, Postal and Warehousing448624.05538.567901234567901234567901234568
Agriculture, Forestry and Fishing956289.011806.03703703703703703703703703704
Electricity, Gas, Water and Waste Services342149.04224.061728395061728395061728395062
Retail Trade823416.010165.62962962962962962962962962963
Public Administration and Safety27670.0341.6049382716049382716049382716049
Financial and Insurance Services2411805.029775.37037037037037037037037037037
Accommodation and Food Services371342.04584.469135802469135802469135802469
Information Media and Telecommunications221778.02738.0
Professional, Scientific and Technical Services620627.07662.061728395061728395061728395062
All Industries5543169.0615907.6666666666666666666666666667
Education and Training126562.01562.493827160493827160493827160494
Construction588202.07261.753086419753086419753086419753
Wholesale Trade787761.09725.444444444444444444444444444444
Arts and Recreation Services141223.01743.493827160493827160493827160494
201518489341.011944.01873385012919896640826873385
Rental, Hiring and Real Estate Services1052271.012991.0
Mining113810.01405.061728395061728395061728395062
Other Services241886.02986.246913580246913580246913580247
Administrative and Support Services315654.03896.962962962962962962962962962963
Health Care and Social Assistance484024.05975.604938271604938271604938271605
Manufacturing1333676.016465.13580246913580246913580246914
Transport, Postal and Warehousing477372.05893.481481481481481481481481481481
Agriculture, Forestry and Fishing1057814.013059.43209876543209876543209876543
Electricity, Gas, Water and Waste Services374604.04624.740740740740740740740740740741
Retail Trade902358.011140.22222222222222222222222222222
Public Administration and Safety36404.0449.4320987654320987654320987654321
Financial and Insurance Services2614941.032283.22222222222222222222222222222
Accommodation and Food Services415225.05126.234567901234567901234567901235
Information Media and Telecommunications223511.02759.395061728395061728395061728395
Professional, Scientific and Technical Services711419.08782.950617283950617283950617283951
All Industries6185603.0687289.2222222222222222222222222222
Education and Training142863.01763.740740740740740740740740740741
Construction747389.09227.024691358024691358024691358025
Wholesale Trade894847.011047.49382716049382716049382716049
Arts and Recreation Services163670.02020.617283950617283950617283950617
201619124775.012354.50581395348837209302325581395
Rental, Hiring and Real Estate Services1121242.013842.49382716049382716049382716049
Mining91742.01132.617283950617283950617283950617
Other Services249918.03085.407407407407407407407407407407
Administrative and Support Services331248.04089.481481481481481481481481481481
Health Care and Social Assistance496094.06124.617283950617283950617283950617
Manufacturing1337211.016508.77777777777777777777777777778
Transport, Postal and Warehousing495140.06112.839506172839506172839506172840
Agriculture, Forestry and Fishing1073602.013254.34567901234567901234567901235
Electricity, Gas, Water and Waste Services360285.04447.962962962962962962962962962963
Retail Trade932624.011513.87654320987654320987654320988
Public Administration and Safety42900.0529.6296296296296296296296296296296
Financial and Insurance Services2772232.034225.08641975308641975308641975309
Accommodation and Food Services441040.05444.938271604938271604938271604938
Information Media and Telecommunications194420.02400.246913580246913580246913580247
Professional, Scientific and Technical Services736316.09090.320987654320987654320987654321
All Industries6414442.0712715.7777777777777777777777777778
Education and Training151203.01866.703703703703703703703703703704
Construction798814.09861.901234567901234567901234567901
Wholesale Trade913542.011278.29629629629629629629629629630
Arts and Recreation Services170760.02108.148148148148148148148148148148
201216950881.010950.18152454780361757105943152455
Rental, Hiring and Real Estate Services905334.011176.96296296296296296296296296296
Mining115803.01429.666666666666666666666666666667
Other Services223915.02764.382716049382716049382716049383
Administrative and Support Services286350.03535.185185185185185185185185185185
Health Care and Social Assistance440834.05442.395061728395061728395061728395
Manufacturing1285851.015874.70370370370370370370370370370
Transport, Postal and Warehousing431622.05328.666666666666666666666666666667
Agriculture, Forestry and Fishing981298.012114.79012345679012345679012345679
Electricity, Gas, Water and Waste Services357895.04418.456790123456790123456790123457
Retail Trade837105.010334.62962962962962962962962962963
Public Administration and Safety31041.0383.2222222222222222222222222222222
Financial and Insurance Services2455656.030316.74074074074074074074074074074
Accommodation and Food Services385758.04762.444444444444444444444444444444
Information Media and Telecommunications223639.02760.975308641975308641975308641975
Professional, Scientific and Technical Services631644.07798.074074074074074074074074074074
All Industries5664227.0629358.5555555555555555555555555556
Education and Training127219.01570.604938271604938271604938271605
Construction604158.07458.740740740740740740740740740741
Wholesale Trade807250.09966.049382716049382716049382716049
Arts and Recreation Services154282.01904.716049382716049382716049382716
201316984331.010971.79005167958656330749354005168
Rental, Hiring and Real Estate Services938279.011583.69135802469135802469135802469
Mining123839.01528.876543209876543209876543209877
Other Services224661.02773.592592592592592592592592592593
Administrative and Support Services292889.03615.913580246913580246913580246914
Health Care and Social Assistance447094.05519.679012345679012345679012345679
Manufacturing1269578.015673.80246913580246913580246913580
Transport, Postal and Warehousing435694.05378.938271604938271604938271604938
Agriculture, Forestry and Fishing977816.012071.80246913580246913580246913580
Electricity, Gas, Water and Waste Services342338.04226.395061728395061728395061728395
Retail Trade850872.010504.59259259259259259259259259259
Public Administration and Safety34749.0429.0
Financial and Insurance Services2364424.029190.41975308641975308641975308642
Accommodation and Food Services393673.04860.160493827160493827160493827160
Information Media and Telecommunications214417.02647.123456790123456790123456790123
Professional, Scientific and Technical Services650865.08035.370370370370370370370370370370
All Industries5680357.0631150.7777777777777777777777777778
Education and Training126114.01556.962962962962962962962962962963
Construction635817.07849.592592592592592592592592592593
Wholesale Trade825011.010185.32098765432098765432098765432
Arts and Recreation Services155844.01924.0
201922040794.014238.23901808785529715762273901809
Rental, Hiring and Real Estate Services1374826.016973.16049382716049382716049382716
Mining91471.01129.271604938271604938271604938272
Other Services287599.03550.604938271604938271604938271605
Administrative and Support Services394332.04868.296296296296296296296296296296
Health Care and Social Assistance558627.06896.629629629629629629629629629630
Manufacturing1500013.018518.67901234567901234567901234568
Transport, Postal and Warehousing572059.07062.456790123456790123456790123457
Agriculture, Forestry and Fishing1206071.014889.76543209876543209876543209877
Electricity, Gas, Water and Waste Services412243.05089.419753086419753086419753086420
Retail Trade1047128.012927.50617283950617283950617283951
Public Administration and Safety45620.0563.2098765432098765432098765432099
Financial and Insurance Services3119342.038510.39506172839506172839506172840
Accommodation and Food Services540263.06669.913580246913580246913580246914
Information Media and Telecommunications216052.02667.308641975308641975308641975309
Professional, Scientific and Technical Services875036.010802.91358024691358024691358024691
All Industries7366333.0818481.4444444444444444444444444444
Education and Training170110.02100.123456790123456790123456790123
Construction1024114.012643.38271604938271604938271604938
Wholesale Trade1067885.013183.76543209876543209876543209877
Arts and Recreation Services171670.02119.382716049382716049382716049383
201820914247.013510.49547803617571059431524547804
Rental, Hiring and Real Estate Services1268035.015654.75308641975308641975308641975
Mining82182.01014.592592592592592592592592592593
Other Services276024.03407.703703703703703703703703703704
Administrative and Support Services380718.04700.222222222222222222222222222222
Health Care and Social Assistance529891.06541.864197530864197530864197530864
Manufacturing1443526.017821.30864197530864197530864197531
Transport, Postal and Warehousing542080.06692.345679012345679012345679012346
Agriculture, Forestry and Fishing1161882.014344.22222222222222222222222222222
Electricity, Gas, Water and Waste Services388419.04795.296296296296296296296296296296
Retail Trade1017321.012559.51851851851851851851851851852
Public Administration and Safety46008.0568.0
Financial and Insurance Services2930553.036179.66666666666666666666666666667
Accommodation and Food Services514228.06348.493827160493827160493827160494
Information Media and Telecommunications215095.02655.493827160493827160493827160494
Professional, Scientific and Technical Services819811.010121.12345679012345679012345679012
All Industries6986319.0776257.6666666666666666666666666667
Education and Training161120.01989.135802469135802469135802469136
Construction956983.011814.60493827160493827160493827160
Wholesale Trade1016941.012554.82716049382716049382716049383
Arts and Recreation Services177111.02186.555555555555555555555555555556