SSRS+MDX+scalar axis -> Evaluate Y-values for missing X-values

Question

My MDX-query retrieves about 10 columns names (one is actually the key EVENT_ID - to be used in the report for linking purpose and drilldown behavour) + a time measure (time losses per day in our factory) + a budget measure.

Since I have all these columns, I will not be able to "Include empty cells" -> results in miljons of rows.

I am presenting the time losses in a column chart + evaluating a moving avereage line (from code behind, quite complex..) + presenting a budget line. I have put, using subreports, a series of charts in an top-down layout, only chart-series are changed using parameters, so I wish all charts to look the same (same start- and ending point on the x-axis) when scolling though them, only the series group will be changed -> comparisation will be easy.

My problem is,

Since the MDX-query is only reporting days where we actually have time losses,

I will not be able to avaluate a moving avereage value for days occuring after the last day in the dataset.
I am not be able to show a budget value for days occuring after the last day in the dataset.

This results in a very unpleasent layout or the subreports, where some charts has values the last month/day and does not...

Please, has anyone solved any similar problem? I have eaven tried solving this using the Lookup-function between an other DataSet holding all dates, but the use of seriesGroups makes it kind of unuseable.

Added info:

Look at image below

da*n I only have 6 point, need 10 to post images :/

The thin line evaluates a gliding average ok, but I am not able to populate a value after mar since my dataset does not have values after mars.

The image should show a column chart Jan, Feb, Mar, Apr, May. I have values and a gliding average evaluated only for Feb, Mar -> the gliding average will not show after Mar. I would like it to be evaluated also Apr and May. The gliding avereage is evaluated from code-behind:

iif(DateValue(Fields!Date.Value)>=DateValue(First(Fields!Date.Value, "TimeData"))
,Code.movAvg.movingAverage(
    Sum(Fields!Time.Value,"CategoryGroup")
    ,DateValue(Min(Fields!Date.Value, "TimeData"))
    ,DateValue(Fields!Date.Value)
    ,DateValue(Max(Fields!Date.Value, "TimeData"))
    ,Parameters!Type.Value
    ,"TOTAL"
)
,Nothing

)

But this code is not executed for "empty" Dates...

I see two options:

Change the mdx query to give me 0-values for all periods (include empty cells?)
Or somehow in SSRS able to populate values for time-periods not existing in the dataset

If I go for option 1, I need to reduce the field-count in my query (current 10 is not ok), but since one of the report-parameters is actually mapped to 5 of these fields (abillity to change group-by behavour) I need to make the query dynamic, evaluated fron concatenated strings which results in no abillity to use the query designer ever after...

Thats why I was aiming for option 2. But I have started to understand option 1 is the only was to go...

How do you populate you date dimension? Is it based on only days that exists in facts or all dates? — Rick
I only view days that exists in the fact, otherwize I will get millions of rows since I have about 10 column in the query... Sincething like this on rows: NON EMPTY { ([Date].[Date].[Date].ALLMEMBERS * [Department].[Department].[Department].ALLMEMBERS * ... ) } — Henrik Nordling
Can you show some sample output of what you are getting and what you need? — Rick
Ok, I will try to describe in more detail, I am not quite comfortable with this editor, missing new line for example... the info will be posten above in the main question. — Henrik Nordling

Henrik Nordling Henrik Nordling · Accepted Answer · 2012-05-30T09:03:32

I have solved this now, doing this:

I created two datasets in SSRS.

This dataset included all dates in the period, and all "Measures", no other dimension attributes in the MDX-query.
This dataset included only the NON EMPTY dates in the period, and the main measure (hours), and all dimension attributes I have interest in (about 10 attributes).

Then I set dataset #1 (all dates are showing) in my chart, and where I had interest in showing dimension attributes, I used the SSRS function LookupSet to join data between the datasets (i joined data on the date field). Not the most beautiful solution, but it sure worked out good in the end.

SSRS+MDX+scalar axis -> Evaluate Y-values for missing X-values

1 Answers