# make sure the module is installed:
Install-Module -Name ReportingServicesTools

# add computername to trusted hosts list to be able to connect remotely
Set-Item WSMan:\localhost\Client\TrustedHosts -Value "POWERBI"

# view content of the folder
$reportServerUri = 'http://10.36.8.17/ReportServer/'
$folderName = "/AFH/Align General/Programs"
Get-RsFolderContent -ReportServerUri $reportServerUri -RsFolder $folderName | Format-Table -AutoSize


# Return existing data source information for "GrossBudget" report. If multiple datasources uses for queries - it will return all of them. 
$ReportPortalUri = "http://10.36.8.17/Reports"
Get-RsRestItemDataSource -RsItem "/AFH/GrossBudget" -ReportPortalUri $ReportPortalUri

# Get connection string info for "Construction - AY Loss Ratios" report
$ReportPortalUri = "http://10.36.8.17/Reports"
Get-RsRestItemDataSource -RsItem "/AFH/Align General/Programs/Construction - AY Loss Ratios" -ReportPortalUri $ReportPortalUri

#----------------------------------------------------------------------------------------------
#----------Change connection string username and passwork in PowerBI reports-------------------
#----------------------------------------------------------------------------------------------

# connect remotely to POWERBI server
Enter-PSSession -ComputerName POWERBI -Credential ssrsserviceaccount
# specify parameters
$ReportPortalUri = "http://10.36.8.17/Reports"
$ReportName = '/AFH/Catalytic/Underwriter Reports/Underwriter Production'
$Username = 'catalyticSSRS'
$Password = 'RND1101!'
$ServerName = '10.36.8.18'
# grab parameters that are currently exists in a report
$parameters = Get-RsRestItemDataModelParameters $ReportName -ReportPortalUri $ReportPortalUri
$parameters
# Map to a dictionary to access the parameter values.
$parameterdictionary = @{}
foreach ($parameter in $parameters) { $parameterdictionary.Add($parameter.Name, $parameter); }
# change parameter value. "ServerName" or "datasource" whatever the name of the parameter in PowerBI
$parameterdictionary[“ServerName”].Value = $ServerName
# update parameters that are currentl in a report
Set-RsRestItemDataModelParameters -RsItem $ReportName -ReportPortalUri $ReportPortalUri -DataModelParameters $parameters
# print what changed
$parameterdictionary.Values
# set authentification mode, username and password
$dataSources = Get-RsRestItemDataSource -RsItem $ReportName -ReportPortalUri $ReportPortalUri
$dataSources[0].DataModelDataSource.AuthType = 'UsernamePassword' # UsernamePassword should be used when specifying SQL or Basic Credentials
$dataSources[0].DataModelDataSource.Username = $Username
$dataSources[0].DataModelDataSource.Secret = $Password
Set-RsRestItemDataSource -RsItem $ReportName -RsItemType PowerBIReport -DataSources $datasources