Snowflake report
-
connected with Snowflake SDK for node
Report "Daily_State_Balance" render failed. Cell not found, make sure there are td elements inside tr Error: Cell not found, make sure there are td elements inside tr at addRow (/home/ubuntu/jsreportapp/node_modules/html-to-xlsx/lib/tableToXlsx.js:71:11) at /home/ubuntu/jsreportapp/node_modules/html-to-xlsx/lib/tableToXlsx.js:45:7 at /home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-html-to-xlsx/lib/htmlToXlsxProcess.js:105:17 at new Promise (<anonymous>) at Object.getRows (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-html-to-xlsx/lib/htmlToXlsxProcess.js:99:16) at tableToXlsx (/home/ubuntu/jsreportapp/node_modules/html-to-xlsx/lib/tableToXlsx.js:44:17) at async convert (/home/ubuntu/jsreportapp/node_modules/html-to-xlsx/lib/conversion.js:53:20) at async scriptHtmlToXlsxProcessing (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-html-to-xlsx/lib/htmlToXlsxProcess.js:57:20) at async module.exports (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-html-to-xlsx/lib/recipe.js:120:18) at async invokeRender (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/render.js:95:5) at async WorkerReporter._render (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/render.js:147:7) at async /home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/reporter.js:179:19 at async Domain.<anonymous> (/home/ubuntu/jsreportapp/node_modules/@jsreport/advanced-workers/lib/workerHandler.js:141:19)Data script
// Use the "beforeRender" or "afterRender" hook // to manipulate and control the report generation async function afterRender (req, res) { // Load the Snowflake Node.js driver. var snowflake = require('snowflake-sdk'); // Create a Connection object that we can use later to connect. var connection = snowflake.createConnection({ account: 'gm******.eu-west-2', username: '********', password: '****************' }); // Try to connect to Snowflake, and check whether the connection was successful. connection.connect( function(err, conn) { if (err) { console.error('Unable to connect: ' + err.message); } else { console.log('Successfully connected to Snowflake.'); // Optional: store the connection ID. connection_ID = conn.getId(); } } ); connection.execute({ sqlText: 'select * FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;' }); };Template report
<style> td, th { padding: 10px; text-align: right; } </style> <table name="Data"> <tr> <td class="title" colspan="10">DAILY STATE BALANCE</td> </tr> <tr class="cell-titles"> <tr> <th>STATE</th> <th>CREDITS</th> <th>ACTUAL_CASH</th> <th>UNREVEALED</th> <th>CUSTOMER_FUNDS</th> </tr> {{#each rows}} <tr> <td>{{{STATE}}}</td> <td>{{{STATE_CREDITS}}}</td> <td>{{{STATE_ACTUAL_CASH}}}</td> <td>{{{STATE_UNREVEALED}}}</td> <td>{{{STATE_CUSTOMER_FUNDS}}}</td> </tr> {{/each}} </table>I have checked the query in db tools and here is the sample data with table headers.
STATE STATE_CREDITS STATE_ACTUAL_CASH STATE_UNREVEALED STATE_CUSTOMER_FUNDS "NH" 0.00 392278.37 0.00 392278.37 "Wisconsin" 0.00 583727.00 0.00 583727.00 "Ohio" 0.00 737135.00 0.00 737135.00 "ON" 0.00 998745.00 0.00 998745.00
-
This post is deleted!
-
Unended tag:
<tr class="cell-titles">
-
Thanks that worked for snowflake
-
@jan_blaha no data populated
info+0Render request 6 queued for execution and waiting for availible worker info+19Starting rendering request 6 (user: admin) info+1Rendering template { name: Daily_State_Balance, recipe: html-to-xlsx, engine: handlebars, preview: true } debug+2Data item not defined for this template. debug+3Executing script snowfake-daily-balance (beforeRender) debug+14Base url not specified, skipping its injection. debug+0Rendering engine handlebars debug+16Executing recipe html-to-xlsx info+0html-to-xlsx generation is starting info+337html-to-xlsx generation was finished debug+165Executing script snowfake-daily-balance (afterRender) debug+454Skipping storing report. info+1Rendering request 6 finished in 1012 ms
-
Updated report to match sample exl
<html> <head> <style> {{asset "sd-styles.css" "utf8"}} </style> </head> <body> <table name="daily_state_balance"> <tbody class="head"> <tr> <td></td> <td colspan="5" class="title">DAILY STATE BALANCE</td> <td></td> </tr> <tr> {{generateEmptyCell 7}} </tr> <tr> {{generateEmptyCell 1}} <td class="label content border-top border-left">SITE</td> <td class="content border-top">igamingsd.com</td> {{generateEmptyCell 1 "content border-top"}} <td class="label content border-top">Report Date</td> <td class="content border-top border-right t-right" data-cell-type="date" data-cell-format-str="dd-mm-yyyy">{{nowStr}}</td> {{generateEmptyCell 1}} </tr> <tr> {{generateEmptyCell 1}} <td class="label content border-bottom border-left">Month</td> <td class="content border-bottom">{{month}}</td> {{generateEmptyCell 1 "content border-bottom"}} <td class="label content border-bottom">License</td> <td class="content border-bottom border-right t-right">ORC</td> {{generateEmptyCell 1}} </tr> <tr> {{generateEmptyCell 7}} </tr> </tbody> <tbody class="detail"> <tr> <th class="t-center">State</th> <th class="t-center">Credits</th> <th class="t-center">Actual Cash</th> <th class="t-center"> Unrevealed </th> <th class="t-center"> Customer Funds </th> </tr> {{#each detail}} <tr class="{{oddClassName 5}}"> <td dclass="t-right" data-cell-type="text">{{{STATE}}}</td> <td class="t-right" data-cell-type="number">{{{STATE_CREDITS}}}</td> <td class="t-right" data-cell-type="number">{{{STATE_ACTUAL_CASH}}}</td> <td class="t-right" data-cell-type="number">{{{STATE_UNREVEALED}}}</td> <td class="t-right" data-cell-type="number">{{{STATE_CUSTOMER_FUNDS}}}</td> </tr> {{/each}} </tbody> </table> </body> </html>const moment = require('moment') const rowOffset = 5 function nowStr () { return moment().format('YYYY-MM-DD') } function generateEmptyCell (repeat, className) { const cells = [] for (let i = 0; i < repeat; i++) { cells.push(`<td class="empty-cell ${className || ''}"></td>`) } return new Handlebars.SafeString(cells.join('')) } function oddClassName (index) { return (index + 1) % 2 !== 0 ? 'odd' : '' } function sum (a, b) { return a + b } function getDetailRowIndex (index) { return (index + 1) + rowOffset }
-
created a js file to test
ubuntu@jsreports:~/jsreportapp$ node testsf-conn.js
Start: 1 tests loaded ā testsf-conn.js Math.random() should return a number between 0 and 1 0.2ms Completed in 0ms. Pass: 1, fail: 0, skip: 0.Tom } = require('test-runner') const assert = require('assert').strict const fetch = require('node-fetch') const tom = new Tom() tom.test('Math.random() should return a number between 0 and 1', function () { const result = Math.random() async function beforeRender(req, res) { // Load the Snowflake Node.js driver. var snowflake = require('snowflake-sdk'); // Create a Connection object that we can use later to connect. var connection = snowflake.createConnection({ account: 'gm36774.eu-west-2', username: 'dbrody', password: 'k5MeAqgNs7hgQZ3' }) // Try to connect to Snowflake, and check whether the connection was successful. connection.connect( function (err, conn) { if (err) { console.error('Unable to connect: ' + err.message); } else { console.log('Successfully connected to Snowflake.'); // Optional: store the connection ID. connection_ID = conn.getId(); } } ) connection.execute({ sqlText: 'select * FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;', complete: function (err, stmt, rows) { if (err) { console.error('Failed to execute statement due to the following error: ' + err.message); } else { console.log('Successfully executed statement: ' + stmt.getSqlText()); } } }); } assert.ok(result >= 0 && result <= 1) }) module.exports = tomThis may help some test.
