Powershell

Available for Version 10.0.144 and above running XLCubedWeb in a role-based SQL Repository running Windows Authentication or on an App Service.

Introduction

The PowerShell cmdlet is available by contacting support@xlcubed.com - it helps automate deployment of reports, folders and permissions.

Installing

Extract the supplied zip file, you can then install it by running the following command, either using the extracted folder as the current directory or by passing a full path:

 Import-Module '.\XLCubed.PowerShell.dll' -force

All commands take the following parameters:

-BaseUri  => The full URL to the XLCubedWeb server, for example: https://someserver/xlcubedweb

-AuthToken => Optional JWT authorisation token to use when accessing an App Service instance of XLCubedWeb

Methods

Get-XL3Connections

Return the connections used by the supplied report

Parameters

• -Data (Dictionary<string,byte[]>)
  • Report data - can be retrieved using Select-XL3Report

Output

RepositoryConnection[]

• The connections in the report

Set-XL3Connections

Update the connections in the report

Parameters

• -Data (Dictionary<string,byte[]>)
  • Report data - can be retrieved using Select-XL3Report

• Connections (RepositoryConnection[])
  • Connections to update - connections in the report will be matched by Id and updated to reflect the given Type, Server, Database and Cube

Output

Dictionary<string,byte[]>

• The updated report data

Add-XL3Folders

Add a new folder to the repository

Parameters

• -Path (string)
  • Path to the folder to add the new folder to

• -Name (string)
  • Name of the folder to add

Output

RepostioryFolder

• The folder object that has been added

Get-XL3Folders

Get the folders in the given path

Parameters

• -Path (string)
  • Path to the folder to get the contents of

Output

RepostioryFolder[]

• List of folders in the Path

Remove-XL3Folders

Delete the given folder

Parameters

• -Path (string)
  • Path to the folder to delete

Rename-XL3Folders

Rename the given folder

Parameters

• -Path (string)
  • Path to the folder to rename
• -Name (string)
  • The new name for the folder

Clear-XL3Permissions

Clear all the permissions set on the supplied folder

Parameters

• -Path (string)
  • Path to the folder to clear the permissions from

Get-XL3Permissions

Get the permissions for the given folder

Parameters

• -Path (string)
  • Path to the folder to get list of permissions for.

Output

RepostioryPermission[]

• List of permissions set on the given folder

Remove-XL3Permissions

Remove the explicit set of permissions from the given folder

Parameters

• -Path (string)
  • Path to the folder to remove the permissions from
• -PermissionID (int[])
  • List of permissions, by ID, to remove

Set-XL3Permissions

Add the given permissions to the specified folder

Parameters

• -Path (string)
  • Path to the folder to add the permissions to
• -Permission (RepositoryPermission[])
  • List of permissions to add
• -OverridePermissions (bool))
  • Set "override permission" setting on the folder

Add-XL3Reports

Add the supplied report to the given folder

Parameters

• -Path (string)
  • Path for the folder to add the report to
• -Name (string)
  • Name of the report to add
• -Data (Dictionary<string,byte[]>)
  • Report data - can be retrieved using Select-XL3Report

Output

RepositoryReport

• The report object that was added

Get-XL3Reports

Get the reports in the given folder

Parameters

• -Path (string)
  • Path to the folder to get the list of reports from

Output

RepositoryReport[]

• The reports in the folder

Open-XL3Reports

Render the given report as either Excel, PDF, LiveExcel or PowerPoint

Parameters

• -Path (string)
  • Path to the report to render or path to a folder to render all reports in that folder
• -Format (ReportFormats)
  • Format of the output: Excel, PDF, LiveExcel, PowerPoint

Output

RenderedReport[]

• Array of all the rendered reports

Remove-XL3Reports

Delete the given report

Parameters

• -Path (string)
  • Path to the report to delete

Rename-XL3Reports

Renamed the given report

Parameters

• -Path (string)
  • Path to the report to rename
• -Name (string)
  • New name for the report

Select-XL3Reports

Retrieve the report data for the given report, the report data can be use by Add-XL3Reports and *-XL3Connections

Parameters

• -Path (string)
  • Path to the report

Output

Dictionary<string,byte[]>

• Report data, can be used by Add-XL3Reports and the *-XL3Connection methods

Get-XL3Repository

Get information about the given repository

Output

RepositoryInfo

• Information about the repository

Data Types

RepositoryConnection

public class RepositoryConnection
{
 public int Id { get; set; }
 public string Type { get; set; }
 public string Server { get; set; }
 public string Database { get; set; }
 public string Cube { get; set; }
}

RepositoryFolder

public class RepositoryFolder
 {
       public string Name { get; set; }
       public string Path { get; set; }
       public bool? OveridePermissions { get; set; }
  }

RepositoryInfo

   public class RepositoryInfo
   {
       public string Name { get; set; }
       public Version Version { get; set; }
   }

RepositoryPermissions

   public class RepositoryPermission
   {
       public int PermissionID { get; set; }
       public string MemberType { get; set; }
       public int MemberID { get; set; }
       public char AccessRight { get; set; }
       public string Description { get; set; }
       public string MemberDescription { get; set; }
   }

RepositoryReport

   public class RepositoryReport
   {
       public string Name { get; set; }
       public string Path { get; set; }
       public DateTime DatePublished { get; set; }
       public string Description { get; set; }
       public string PublishedBy { get; set; }
   }

RenderedReport

public class RenderedReport
   {
       public string Name { get; set; }
       public bool Successful { get; set; }
       public byte[] Data { get; set; }
       public Exception Error { get; set; }
   }

Examples

Connect and get repo info

Get-XL3Repository -BaseUri http://localhost/xlcubedweb

Get list of folders on the root

Get-XL3Folders -BaseUri http://localhost/xlcubedweb

Get list of reports in the first folder of site

$folders = Get-XL3Folders -BaseUri http://localhost/xlcubedweb
Get-XL3Reports -BaseUri http://localhost/xlcubedweb -Path $folders[0].Path

Copy reports from "Test1" folder on one server to the "Test2" folder on another server

$reports = Get-XL3Reports -BaseUri http://localhost/xlcubedweb -Path 'Test1' 
foreach($rep in $reports)
{
  $data = Select-XL3Reports -BaseUri http://localhost/xlcubedweb -Path $rep.Path
  Add-XL3Reports -BaseUri http://somenewserver/xlcubedweb -Path 'Test2' -Data $data -Name $rep.Name	
}

Copy reports between servers and update the connection servers

$reports = Get-XL3Reports -BaseUri http://localhost/xlcubedweb -Path 'Test1'
foreach($rep in $reports)
{
  $data = Select-XL3Reports -BaseUri http://localhost/xlcubedweb -Path $rep.Path
  # Get the connections in the file
  $conns = Get-XL3Connections -data $data
  foreach($conn in $conns)
  {
       # update server - note the specific format to still support excel range vs literal values
       $conn.Server = '<value>newserver</value>'
  }
  # Update the connection in the report data
  $data = Set-XL3Connections -data $data -Connections $conns
  # send to new server
  Add-XL3Reports -BaseUri http://newserver/xlcubedweb -Path 'Test2' -Data $data -Name $rep.Name	
}