Getting data from a data source in reports using scripting

boster281 · July 14, 2020, 2:36pm

In a report I have a SQL query data source that gets all of my results. One of those results is a Pass/Fail int. I want to use this int to drive a color change and a Pass Fail read out.

can you define one of the SQL query results as a parameter that can then be used in other Data sources
can you get one piece of data out of a script query to use?

code_skin · July 14, 2020, 2:47pm

Yes you can. Try This:

You should have 2 identical data sources in the design tab, test1 and test2.

As for your 2nd question, you can definitely use scripting to get 1 piece of data.

boster281 · July 14, 2020, 2:52pm

Thank you that will be very helpful in something else I am working on.
But what I meant in that part of the question was more for the Query data source.

Like this

code_skin · July 14, 2020, 2:54pm

Replace Test_Pressure with a ?
A parameter box should appear at the bottom.

Edit; screenshot

boster281 · July 14, 2020, 6:20pm

The ? only seems to work to retrieve a parameter for a where clause not insert a returned result into a parameter.

bpreston · July 14, 2020, 6:45pm

With what you posted, it looks like your query only returns one row, the Test_Pressure is a parameter your feeding into the query. Are you trying to feed the result of the query back into the parameter? If you want to write back into the parameter you would have to do that through a script. I’m not sure with the example you gave why you would want to do that though. If you returned just one row of data, you can reference the value directly from the data returned from the query, in your report.

If you want to take the results of your one row returned and feed the Test_Pressure that was returned back into a parameter you can try something like:

rData = data['query'].getCoreResults()
data['Test_Pressure'] = rData.getValueAt(0,'Test_Pressure')

Or

data['Test_Pressure'] = data['query'].getCoreResults().getValueAt(0,'Test_Pressure')

boster281 · July 14, 2020, 6:48pm

That makes sense. I tried using getCoreResults before but missed the GetValueAt portion.

Thanks