Error: At least one invalid member reference was encountered - OLAP Report Auth

[gosoccer]

Folks,

When the actual category doesn't exist after building the cube, the report sends out an ugly message such as below. In this case, work-address doesn't exists in our database table as a value. Unfortunately, I can't get rid of it as a part of our report since some other situation with other reports we could have this category. Is there anyway to get away from this error? To logically ignore this error when it occurs within the report author. Or even if I can assign a value (work-address in this case) when the cube is generated for the category items that are not available on all cubes utilized.

Here is the error,

At least one invalid member reference was encountered in the query.'[Cube_pilot].[PAR].[PAR].[PAR1]->:[PC].[@MEMBER].[work-address]'

Thank you in advance for your time.

[MFGF]

Folks,

When the actual category doesn't exist after building the cube, the report sends out an ugly message such as below. In this case, work-address doesn't exists in our database table as a value. Unfortunately, I can't get rid of it as a part of our report since some other situation with other reports we could have this category. Is there anyway to get away from this error? To logically ignore this error when it occurs within the report author. Or even if I can assign a value (work-address in this case) when the cube is generated for the category items that are not available on all cubes utilized.

Here is the error,

At least one invalid member reference was encountered in the query.'[Cube_pilot].[PAR].[PAR].[PAR1]->:[PC].[@MEMBER].[work-address]'

Thank you in advance for your time.

Hi,

If you are referring directly to that specific member in your report, then you will always get this error if the member does not exist. An alternative might be to try to derive it using a dimensional function - I assume it has a parent member which will always exist? If so, and the member in question has a known caption, you might derive it using an approach such as:

filter(children([parent member]),caption(children[parent member])) = 'the caption of your member goes here')

Cheers!

MF.

[gosoccer]

 8)
The problem is that the members can be changed depending on the date in which the cube is generated?
i.e. G8 could be an error code that may happen or may not happen on our system prior to the cube rebuild.

So, basically,
1) I need a way to tell the Report Author report to ignore the non-existence of an error code.
Or,
2) I could get the FM Model that is feeding the cube through it's package to create these entries anyway since
there are not that many of them so it always exist in the cube.

Any recommendations for 1 or 2 could really help my project.

I have attached as snapshot showing how the member is used within the Dim-Hierarchy.

Thx so much in advance for your time.
::)

[gosoccer]

OK,
filter(children([parent member]),caption(children[parent member])) = 'the caption of your member goes here') works but
The problem is I have a Member Set that includes Definition based on a custom grouping and I don't know how to apply
this filter to this Member Set.

Thx so much!! :-[

[gosoccer]

Folks,
Since using the filter is not possible when the Custom Grouping is used on a Member Set, I approached the problem in
a different way. We are not dealing with trillion records in our environment (not yet) so here is what I did and the problem
is solved.

1) Used the same FM Model but created a Subject Query to get distinct list of the 20 PARS such as 'work-address' that didn't exist.
This way, no matter which report is used, the listing is always the same. I created a (Outer) to (Outer) relationship between the main table used and this new subject query.
2) Used the Transformer to assign the listing a Dimension and have the Dimension done with the level that matches to the level
associated to the main fact table. This way, although the work-address is not one of the PARS for this dataset, the Transformer still
create the "work-address" and add it to the rest of the cube as it's own level.
3) Since the new members had a relationship established, the new Level created and shown as a part of the Report Author, I could
redo the report but this time using the new level as a part of the Crosstab Edge.

The reports are running exceptionally well and I don't have to worry about getting this ugly error when a Category doesn't exist.

I do not recommend this method for dealing with bigger datasets since getting the distinct listing will add more time to the cube creation process. In our case, it didn't make a big difference.

Using the filter is a better way to handle this issue but unfortunately, I really need to the Custom Grouping.

Thanks MF for your help. 8) :D