Bulk or Batch Publishing

The EQuIS API includes a Bulk or Batch publishing function that can be called from a Windows PowerShell script.

Script Requirements

•The reports to publish must be *.dll assembly files. Other report types (e.g., *.rdl, *.sql or format exports) are not presently supported.

•The report assemblies to be published must be in the EQuIS folder.

•The PowerShell script must contain at least the following two elements.

1.Set the location of your EQuIS Installation using the Set Location -Path cmdlet.

2.EarthSoft.Reports.dll is added as a type using the Add-Type cmdlet.

Set-Location -Path "C:\Program Files\EarthSoft\EQuIS"

$dll = "C:\Program Files\EarthSoft\EQuIS\EarthSoft.Reports.dll"

Add-Type - Path $dll

3.The script calls the BulkReportPublish function in the EarthSoft.Reports.dll with the necessary arguments as follows, where bracketed <> items are replaced with the actual argument values.

[EarthSoft.Reports.Forms.ReportPublisher]::BulkReportPublish(<connection string>, <file name>, <store assembly>, <replace existing parameters>, <logging level>)

The Add-Type command only needs to be called once. The call to the BulkReportPublish function can be added multiple times in the script. Doing this allows the flexibility to publish selected reports to multiple databases within a single script.

Function Arguments

The BulkReportPublish function arguments can be inline with the function call or set as variables in the PowerShell script, and then passed to the function as follows.

[EarthSoft.Reports.Forms.ReportPublisher]::BulkReportPublish($connectionString, $fileName, $storeAssembly, $keepExistingParameters, $loggingLevel)

Connection String

A valid connection string pointing to the database server and target database with the appropriate credentials and settings. Each call to the BulkReportPublish function will only publish to the database specified in this connection string. The following is the structure of and an example for a Windows User connection.

$connectionString = "Data Source=[local instance];Database=[database];Trusted_Connection=Yes;"

$connectionString = "Data Source=localhost;Database=myDatabase;Trusted_Connection=Yes;"

Below, you will find an example of the structure of and a connection string for an EarthSoft hosted database, using SQL Server Management Studio login credentials:

$connectionString = "Data Source=[server name],1433;User ID=[SQL username];Password=[SQL password];Pooling=False;Database=[database name];Connection Timeout=600;Encrypt=True;TrustServerCertificate=False;"

$connectionString = "Data Source=myServerAddress.database.windows.net,1433;User ID=myUsername;Password=myPassword;Pooling=False;Database=myDatabase;Connection Timeout=600;Encrypt=True;TrustServerCertificate=False;"

File Name

The file name of the report assemblies to be published. The file name should not contain any path information. The file name needs to have the file extension included. The file name also includes wildcard '*' functionality that will grab any file that starts with what is before the '*' character as well as that ends with anything after the '*' character. The '*' wildcard can be added to both the name and extension parts of the file name; however, currently using the wildcard in the extension does not add any functionality since the BulkReportPublish function only supports .dll files. The following example will result in publishing all the .dll files in the EQuIS folder that begin with EarthSoft.Reports.Library.

$fileName = "EarthSoft.Reports.Library*.dll"

Store Assembly

The store assembly argument can be either true or false. It specifies whether the report assembly should be stored in the database.

$storeAssembly = $true

Keep Existing Parameters

This argument can be either true or false. It specifies whether to keep editable properties of the existing published report parameters or to overwrite them with the report parameter properties specified in the report assembly. If set to true, the following report parameter properties will not be overwritten, if different.

•DefaultValue

•Caption (Name and Tree Location)

•Remark (Description)

•Visibility

•If parameter is SingleSelect or MultiSelect then: data_source

•Required

$keepExistingParameters = $true

Logging Level

This argument can be one of the following values: off, error, warn, info, debug or all. The levels will cause the console logger to output the specified level of logging to the PowerShell console as the script is being run. They are the same as the logging levels used with Professional and Enterprise.

$loggingLevel = "all"

Note: If any of the report assemblies being published are dependent on another assembly, this information will be logged with a warning message (for loggingLevel = "warn" or higher).

Running the Script

