Salesforce Connector for PowerShell How to Get Beta Report

Introduction

In this article we will delve deeper into Salesforce and PowerShell integration, and will learn how to get beta report. We are continuing from where we left off. By this time, you must have installed ODBC PowerPack, created ODBC Data Source, and configured authentication settings in your Salesforce account .

So, let's not waste time and begin.

Use Query Builder to generate SQL query

The first thing you have to do is open Query Builder:

ZappySys API Driver - Salesforce

Amazon Ads Connector can be used to get Amazon advertisements using Amazon Advertisements API, download various advertisement-related reports.

SalesforceDSN
Then simply select the Get Beta Report endpoint (action).
Continue by configuring the Required parameters. You can also set optional parameters too.
Move on by hitting Preview Data button to preview the results.

If you see the results you need, simply copy the generated query:

Get Beta Report

Optional Parameters
Report Name	Test Name
Report Type	spCampaigns
Time Unit	SUMMARY
Group By	campaign
Group By	targeting
Group By	searchTerm
Group By	advertiser
Group By	asin
Report Start Date (yyyy-MM-dd) Maximum date range: 31 days
Report End Date (yyyy-MM-dd) Maximum date range: 31 days
Report Start Date (yyyy-MM-dd) Maximum date range: 731 days
Report End Date (yyyy-MM-dd) Maximum date range: 731 days
Metrics	clicks
Metrics	clicks
Metrics	clicks
Metrics	clicks
Metrics	clicks
Metrics	clicks

SELECT * FROM get_beta_report
WITH
(
    "ReportName" = 'Test Name',
    "ReportType" = 'spCampaigns',
    "StartDatePurchasedProductReportTypeGroupByAsin" = 'today-30d',
    "EndDatePurchasedProductReportTypeGroupByAsin" = 'today',
    "StartDatePurchasedProductReportTypeGroupByPurchasedAsin" = 'today-365d',
    "EndDatePurchasedProductReportTypeGroupByPurchasedAsin" = 'today'
)

That's it! You can use this query in PowerShell.

Let's not stop here and explore SQL query examples, including how to use them in Stored Procedures and Views (virtual tables) in the next steps.

SQL query examples

Use these SQL queries in your PowerShell data source:

How to Get beta report

SELECT * FROM get_beta_report
WITH
(
    "ReportName" = 'Test Name',
    "ReportType" = 'spCampaigns',
    "StartDatePurchasedProductReportTypeGroupByAsin" = 'today-30d',
    "EndDatePurchasedProductReportTypeGroupByAsin" = 'today',
    "StartDatePurchasedProductReportTypeGroupByPurchasedAsin" = 'today-365d',
    "EndDatePurchasedProductReportTypeGroupByPurchasedAsin" = 'today'
)

Get Beta Report in PowerShell

Open your favorite PowerShell IDE (we are using Visual Studio Code).

Use this code snippet to read the data using SalesforceDSN data source:

"DSN=SalesforceDSN"

Read API data with PowerShell using ODBC DSN in Visual Code

For your convenience, here is the whole PowerShell script:

# Configure connection string and query
$connectionString = "DSN=SalesforceDSN"
$query = "SELECT * FROM Customers"

# Instantiate OdbcDataAdapter and DataTable
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($query, $connectionString)
$table = New-Object System.Data.DataTable

# Fill the table with data
$adapter.Fill($table)

# Since we know we will be reading just 4 columns, let's define format for those 4 columns, each separated by a tab
$format = "{0}`t{1}`t{2}`t{3}"

# Display data in the console
foreach ($row in $table.Rows)
{
    # Construct line based on the format and individual Salesforce fields
    $line = $format -f ($row["CustomerId"], $row["CompanyName"], $row["Country"], $row["Phone"])
    Write-Host $line
}

Access specific Salesforce table field using this code snippet:

$field = $row["ColumnName"]

You will find more info on how to manipulate DataTable.Rows property in Microsoft .NET reference.

For demonstration purposes we are using sample tables which may not be available in Salesforce.

To read values in a console, save the script to a file and then execute this command inside PowerShell terminal:
You can also use even a simpler command inside the terminal, e.g.:
```
. 'C:\Users\john\Documents\dsn.ps1'
```