Get the language independent codes for the parameter values selected by the user from the LOV table.
ThenParameter = represents a group key.
ValueParameter = represents a metric key.
ByParameter = represents a date key.
These values are necessary to support internal report logic that is language independent. The parameter retrieval is based on the ssSmart.rol library custom DataStream control ssList_Of_ValuesQuery (select ssSmart.rol from Library Organizer menu, double click on ssList_Of_ValuesQuery, and select Class tab).
Modify the control methods Start() and Fetch() to set a search specification dynamically and store retrieved parameter language independent values.
Function Start() As Boolean
Dim DateSt as String
Dim ValueSt as String
Dim ThenSt as String
' create Search Spec date portion based on LOV or default '
' value
if revdet::ByParameterV <> "" Then
DateSt = "[Value] = '" & revdet::ByParameterV & "'"
else
DateSt = "[Name] = 'Month'"
end if
' create Search Spec Value portion based on LOV or default '
' value
if revdet::ValueParameterV <> "" Then
ValueSt = "[Value] = '" & revdet::ValueParameterV &"'"
else
ValueSt = "[Name] = 'Revenue'"
end if
' create Search Spec Then portion based on LOV or default '
' value
if revdet::ThenParameterV <> "" Then
ThenSt = "[Value] = '" & revdet::ThenParameterV & "'"
else
ThenSt = "[Name] = 'Account'"
end if
' dynamic search spec
SearchSpec="[Type] = 'REVN_FUNCTIONCAPTIONS' AND " & ValueSt
+ & " OR [Type] = 'REVN_SERIESCAPTIONS' AND " & ThenSt
+ & " OR [Type] = 'FCST_INTVL_PERD_TYPE' AND " & DateSt
Start = Super::Start( )
End Function
Function Fetch( ) As AcDataRow
Dim aRow As ssList_Of_ValuesDataRow
Set Fetch = Super::Fetch( )
If Fetch Is Nothing Then
Exit Function
End If
set aRow = fetch
Select Case aRow.ssValue
case revdet::ByParameterV
revdet::ByParameter=aRow.ssName
case revdet::ValueParameterV
revdet::ValueParameter=aRow.ssName
case revdet::ThenParameterV
revdet::ThenParameter=aRow.ssName
End Select
End Function
Use ByParameter and ValueParameter parameter values to fill out revenue Line Item record dynamically. The purpose is to create a list of revenue records that are independent of user parameters to make sure that general logic works correctly. Later, line item records are stored in a memory list for further aggregation.
Function CreateUListRow(Rec as acDataRow ) as uListRow
Dim uRec as uListRow
Dim aRec as ssRevenueDataRow
dim CurrentSection as integer ' put into root
' calculate section key and UnitKeyStat
CurrentSection=GetGroupKey()
Set aRec = rec
set uRec = New uListRow
' filter ByParameter
Select Case LCase(revdet::ThenParameter)
case "account"
uRec.uThen=aRec.GetValue("ssAccount")
case "campaign"
uRec.uThen=aRec.GetValue("ssCampaign")
case "opportunity"
uRec.uThen=aRec.GetValue("ssOpportunity")
case "project"
uRec.uThen=aRec.GetValue("ssProject")
case "partner"
uRec.uThen=aRec.GetValue("ssPartner")
case "product"
uRec.uThen=aRec.GetValue("ssProduct")
case "product line"
uRec.uThen=aRec.GetValue("ssProduct_Line")
case "description"
uRec.uThen=aRec.GetValue("ssDescription")
case "revenue type"
uRec.uThen=aRec.GetValue("ssRevenue_Type")
case "revenue class"
uRec.uThen=aRec.GetValue("ssRevenue_Class")
case "win probability"
uRec.uThen=aRec.GetValue("ssWin_Probability")
case "sales rep"
uRec.uThen=aRec.GetValue("ssSales_Rep")
case "contact last name"
uRec.uThen=aRec.GetValue("ssContact_Last_Name")
case "Quote"
uRec.uThen=aRec.GetValue("ssQuote")
Case Else
if gErrorMsg = "" then gErrorMsg =
"'Then' parameter is invalid"
Exit Function
End Select
' filter ValueParameter
Select Case LCase(revdet::ValueParameter)
case "revenue"
uRec.uValue=toCur(aRec.GetValue("ssFunctional_Revenue_
Formatted"))
case "margin"
uRec.uValue=toCur(aRec.GetValue("ssFunctional_Margin_
Formatted"))
case "cost"
uRec.uValue=toCur(aRec.GetValue("ssFunctional_Cost_
Formatted"))
case "upside"
uRec.uValue=toCur(aRec.GetValue("ssFunctional_Upside_
Formatted"))
case "downside"
uRec.uValue=toCur(aRec.GetValue("ssFunctional_Downside
_Formatted"))
case "average price"
uRec.uValue=toCur(aRec.GetValue("ssFunctional_Average_
Price_Formatted"))
case "quantity"
uRec.uValue=toCur(aRec.GetValue("ssQuantity_Formatted"))
Case Else ' default
if gErrorMsg = "" then gErrorMsg = "'Show' parameter
is invalid"
Exit Function
End Select
' Get values for the remaining fields
uRec.uItem=aRec.GetValue("ssProduct")
uRec.uCommit=aRec.GetValue("ssCommitted")
uRec.uProb=aRec.GetValue("ssWin_Probability_Formatted"))
uRec.uSalesRep=aRec.GetValue("ssSales_Rep")
uRec.uRevCls=aRec.GetValue("ssRevenue_Class")
uRec.uRevTp=aRec.GetValue("ssRevenue_Type")
uRec.uCurrencyCode=aRec.GetValue("ssFunctional_Currency_Code")
uRec.uDate=CDate(aRec.GetValue("ssDate_Formatted"))
uRec.SectionKey = CurrentSection
uRec.uDateUnit = UnitKeyStat ' GetUnitKey(uRec.uDate)
' create key for Item grouping based on displayed attributes
' Item+Commit+Prob
uRec.ItemKey= Trim(uRec.uItem) & Trim(uRec.uCommit) &
Trim(uRec.uProb)
Set CreateUListRow = uRec
End Function