cognos dimensional report with count Issue

[cognos05]

Hi ,

I am facing an issue with the below scenario , I am not sure how to handle this , any suggestions is appreciated .

I have a dimension as Customers , where  i have Agencies and reps and customers as level 1 ,level2 and level 3 in dimension.

so in my report i am showing agencies and reps are nested with in the agencies .with a total on reps to see the individual agency total.

In my columns I have a YTD grouped column and Account Count Column . Under these 2 columns I have nested few products.

Now the issue is happening with Account count column expression .

The expression for account count is :

Give me the count of all customers under the specific rep who had a sale of greater than zero for the specified products in the column .

Count(([Sales]) within set filter(children(currentmember([DistributorSalesCube].[ShipTo Customer].[ShipTo Customer])),tuple(currentMember([DistributorSalesCube].[Products].[Products]),[Current FYTD],[Sales])>0))

it will say to take the current product in the column and go to the row and row will have reps nested with in agency , take the rep and its children will be the customers , for all the customers for that rep count if it had sales for that product .

This works fine until I add a total nested to my rep in rows , this Account expression expects the member to be a rep since we count the children of the member , but when total comes it doesnt know how to evaluate Account Count Expression.

The values for account count is shown blank or 1 which is wrong .

                                        YTD                     Account Count
                                      Prd1 Prd2              Prd1 Prd2 Prd3
Agency 1   REp1             100  200               5         6     7
                 Rep2              123  333               3         3     2
                 Rep3              567  343               3         1     0
                 Total              790   876               blank or 1 value is populating -  should have the total accounts  like 11,10,9.
Any suggestions on how to fix this issue is highly appreciated .

Thanks,
Nithya

[cognos05]

cognos dimensional gurus , any help on this is appreciated. Thanks

[CognosPaul]

The expression looks fine. My guess is the problem is with the total. How is this defined? Are you using the wizard, or manually defining total(currentMeasure within set [Reps]), or is it just the agency member?

If you're using the wizard, make sure the properties are set correctly. If I recall, Cognos defaults to using "within detail", which won't work well in olap sources.

[cognos05]

I tried both wizard and also tried creating total member but both is not working fine .

I use total(currentMeasure within set [reps]) and also the wizard summary total.

Thanks,
Nithya

[cognos05]

Okay I think the issue is with solve order that i had set . I had to set sole orders differently for totals, ytd and account count to work .

I will test and keep you posted.

Thanks Paul !!

Thanks,
Nithya

[cognos05]

okay i found the issue , i have a text item just to group certain products . This is causing the totals issue. If i remove the text item in the column it works well. Any idea on how to use a text item in a column.
                          Ytd            Account Count
                        P1 P2           product category1 - text item
                                              P3 P4

Agency Rep     34 45              33 45
             Total    34 45             12 12

Thanks,
Nithya

[cognos05]

Now I have an additional requirement to show % Account Count with respect to each product with respect to a fixed product
                                        YTD                     Account Count        % Account Count
                                      Prd1 Prd2              Prd1 Prd2 Prd3         Prd2 Prd3
Agency 1   REp1             100  200               5         6     7             6/5    7/5
                 Rep2              123  333               3         3     2              3/3    2/3
                 Rep3              567  343               4         1     0              1/3   0/3
                 Total              790   876               12     10    9              10/12 9/12[/b]

% Account count is total number of accounts of a rep for a specific product divided by totl number of accounts for prd1 products.

% account count - expression used

Count(([H0 Sales]) within set filter(Children(currentmember([DistributorSalesCube].[ShipTo Customer].[ShipTo Customer])),tuple(currentMember([DistributorSalesCube].[Items].[Items]),[Current FYTD],[H0 Sales])>0))/Count(([H0 Sales]) within set filter(Children(currentmember([DistributorSalesCube].[ShipTo Customer].[ShipTo Customer])),tuple([20 - Shoulder],[Current FYTD],[H0 Sales])>0))


so the total for % Account count against total member under reps  is showing as 100 % , it should show 10/12 which is 83.33 instead it shows 100% for % accounts across the total row for all products.

My solve order if given for % Account count is less than total, then it totals all % in cloumns but we dont need that, so when solved order for % Account is set more its showing 100%

Thanks,
Nithya