library(testthat) # most common expectations: # equality: expect_equal() and expect_identical() # regexp: expect_match() # catch-all: expect_true() and expect_false() # console output: expect_output() # messages: expect_message() # warning: expect_warning() # errors: expect_error() escapeString <- function(s) { t <- gsub("(\\\\)", "\\\\\\\\", s) t <- gsub("(\n)", "\\\\n", t) t <- gsub("(\r)", "\\\\r", t) t <- gsub("(\")", "\\\\\"", t) return(t) } prepStr <- function(s) { t <- escapeString(s) u <- eval(parse(text=paste0("\"", t, "\""))) if(s!=u) stop("Unable to escape string!") t <- paste0("\thtml <- \"", t, "\"") utils::writeClipboard(t) return(invisible()) } evaluationMode <- "sequential" processingLibrary <- "dplyr" description <- "test: sequential dplyr" countFunction <- "n()" isDevelopmentVersion <- (length(strsplit(packageDescription("pivottabler")$Version, "\\.")[[1]]) > 3) testScenarios <- function(description="test", releaseEvaluationMode="batch", releaseProcessingLibrary="dplyr", runAllForReleaseVersion=FALSE) { isDevelopmentVersion <- (length(strsplit(packageDescription("pivottabler")$Version, "\\.")[[1]]) > 3) if(isDevelopmentVersion||runAllForReleaseVersion) { evaluationModes <- c("sequential", "batch") processingLibraries <- c("dplyr", "data.table") } else { evaluationModes <- releaseEvaluationMode processingLibraries <- releaseProcessingLibrary } testCount <- length(evaluationModes)*length(processingLibraries) c1 <- character(testCount) c2 <- character(testCount) c3 <- character(testCount) c4 <- character(testCount) testCount <- 0 for(evaluationMode in evaluationModes) for(processingLibrary in processingLibraries) { testCount <- testCount + 1 c1[testCount] <- evaluationMode c2[testCount] <- processingLibrary c3[testCount] <- paste0(description, ": ", evaluationMode, " ", processingLibrary) c4[testCount] <- ifelse(processingLibrary=="data.table", ".N", "n()") } df <- data.frame(evaluationMode=c1, processingLibrary=c2, description=c3, countFunction=c4, stringsAsFactors=FALSE) return(df) } context("OUTLINE LAYOUT TESTS") scenarios <- testScenarios("Basic outline layout (outline before)") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=TRUE) pt$addRowDataGroups("PowerType") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getCss())) # prepStr(as.character(pt$getHtml())) css <- ".Table {display: table; border-collapse: collapse; }\r\n.ColumnHeader {font-family: Arial; font-size: 0.75em; border: 1px solid lightgray; vertical-align: middle; font-weight: bold; background-color: #F2F2F2; padding: 2px; text-align: center; }\r\n.RowHeader {font-family: Arial; font-size: 0.75em; border: 1px solid lightgray; vertical-align: middle; font-weight: bold; background-color: #F2F2F2; padding: 2px 8px 2px 2px; text-align: left; }\r\n.Cell {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; }\r\n.OutlineColumnHeader {font-family: Arial; font-size: 0.75em; border: 1px solid lightgray; vertical-align: middle; font-weight: bold; background-color: #F2F2F2; padding: 2px; text-align: center; }\r\n.OutlineRowHeader {font-family: Arial; font-size: 0.75em; border: 1px solid lightgray; vertical-align: middle; font-weight: bold; background-color: #F2F2F2; padding: 2px 8px 2px 2px; text-align: left; }\r\n.OutlineCell {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; background-color: #F8F8F8; font-weight: bold; }\r\n.Total {font-family: Arial; font-size: 0.75em; padding: 2px 2px 2px 8px; border: 1px solid lightgray; vertical-align: middle; text-align: right; }\r\n" html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales 
 DMU30798303909
Total30798303909
CrossCountry 
 DMU221336322196
HST732732
Total228656322928
London Midland 
 DMU5638559111229
EMU88492820137050
Total144873379248279
Virgin Trains 
 DMU21372137
EMU64576457
Total85948594
Total 490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getCss()), css) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Basic outline layout (outline before and after)") for(i in 1:nrow(scenarios)) { evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=TRUE, outlineAfter=TRUE) pt$addRowDataGroups("PowerType") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales 
 DMU30798303909
