Forum Discussion

Davide_Callegar's avatar
Davide_Callegar
New Contributor II
9 months ago

Conditional formatting based on the Scale

Hi All,

I'm trying to create conditional formatting based on the scale. I would like to have the number format without decimals when the scale is set to 3 or above and vice versa when the scale is set to zero.

Do you have any idea?

FYI - The scale is a parameter that the end-user can select in the cube view. 

Thanks,

Davide

Cube View
Reports
  • MarkBird's avatar
    MarkBird
    9 months ago

    Hi Davide

    I think that we have done something similar to what you are trying to achieve using an XFBR rule and a parameter. 

    Parameter Config:

    XFBR Rule:

    Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
	Try
		If args.FunctionName.XFEqualsIgnoreCase("GetScaleFormat") Then
		'------------------------------------------------------------------------------------------------------------
		'Description:   Return a scale format string based on the P_MS_Scale parameters
		'
		'Usage:     Cubeviews, Dashboards, Etc 
		'
		'Parameter Examples
		'	Get formula : XFBR(PIB_Formatting, GetScaleFormat, Scale = [|!P_MS_Scale!|])
		'
		'Created By:   Mark Bird
		'Date Created:   24-01-2023
		'------------------------------------------------------------------------------------------------------------  
		
			'Get parameter values
			Dim scale As String = args.NameValuePairs.XFGetValue("Scale")
			Dim returnString As New Text.StringBuilder
			
			If (scale = "Millions_3dp") Then
				returnString.AppendLine("NumberFormat = [0.000;(0.000);-], Scale = 6, ExcelNumberFormat = [0.000;(0.000);-]")

			Else If (scale = "Thousands_2dp") Then
				returnString.AppendLine("NumberFormat = [#,##0.00;(#,##0.00);-], Scale = 3, ExcelNumberFormat = [#,##0.00;(#,##0.00);-]")

			Else If (scale = "Thousands_0dp") Then
				returnString.AppendLine("NumberFormat = [#,##0;(#,##0);-], Scale = 3, ExcelNumberFormat = [#,##0;(#,##0);-]")

			Else If (scale = "Units_2dp") Then
				returnString.AppendLine("NumberFormat = [#,##0.00;(#,##0.00);-], Scale = 0, ExcelNumberFormat = [#,##0.00;(#,##0.00);-]")

			Else If (scale = "Units_0dp") Then
				returnString.AppendLine("NumberFormat = [#,##0;(#,##0);-], Scale = 0, ExcelNumberFormat = [#,##0;(#,##0);-]")

			 
			End If
			
			Return returnString.ToString
			
		End If

		Return Nothing
	Catch ex As Exception
		Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
	End Try
End Function

    Cube View:

     

    Hope this makes sense?

    Mark

  • MarkBird's avatar
    MarkBird
    Contributor III
    9 months ago

    Hi Davide

    I think that we have done something similar to what you are trying to achieve using an XFBR rule and a parameter. 

    Parameter Config:

    XFBR Rule:

    Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
	Try
		If args.FunctionName.XFEqualsIgnoreCase("GetScaleFormat") Then
		'------------------------------------------------------------------------------------------------------------
		'Description:   Return a scale format string based on the P_MS_Scale parameters
		'
		'Usage:     Cubeviews, Dashboards, Etc 
		'
		'Parameter Examples
		'	Get formula : XFBR(PIB_Formatting, GetScaleFormat, Scale = [|!P_MS_Scale!|])
		'
		'Created By:   Mark Bird
		'Date Created:   24-01-2023
		'------------------------------------------------------------------------------------------------------------  
		
			'Get parameter values
			Dim scale As String = args.NameValuePairs.XFGetValue("Scale")
			Dim returnString As New Text.StringBuilder
			
			If (scale = "Millions_3dp") Then
				returnString.AppendLine("NumberFormat = [0.000;(0.000);-], Scale = 6, ExcelNumberFormat = [0.000;(0.000);-]")

			Else If (scale = "Thousands_2dp") Then
				returnString.AppendLine("NumberFormat = [#,##0.00;(#,##0.00);-], Scale = 3, ExcelNumberFormat = [#,##0.00;(#,##0.00);-]")

			Else If (scale = "Thousands_0dp") Then
				returnString.AppendLine("NumberFormat = [#,##0;(#,##0);-], Scale = 3, ExcelNumberFormat = [#,##0;(#,##0);-]")

			Else If (scale = "Units_2dp") Then
				returnString.AppendLine("NumberFormat = [#,##0.00;(#,##0.00);-], Scale = 0, ExcelNumberFormat = [#,##0.00;(#,##0.00);-]")

			Else If (scale = "Units_0dp") Then
				returnString.AppendLine("NumberFormat = [#,##0;(#,##0);-], Scale = 0, ExcelNumberFormat = [#,##0;(#,##0);-]")

			 
			End If
			
			Return returnString.ToString
			
		End If

		Return Nothing
	Catch ex As Exception
		Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
	End Try
End Function

    Cube View:

     

    Hope this makes sense?

    Mark