CODEWITHSUNDEEP
business-intelligencemicrostrategy
Shaderlolz
Shaderlolz

Reputation: 11

Microstrategy: Force metric level. Attribute + Report level

In order to get the correct value from an invoice, we need to force this calculation at the attribute 'Invoice ID' level and Report level aswell.

Image 1

At this moment, we added this atribute to dimensionality but the SQL generated by the report is not grouping by invoice ID field.

Report Image

select  a11.ID_EMISOR,
    max(a13.DESCRIPCION),
    max(a13.COD_EMPRESA_DB2),
    sum((a11.UNIDADES * a11.PRECIO_UNITARIO))  WJXBFS1
from    TABLE   a11
    join    DIM_1   a12
      on    (a11.ID_PAIS_EMISOR = a12.ID_PAIS)
    join    DIM_2 a13
      on    (a11.ID_EMISOR = a13.ID_EMPRESA)
group by    a11.ID_EMISOR

Is there a way getting this invoice ID level despite it's not in the report?

The SQL should be like:

select  a11.ID_EMISOR,
    max(a13.DESCRIPCION),
    max(a13.COD_EMPRESA_DB2),
    a11.COD_FAC,
    sum((a11.UNIDADES * a11.PRECIO_UNITARIO))  WJXBFS1
from    TABLE   a11
    join    DIM_1   a12
      on    (a11.ID_PAIS_EMISOR = a12.ID_PAIS)
    join    DIM_2 a13
      on    (a11.ID_EMISOR = a13.ID_EMPRESA)
group by    a11.ID_EMISOR,
    a11.COD_FAC

After that, Microstrategy should group up the results at the report level. (The same performance we should get if we add the attribute to the report but taking it off from the grid)

Report attribute added

Upvotes: 1

Views: 792

Answers (1)

DesertCoot
DesertCoot

Reputation: 31

If you have individual facts created for the units and unit price, you could make a "Units (Invoice)" metric that is

Sum(Unidades){~+,IdFactura+,[Empresa Emisora]+}

and also "Unit Price (Invoice)" that is something like

Max([Precio Unitario]){~+,IdFactura+,[Empresa Emisora]+}

Once those are created, create a third that is

Sum([Units (Invoice)] * [Unit Price (Invoice)]){~+}

You might even be able to build a nested metric with both of those base metrics defined in the final metric, but I have done something similar using the "helper" metrics.

Upvotes: 3

Related Questions