OP-ERR-0181 At least one invalid member reference was encountered in the query.'

[gosoccer]

Please HELP!!!
Using Report Author 10.1.2 and OLAP implementation.

If anyone can help with this, I would greatly greatly appreciate it. It is the last part of the
project that I'm truly stuck!!!!
OP-ERR-0181  
      At least one invalid member reference was encountered in the query.'[dsCube].[T_LIST].[T_LIST].[T]->:[PC].[@MEMBER].[act-p-auth]'

I'm using Custom Grouping as the attachement shows and everything works perfectly except when an item that
was put in as a part of the Exclusive List of the Custom Grouping, is not in the CUBE.

The report fails terribly when this occurs with the above message.

HELP HELP@!!!@
:(

[adam_mc]

I'm not sure there is anyway to avoid this.
Unless there is some methodology that I don't know about either (which could be very likely), you cannot reference a member that doesn't exist!

Assuming, you NEED to have this member in the cube, do you have access to the source data that produces the cube?
When I've encountered this issue, I in effect create a union with all the members I need to exist and set all their measures to be zero.
In that way, I always have all the required members present.

Maybe there is another way that one of the real experts on this board can help us both with, but the method above does work for me.
Perhaps something along the lines of child of parent relationship (or some other "family" logic)?

Hope this helps.
Thanks,
Adam

[gosoccer]

Adam,
That is exactly what I need. Union with other members.
So, did you do the UNION as a part of the FM Model/Subject Query.

I'm so stuck with this.

Could you please give me an example of how you established the UNION?
Thank you so much

[MFGF]

Adam,
That is exactly what I need. Union with other members.
So, did you do the UNION as a part of the FM Model/Subject Query.

I'm so stuck with this.

Could you please give me an example of how you established the UNION?
Thank you so much

Isn't this the same question you asked in the other thread?

http://www.cognoise.com/index.php/topic,25985.0.html

Adam is suggesting that you change your cube design so that the members are always there - ie if they are not referenced, add them with a zero measure. It's a cube design issue, not a reporting solution, though.

Regards,

MF.

[gosoccer]

Yes, if I could change my cube design to deal with this, it'll save me a huge headache.
The previous treat you mentioned worked just fine but it is not 100%.

Let me know and thank so much!

[adam_mc]

gosoccer...

MFGF is one of the experts I was hoping would give us his input.
He indicates in the thread he references that there may be a way to solve the problem.
However, I'm not sure if that worked for you.

He is also right, my suggested solution is not a reporting solution, but a cube design/build solution.

I do make the changes in FM (we use Transformer Cubes created from FM SQL's).
As I don't know your specific database design, I'll try and explain what we do but it will be very generic and with the assumption that you can get all the members you need.

--- Existing Member SQL

select
member_table.member as existing_member,
member_table.measure as measure1
from member_table

union all
--- Add All Member SQL
select
all_members_table.member as existing_member
0 as measure1
from all_members_table.member


Something like the above should work.
Or worst case, you could have multiple unions with the member hardcoded - Not very good practice.

Hope this helps!
Adam

[gosoccer]

OK,
This is excellent. I agree with what you and MFGF saying. I won't use the Custom Grouping again since
it is so limited in this case. Using filters and dimensional H. commands is the way to go. I just have to
get better at it.

Thank you for the code. It is perfect. Although I don't have a different table but I can use the same table.
When we switched the cube creation to use a different testbed for our QA folks, we were missing one
of the items and the report failed.

It seems like, using Custom Groups, there is no way to escape (have it to ignore) when a category doesn't
exist.

Thank you again@ :) :)