Hi all
I have created a "Self-service" report using a pivot table where the user select dimensions and measures from a list of available Dimensions and Measures. These are stored in code as follows (extract):
DIMENSIONS:
LOAD * INLINE [
DimenionArea, Dimension, Description, DimensionNo
Organization, MasterCategory, Master Category, 1
Organization, SubCategory, Sub-Category, 2
];
//Periodic, Report_Month, Month, 6
//Periodic, Document Month, Month, 6
//Periodic, 'Document Month', Month2, 7
MEASURES:
LOAD * INLINE [
MetricType, Metric, MetricNo, MetricFormula
Money, Turnover(CY), 1, Report_TurnoverCY
Money, Turnover(LY), 2, Report_TurnoverLY
Money, Turnover(CY vs. LY), 3, Report_TurnoverCYvLY
Percent, Turnover(CY vs. LY (%)), 4, Report_TurnoverCYvLY%
];
The "Metric Formula" is a series of Master Items that have in their definition the formatting of each master item (percent, money, integer). When I make my selections of what dimensions and measures I want on the report, the formatting that I defined when I creatred the master items is not obeyed.
Because my report is dynamic, my measure expression on the pivot table is something of the order of:
=$(=FirstSortedValue([MetricFormula],[MetricNo],1))
and the label is:
=FirstSortedValue([Metric],[MetricNo],1)
For the formatting someone suggested incorporating something like:
if (Getfieldselections(MetricType)='Percent',num(sum(per_sales)/100,'#,##0%' ),Num(sum(sales),'#,##0'))
Anyone know I can incorporate the above methodology (shown in italics) which in effect replaces "Sum(per_sales)/100" with my rather more complex =$(=FirstSortedValue([MetricFormula],[MetricNo],1))