Total30798303909
  
CrossCountry 
 DMU221336322196
HST732732
Total228656322928
  
London Midland 
 DMU5638559111229
EMU88492820137050
Total144873379248279
  
Virgin Trains 
 DMU21372137
EMU64576457
Total85948594
  
Total 490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Basic outline layout (doNotMerge)") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(mergeSpace="doNotMerge"), outlineTotal=TRUE) pt$addRowDataGroups("PowerType") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales 
 DMU30798303909
Total30798303909
CrossCountry 
 DMU221336322196
HST732732
Total228656322928
London Midland 
 DMU5638559111229
EMU88492820137050
Total144873379248279
Virgin Trains 
 DMU21372137
EMU64576457
Total85948594
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Basic outline layout (dataGroupsOnly)") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(mergeSpace="dataGroupsOnly"), outlineTotal=TRUE) pt$addRowDataGroups("PowerType") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales
 DMU30798303909
Total30798303909
CrossCountry
 DMU221336322196
HST732732
Total228656322928
London Midland
 DMU5638559111229
EMU88492820137050
Total144873379248279
Virgin Trains
 DMU21372137
EMU64576457
Total85948594
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Basic outline layout (cellsOnly)") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(mergeSpace="cellsOnly"), outlineTotal=TRUE) pt$addRowDataGroups("PowerType") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales  
 DMU30798303909
Total30798303909
CrossCountry  
 DMU221336322196
HST732732
Total228656322928
London Midland  
 DMU5638559111229
EMU88492820137050
Total144873379248279
Virgin Trains  
 DMU21372137
EMU64576457
Total85948594
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Basic outline layout (dataGroupsAndCellsAs1)") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(mergeSpace="dataGroupsAndCellsAs1"), outlineTotal=TRUE) pt$addRowDataGroups("PowerType") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales
 DMU30798303909
Total30798303909
CrossCountry
 DMU221336322196
HST732732
Total228656322928
London Midland
 DMU5638559111229
EMU88492820137050
Total144873379248279
Virgin Trains
 DMU21372137
EMU64576457
Total85948594
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Basic outline layout (dataGroupsAndCellsAs2)") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(mergeSpace="dataGroupsAndCellsAs2"), outlineTotal=TRUE) pt$addRowDataGroups("PowerType") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales 
 DMU30798303909
Total30798303909
CrossCountry 
 DMU221336322196
HST732732
Total228656322928
London Midland 
 DMU5638559111229
EMU88492820137050
Total144873379248279
Virgin Trains 
 DMU21372137
EMU64576457
Total85948594
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Outline layout - with totals") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly"), outlineTotal=TRUE) pt$addRowDataGroups("PowerType", addTotal=FALSE) pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales30798303909
 DMU30798303909
CrossCountry228656322928
 DMU221336322196
HST732732
London Midland144873379248279
 DMU5638559111229
EMU88492820137050
Virgin Trains85948594
 DMU21372137
EMU64576457
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Outline layout - simple 3 row group levels)") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(mergeSpace="dataGroupsAndCellsAs2"), outlineTotal=TRUE) pt$addRowDataGroups("PowerType", outlineBefore=list(mergeSpace="dataGroupsAndCellsAs2"), outlineTotal=TRUE) pt$addRowDataGroups("Status") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales 
 DMU 
 A30188153833
C591574
R22
Total30798303909
Total30798303909
CrossCountry 
 DMU 
 A215616021621
C5462548
R26127
Total221336322196
HST 
 A709709
C2323
Total732732
Total228656322928
London Midland 
 DMU 
 A5534552011054
C10167168
R347
Total5638559111229
EMU 
 A85992733135930
C2358471082
R152338
Total88492820137050
Total144873379248279
Virgin Trains 
 DMU 
 A20282028
C107107
R22
Total21372137
EMU 
 A63316331
