Very Strange Report Parameter Behavior

Question

I have a data set for units.  The user can select an entry from the list and the report will calculate the proper value on the fly.

The dataset is populated with the following code:

WITH MEMBER [Measures].[ParameterCaption] AS '[Units].[Units].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Units].[Factor].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Units].[Factor].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Units].[Units].[Units] * [Units].[Factor].[Factor] ON ROWS FROM [Heidtman DW]

In my case this produces a listing somewhat like:
   Units                     Factor         Parameter Caption     Parameter Value                 Parameter Level
Gross Ton               2240.0000         Gross Ton             [Units].[Factor].&[2.24E3]            1
Hundred Weight      100.000000         Hundred Weight
Milligrams               1.00000000        Milligrams
Net Ton                 2000.00000        Net Ton
Pounds                  1.00000000        Pounds


You might notice Milligrams is a mistake.  It won't be the same as pounds.  This is causing strange behavior in my report. 

I'm using Units as a Report Parameter.  It gets its values from the Units dataset, it's value = ParameterValue, and its label = Units (Or parameter caption - both exhibit same behavior).

The default is Net Ton. 

The column on the report is: 

=Sum(Fields!Coil_Weight.Value)/code.Expon(Parameters!Units.Value)

(Expon is custom code I had to write to strip off the extra stuff around Parameter Value - I never could find a way to directly get the conversion factor.  I couldn't figure how to pick up Factor on the fly inside the report.)

The report column has a label defined as: 

LTrim(Parameters!Units.Label))

Here's my problem.  When I select net ton, everything is fine.  When I select Gross Ton everything is fine.  The problem comes when I select pounds.  The label on the report reads "Milligrams".  Additionally when I select milligrams from the parameter box, the label says "Milligrams" but the parameter box I just selected milligrams in changes from milligrams to pounds when the view report button is selected.

Answer 1

If you are using the Factor as your Parameter Value then you are seeing the expected behavior.  Parameter values must be unique to work properly.  You should correct Milligrams so that it does not match Pounds.  Note that the label is just that: a label; it is the parameter value which is used for identification, so when you select "Pounds" you are really selecting the value "1.0" which gets mapped to "Milligrams" when we look it up again in the dataset.

Answer 2

  Thanks.  I thought it was something like that.  Here's my problem.  Milligrams is ovbiously incorrect but there may be a case where two labels DO have the same factor.  I could think of cc's and ml's for example.  Is there any other way I might force uniqueness.  I suppose I could use the caption as the ParameterValue but then I'd need to pick up the actual value somehow inside the report.

Answer 3

COOL!  All I had to do was switch Value and Caption and it works better than before!  I don't have to convert something like "[Units].[Factor].&[2.E3]" to 2000 any more.

Sweet.  Why didn't I think of that?