// DB Connection parameters var server = "BI"; // name of the server registered in the Servers folder var db = "bi"; // name of the database that you will be running your queries against try { buildFormActions(aqua.request, aqua.response); var customerCriteria = aqua.request.getParameter("City"); aqua.console.println("criteria: " + customerCriteria); if(customerCriteria) { processRequest(aqua.request); } } catch (err) { aqua.response.write("A server error occurred. Please contact your server administrator: " + err.lineNumber); aqua.response.write(err.lineNumber + ": " + err); } function processRequest(request) { createAndRenderRevenueReportdAndChart(request); // Close the right hand column containing the chart and the table below it aqua.response.write(''); } function createAndRenderRevenueReportdAndChart(request) { var pivotDataSet = createPivotGrid(request); aqua.response.write('

City Names

'); // Create chart and report createAndRenderChart(pivotDataSet, aqua.response, aqua.chart); createAndRenderReport(pivotDataSet, aqua.response, aqua.chart); } function createAndRenderReport(pivotDataSet, response) { var report = createReport(pivotDataSet); response.write(report.renderHtml()); } function createReport(pivotDataSet) { // Create a report object so we can customize our pivot grid prior to rendering var settings = createReportSettings(); var report = aqua.report.newReport(); report.addDataSet(pivotDataSet, settings); return report; } function createReportSettings() { var settings = aqua.report.newReportDataSetSettings(); return settings; } function createPivotGrid(request) { // Build the query string based on user's search criteria var queryString = generateQueryString(request); var conn = aqua.project.getServerConnection(server); conn.connect(); conn.changeDatabase(db); // Query the necessary data and save it in order to perform ETL operations var dataSet = conn.executeSnapshot(queryString); // Create a pivot displaying the countires as rows, the months as columns, and the revenue in the intersection var pivotDataSet = aqua.data.newPivotDataSet(); pivotDataSet.setDataSet(dataSet); pivotDataSet.addRowField('city'); pivotDataSet.addDataField('count(1)'); pivotDataSet.setRowFieldSortAscending('city'); pivotDataSet.calculateGrid(); // creates/updates the internal pivot data set based upon the current settings request.session.setAttribute("pivotDataSet",pivotDataSet); return pivotDataSet; } function generateQueryString(request) { // Build the query string based upon user's search criteria var queryString = "SELECT count(1), city FROM City "; // Inspect the request stream to determine if any filter criteria has been specified var customerCriteria = request.getParameter("City"); if (customerCriteria != "") { queryString += " WHERE Name LIKE '%" + aqua.util.sqlEncode(customerCriteria) + "%'"; } return queryString; } function buildFormActions(request, response) { // Create a table containing two columns and force left hand column to 25% width. response.write('
'); // Create "Search" form var searchForm = aqua.form.newForm(5, 2); // 5 rows, 2 columns var row = 0; searchForm.setHeader("Search", "background:#c2dafe;"); // Set the width of each table so it is 100% to fill out the column it sits in (each form's table sits in a column // of 25% browser width created above) searchForm.setStyle("width:100%; background:white; border-style:solid; border-width:2px; border-color:#eeeeee; margin:0"); // Search filters: Customer, Country, Start Date, End Date searchForm.add(row, 0, "City:"); searchForm.addInput(row++, 1, "text", "City", request.getParameter("City")); searchForm.addSubmitButton(row++, 1, "SearchSubmit", "Search"); searchForm.setAction("", "get", ""); response.write(searchForm); // End the containing column on the left and begin the column containing the chart and table response.write(''); } function createReportAndChart(pivotDataSet) { // Create a report object with a chart so we can customize our pivot grid prior to rendering var settings = createReportSettings(); var chart = createChart(pivotDataSet, aqua.chart); var report = aqua.report.newReport(); report.addDataSet(pivotDataSet, settings, chart); return report; } function createAndRenderChart(pivotDataSet, response, aqchart) { var chart = createChart(pivotDataSet, aqchart); // Render the chart and display it in the browser url = chart.cache(3600); response.write('

'); response.write('
'); response.write(''); aqua.console.println(url); response.write('
'); response.write('