Place the PowerShell .ps1 script in the EQuIS folder; all report assemblies specified by the script are also placed there. Open Windows PowerShell, type in the path and filename at the prompt, then press Enter. The .ps1 file extension is not needed. Also, include quotes around "Program Files" if in path and any other folder/file name that contains spaces.

For example, if the name of your PowerShell script is "PublishReportsEQv7.ps1", and it is located in the EQuIS folder, in the default location, the command at the PowerShell prompt would be as follows:

PS C:\WINDOWS\system32> C:\"Program Files"\EarthSoft\EQuIS\PublishReportsEQv7

Note: To be able to run saved PowerShell scripts, the execution of scripts must be enabled in PowerShell. You can set the execution policy to RemoteSigned using the Set-ExecutionPolicy cmdlet.

Example:

Set-ExecutionPolicy RemoteSigned

The RemoteSigned execution policy allows running scripts, but only downloaded scripts if they are signed. For more information, refer to this online document. On 64-bit versions of Windows, the execution policy is set separately for 32-bit and 64-bit PowerShell versions.

The BulkReportPublish function will return the number of reports that were published with the call. You can store the result in a variable and use the PowerShell Write-Host command to output the variable to the PowerShell console.

Note: The number the report returns includes all newly published or updated reports. For updated reports, this can include where copies of a report have been published so that for a single report assembly, the return value will be included for each copy of an updated report in the database.

If you need to preserve some copies of a report to an earlier version, you should not use this bulk publish function to update the other copies of that report, since all copies will be updated.

The example below shows a script to update all reports in a local database with those in your local EQuIS installation. It also includes output of the sum of published reports from all the calls to the BulkReportPublish function contained in the script.

# The default location is "C:\Program Files\EarthSoft\EQuIS\".

# As the Program Files path is being statically set, find and set that if you have not saved the software to the default location.

Set-Location -Path "C:\Program Files\EarthSoft\EQuIS"

$dll = "C:\Program Files\EarthSoft\EQuIS\EarthSoft.Reports.dll"

Add-Type -Path $dll

# The name of the report assembly to be published. The name can include a '*' wildcard that will match any files that match the pattern.

# For example, the below $fileName variable will cause any report file that starts with 'EarthSoft.Reports.Library.' and ends with '.dll'.

$fileName = "EarthSoft.Reports.Library.*.dll"

# Whether to store the assembly in the database.

$storeAssembly = $true

# Whether to keep the parameter information such as default values for already published reports instead of what is specified in the report file.

$keepExistingParameters = $true

# this controls the console logging level (can be "off", "error", "warn", "info", "debug", "all")

$loggingLevel = "info"

# The BulkReportPuplish function returns the number of reports inserted or updated. The statement below sets this value to the $reportPublishCount variable.

# The connection string (or any other argument) can be written inline or set to a variable that is passed in.

# The example connection string here is for a local database with windows authentication.

$reportPublishCount = [EarthSoft.Reports.Forms.ReportPublisher]::BulkReportPublish("Data Source=[server name];Database=[database name];Trusted_Connection=Yes;", $fileName, $storeAssembly, $keepExistingParameters, $loggingLevel)

# Use the Write-Host command to output the results from the script to the powershell output if calling the script from there.

$newlinechar = "`r`n"

Write-Host "$($newlinechar)**************************$($newlinechar)Number of reports published or updated on Database1: $($reportPublishCount)"

# Everything below here can be commented out/removed if you are only updating reports for a single database.

# keep track of total reports published.

$totalPublishCount = $reportPublishCount

# Multiple calls to the BulkReportPublish function may be used in this script. This allows flexibility of what arguments to use with each set of reports.

# It also allows the ability to publish to multiple databases within one script by using a different connection string with each call or groups of calls.

# This second call would publish the report files in C:\Temp\ to Database2 after the first call published them into Database1. It also increments the $reportPublishCount variable.

# Write out to console the number of reports published for this database.

Write-Host "$($newlinechar)**************************$($newlinechar)Number of reports published or updated on Database2: $($reportPublishCount)"

# add second database count to total

$totalPublishCount += $reportPublishCount

# Write out to console the total number of reports published.

Write-Host "$($newlinechar)**************************$($newlinechar)Total number of reports published or updated on all databases: $($totalPublishCount)"