- The Citrix service in CloudPortal Services Manager is capable of setting prices per application to enable the billing reports to display these prices for each user provisioned with the application. However, there is no user interface functionality to allow this to be done.
To set prices for Applications, Resources, or Groups from the Citrix service involves executing a stored procedure in the OLM database and specifying the name of the application, and the stock code and price to assign to the application. The name of this stored procedure is [dbo].[sp_ServiceItem_SetStockCode], and an example of the use of this stored procedure is shown below.
Firstly, to determine the name of the application execute the following query using SQL Server Management Studio while connected to the OLM database:
SELECT * FROM [dbo].[ServiceItems]
To set the Stock Code and Price for a specific application, execute the statements below using the value displayed in the Name column from the results shown above for the @ServiceItemName parameter. The values of the @StockCode, @SalesPrice, and @CostPrice parameters should be set to the values that you require.
DECLARE @ServiceItemName nvarchar(128) DECLARE @StockCode nvarchar(max) DECLARE @SalesPrice nvarchar(max) DECLARE @CostPrice nvarchar(max) SELECT @ServiceItemName = 'Excel' SELECT @StockCode = 'XLS' SELECT @SalesPrice = '1.10' SELECT @CostPrice = '' EXECUTE [dbo].[sp_ServiceItem_SetStockCode] @ServiceItemName, @StockCode, @SalesPrice, @CostPrice
This stored procedure will need to be executed for each Application, Resource, or Group that you would like to set the price for. To edit a price that has previously been set, simply execute the stored procedure again specifying the same name for the @ServiceItemName parameter. To remove a price that has previously been set, provide empty quotes for the @SalesPrice parameter.
These prices are not able to be overridden at Reseller or Customer level in the CloudPortal Services Manager web application, as would generally be possible in a User Plan or Customer Plan.
Once the prices have been set you will need to execute the transfer process to copy them to the data warehouse where billing reports are read from. To do this, navigate to the Reports->Configuration->Data Warehouse page in CloudPortal Services Manager and click the “Transfer data from views to data warehouse” link in the sidebar. Once the transfer has completed you will be able to execute the Customer Detail or Reseller Detail billing report and view the results. Prices will not be updated for historical data, so be sure to change the report date parameter to today’s date to retrieve the current set of data.