C119119
R77
Total64576457
Total85948594
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 669680) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Outline layout - formatted 3 row group levels") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly", groupStyleDeclarations=list(color="blue"), cellStyleDeclarations=list(color="blue")), outlineTotal=list(groupStyleDeclarations=list(color="blue"), cellStyleDeclarations=list(color="blue"))) pt$addRowDataGroups("PowerType", addTotal=FALSE, outlineBefore=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly")) pt$addRowDataGroups("Status", addTotal=FALSE) pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales30798303909
 DMU30798303909
 A30188153833
C591574
R22
CrossCountry228656322928
 DMU221336322196
 A215616021621
C5462548
R26127
HST732732
 A709709
C2323
London Midland144873379248279
 DMU5638559111229
 A5534552011054
C10167168
R347
EMU88492820137050
 A85992733135930
C2358471082
R152338
Virgin Trains85948594
 DMU21372137
 A20282028
C107107
R22
EMU64576457
 A63316331
C119119
R77
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 669680) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Outline layout - simple 3 row group levels part tabular") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly"), outlineTotal=TRUE) pt$addRowDataGroups("PowerType", addTotal=FALSE) pt$addRowDataGroups("Status") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales30798303909
 DMUA30188153833
C591574
R22
Total30798303909
CrossCountry228656322928
 DMUA215616021621
C5462548
R26127
Total221336322196
HSTA709709
C2323
Total732732
London Midland144873379248279
 DMUA5534552011054
C10167168
R347
Total5638559111229
EMUA85992733135930
C2358471082
R152338
Total88492820137050
Virgin Trains85948594
 DMUA20282028
C107107
R22
Total21372137
EMUA63316331
C119119
R77
Total64576457
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 669680) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Outline layout - formatted 3 row group levels part tabular") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly", groupStyleDeclarations=list(color="blue")), outlineTotal=list(groupStyleDeclarations=list(color="blue"))) pt$addRowDataGroups("PowerType", addTotal=FALSE) pt$addRowDataGroups("Status") pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales30798303909
 DMUA30188153833
C591574
R22
Total30798303909
CrossCountry228656322928
 DMUA215616021621
C5462548
R26127
Total221336322196
HSTA709709
C2323
Total732732
London Midland144873379248279
 DMUA5534552011054
C10167168
R347
Total5638559111229
EMUA85992733135930
C2358471082
R152338
Total88492820137050
Virgin Trains85948594
 DMUA20282028
C107107
R22
Total21372137
EMUA63316331
C119119
R77
Total64576457
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 669680) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Outline layout - outlined calculations on rows") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addColumnDataGroups("PowerType") pt$defineCalculation(calculationName="NumberOfTrains", caption="Number of Trains", summariseExpression=countFunction) pt$defineCalculation(calculationName="MaximumSpeedMPH", caption="Maximum Speed (MPH)", summariseExpression="max(SchedSpeedMPH, na.rm=TRUE)") pt$addRowCalculationGroups(outlineBefore=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly", groupStyleDeclarations=list(color="blue"), cellStyleDeclarations=list(color="blue")), outlineAfter=TRUE) pt$addRowDataGroups("TOC", addTotal=FALSE) pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
DMUEMUHSTTotalDMUEMUTotal 
Number of Trains3298715306732490256484282013468583710
 Arriva Trains Wales307930798308303909
CrossCountry2213373222865636322928
London Midland56388849144875591282013379248279
Virgin Trains2137645785948594
  
Maximum Speed (MPH)125125125125100100100125
 Arriva Trains Wales9090909090
CrossCountry125125125100100125
London Midland100110110100100100110
Virgin Trains125125125125
  
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 505565) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Sort outlined groups - by group value") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(groupStyleDeclarations=list(color="blue")), outlineAfter=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly", caption="Total ({value})", groupStyleDeclarations=list("font-style"="italic")), outlineTotal=list(groupStyleDeclarations=list(color="blue"), cellStyleDeclarations=list("color"="blue"))) pt$addRowDataGroups("PowerType", addTotal=FALSE) pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$sortRowDataGroups(levelNumber=1, orderBy="value", sortOrder="desc") pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Virgin Trains 
 DMU21372137
EMU64576457
Total (Virgin Trains)85948594
London Midland 
 DMU5638559111229
EMU88492820137050
Total (London Midland)144873379248279
CrossCountry 
 DMU221336322196
HST732732
Total (CrossCountry)228656322928
Arriva Trains Wales 
 DMU30798303909
