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 = tom
    

    This may help some test.


Log in to reply
 

Looks like your connection to jsreport forum was lost, please wait while we try to reconnect.