Difference between revisions of "Powershell"

(→‎Data)
(→‎Introduction)
 
(40 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
Available for Version 10.0.144 and above running XLCubedWeb in a role-based SQL Repository running Windows Authentication or on an App Service.
+
Available for Version 10.0.144 and above running FluenceXLWeb in a role-based SQL Repository running Windows Authentication or on an App Service.
  
 
=Introduction=
 
=Introduction=
Line 5: Line 5:
 
The PowerShell cmdlet is available by contacting support@xlcubed.com - it helps automate deployment of reports, folders and permissions.
 
The PowerShell cmdlet is available by contacting support@xlcubed.com - it helps automate deployment of reports, folders and permissions.
  
−
=Installing==
+
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:
 
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:
Line 13: Line 15:
 
All commands take the following parameters:
 
All commands take the following parameters:
  
−
  -BaseUri  => The full URL to the XLCubedWeb server, for example: https://someserver/xlcubedweb
+
  -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 XLCubedWeb
 
  
 +
-AuthToken => Optional JWT authorisation token to use when accessing an App Service instance of FluenceXLWeb
  
 
=Methods=
 
=Methods=
Line 22: Line 23:
 
==Get-XL3Connections==
 
==Get-XL3Connections==
  
−
Return the connections used by the supplied report - report data can be retrieved using Select-XL3Report
+
Return the connections used by the supplied report  
  
 
===Parameters===
 
===Parameters===
  
−
====Data====
+
*-Data (''Dictionary<string,byte[]>'')
−
''Dictionary<string,byte[]>''
+
**Report data - can be retrieved using Select-XL3Report
−
Report data - can be retrieved using Select-XL3Report
 
  
 
===Output===
 
===Output===
−
RepositoryConnection[]
+
''[[#RepositoryConnection|RepositoryConnection[]]]''
−
{
+
*The connections in the report
−
  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; }
 
−
}
 
  
 
==Set-XL3Connections==
 
==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-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===
 +
''[[#RepositoryFolder|RepostioryFolder]]''
 +
*The folder object that has been added
 +
 
==Get-XL3Folders==
 
==Get-XL3Folders==
 +
Get the folders in the given path
 +
 +
===Parameters===
 +
*-Path (''string'')
 +
**Path to the folder to get the contents of
 +
 +
===Output===
 +
''[[#RepositoryFolder|RepostioryFolder[]]]''
 +
*List of folders in the Path
 +
 
==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 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-XL3Repository==
 +
Get information about the given repository
 +
===Output===
 +
''[[#RepositoryInfo|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|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
 +
}

Latest revision as of 09:19, 15 November 2023

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’