'); } function createChart(pivotDataSet, aqchart) { // Create a chart based off this pivot data var chart = aqchart.newChart(); chart.setDataSource(pivotDataSet); // Setting chart visual display properties. An easy way to do this is to generate a chart via ADStudio GUI and use // the "Generate AquaScript" action to simply copy and paste all the chart properties into your AquaScript. chart.properties.setBackgroundColorBottom('#d7dcf5'); chart.properties.setBackgroundColorTop('#ebf0f5'); chart.properties.setCategoryAxisDateFormat(""); chart.properties.setCategoryAxisDateTimeFormat(""); chart.properties.setCategoryAxisTimeFormat(""); chart.properties.setCategoryAxisNumberFormat(""); chart.properties.setCategorySpacing(1.0); chart.properties.setDepth(5.0); chart.properties.setNumericCategoryAxis(false); chart.properties.setFixedCategoryCount(0); chart.properties.setShowFixedCategoryCount(false); chart.properties.setFont('ARIAL-PLAIN-10'); chart.properties.setLegendFontSize(1.0); chart.properties.setSeriesFontSize(1.0); chart.properties.setCategoryFontSize(1.0); chart.properties.setValueFontSize(1.0); chart.properties.setTitleFontSize(1.75); chart.properties.setAxisTitleFontSize(1.25); chart.properties.setDataLabelFontSize(1.0); chart.properties.setGroupSmallValuesThreshold(3.0); chart.properties.setGroupSmallValues(true); chart.properties.setHeight(10.0); chart.properties.setImageWidth(640); chart.properties.setImageHeight(480); chart.properties.setShowLegend(true); chart.properties.setShowLegendBorder(true); chart.properties.setLegendHeight(160); chart.properties.setLegendHeightTypeFit(); chart.properties.setLegendPositionTopRight(); chart.properties.setLegendWidth(100); chart.properties.setLegendWidthTypeFit(); chart.properties.setUseLegendMargin(false); chart.properties.setLightingDefault(); chart.properties.setOpacity(1.0); chart.properties.setLineWidth(1.0); chart.properties.setShowMarkers(true); chart.properties.setMapRange(''); chart.properties.setMapRangeColors(''); chart.properties.setMapShowBackground(false); chart.properties.setMapShowDetails(false); chart.properties.setMergeAxis(true); chart.properties.setPanX(0.0); chart.properties.setPanY(0.0); chart.properties.setPieShowLines(false); chart.properties.setPieShowValues(false); chart.properties.setPieExplode(0.0); chart.properties.setPieLabelDistance(3.0); chart.properties.setReverseValueAxis(false); chart.properties.setRotationX(325); chart.properties.setRotationY(0); chart.properties.setRotationZ(0); chart.properties.setSeriesAxisDateFormat(""); chart.properties.setSeriesAxisDateTimeFormat(""); chart.properties.setSeriesAxisTimeFormat(""); chart.properties.setSeriesAxisNumberFormat(""); chart.properties.setSeriesSpacing(1.0); chart.properties.setStackedPercent(false); chart.properties.setTitle(''); chart.properties.setTitlePositionBottomCenter(); chart.properties.setUseTitleMargin(false); chart.properties.setUseAxisTitleMargin(false); chart.properties.setAxisTitleOnEdge(false); chart.properties.setTitleCategory(''); chart.properties.setTitleValue(''); chart.properties.setValueAxisNumberFormat(""); chart.properties.setWidth(16.0); chart.properties.setView2D(false); chart.properties.setFitChartToImage(false); chart.properties.setZoom(1.0); chart.properties.setShowDataLabels(false); chart.properties.setShowValueAxis(true); chart.properties.setShowValueAxisTicks(true); chart.properties.setShowValueAxisLines(true); chart.properties.setShowCategoryAxis(true); chart.properties.setShowCategoryAxisTicks(true); chart.properties.setShowCategoryAxisLines(true); chart.properties.setShowSeriesAxis(true); chart.properties.setShowGrid(true); chart.properties.setFillGrid(true); chart.properties.setGridOpacity(0.5); chart.properties.setGridLineWidth(1.0); chart.properties.setChartTypeColumn(0); chart.properties.setColumnTypeCube(0); chart.properties.setShowDataLabels(0, false); return chart; }