Powershell

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

Contents

 [hide] 

Introduction

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

You must be running the SQL Repository for this functionality to work although Get-XL3Repository will work with a file-based repo.

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 FluenceXLWeb server, for example: https://someserver/xlcubedweb

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

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

Pop-XL3Reports

Export FluenceXL reports as an XL3RZ package

Parameters

  • -Path (string)
    • Path to export
  • -IsRecursive (boolean)
    • True to include sub folders
  • -Filename (string)
    • XL3RZ file to export to

Output

Will write directly to the given file

Push-XL3Reports

Import FluenceXL reports from an XL3RZ package. The Folder structure will match those imported.

Parameters

  • -InputFile (string)
    • XL3RZ file to import from

Output

Will show a list of the files imported.


Get-XL3Repository

Get information about the given repository

Output

RepositoryInfo

  • Information about the repository

Get-XL3RelationalConnections

Return the relational connections used by the supplied report - new in V2311.1

Parameters

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

Output

RepositoryRelationalConnection[]

  • The connections in the report

Set-XL3RelationalConnections

Update the relational connections in the report - new in V2311.1

Parameters

  • -Data (Dictionary<string,byte[]>)
    • Report data - can be retrieved using Select-XL3Report
  • Connections (RepositoryRelationalConnection[])
    • Connections to update - connections in the report will be matched by Id and updated to reflect the given Type and connection string

Output

Dictionary<string,byte[]>

  • The updated report data

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; }
       public Dictionary<string,string> LicenseInfo { get; set; } // new from V2311.1 
   }

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; }
   }

RepositoryRelationalConnection

New in V2311.1 

public class RepositoryRelationalConnection
{
 public int Id { get; set; }
 public string Type { get; set; }
 public string ConnectionsString { 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	
}
Retrieved from ‘https://help.fluencexl.com/index.php?title=Powershell&oldid=12171’