How do I display a fixed list of dimension members in an SSRS tablix?

[Man5ell]

Hi,

I have a dimension "mill name" for which I have production data measures in the same cube.

The problem I have is that there are some days where a mill does not produce any product and their row is therefore missing in the tablix I have created to summarise the production data per mill.

The customer would like to see every mill in the tablix, even if they have not produced anything.

This is almost asking for an out-left-join on the cube dimension! How is this possible?

Any advice would be greatly received.

Thanks in advance

Steve

 

[RiverGuy]

Don't use NON EMPTY in your MDX query?

 

[Man5ell]

If I use NON EMPTY and/or do not include the formula:

=Fields!WgtGross.Value+Fields!WgtPaw.Value <> 0

In the dataset, then the report just fails anyway - I think because there is too much data to process.

Any other suggestions?

Thanks again

 

[RiverGuy]

I don't understand what you mean. Using NON EMPTY will result in less data in your resultset.

If you DO NOT use NON EMPTY, you'll get something like this:

MEMBER 1     $100
MEMBER 2     NULL
MEMBER 3     $200
MEMBER 4     NULL

If you use NON EMPTY, you'll get this:

MEMBER 1     $100
MEMBER 3     $200

So option 1 should be what you want. As far as too much data to process - I think SSRS can handle somewhere around 4 GB in a dataset. If you've got that much data on a report, you're going to have other problems. No human being is capable of consuming 4 GB of text.

 

[Man5ell]

Ok, this is a little off topic, but it might help me to understand what it going on - if you are willing to help?

I have two filters on my dataset (dataset_AutoDailyProd):

Filter 1:

=Fields!WgtGross.Value + Fields!WgtPaw.Value <> 0

Filter 2:

=DateValue(Fields!Date_Key.Value) 

BETWEEN

 =DateSerial(Year(dateadd("d",0,Parameters!paramDate.Value)),Month(dateadd("d",0,Parameters!paramDate.Value)),1) 

AND

[i]datasetUniqueDates\Date_day as paramDate (default = yesterday)[/i]

If I remove Filter 1 then I get the following error message:

An error occurred during local report processing.
An error has occurred during report processing.
Failed to evaluate the FilterExpression of the DataSet 'dataset_AutoDailyProd'.

And the report obviously fails to run. As soon as I add Filter 1 back again, it is ok. I assumed that this was because there was too much data for it to process without removing the zeros but, as you say, there is no way that this is >4gb, not even close.

Have you experienced this before?

Thanks again