Adhoc Report and Metrics

[tmumaw]

Hi all,

Having a little issue with the metric selection for my Adhoc report.  I have the following:

MetricsOptions:
LOAD * INLINE [_metricsNo, _metrics
1, LID
2, Fuel
3, R&M
4, Total Costs
5, Billed Hrs
6, Std Hrs
7, Util %
8, No Units
9, Avg Cost
10, Billed
];

When I select billed I am also getting LID.  Here are my parameters for both:

=SubStringCount(Concat( _metricsNo, '|'),1)     <----- LID

=SubStringCount(Concat( _metricsNo, '|'), 10)  <---- Billed

Any ideas?

Thanks

[mvanlutterveld · Accepted solution]

Hi Thom,

You get LID when selecting Billed because _metricsNo 1 (LID) is part of 10 (Billed). By adding a preceding zero to the _metricsNo values 1-9 this problem is solved. An alternative solution is to use the _metrics field in your SubStringCount instead of _metricsNo.

Cheers,

Michiel

[tmumaw]

I changed it to this with no luck.......

MetricsOptions:
LOAD * INLINE [_metricsNo, _metrics
10, LID 
20, Fuel
30, R&M
40, Total Costs
50, Billed Hrs
60, Std Hrs
70, Util %
80, No Units
90, Avg Cost
100, Billed
];

[tmumaw]

Changed it to use the names vs numbers.  Just did not remember to put them in quotes. Thanks for you help Michiel.

[mvanlutterveld]

Glad to help. I prefer using descriptive names instead of numbers. It's a bit more work at first, but they are much easier to remember. There comes a time sooner or later when you ask yourself 'what does 7 mean'.

[tmumaw]

Michiel,

Another question for you why would both R&M and % R&M come out together when I only select  % R&M ?  I am working off the _metrics.

MetricsOptions:

LOAD * INLINE [_metricsNo, _metrics

1, LID,

2, Fuel

3, R&M

4, Total Costs

5, Billed Hrs

6, Std Hrs

7, Util %

8, No Units

9, Avg Cost

10, Billed

11, % R&M

12, % Fuel

];

=SubStringCount(Concat(_metrics, '|'),'R&M')

=SubStringCount(Concat(_metrics, '|'), '% R&M')

[mvanlutterveld]

Hi Thom,

Because R&M is part of both 'R&M' and '% R&M'. You should expect the same results for 'Billed Hrs' and 'Billed'. This can solved by making the result of the substringcount more unique. I've added some code that I've used in an adhoc report.

Prod_SMD_Table_Dim_detail:

LOAD * Inline [

      _Prod_SMD_Dim_detail

      Analysis

      Analysis Component

      CRT Code

      CRT Description

      CRT Number

      Sample

Sampled Date

      Sampled Time

      Sampled DateTime

      Manuf. Date

      Manuf. Time

      Manuf. DateTime

      Product

      Product Name

Sampling Point

      Shift

            SKU

      Source Maker

  ];

The substringcount for the 'Sample' column: SubStringCount('|' & Concat(distinct _Prod_SMD_Dim_detail, '|') & '|', 'Sample|')