I am trying to create a report that shows the past 6 months of based on downtime reason.
The rows in a chart would be the downtime reason and the columns would be each month.
I have one query that returns all downtime reasons for the past 6 months. The downtime reasons are dynamic and can be added by an operator.
I have another query that takes a down time reason parameter, and returns the past 6 months duration based on the month.
I am trying to use data from the first query to iterate in a second query and combine that data using a script.
Query 1
SELECT downtime_reason_code.downtime_id AS dtId
FROM downtime
INNER JOIN downtime_reason_code ON downtime_reason_code.downtime_id =
downtime.reason
WHERE (downtime.reason BETWEEN 11 AND 23) OR
(downtime.reason > 50) AND month(downtime.t_stamp)<=month(now())
GROUP BY downtime_reason_code.downtime_id
Query 2
SELECT
MONTH(downtime.t_stamp) AS dtMonth,
Sum(DISTINCT downtime.duration) /60 AS Sum_duration,
Count(DISTINCT downtime.downtime_ndx) AS Count_downtime_ndx
FROM downtime
INNER JOIN downtime_reason_code ON downtime_reason_code.downtime_id =
downtime.reason
WHERE month(downtime.t_stamp)<=month(now()) AND
(downtime.reason = ?)
GROUP BY downtime_reason_code.description, MONTH(downtime.t_stamp)
ORDER BY MONTH(downtime.t_stamp) DESC
Script
dtReason = data['dtReason'].getCoreResults()
dtReasonDataset = system.dataset.toPyDataSet(dtReason)
headers = ['description', 'm1', 'm2', 'm3']
dataOut = []
m1=0
m2=0
m3=0
for reason in dtReasonDataset:
data['currentReason1'] = reason[0]
dtDuration = data['dtDuration'].getCoreResults()
dtDurationDataset = system.dataset.toPyDataSet(dtDuration)
for duration in dtDurationDataset:
if duration[0] == 10:
m1 = duration[1]
if duration[0] == 9:
m2 = duration[1]
if duration[0] == 8:
m3 = duration[1]
dataOut.append([reason[0],m1,m2,m3])
data['dtDataCombined'] = system.dataset.toDataSet(headers, dataOut)
I am not sure how to use the data from the first query into the second query
It seems like it would be simpler to just combine the two queries into one. This would eliminate the need for subsequent scripting iterations:
SELECT
downtime_reason_code.downtime_id AS dtId,
MONTH(downtime.t_stamp) AS dtMonth,
Sum(DISTINCT downtime.duration) /60 AS Sum_duration,
Count(DISTINCT downtime.downtime_ndx) AS Count_downtime_ndx
FROM downtime
INNER JOIN downtime_reason_code ON downtime_reason_code.downtime_id =
downtime.reason
WHERE (month(downtime.t_stamp)<=month(now()) AND
(downtime.reason = ?))
AND
((downtime.reason BETWEEN 11 AND 23) OR
(downtime.reason > 50) AND month(downtime.t_stamp)<=month(now()))
GROUP BY downtime_reason_code.description, MONTH(downtime.t_stamp)
ORDER BY MONTH(downtime.t_stamp) DESC;
Using the test data generated by the sample query in my previous post, I was able to script the pivot you are wanting. One noteworthy change I made to the original query is that I added this as the second line: downtime_reason_code.description as Description
This generates the downtime description column that seems to be necessary for the desired outcome depicted in your post
For testing, I used a power table, and I added a custom dataset property called rawData. I then used a SQL Query binding to bind the dataset to the data returned from the sample query.
The following script ran from a test button produces the desired result:
from java.text import DateFormatSymbols
rawData = event.source.parent.getComponent('Power Table').rawData
iterators = []
headers = ['Description']
data = []
for row in range(rawData.getRowCount()):
value = rawData.getValueAt(row, "dtMonth")
if value not in iterators:
iterators.append(value)
headers.append(DateFormatSymbols().getMonths()[value - 1])
for row in range(rawData.getRowCount()):
target = rawData.getValueAt(row, "Description")
dataRow = [target]
for iterator in iterators:
dataRowAddition = None
for row in range(rawData.getRowCount()):
targetValue = rawData.getValueAt(row, "Description")
iteratorValue = rawData.getValueAt(row, "dtMonth")
downTimeValue = rawData.getValueAt(row, "Sum_duration")
if target == targetValue and iterator == iteratorValue:
dataRowAddition = downTimeValue
break
dataRow.append(dataRowAddition)
data.append(dataRow)
dataset = system.dataset.toDataSet(headers, data)
event.source.parent.getComponent('Power Table').data = system.dataset.sort(dataset, 0)
The preceding code fired from a button's actionPerformed event handler produces the following result when the button and power table are both nested directly on the root container:
The code first builds a list of headers based on the months that are in the dtMonth column. Then it just iterates through the raw data and builds the rows matching up the the reason code with downtime sums based on the month integers.
This script could be adapted for a power table's initialize extension function by replacing event.source.parent.getComponent('Power Table') with self, so the data is loaded when the power table is first created. Just remember when testing that the initialize extension function doesn't fire inside the design environment, so it will have to be tested in a launched application.
dtReason = data['monthReason'].getCoreResults()
rawData = system.dataset.toPyDataSet(dtReason)
iterators = []
headers = ['Description']
dataDtRows = []
#get all the months
for row in range(rawData.getRowCount()):
value = rawData.getValueAt(row, "dtMonth")
if value not in iterators:
iterators.append(value)
headers.append(DateFormatSymbols().getMonths()[value - 1])
#Get all Descriptions
for row in range(rawData.getRowCount()):
target = rawData.getValueAt(row, "Description")
#Only add ones that are not in the list
if not any(target in element for element in dataDtRows):
dataRow = [target]
for iterator in iterators:
dataRowAddition = None
for row in range(rawData.getRowCount()):
targetValue = rawData.getValueAt(row, "Description")
iteratorValue = rawData.getValueAt(row, "dtMonth")
downTimeValue = rawData.getValueAt(row, "Sum_duration")
if target == targetValue and iterator == iteratorValue:
dataRowAddition = downTimeValue
break
dataRow.append(dataRowAddition)
dataDtRows.append(dataRow)
dataset = system.dataset.toDataSet(headers, dataDtRows)
data['downtimeRows'] = system.dataset.sort(dataset, 0)
I had to add if not any(target in element for element in dataDtRows): to remove duplicates
