Question & Answer
Question
This Technote explains how a Websheet can be designed to work with multiple TM1 servers with different model names. This is useful when a TM1 model name changes based on the environment. In this example we assume that the same TM1 model exists in a development and production environment. The TM1 model is named DevPlanSamp in the development environment, and ProdPlanSamp in the production environment.
Answer
In this example we show an Active Form. This design can also be implemented on a slice Websheet.
Step 1 - TM1USER() functions are used to test which environment the Websheet is being used with.
Formula in Cell L11 =TM1USER("DevPlanSamp")
Formula in Cell L12 =TM1USER("ProdPlanSamp")
If the current user in Perspectives or TM1Web is not connected to a server, or if the specified server is not running, TM1User returns an empty string. Nested IF statements are used to determine which TM1USER function returned a value.
Formula in Cell L14 =IF(L11<>"","DevPlanSamp",IF(L12<>"","ProdPlanSamp",""))
Step 2 - Modify the TM1RPTVIEW function to use the model name determined by the IF statements. Note that the TM1RPTVIEW function references cell L14 instead of a hard coded TM1 model name.
=TM1RPTVIEW(L14&":plan_BudgetPlan:1", 0, TM1RPTTITLE("DevPlanSamp:plan_version",$C$11), TM1RPTTITLE("DevPlanSamp:plan_business_unit",$C$12), TM1RPTTITLE("DevPlanSamp:plan_department",$C$13), TM1RPTTITLE("DevPlanSamp:plan_exchange_rates",$C$14), TM1RPTTITLE("DevPlanSamp:plan_source",$C$15),TM1RPTFMTRNG,TM1RPTFMTIDCOL)
If a slice is used the VIEW formula would be updated instead of TM1RPTVIEW.
Step 3 - Any SUBNM formulas must also be updated to use the value retuned by the nested IF statements.
=SUBNM(L14&":plan_version","All Versions","FY 2004 Budget","VersionName")
=SUBNM(L14&":plan_business_unit","All Business Units","10110","BusinessUnit")
=SUBNM(L14&":plan_department","All Departments","105","Department")
=SUBNM(L14&":plan_exchange_rates","local exchange rate","local")
=SUBNM(L14&":plan_source","input","input")
Step 4 (Active Forms only) - The TM1RPTROW, at the top left of the Active Form data area, must also be updated to use the value returned by the nested IF statements.
=TM1RPTROW($B$9,L14&":plan_chart_of_accounts","",'{AR}01'!$B$19:$B$60,"AccountName",0)
The Websheet file for the above example is attached to this Technote.
Note: This technique only works if the development and production environment use their own instance of TM1Web or TM1 Admin Server. If TM1Web is connected to a TM1 Admin Server with both the development and production TM1 models, both TM1USER functions will return a value.
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21959813