Parameter-based report column headings

• Lempster

  SSCoach

  Points: 15651

  More actions

  July 31, 2009 at 3:32 am

  #224318

  I've got a report that displays data from an OLAP cube. One of my report parameters enables the data to be grouped by 'Day', 'Month', 'Quarter' or 'Year'. This works in conjunction with another parameter - StartDate - that enables the starting date for the reporting period to be chosen at the 'Day', 'Month', 'Quarter', or 'Year' level of the Date hierarchy.

  Everything is fine if my StartDate parameter is selected at either the 'Day' or 'Month' level (and any of the Group By parameter values), but if I select the 'Quarter' or 'Year' levels as the StartDate and the corresponding Group By value, the column headings disappear although the correct data is displayed.

  The expression used to obtain the column headings is:

  =IIf(Parameters!ColumnGroupBy.Value = "Day", Fields(Parameters!ColumnGroupBy.Value).Value &" " &Fields!Month.Value & " " &Fields!Year.Value,

  IIf(Parameters!ColumnGroupBy.Value = "Month", Fields(Parameters!ColumnGroupBy.Value).Value & " " &Fields!Year.Value,

  IIf(Parameters!ColumnGroupBy.Value = "Quarter", Fields(Parameters!ColumnGroupBy.Value).Value & " " &Fields!Year.Value,

  IIf(Parameters!ColumnGroupBy.Value = "Year", Fields(Parameters!ColumnGroupBy.Value).Value, "Error"))))

  ..so basically the column heading also contains the 'Month' and 'Year' if the GroupBy value is 'Day' and for all the other GroupBy values it will show that value plus 'Year', e.g.

  31 July 2009

  July 2009

  Quarter 3 2009

  2009

  The dataset that contains the values for the GroupBy parameter is:

  SELECT

  'Year' AS ParameterCaption,

  'Year' AS ParameterValue

  UNION

  SELECT 'Quarter' AS ParameterCaption,

  'Quarter' AS ParameterValue

  UNION

  SELECT 'Month' AS ParameterCaption,

  'Month' AS ParameterValue

  UNION

  SELECT 'Day' AS ParameterCaption,

  'Day' AS ParameterValue

  The first image shows a correct report - the column headings are present.

  

  The second image shows an incorrect report - the column headings are missing, but the data is correct.

  

  I appreciate there's a lot to wade through here, but has anyone got an idea why my column headings are missing?

  Thanks and regards

  Lempster