Summary

This article demonstrates  how to query a SQL Server database for a list of values, then call Report Commander to execute a report for each value in the list.

More Information

Suppose you want to query a database for a list of customers that meet certain criteria, and then have Report Commander export a report separately for each of those customers. This capability is similar to report bursting, where a report is run and then split out into separate outputs based on some value.

Neither capability is currently built in to Report Commander, but because Report Commander is designed to accept parameter values and other options on the command line, it is simple to query the database independently of Report Commander, and then call Report Commander for each customer.

In this example we use a Windows PowerShell script to do the following:

1. Connect to a SQL Server database
2. Query the database for customers that meet certain criteria
3. Execute Report Commander for each of those customers, to export a report to PDF.

 You can create the script yourself using a text editor from the following code, or download a ZIP containing the script here.

#This script queries a SQL Server database for a list of customers, then executes a report for each customer.
#See https://www.arcanadev.com/support/kb/K00000581 for more information

	$serverName="dbservername"					#replace with the name of your database server
	$databaseName="dbname"						#replace with the name of your database
	
	#specify the SQL query needed to select the values you want to run the report for. This query should return only 1 column.
	$sqlCommand="select customerId from customers"

	#specify the name of the parameter defined in your report
	$reportName="D:\test\dailysales.rpt"	#the path/name of the report to run
	$outputPath="d:\output"			#the path where you want the output written

	#build the connection string. Use integrated security--no user ID or password
	$connectionString = "Data Source=$serverName; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$databaseName"
			
	#connect to the database
	$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
	$connection.Open()
	

	#query the database
	$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
	$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
	$dataset = New-Object System.Data.DataSet
	$adapter.Fill($dataSet) | Out-Null

	$connection.Close()
	

	#for each row, call Report Commander, passing the value returned from the database as a parameter on the command line.
	foreach ($Row in $dataSet.Tables[0].Rows)
	{ 
		$commandLine= @"
"C:\Program Files (x86)\Arcana Development\Report Commander\2.0\adcrutil.exe" -report="$reportName"  -exportformat=PDF -exportfile="$outputPath\`${Report.Name}_$($Row[0]).pdf" -logfile="`${TEMP}\`${Report.Name}.log" -appendlog -parameters "$($Row[0])"
"@
cmd /c $commandLine
}

You will need to edit the script to set the variables on the first 10 lines. You will also need to change the path to adcrutil.exe on the command line (line 38) and make any other changes you need to the command line options passed to Report Commander.

In the sample code, Report Commander runs a report that expects one parameter (the customer ID retrieved from the database). It exports the report to PDF and creates the PDF name by combining the report name and the parameter value. For example, if the report is "dailysales.rpt" and you run it for customer "1234", the PDF will be named "dailysales_1234.pdf".