Forum Discussion

Colin's avatar
Colin
New Contributor II
2 years ago

Variable Name in Bi VIewer

Hi All, For a Bi Viewer Dashboard I use a Cube View which calculates values with a GetDataCell calculation. I display the values in a line chart. The values are displayed correctly, but the calcula...
BI Viewer
Cube View
Dashboards
  • JackLacava's avatar
    JackLacava
    2 years ago

    If I understand correctly, you'd want those records to be marked "COGS %", is that right?

    The easiest way to go about that is to create a Calculated Field in Bi Viewer, by right-clicking on the datasource name:

    In the Expression Editor that comes up, you just enter the name you want, surrounded by single-quotes:

    That will return the value 'My Name' in that field for all records. If you need more complex logic, you can implement it with a more complex expression, like IIF etc. For example, to replace an account name, you'd use Replace([Account], 'MyWrongAccount', 'COGS %')

Colin's avatar
Colin
New Contributor II

Hi Jack,

this is my CV calculation: GetDataCell(Divide((A#GP - A#Sales), A#Sales)):Name(COGS %)

And I´m using the CV MD Command Type.

JackLacava's avatar
JackLacava
Honored Contributor
2 years ago

If I understand correctly, you'd want those records to be marked "COGS %", is that right?

The easiest way to go about that is to create a Calculated Field in Bi Viewer, by right-clicking on the datasource name:

In the Expression Editor that comes up, you just enter the name you want, surrounded by single-quotes:

That will return the value 'My Name' in that field for all records. If you need more complex logic, you can implement it with a more complex expression, like IIF etc. For example, to replace an account name, you'd use Replace([Account], 'MyWrongAccount', 'COGS %')

  • Colin's avatar
    Colin
    New Contributor II
    2 years ago

    Yes the Replace function solved the problem, thank you Jack!

  • Colin's avatar
    Colin
    New Contributor II
    2 years ago

    Is it possible to use more than one replace function in a calculated field? If so how do I do that, 'AND' and a comma(,) did not work as separators between two replace functions.

    • JackLacava's avatar
      JackLacava
      Honored Contributor
      2 years ago

      I suspect the logic you're after is something like "if we have name X in the record, replace it with A; if we have Y, replace it with B; otherwise, leave it as it was". You can achieve this by nesting IIF calls.

       

      Iif( Contains( [Account], 'SomeName'),
     Replace([Account], 'SomeName', 'FirstReplacement'),
     iif( Contains([Account], 'SomeOtherName'),
	    Replace([Account], 'SomeOtherName', 'SecondReplacement'),
	    [Account]
     )
)

       

      Note that Replace is necessary only if you want to replace part of the original text; if you simply want to wipe what was there before, you can just return the value directly, i.e.

       

      IIF( Contains( [Account], 'SomeName'),
     'New Value',
     IIF( Contains( [Account], 'SomeOtherName'),
          'Another Value',
          [Account]
     )
)

       

       

      • Colin's avatar
        Colin
        New Contributor II
        2 years ago

        Thank you again, it worked!