COGNOiSe.com - The IBM Cognos Community

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

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

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.

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

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

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

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