Total (Arriva Trains Wales)30798303909
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Sort outlined groups - by calculation sorting on two levels") for(i in 1:nrow(scenarios)) { evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { skip_on_cran() library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(groupStyleDeclarations=list(color="blue")), outlineAfter=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly", caption="Total ({value})", groupStyleDeclarations=list("font-style"="italic")), outlineTotal=list(groupStyleDeclarations=list(color="blue"), cellStyleDeclarations=list("color"="blue"))) pt$addRowDataGroups("PowerType", addTotal=FALSE) pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$sortRowDataGroups(levelNumber=1, orderBy="calculation", sortOrder="desc") pt$sortRowDataGroups(levelNumber=2, orderBy="calculation", sortOrder="desc") pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
London Midland 
 EMU88492820137050
DMU5638559111229
Total (London Midland)144873379248279
CrossCountry 
 DMU221336322196
HST732732
Total (CrossCountry)228656322928
Virgin Trains 
 EMU64576457
DMU21372137
Total (Virgin Trains)85948594
Arriva Trains Wales 
 DMU30798303909
Total (Arriva Trains Wales)30798303909
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("Sort outlined groups - sorting only a subset of the row groups") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$addData(bhmtrains) pt$addColumnDataGroups("TrainCategory") pt$addRowDataGroups("TOC", outlineBefore=list(groupStyleDeclarations=list(color="blue")), outlineAfter=list(isEmpty=FALSE, mergeSpace="dataGroupsOnly", caption="Total ({value})", groupStyleDeclarations=list("font-style"="italic")), outlineTotal=list(groupStyleDeclarations=list(color="blue"), cellStyleDeclarations=list("color"="blue"))) pt$addRowDataGroups("PowerType", addTotal=FALSE) pt$defineCalculation(calculationName="TotalTrains", summariseExpression=countFunction) pt$sortRowDataGroups(levelNumber=1, fromIndex=4, orderBy="value", sortOrder="desc") pt$evaluatePivot() # pt$renderPivot() # sum(pt$cells$asMatrix(), na.rm=TRUE) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Express PassengerOrdinary PassengerTotal
Arriva Trains Wales 
 DMU30798303909
Total (Arriva Trains Wales)30798303909
Virgin Trains 
 DMU21372137
EMU64576457
Total (Virgin Trains)85948594
London Midland 
 DMU5638559111229
EMU88492820137050
Total (London Midland)144873379248279
CrossCountry 
 DMU221336322196
