PDF Creation Passing Data In Issues

[Tyler_Folsom]

Hello,

I am getting close but not quite there. I have been at this problem for a few days so I decided it was time to reach out here. What I am trying to do is pass multiple IDs from a table (originated from a database) into a query that will display the ticket information into a PDF.

What I have so far is a table that calls a popup that then initiates a report. This is working with hardcoded ticket IDs.

--I apologize right here because I don't know how to frame my question well.--

I am slowly working my way, step by step, to the end goal. Where I am at now is between the popup screen and the PDF report creation. I think I am passing a dataset but on the report side I am unsure how to utilize it. I have a for loop that builds the WHERE clause for the query.
For example: ticket_ids = ticket_ids + " OR t1.id = " + str(73555)...so on and so forth

First question is how to do I find the number of rows being pass through idList2?
I have watched a few videos and this one was particularly helpful (Scripting Data Source Video at Inductive University). I am still missing something.

Passing out idList2 as a dataset.

Hardcoding the dataset inside the popup's parameters.
5 Rows x 1 Column

The dataset (idList2) LIVES

Scipt inside the report. The query in the script works. Where I am stuck is how to find the length of idList2. From there, I am sure it will become clear how I can put the ticket ID from the dataset.

Thought on this one?
Cheers fellow Perspective friends and happy weekend!

[pturmel]

@Tyler_Folsom

I have a for loop that builds the WHERE clause for the query.
For example: ticket_ids = ticket_ids + " OR t1.id = " + str(73555)...so on and so forth

Don't do that. If you can script the construction of a where clause, you can construct it with parameter placeholders and pass the parameters without string conversion. You would have code something like this:

	idList = list(data['idList2'].getColumnAsList(0))
	if idList:
		if len(idList) > 1:
			q_where = "ticket_ids IN (%s)" % (', '.join(['?'] * len(idList)))
		else:
			q_where = "ticket_ids = ?"
	else:
		q_where = "0=1"
	sql_query = "Select ...... From tickets Where %s" % q_where
	pyds = system.db.runPrepQuery(sql_query, idList, 'myDB')
	data['tickets'] = pyds.underlyingDataset

If you need subqueries to go with each ticket, it gets more complicated:

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…

[Tyler_Folsom]

I want to thank you for your suggestions. I am running them and making progress.
So far your methods have been implemented into the report and are working

On Tuesday, I will work on removing the hardcodes ticket IDs to replace them with ones passed in through a another SQL query.

Thanks again!!