Using altered dataset from nested query in table group

[bebel]

I'm using code very similar to the example in the manual:nested = data['Area

nested = data['Area Data'].getNestedQueryResults()    # Gets results from our parent query
subQuery = nested['Area Details']    # Gets the subquery we want -- there can be more than one
header = ['productName', 'cost', 'triple']
alteredDataset = []
for child in subQuery:
    children = child.getCoreResults()    # children is a dataset
    for row in range(children.rowCount):
        valProductName = children.getValueAt(row,'productName')
        valCost = children.getValueAt(row,'cost')
        valTimesThree =  None
        if valCost != None:
            valTimesThree = 3 * valCost
        alteredDataset.append([valProductName,valCost,valTimesThree])
 
# convert the pydataset to a standard dataset
alteredDataset = system.dataset.toDataSet(header, alteredDataset)
 
# create a new data source with the altered results
data['Updated Area Details'] = alteredDataset

This example says it is useful for table groups but it doesn't go full circle and show how to use the alteredDataset in the existing table group.

In my case the sub query returns a height in inches like 1.75 from the database table, among other machine settings. I have a script that turns those values in a string for shims that would be used. (1", 0.5", 0.25" in this case). For each product in the schedule I have a sub table that displays the machine settings, but I want it to include the shims string, not the height as a number. I have created the altered dataset and assigned it to a key, but I can't figure out how to get the table group to use the altered dataset. If I simply drag the data key into a table cell, every setup for the day displays the first shim stack.

[pturmel]

You need the techniques in this topic to construct nested query data:

Report with Scripted Nested Data

The report datasource scripting examples explain how to access the nested data in another key for use constructing a custom dataset. What I don’t see is how to construct nested data that would be suitable for use with a child table in a table group. I have a parent dataset that I’m constructing via script due to annoying limitations of an ancient SQL Server 2000 instance. I need a subquery for each row that could be done with a normal query, but there’s no way to add a regular subquery to a s…

[bebel]

I ended up creating a dataset outside of the report and then sending that in as a parameter, instead of just sending the date, and having the report collect the data from the database. It works with the table group as I needed, just looks a little different than I originally intended.