HST732732
Total (CrossCountry)228656322928
Total490253468583710
" expect_equal(sum(pt$cells$asMatrix(), na.rm=TRUE), 502260) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("pvt row/col tests: custom balance sheet 1") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { df <- data.frame( Level1 = c("Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income","Net entrepreneurial income"), Level2 = c("Net operating surplus","Net operating surplus","Net operating surplus","Net operating surplus","Net operating surplus","Net operating surplus","Net operating surplus","Net operating surplus","Net operating surplus","Interests and rents","Interests and rents","Interests and rents"), Level3 = c("Factor income","Factor income","Factor income","Factor income","Factor income","Factor income","Factor income","Factor income","Compensation of employees","Paid rent","Paid interest","Received interest"), Level4 = c("Net value added","Net value added","Net value added","Net value added","Net value added","Net value added","Taxes and subsidies","Taxes and subsidies",NA,NA,NA,NA), Level5 = c("Gross value added","Gross value added","Gross value added","Gross value added","Gross value added","Depreciation","Other taxes on production","Other subsidies (non-product specific)",NA,NA,NA,NA), Level6 = c("Production of the agricultural industry","Production of the agricultural industry","Production of the agricultural industry","Production of the agricultural industry","Intermediate services",NA,NA,NA,NA,NA,NA,NA), Level7 = c("Crop production","Livestock production","Production of agricultural services","Other production",NA,NA,NA,NA,NA,NA,NA,NA), MaxGroupLevel = c(7,7,7,7,6,5,5,5,3,3,3,3), Value = c(4210.9,4857.7,676.6,405.8,-6299,-2086.7,-145.4,2920.6,-1245,-236.5,-244.7,10.1) ) library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode, compatibility=list(noDataGroupNBSP=TRUE)) pt$addData(df) pt$addRowDataGroups("Level1", onlyAddGroupIf="MaxGroupLevel>=1", addTotal=FALSE, outlineBefore=list(isEmpty=FALSE)) pt$addRowDataGroups("Level2", onlyAddGroupIf="MaxGroupLevel>=2", addTotal=FALSE, outlineBefore=list(isEmpty=FALSE), dataSortOrder="custom", customSortOrder=c("Net operating surplus", "Interests and rents")) pt$addRowDataGroups("Level3", addTotal=FALSE, onlyAddGroupIf="MaxGroupLevel>=3", outlineBefore=list(isEmpty=FALSE), dataSortOrder="custom", customSortOrder=c("Factor income", "Compensation of employees", "Paid rent", "Paid interest", "Received interest")) pt$addRowDataGroups("Level4", onlyAddGroupIf="MaxGroupLevel>=4", addTotal=FALSE, outlineBefore=list(isEmpty=FALSE)) pt$addRowDataGroups("Level5", onlyAddGroupIf="MaxGroupLevel>=5", addTotal=FALSE, outlineBefore=list(isEmpty=FALSE), dataSortOrder="custom", customSortOrder=c("Gross value added", "Depreciation", "Other taxes on production", "Other subsidies (non-product specific)")) pt$addRowDataGroups("Level6", onlyAddGroupIf="MaxGroupLevel>=6", addTotal=FALSE, outlineBefore=list(isEmpty=FALSE), dataSortOrder="custom", customSortOrder=c("Production of the agricultural industry", "Intermediate Services")) pt$addRowDataGroups("Level7", onlyAddGroupIf="MaxGroupLevel>=7", addTotal=FALSE, dataSortOrder="custom", customSortOrder=c("Crop production", "Livestock production", "Production of agricultural services", "Other production")) pt$defineCalculation(calculationName="Value", summariseExpression="sum(Value)") pt$evaluatePivot() pt$removeEmptyRows() # pt$renderPivot() # round(sum(pt$cells$asMatrix(), na.rm=TRUE)) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 Value
Net entrepreneurial income2824.4
Net operating surplus3295.5
Factor income4540.5
Net value added1765.3
Gross value added3852
Production of the agricultural industry10151
Crop production4210.9
Livestock production4857.7
Production of agricultural services676.6
Other production405.8
Intermediate services-6299
Depreciation-2086.7
Taxes and subsidies2775.2
Other taxes on production-145.4
Other subsidies (non-product specific)2920.6
Compensation of employees-1245
Interests and rents-471.1
Paid rent-236.5
Paid interest-244.7
Received interest10.1
" expect_equal(round(sum(pt$cells$asMatrix(), na.rm=TRUE)), 31557) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("pvt row/col tests: custom balance sheet 2") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { df <- data.frame( Level1 = rep("Net entrepreneurial income", times=12), Level2 = c(rep("Net operating surplus", 9), rep("Interests and rents", 3)), Level3 = c(rep("Factor income", 8),"Compensation of employees","Paid rent", "Paid interest","Received interest"), Level4 = c(rep("Net value added", 6), rep("Taxes and subsidies", 2), rep(NA, 4)), Level5 = c(rep("Gross value added", 5),"Depreciation","Other taxes on production", "Other subsidies (non-product specific)", rep(NA, 4)), Level6 = c(rep("Production of the agricultural industry", 4), "Intermediate services", rep(NA, 7)), Level7 = c("Crop production","Livestock production", "Production of agricultural services","Other production", rep(NA, 8)), MaxGroupLevel = c(7,7,7,7,6,5,5,5,3,3,3,3), Budget2019 = c(4150.39,4739.2,625.6,325.8,-6427,-2049.3, -145.4,2847.3,-1149,-221.2,-307.6,12.8), Actual2019 = c(3978.8,4341.1,603.7,343,-6063.9,-2079.6, -136.8,2578.6,-1092.9,-203.3,-327.6,14.1), Budget2020 = c(4210.9,4857.7,676.6,405.8,-6299,-2086.7, -145.4,2920.6,-1245,-236.5,-244.7,10.1), Actual2020 = c(4373.7,5307.6,693.9,408.2,-7065.3,-1985, -154.2,3063,-1229.3,-268.2,-250.3,11.1) ) library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt$setDefault(addTotal=FALSE, outlineBefore=list(isEmpty=FALSE)) pt$addData(df) pt$addRowDataGroups("Level1", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>1") pt$addRowDataGroups("Level2", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>2", dataSortOrder="custom", customSortOrder=c("Net operating surplus", "Interests and rents")) pt$addRowDataGroups("Level3", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>3", dataSortOrder="custom", customSortOrder=c("Factor income", "Compensation of employees", "Paid rent", "Paid interest", "Received interest")) pt$addRowDataGroups("Level4", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>4") pt$addRowDataGroups("Level5", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>5", dataSortOrder="custom", customSortOrder=c("Gross value added", "Depreciation", "Other taxes on production", "Other subsidies (non-product specific)")) pt$addRowDataGroups("Level6", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>6", dataSortOrder="custom", customSortOrder=c("Production of the agricultural industry", "Intermediate Services")) pt$addRowDataGroups("Level7", dataSortOrder="custom", customSortOrder=c("Crop production", "Livestock production", "Production of agricultural services", "Other production")) pt$defineCalculation(calculationName="Budget", summariseExpression="sum(Budget2020)") pt$defineCalculation(calculationName="Actual", summariseExpression="sum(Actual2020)") pt$defineCalculation(calculationName="Variance", summariseExpression="sum(Actual2020)-sum(Budget2020)", format="%.1f") pt$evaluatePivot() # get the row groups relating to outline groups # (above leaf level) and leaf level groups grps <- pt$findRowDataGroups(outlineGroups="only", outlineLinkedGroupExists=FALSE) grps <- c(grps, pt$findRowDataGroups(atLevel=7)) # set the styling of these groups so the text isn't bold pt$setStyling(groups=grps, declarations =list("font-weight"="normal")) # find the cells corresponding to these groups rowNumbers <- sapply(grps, pt$findGroupRowNumbers) cells <- pt$getCells(rowNumbers=rowNumbers) # set the styling of these cells to be normal cells # instead of the darker outline styling pt$setStyling(cells=cells, baseStyleName="Cell") # apply the red style for negative variance cells <- pt$findCells(calculationNames="Variance", minValue=-1000, maxValue=0, includeNull=FALSE, includeNA=FALSE) pt$setStyling(cells=cells, declarations=list("color"="#9C0006")) # apply the green style for positive variance cells <- pt$findCells(calculationNames="Variance", minValue=0, maxValue=10000, includeNull=FALSE, includeNA=FALSE) pt$setStyling(cells=cells, declarations=list("color"="#006100")) # pt$renderPivot() # round(sum(pt$cells$asMatrix(), na.rm=TRUE)) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 BudgetActualVariance
Net entrepreneurial income2824.42905.280.8
 Net operating surplus3295.53412.6117.1
 Factor income4540.54641.9101.4
 Net value added1765.31733.1-32.2
 Gross value added38523718.1-133.9
 Production of the agricultural industry1015110783.4632.4
 Crop production4210.94373.7162.8
Livestock production4857.75307.6449.9
Production of agricultural services676.6693.917.3
Other production405.8408.22.4
Intermediate services-6299-7065.3-766.3
Depreciation-2086.7-1985101.7
Taxes and subsidies2775.22908.8133.6
 Other taxes on production-145.4-154.2-8.8
Other subsidies (non-product specific)2920.63063142.4
Compensation of employees-1245-1229.315.7
Interests and rents-471.1-507.4-36.3
 Paid rent-236.5-268.2-31.7
Paid interest-244.7-250.3-5.6
Received interest10.111.11.0
" expect_equal(round(sum(pt$cells$asMatrix(), na.rm=TRUE)), 65002) expect_identical(as.character(pt$getHtml()), html) }) } scenarios <- testScenarios("pvt row/col tests: custom balance sheet 3") for(i in 1:nrow(scenarios)) { if(!isDevelopmentVersion) break evaluationMode <- scenarios$evaluationMode[i] processingLibrary <- scenarios$processingLibrary[i] description <- scenarios$description[i] countFunction <- scenarios$countFunction[i] test_that(description, { df <- data.frame( Level1 = rep("Net entrepreneurial income", times=12), Level2 = c(rep("Net operating surplus", 9), rep("Interests and rents", 3)), Level3 = c(rep("Factor income", 8),"Compensation of employees","Paid rent", "Paid interest","Received interest"), Level4 = c(rep("Net value added", 6), rep("Taxes and subsidies", 2), rep(NA, 4)), Level5 = c(rep("Gross value added", 5),"Depreciation","Other taxes on production", "Other subsidies (non-product specific)", rep(NA, 4)), Level6 = c(rep("Production of the agricultural industry", 4), "Intermediate services", rep(NA, 7)), Level7 = c("Crop production","Livestock production", "Production of agricultural services","Other production", rep(NA, 8)), MaxGroupLevel = c(7,7,7,7,6,5,5,5,3,3,3,3), Budget2019 = c(4150.39,4739.2,625.6,325.8,-6427,-2049.3, -145.4,2847.3,-1149,-221.2,-307.6,12.8), Actual2019 = c(3978.8,4341.1,603.7,343,-6063.9,-2079.6, -136.8,2578.6,-1092.9,-203.3,-327.6,14.1), Budget2020 = c(4210.9,4857.7,676.6,405.8,-6299,-2086.7, -145.4,2920.6,-1245,-236.5,-244.7,10.1), Actual2020 = c(4373.7,5307.6,693.9,408.2,-7065.3,-1985, -154.2,3063,-1229.3,-268.2,-250.3,11.1) ) library(pivottabler) pt <- PivotTable$new(processingLibrary=processingLibrary, evaluationMode=evaluationMode) pt <- PivotTable$new() ob <- list(isEmpty=FALSE, nocgApplyOutlineStyling=FALSE, nocgGroupStyleDeclarations=list("font-weight"="normal")) pt$setDefault(addTotal=FALSE, outlineBefore=ob) pt$addData(df) pt$addRowDataGroups("Level1", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>1") pt$addRowDataGroups("Level2", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>2", dataSortOrder="custom", customSortOrder=c("Net operating surplus", "Interests and rents")) pt$addRowDataGroups("Level3", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>3", dataSortOrder="custom", customSortOrder=c("Factor income", "Compensation of employees", "Paid rent", "Paid interest", "Received interest")) pt$addRowDataGroups("Level4", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>4") pt$addRowDataGroups("Level5", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>5", dataSortOrder="custom", customSortOrder=c("Gross value added", "Depreciation", "Other taxes on production", "Other subsidies (non-product specific)")) pt$addRowDataGroups("Level6", outlineBefore=TRUE, onlyAddOutlineChildGroupIf="MaxGroupLevel>6", dataSortOrder="custom", customSortOrder=c("Production of the agricultural industry", "Intermediate Services")) pt$addRowDataGroups("Level7", dataSortOrder="custom", customSortOrder=c("Crop production", "Livestock production", "Production of agricultural services", "Other production"), styleDeclarations=list("font-weight"="normal")) pt$defineCalculation(calculationName="Budget", summariseExpression="sum(Budget2020)") pt$defineCalculation(calculationName="Actual", summariseExpression="sum(Actual2020)") pt$defineCalculation(calculationName="Variance", summariseExpression="sum(Actual2020)-sum(Budget2020)", format="%.1f") pt$evaluatePivot() # apply the red style for negative variance cells <- pt$findCells(calculationNames="Variance", minValue=-1000, maxValue=0, includeNull=FALSE, includeNA=FALSE) pt$setStyling(cells=cells, declarations=list("color"="#9C0006")) # apply the green style for positive variance cells <- pt$findCells(calculationNames="Variance", minValue=0, maxValue=10000, includeNull=FALSE, includeNA=FALSE) pt$setStyling(cells=cells, declarations=list("color"="#006100")) # pt$renderPivot() # round(sum(pt$cells$asMatrix(), na.rm=TRUE)) # prepStr(as.character(pt$getHtml())) html <- "\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
 BudgetActualVariance
Net entrepreneurial income2824.42905.280.8
 Net operating surplus3295.53412.6117.1
 Factor income4540.54641.9101.4
 Net value added1765.31733.1-32.2
 Gross value added38523718.1-133.9
 Production of the agricultural industry1015110783.4632.4
 Crop production4210.94373.7162.8
Livestock production4857.75307.6449.9
Production of agricultural services676.6693.917.3
Other production405.8408.22.4
Intermediate services-6299-7065.3-766.3
Depreciation-2086.7-1985101.7
Taxes and subsidies2775.22908.8133.6
 Other taxes on production-145.4-154.2-8.8
Other subsidies (non-product specific)2920.63063142.4
Compensation of employees-1245-1229.315.7
Interests and rents-471.1-507.4-36.3
 Paid rent-236.5-268.2-31.7
Paid interest-244.7-250.3-5.6
Received interest10.111.11.0
" expect_equal(round(sum(pt$cells$asMatrix(), na.rm=TRUE)), 65002) expect_identical(as.character(pt$getHtml()), html) }) }