detail filter affecting a hierarchy projected by a relative member function

[cognos05]

Error : "The query is not supported. It contains a detail filter affecting a hierarchy projected by a relative member function."
Package: Dimensional package
                         YTD      PYTD    Account count for YTD with negative value
Sales rep Acct1    1            45          1
                Acct2    34          -36         1
                Acct3    -456     -456         1

Expression used for account count : Count([YTD] within set filter(siblings(currentMember(Hierarchy)),[YTD]<0))

Now when I add a prompt to this report, say if sales rep is level 3 then I add some prompt like distributor which is level 2 from the same hierarchy and a detailed filter condition is added.

Ideally i should not add a detail filter,but the problem is I dont have a All member in my distributor, so that if i go with slicer i should select any one of the distributor and I am not sure which distributor user will have access to  .but when i add a detail filter,by default all the distributors are selected and cognos security takes care of showing the required distributors with in all.

Just curious why level 2 detail filter is affecting my report. the current member will pick my level 3 salesrep and take its siblings right.

So how can i achieve this ,

Thanks,
Nithya

[MFGF]

Error : "The query is not supported. It contains a detail filter affecting a hierarchy projected by a relative member function."
Package: Dimensional package
                         YTD      PYTD    Account count for YTD with negative value
Sales rep Acct1    1            45          1
                Acct2    34          -36         1
                Acct3    -456     -456         1

Expression used for account count : Count([YTD] within set filter(siblings(currentMember(Hierarchy)),[YTD]<0))

Now when I add a prompt to this report, say if sales rep is level 3 then I add some prompt like distributor which is level 2 from the same hierarchy and a detailed filter condition is added.

Ideally i should not add a detail filter,but the problem is I dont have a All member in my distributor, so that if i go with slicer i should select any one of the distributor and I am not sure which distributor user will have access to  .but when i add a detail filter,by default all the distributors are selected and cognos security takes care of showing the required distributors with in all.

Just curious why level 2 detail filter is affecting my report. the current member will pick my level 3 salesrep and take its siblings right.

So how can i achieve this ,

Thanks,
Nithya

A detail filter against a dimensional source? Well, that's your problem right there! :) It's like putting petrol (gasoline) in a diesel car. You just shouldn't do it.

Detail filters are relational constructs and should be confined to relational reports (ie from a package containing query subjects and query items).

I'm struggling to follow your requirement. If you already have a sales rep, why would you logically want to filter on distributor? If distributor is at a higher level in the same hierarchy then surely a rep would only belong to one distributor? What am I missing? You mention slicers but that doesn't make any sense (to a dim old muppet like me) if distributor is in the same hierarchy as sales rep. Can you explain?

Cheers!

MF.

[cognos05]

Hi MFGF,
One distributor will have several reps and each rep will have several accounts.
Distributor-Reps-Accounts is the hierarchy levels.

So in my report I show reps and accounts as shown in previous posts. Now I will place two prompts in my report which will be distributor and reps.

so when I select my distributor, the reps and accounts for that distributor is displayed.and I will also have an option to select specific reps cascading with the distributor.

1) I have two use  a cascading prompts in my slicer.How to use this one.I want to have both propmts in the same report page
2) I dont want user to select any valueby default .And the issue here is I dont have a ALL Mun Value for distributor. And I dont know which distributors to show for which users.My cognos security takes care of showing only specific distributors for a user.In case of propmts with detail filter, it automatically adds a all value by default in your prompt.

Thanks,
Nithya

[MFGF]

Hi MFGF,
One distributor will have several reps and each rep will have several accounts.
Distributor-Reps-Accounts is the hierarchy levels.

So in my report I show reps and accounts as shown in previous posts. Now I will place two prompts in my report which will be distributor and reps.

so when I select my distributor, the reps and accounts for that distributor is displayed.and I will also have an option to select specific reps cascading with the distributor.

1) I have two use  a cascading prompts in my slicer.How to use this one.I want to have both propmts in the same report page
2) I dont want user to select any valueby default .And the issue here is I dont have a ALL Mun Value for distributor. And I dont know which distributors to show for which users.My cognos security takes care of showing only specific distributors for a user.In case of propmts with detail filter, it automatically adds a all value by default in your prompt.

Thanks,
Nithya

Ok. So in the crosstab you have this expression for your sales reps:

set([your sales rep level] -> ?YourRepParameter?)

In the query that drives the value prompt for sales reps, you have a calculated query item (set as the Use Value property of the prompt) with the expression

descendants(set([your distributor level] -> ?YourDistributorParameter?), [your sales rep level])

In the query that drives the value prompt for distributors, you use the [your distributor level] level and set this as the Use Value of the distributor value prompt.

Cheers!

MF.

[cognos05]

But this will still ask me a value for distributor to be selected .I wanted all value to be selected by default.But not sure how to bring that in to prompt. I am having this doubt for a long time about having a All member MUN value for each levels in a dimension. is it necessary to have ALL Member Mun value.
Please reply at your Leisure.

My Dimensions Level looks like below

1) ParentLevel--First Level in Hierarchy

Has one member say Allregions and this belongs to ParentLevel

2)  RegionLevel -Second Level in Hierarchy
Has many members being individual regions like East, West each being a member

3)  Distributor Level - Third Level in Hierarchy
has many distributors each being member like A,B,C

4) Rep Level - Fourth level in Hierarchy
has many reps each being single member TOM.JOHN

5) CustomerAccount Level - fifth level in Hierarchy
has many customers each being individual members like 1244545,12121

There is as such no all members MUN value at each level.Only my parent level has a member all regions which when clicked goes down till customer accounts.

So the requirement is to have a prompt say for any level 2 or level or level 4  and automatically show a all value on my prompt.


For example  In relational lets say I have a normal value prompt for distributors with  default  filter  and when I run the report i have an  all value  shown by default in my distributor prompt and the rest values will be the respective distributors entitled for that user logged in .since these values are based on user logging in cognos security will take care of showing the required other entitled distributor values.And report shows all distributor data for that user.if there are 3 all will show all 3 and you can also select other 3 individually.

Now when i use a slicer, I give [Distributor]->?promptparametername? This asks me for a value to select.I dont have a ALL MUN variable for my distributors.

How to achieve the above explained result. I am trying this for a long time and could nt find the solution. is that there should always be a ALL MUN for each levels defined.

I also tried
union([All Regions], descendants([All Regions], [Distributor Level])). This will have values like (Allregion from level1 and other distributors under this.)
I assume the top member in my level 1 is the root member of the hierarchy.
and gave this as use value to my prompt.But what will my filter condition  be one member is from level1 and other is from level3 and it will give me an error . So if iassign to this I will get an error.


I appreciate your help in making me understand on how to achieve this.

Thanks,
Nithya

[cognos05]

Hi MFGF,
I tried the approach which you told in Previous reply.

If I change to a distributor from combo say ABC  to DEF and it has three reps 1,2,3.
I get No data Available even though data is there , because the reps are not selected but the default value (ie) propmt name is selected and doesnt have any data. but if this is the same in detail filters it would have replaced all for the sales rep and shown all three reps. Can I achieve the same here.
Thanks,
Nithya