# Report Table Formatting **URL:** \ **Category:** Ignition\ **Tags:** ignition81, perspective\ **Created:** [June 30, 2022, 1:16pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389 "2022-06-30T13:16:10Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [June 30, 2022, 1:16pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/1 "2022-06-30T13:16:10Z")
I am working on creating a report that has a table included. This table is made up of 4 columns (“Start Time”, “End Time”, “Onsite Hours” and “Offsite Hours”). The table is populated by tags, which is based user entry from a separate popup view that I have created. The largest amount of data this table would have is pictured below: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/9/6935c0c5cd20eb022c8bdc05607f6c76daea8c4d.png) The first row is my header, and the remaining rows would be populated by the respective tags. I can create this table in my report and it all looks good. The issue is, say the user does not enter a “Start Time3b”, but they time entered in every other “Start Time”. If this were the case, the table would appear like this in my report: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/8/581466f1016ccf35fdf171da090188468d50975c.png) But I would like it to appear like this: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/7/c758f8da427901d4ac49fe7109ff558eaeda935e.png) My question is, rather than creating multiple tables for each scenario (which I started to do, and realized that this is not the way to do it), is it possible to create one table and add some scripting that would allow for this formatting? Thank you in advance ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [June 30, 2022, 1:26pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/2 "2022-06-30T13:26:14Z")
I’d be inclined to leave the blank row there and put an “n/a” or “-” in each field. That way it’s obvious what’s going on and the rows layout remains constant. You could do this on the binding of the popup labels with something like this: ```auto if({../Label_1.props.text} > "", {../Label_1.props.text}, "n/a" ) ``` Without more details on how your popup is created I’m not sure how you’d incorporate this into your report. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 30, 2022, 2:37pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/3 "2022-06-30T14:37:53Z")
The best way to do what you want is probably to use a script datasource to assemble these parameters (or directly read the tags in question, dealer’s choice) into a dataset, then use the dataset on a standard table object. ---
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [June 30, 2022, 3:18pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/5 "2022-06-30T15:18:49Z")
@paul-griffith so would I have to create a script for each scenario? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 30, 2022, 3:46pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/6 "2022-06-30T15:46:55Z")
No. There would only be one script, and if one of the values is not set the script just wouldn’t put it into the dataset. If you’re already collecting user entry somewhere else in Ignition though, it would probably be easiest to store it in a dataset _there_, then pass that single dataset in as a report parameter. ---
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [June 30, 2022, 4:00pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/7 "2022-06-30T16:00:05Z")
@paul-griffith I am not sure if I understand, but let me explain what is happening… When a user is filling out their “Service Report” (which is done through a popup window in perspective), once they have it filled out, they will click a button that will take the entered information and pass that into a mysql database and the information is also passed to the respective tags (I passed the information to tags so that I could read that information into the report that I am creating). Everything all works great, I can pass the data with no issue at all and the information shows up as it should. So here is the script that I currently have in my report for one of the time scenarios (When there is time entered in startTime1 and startTime2): ```auto if({[default]ServiceReports/startTime1.value}!="" &&{[default]ServiceReports/startTime1b.value}="" &&{[default]ServiceReports/startTime2.value}!="" &&{[default]ServiceReports/startTime2b.value}="" &&{[default]ServiceReports/startTime3.value}="" &&{[default]ServiceReports/startTime3b.value}="" &&{[default]ServiceReports/startTime4.value}="" &&{[default]ServiceReports/startTime4b.value}="" &&{[default]ServiceReports/startTime5.value}="" &&{[default]ServiceReports/startTime5b.value}="" ,true,false) ``` So what would my script look like based on what you are saying? Sorry, still pretty new with all of this, so I am trying to learn as I go lol ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 30, 2022, 4:15pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/8 "2022-06-30T16:15:48Z")
If you’re already putting it into the database, you can just directly query that information in your report. That’s definitely going to be the easiest way to go. Is there some marker you can use to known which “batch” of values you should use, or is there a single set of values in the DB that you’re overwriting from the popup each time? If you’re querying from the DB, you would basically just add a condition to the where clause to omit the empty values, and then the report would be driven by that dataset directly. That would automatically give you the correct number of output rows. ---
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [June 30, 2022, 5:22pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/9 "2022-06-30T17:22:49Z")
@paul-griffith Yeah, each report that is filled out and submitted to the database has its own Identifier. So I could use that as the marker. But going off of that, is that just a sql statement that selects the values based off of the Identifier? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 30, 2022, 5:30pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/10 "2022-06-30T17:30:47Z")
Yeah, I think that’s all you would really need. Something like: ```sql SELECT start, end, onsite, offsite FROM someTable WHERE identifier = ? AND start IS NOT NULL AND end IS NOT NULL ``` ---
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [June 30, 2022, 6:17pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/11 "2022-06-30T18:17:35Z")
@paul-griffith That’s what I thought, here is the script I am using: ```auto SELECT startTime, endTime, onsiteHours, offsiteHours, startTime1b, endTime1b, onsiteHours1b, offsiteHours1b, startTime2, endTime2, onsiteHours2, offsiteHours2, startTime2b, endTime2b, onsiteHours2b, offsiteHours2b, startTime3, endTime3, onsiteHours3, offsiteHours3, startTime3b, endTime3b, onsiteHours3b, offsiteHours3b, startTime4, endTime4, onsiteHours4, offsiteHours4, startTime4b, endTime4b, onsiteHours4b, offsiteHours4b, startTime5, endTime5, onsiteHours5, offsiteHours5, startTime5b, endTime5b, onsiteHours5b, offsiteHours5b FROM ServiceReportDatabase WHERE eventID = '{eventID}' AND startTime IS NOT NULL AND startTime1b IS NOT NULL AND endTime IS NOT NULL AND endTime1b IS NOT NULL AND startTime2 IS NOT NULL AND startTime2b IS NOT NULL AND endTime2 IS NOT NULL AND endTime2b IS NOT NULL AND startTime3 IS NOT NULL AND startTime3b IS NOT NULL AND endTime3 IS NOT NULL AND endTime3b IS NOT NULL AND startTime4 IS NOT NULL AND startTime4b IS NOT NULL AND endTime4 IS NOT NULL AND endTime4b IS NOT NULL AND startTime5 IS NOT NULL AND startTime5b IS NOT NULL AND endTime5 IS NOT NULL AND endTime5b IS NOT NULL ``` Testing this with a value entered in startTime and a value entered in startTime2 this is how the table is appearing: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/d/0d0797e4cef3b794b81a0f3933eaffcb5cc00541.png) The query is displaying the correct data, but I am still getting that space between the two times. Perhaps I have an error in my script? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 30, 2022, 6:35pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/12 "2022-06-30T18:35:26Z")
Ah, okay. You’ve got a ‘wide’ table with one column per event type; I assume you just had the four columns. I’m not as comfortable with SQL, I’m sure you could collapse it to a single dataset somehow, but I’d reach for scripting in this case. Something along these lines: ```python def updateData(data, sample): queryData = system.dataset.toPyDataSet(data["yourQuerykey"]) columns = ["startTime", "endTime", "onsiteHours", "offsiteHours"] pivotedData = [] # https://stackoverflow.com/questions/434287/what-is-the-most-pythonic-way-to-iterate-over-a-list-in-chunks # going to sets of four to "unpivot" your columns def chunker(seq, size): return (seq[pos:pos + size] for pos in xrange(0, len(seq), size)) for setOfValues in chunker(queryData[0], 4): # assuming only one row is returned for a given event ID if all(setOfValues): # if all values are true == no values are None/null pivotedData.append(list(setOfValues)) data["pivotedData"] = system.dataset.toDataSet(columns, pivotedData) ``` ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [June 30, 2022, 6:54pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/13 "2022-06-30T18:54:05Z")
If you are using mssql you can use [PIVOT or UNPIVOT](https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16). ---
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [June 30, 2022, 7:37pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/14 "2022-06-30T19:37:14Z")
@paul-griffith Forgive me for my lack of experience with this, but how do I bind this to the table in the report? I created the script, but it is not showing up in the key browser as an option ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 30, 2022, 8:17pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/15 "2022-06-30T20:17:40Z")
If the script ran correctly, you should see a `pivotedData` key in the key browser. Is there an error shown along the bottom of the design tab? You’ll have to fix the first line to point to your actual query datasource’s name, as well. ---
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [June 30, 2022, 8:30pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/16 "2022-06-30T20:30:05Z")
@paul-griffith Nope, I have no errors which is weird. I named the query datasource startTimeQuery So my code is: ```auto def updateData(data, sample): queryData = system.dataset.toPyDataSet(data["startTimeQuery"]) columns = ["startTime", "endTime", "onsiteHours", "offsiteHours"] pivotedData = [] # https://stackoverflow.com/questions/434287/what-is-the-most-pythonic-way-to-iterate-over-a-list-in-chunks # going to sets of four to "unpivot" your columns def chunker(seq, size): return (seq[pos:pos + size] for pos in xrange(0, len(seq), size)) for setOfValues in chunker(queryData[0], 4): # assuming only one row is returned for a given event ID if all(setOfValues): # if all values are true == no values are None/null pivotedData.append(list(setOfValues)) data["pivotedData"] = system.dataset.toDataSet(columns, pivotedData) ``` ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 30, 2022, 9:06pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/17 "2022-06-30T21:06:09Z")
Hmm, I don’t know what’s going wrong. Hard to say without seeing the live system and poking around. ---
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [July 1, 2022, 1:33pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/18 "2022-07-01T13:33:03Z")
@paul-griffith Below is my query datasource: ```auto SELECT startTime, endTime, onsiteHours, offsiteHours, startTime1b, endTime1b, onsiteHours1b, offsiteHours1b, startTime2, endTime2, onsiteHours2, offsiteHours2, startTime2b, endTime2b, onsiteHours2b, offsiteHours2b, startTime3, endTime3, onsiteHours3, offsiteHours3, startTime3b, endTime3b, onsiteHours3b, offsiteHours3b, startTime4, endTime4, onsiteHours4, offsiteHours4, startTime4b, endTime4b, onsiteHours4b, offsiteHours4b, startTime5, endTime5, onsiteHours5, offsiteHours5, startTime5b, endTime5b, onsiteHours5b, offsiteHours5b FROM ServiceReportDatabase WHERE eventID = '{eventID}' AND startTime IS NOT NULL AND startTime1b IS NOT NULL AND endTime IS NOT NULL AND endTime1b IS NOT NULL AND startTime2 IS NOT NULL AND startTime2b IS NOT NULL AND endTime2 IS NOT NULL AND endTime2b IS NOT NULL AND startTime3 IS NOT NULL AND startTime3b IS NOT NULL AND endTime3 IS NOT NULL AND endTime3b IS NOT NULL AND startTime4 IS NOT NULL AND startTime4b IS NOT NULL AND endTime4 IS NOT NULL AND endTime4b IS NOT NULL AND startTime5 IS NOT NULL AND startTime5b IS NOT NULL AND endTime5 IS NOT NULL AND endTime5b IS NOT NULL ``` And then here is the script: ```auto def updateData(data, sample): queryData = system.dataset.toPyDataSet(data["startTimeQuery"]) columns = ["startTime", "endTime", "onsiteHours", "offsiteHours", "startTime1b", "endTime1b", "onsiteHours1b", "offsiteHours1b", "startTime2", "endTime2", "onsiteHours2", "offsiteHours2", "startTime2b", "endTime2b", "onsiteHours2b", "offsiteHours2b", "startTime3", "endTime3", "onsiteHours3", "offsiteHours3", "startTime3b", "endTime3b", "onsiteHours3b", "offsiteHours3b", "startTime4", "endTime4", "onsiteHours4", "offsiteHours4", "startTime4b", "endTime4b", "onsiteHours4b", "offsiteHours4b", "startTime5", "endTime5", "onsiteHours5", "offsiteHours5", "startTime5b", "endTime5b", "onsiteHours5b", "offsiteHours5b"] pivotedData = [] # https://stackoverflow.com/questions/434287/what-is-the-most-pythonic-way-to-iterate-over-a-list-in-chunks # going to sets of four to "unpivot" your columns def chunker(seq, size): return (seq[pos:pos + size] for pos in xrange(0, len(seq), size)) for setOfValues in chunker(queryData[0], 4): # assuming only one row is returned for a given event ID if all(setOfValues): # if all values are true == no values are None/null pivotedData.append(list(setOfValues)) data["pivotedData"] = system.dataset.toDataSet(columns, pivotedData) ``` I believe there may be something wrong with the columns line. and maybe it is just because of my lack of understanding this script, but should I have all of the tags listed (“startTime”, “endTime”, “onsiteHours”, “offsiteHours”, “startTime1b”, “endTime1b”, “onsiteHours1b”, etc.) there or should I have listed the column headers of the actual table in the report (“Arrival / Remote Connection Time:”, “Departure / Remote Disconnection Time:”, “Onsite Hours:” and “Offsite Hours:”)? Also, does it matter if the script or the query is listed first in the Data Sources? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 1, 2022, 1:49pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/19 "2022-07-01T13:49:49Z")
> [@csilk](#): > > or should I have listed the column headers of the actual table in the report (“Arrival / Remote Connection Time:”, “Departure / Remote Disconnection Time:”, “Onsite Hours:” and “Offsite Hours:”)? This is what you want. Though, perhaps just sticking with the simpler startTime,endTime,onsiteHours,offsiteHours is better as you can change the column names that are seen in the report in the design. The "chunker" function is dividing the row returned by the original query into multiple rows, based on the length of "columns" > [@csilk](#): > > Also, does it matter if the script or the query is listed first in the Data Sources? Yes, the query must be listed first in order for the script to use its results. ---
**Author:** ![csilk](https://avatars.discourse-cdn.com/v4/letter/c/9e8a1a/32.png) [@csilk](https://forum.inductiveautomation.com/u/csilk)\ **Post date:** [July 1, 2022, 1:57pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/20 "2022-07-01T13:57:47Z")
@lrose Yeah, that’s what I figured. I changed it back to ```auto columns = ["startTime", "endTime", "onsiteHours", "offsiteHours"] ``` Either way, that shouldn’t prevent this script from executing and not being visible in the key browser, right? I do have the query listed first, but I am still not seeing a `pivotedData` key in my key browser and am not getting any errors. Not really sure what is going on ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 1, 2022, 2:02pm UTC](https://forum.inductiveautomation.com/t/report-table-formatting/61389/21 "2022-07-01T14:02:07Z")
Can you post a quick screen grab of the script source? [Next page](https://forum.inductiveautomation.com/t/report-table-formatting/61389.md?page=2)