XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "Powershell"

(→‎Output)
 
(18 intermediate revisions by the same user not shown)
Line 30: Line 30:
 
===Output===
 
===Output===
 
''[[#RepositoryConnection|RepositoryConnection[]]]''
 
''[[#RepositoryConnection|RepositoryConnection[]]]''
−
The connections in the report
+
*The connections in the report
  
 
==Set-XL3Connections==
 
==Set-XL3Connections==
Line 41: Line 41:
  
 
*Connections (RepositoryConnection[])
 
*Connections (RepositoryConnection[])
−
**Connections to use in the report - connections in the report will be matched by Id and updated to reflect the given Type, Server, Database and Cube
+
**Connections to update - connections in the report will be matched by Id and updated to reflect the given Type, Server, Database and Cube
  
 
===Output===
 
===Output===
−
Dictionary<string,byte[]>
+
''Dictionary<string,byte[]>''
  
−
The updated report data
+
*The updated report data
  
 
==Add-XL3Folders==
 
==Add-XL3Folders==
Line 60: Line 60:
  
 
===Output===  
 
===Output===  
−
[[#RepositoryFolder|RepostioryFolder]]
+
''[[#RepositoryFolder|RepostioryFolder]]''
 +
*The folder object that has been added
  
 +
==Get-XL3Folders==
 +
Get the folders in the given path
  
−
The folder object that has been added
+
===Parameters===
 +
*-Path (''string'')
 +
**Path to the folder to get the contents of
 +
 
 +
===Output===
 +
''[[#RepositoryFolder|RepostioryFolder[]]]''
 +
*List of folders in the Path
  
−
==Get-XL3Folders==
 
 
==Remove-XL3Folders==
 
==Remove-XL3Folders==
 +
Delete the given folder
 +
 +
===Parameters===
 +
*-Path (''string'')
 +
**Path to the folder to delete
 +
 
==Rename-XL3Folders==
 
==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-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-XL3Permissions==
 +
Get the permissions for the given folder
 +
 +
===Parameters===
 +
*-Path (''string'')
 +
**Path to the folder to get list of permissions for.
 +
 +
===Output===
 +
''[[#RepositoryPermission|RepostioryPermission[]]]''
 +
*List of permissions set on the given folder
 +
 
==Remove-XL3Permissions==
 
==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==
 
==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-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|RepositoryReport]]''
 +
*The report object that was added
 +
 
==Get-XL3Reports==
 
==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|RepositoryReport[]]]''
 +
*The reports in the folder
 +
 
==Open-XL3Reports==
 
==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|ReportFormats]]'')
 +
**Format of the output: Excel, PDF, LiveExcel, PowerPoint
 +
 +
===Output===
 +
''[[#RenderedReport|RenderedReport[]]]''
 +
*Array of all the rendered reports
 +
 
==Remove-XL3Reports==
 
==Remove-XL3Reports==
 +
Delete the given report
 +
===Parameters===
 +
*-Path (''string'')
 +
**Path to the report to delete
 +
 
==Rename-XL3Reports==
 
==Rename-XL3Reports==
 +
Renamed the given report
 +
===Parameters===
 +
*-Path (''string'')
 +
**Path to the report to rename
 +
*-Name (''string'')
 +
**New name for the report
 +
 
==Select-XL3Reports==
 
==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 XLCubed 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 XLCubed 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-XL3Repository==
 +
Get information about the given repository
 +
===Output===
 +
''[[#RepositoryInfo|RepositoryInfo]]''
 +
*Information about the repository
 +
 
=Data Types=
 
=Data Types=
 
==RepositoryConnection==
 
==RepositoryConnection==
Line 97: Line 240:
 
         public bool? OveridePermissions { 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
 +
}

Latest revision as of 13:32, 16 May 2023

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

Pop-XL3Reports

Export XLCubed 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 XLCubed 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

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	
}
Retrieved from ‘https://help.xlcubed.com/index.php?title=Powershell&oldid=11152’