# Help inserting rows of totals to a dataset from a query **URL:** \ **Category:** Ignition\ **Created:** [September 26, 2022, 7:46pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498 "2022-09-26T19:46:38Z")\ **Posts on this page:** 14\ **Page:** 2
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 27, 2022, 2:52pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/21 "2022-09-27T14:52:39Z")
This one is a pydataset. Yes. I don’t like the for loops because I end up with a line for each of the values, rather than a line for each row. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 27, 2022, 2:56pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/22 "2022-09-27T14:56:23Z")
That's... unexpected. Can you show me the whole thing ? Where are you doing this ? > [@zacharyw.larson](#): > > I don’t like the for loops because I end up with a line for each of the values, rather than a line for each row. That's not supposed to happen. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/2/f2a808f8d7d3b6fbf30e962c2c2f36074b0d8e3e.png) ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 27, 2022, 3:00pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/23 "2022-09-27T15:00:45Z")
```auto sampleHeaders = ['Line', 'Shift', 'Hours', 'Gross', 'Net', 'Timestamp'] sampleData = [['K01', 1, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')], ['K02', 1, 12, 202, 152, system.date.parse('2022-09-25', 'yyyy-MM-DD')], ['K03', 1, 12, 203, 153, system.date.parse('2022-09-25', 'yyyy-MM-DD')], ['K01', 2, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')], ['K02', 2, 12, 208, 155, system.date.parse('2022-09-25', 'yyyy-MM-DD')], ['K03', 2, 12, 209, 157, system.date.parse('2022-09-25', 'yyyy-MM-DD')] ] datasetIn = system.dataset.toPyDataSet(system.dataset.toDataSet(sampleHeaders, sampleData)) for row in datasetIn: row ``` ```auto >>> >>> ``` or: ```auto for row in datasetIn: for value in row: value ``` ```auto >>> u'K01' 1 12 200 150 Tue Jan 25 00:00:00 EST 2022 u'K02' 1 12 202 152 Tue Jan 25 00:00:00 EST 2022 u'K03' 1 12 203 153 Tue Jan 25 00:00:00 EST 2022 u'K01' 2 12 200 150 Tue Jan 25 00:00:00 EST 2022 u'K02' 2 12 208 155 Tue Jan 25 00:00:00 EST 2022 u'K03' 2 12 209 157 Tue Jan 25 00:00:00 EST 2022 ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 27, 2022, 3:05pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/24 "2022-09-27T15:05:55Z")
Well obviously, if you're nesting loops, you'll get cell values instead of rows. > **click for scripts** > > ```python > sampleHeaders = ['Line', 'Shift', 'Hours', 'Gross', 'Net', 'Timestamp'] > sampleData = [['K01', 1, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')], > ['K02', 1, 12, 202, 152, system.date.parse('2022-09-25', 'yyyy-MM-DD')], > ['K03', 1, 12, 203, 153, system.date.parse('2022-09-25', 'yyyy-MM-DD')], > ['K01', 2, 12, 200, 150, system.date.parse('2022-09-25', 'yyyy-MM-DD')], > ['K02', 2, 12, 208, 155, system.date.parse('2022-09-25', 'yyyy-MM-DD')], > ['K03', 2, 12, 209, 157, system.date.parse('2022-09-25', 'yyyy-MM-DD')] > ] > > data = system.dataset.toPyDataSet(system.dataset.toDataSet(sampleHeaders, sampleData)) > > for row in data: > row > > "#"*10 > > for row in data: > print repr(row) > > "#"*10 > > print '\n'.join(map(repr, data)) > > "#"*10 > > print '\n'.join(repr(row) for row in data) > > ``` > **click for results** > > > [u'K01', 1, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022] > > [u'K02', 1, 12, 202, 152, Tue Jan 25 00:00:00 CET 2022] > > [u'K03', 1, 12, 203, 153, Tue Jan 25 00:00:00 CET 2022] > > [u'K01', 2, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022] > > [u'K02', 2, 12, 208, 155, Tue Jan 25 00:00:00 CET 2022] > > [u'K03', 2, 12, 209, 157, Tue Jan 25 00:00:00 CET 2022] > > '##########' > > [u'K01', 1, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022] > > [u'K02', 1, 12, 202, 152, Tue Jan 25 00:00:00 CET 2022] > > [u'K03', 1, 12, 203, 153, Tue Jan 25 00:00:00 CET 2022] > > [u'K01', 2, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022] > > [u'K02', 2, 12, 208, 155, Tue Jan 25 00:00:00 CET 2022] > > [u'K03', 2, 12, 209, 157, Tue Jan 25 00:00:00 CET 2022] > > '##########' > > [u'K01', 1, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022] > > [u'K02', 1, 12, 202, 152, Tue Jan 25 00:00:00 CET 2022] > > [u'K03', 1, 12, 203, 153, Tue Jan 25 00:00:00 CET 2022] > > [u'K01', 2, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022] > > [u'K02', 2, 12, 208, 155, Tue Jan 25 00:00:00 CET 2022] > > [u'K03', 2, 12, 209, 157, Tue Jan 25 00:00:00 CET 2022] > > '##########' > > [u'K01', 1, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022] > > [u'K02', 1, 12, 202, 152, Tue Jan 25 00:00:00 CET 2022] > > [u'K03', 1, 12, 203, 153, Tue Jan 25 00:00:00 CET 2022] > > [u'K01', 2, 12, 200, 150, Tue Jan 25 00:00:00 CET 2022] > > [u'K02', 2, 12, 208, 155, Tue Jan 25 00:00:00 CET 2022] > > [u'K03', 2, 12, 209, 157, Tue Jan 25 00:00:00 CET 2022] If you want a script to print datasets like @JordanCClark does, I have this in my library: ```python def to_string(ds): headers = ds.getColumnNames() widths = [len(str(ds.getRowCount()))] + [ max( max(len(str(value)) for value in ds.getColumnAsList(c)), len(headers[c]) ) for c in range(ds.getColumnCount()) ] headers = [""] + headers ds = system.dataset.toPyDataSet(ds) return ( " | ".join(h.ljust(w) for h, w in zip(headers, widths)) + '\n' + "-" * (sum(widths) + 3*(len(widths)-1)) + '\n' + '\n'.join(" | ".join(str(v).ljust(w) for v, w in zip([i] + [r for r in row], widths)) for i, row in enumerate(ds)) ) def print_ds(ds): print to_string(ds) ``` It's not pretty, but it can be useful. note that it print datasets, not pydatasets. edit: I'm a bit ashamed of showing this, when I spend half my time badgering people about readability and clean code :X ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [September 27, 2022, 3:14pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/25 "2022-09-27T15:14:33Z")
If you're wondering about what I use... > [@View Dataset from script console](https://forum.inductiveautomation.com/t/view-dataset-from-script-console/36704/4): > > Sorry, I'm on vacation, but family is napping, so I get to play. Yes, this is how I relax. Don't judge. EDIT: Added option for printing from Perspective. Sorry, not quite ready for prime time. roll_eyes def printDataSet(datasetIn): from itertools import izip import ast if 'PropertyTreeScriptWrapper$ArrayWrapper' in str(type(datasetIn)): headers = datasetIn[0].keys() data = [] for row in datasetIn: data.append([item.value for item in row.values()]) datasetIn = system.dataset… ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 27, 2022, 5:48pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/26 "2022-09-27T17:48:38Z")
I was getting an error of keyerror:‘12’ when I tried to implements this. Well I needed to have 16 values instead of 12 in my append for my query. Query had 16 columns, not 12. Other than that, it went great. ```auto params={"machine":-1,"materialPar":-1} queryData = system.dataset.toPyDataSet(system.db.runNamedQuery("MainFolder/SecondFolder/MyQuery",params)) value=queryData datasetIn = system.dataset.toPyDataSet(value) ``` These four lines helped me test with my query. * * * I should use one of those functions for printings datasets. Thanks again. I don’t know if I will do that today. * * * Oh no, someone made gross a string in a table. This is terrible. I used int() to make the string an int for summing. Then I used str() to append a string again. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 28, 2022, 8:18am UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/27 "2022-09-28T08:18:58Z")
> [@zacharyw.larson](#): > > ```auto > params={"machine":-1,"materialPar":-1} > queryData = system.dataset.toPyDataSet(system.db.runNamedQuery("MainFolder/SecondFolder/MyQuery",params)) > value=queryData > datasetIn = system.dataset.toPyDataSet(value) > > ``` Wait, what ? `queryData` is a pyDataSet. `value = queryData` so `value` is also a pyDataSet then you convert `value` to a... pyDataSet, to assign it to yet another variable ! The whole thing is equivalent to: ```python params={"machine":-1,"materialPar":-1} datasetIn = system.dataset.toPyDataSet(system.db.runNamedQuery("MainFolder/SecondFolder/MyQuery",params)) ``` Actually it's not equivalent, as I don't think `toPyDataSet` works if you pass it a pyDataSet. But it's equivalent to what it was supposed to do. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 28, 2022, 12:43pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/28 "2022-09-28T12:43:17Z")
haha Thanks Part of it was copied from one spot, and part from another spot. I will need to go clean it up in my notepad++ file. I was saving a file that contained my test with data generated for the script console. Code for testing with a query in the script console. Finally, it also had code used in the transform. * * * I am trying to make a report of this too. I don’t see how to use scripts in the report area though. Checking videos on it. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [September 28, 2022, 12:56pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/29 "2022-09-28T12:56:55Z")
> [@pascal.fragnoud](#): > > Actually it’s not equivalent, as I don’t think `toPyDataSet` works if you pass it a pyDataSet. Named Queries return a BasicDataset ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 28, 2022, 12:59pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/30 "2022-09-28T12:59:02Z")
Oh no @JordanCClark, you didn’t see the depth of my coding sin yet. Pascal did. I called topydataset on the query call line and then again after. My variable was queryData. * * * I mean that I think Pascal knows that when you call a query it comes in as a dataset normally. I think he is pointing out that in my specific code though, I wrote for a pydataset to become a pydataset in the sample I gave from one of my testing subsets of scripts for using in the script console. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 28, 2022, 12:59pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/31 "2022-09-28T12:59:52Z")
Yes, but on this line: > [@zacharyw.larson](#): > > `datasetIn = system.dataset.toPyDataSet(value)` value is already a pyDataSet ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 28, 2022, 1:04pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/32 "2022-09-28T13:04:18Z")
@dkhayes117 I got to the point where I want to make a report on the same query that I wrote the script on. Is there a way in the report module to do script transforms on your datasource? Or do I need to use a script datasource that calls the query? * * * I found it in [Scripting Data Source - Report Data](https://inductiveuniversity.com/videos/scripting-data-source/8.1) Sorry I pinged you. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 28, 2022, 1:11pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/33 "2022-09-28T13:11:18Z")
> [@zacharyw.larson](#): > > Is there a way in the report module to do script transforms on your datasource Yes, setup up your query in the report, then add a script source after the query (order matters) ```auto queryData = data['nameOfQuery'].getCoreResults() ... do stuff data['datakeyOfYourChoice'] = transformedData ``` Do not use `data` as a variable name in your script, this is reserved for the data sources. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [September 28, 2022, 1:21pm UTC](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498/34 "2022-09-28T13:21:24Z")
This worked fantastically. I copied the table I had made before, used it again with this new key, and took only like 5 minutes. Thanks for the note there too! * * * Well, the integers that I had set to `None` now show on the report as `` instead of a blank space. [Previous page](https://forum.inductiveautomation.com/t/help-inserting-rows-of-totals-to-a-dataset-from-a-query/64498.md?page=1)