I have a report that is pulling a single row of data from my database (about 15 columns total). In the report I display each point of data in a table and also sum up all the data into a total displayed in the last row. The problem is that the totalizing of data is done in the report (by performing addition on keys) and if I export the data to an Excel spreadsheet (via system.dataset.exportExcel) the total is not there because it is not part of the actual dataset of the report. I need to pull the data from the database into the report dataset WITH the total sum as part of the set. I can run a normal SQL query (SELECT * FROM DB WHERE BLAH BLAH BLAH) but how do I also pull the SUM of the data into the same dataset? Any help would be greatly appreciated.
I can think of a couple ways.
One way it use a UNION to combine two queries together.
Here’s a very short example:
(SELECT col1, col2, col3, 1 as sortorder FROM table) UNION (SELECT col1, col2, col3, 2 as sortorder FROM othertable) ORDER BY sortorder DESC
The other idea is to run your database query in a Python script, calculate any additional data and then create a dataset with system.dataset.toDataSet and then assign the dataset to the property that needs it.
Call it Friday funk‚ but I just figured it out. I am now logging the total to my database. Pulls up along with the rest of the data‚ just like I wanted.
Or maybe you need a subquery:
SELECT col1, col2, col3, (SELECT SUM(col2) FROM table) as col4 FROM table