Drill through chart report percentage problem

[Chandrasekar]

Hi Gurus,

I am using Cognos 10.2 - Relational model.

I need to create drill through report. Drill through parent report have crosstab report.

      Dimensions is Product line and Year.
      Measure is Revenue %. From the query is percentage([Revenue] for [Year]).

It works year vise revenue percentage. And each year percentage total is 100% (In this report don't have prompt page).

Drill through child report is bar chart report. Measure is Revenue %. From the query  is percentage([Revenue] for [Product type]). Dimension is Product type. No legend.

My requirement is, Ex:  Report 1: Parent report,
                                                        2010     2011    2012    2013
                               PLine1                40%      30%    50%      45%
                               PLine2                 60%      70%    50%      65%
                               Total                   100%     100%   100%   100%
Report 2: (If i click PLine2, Year 2011, Revenue is 70%.) Total of product type is need to 70.
But i get 100% for each product type. For your reference i am attached image.

How to solve this?

Please help me.

Thanks & Regards,
Chandrasekar.




 

[MFGF]

Hi Gurus,

I am using Cognos 10.2 - Relational model.

I need to create drill through report. Drill through parent report have crosstab report.

      Dimensions is Product line and Year.
      Measure is Revenue %. From the query is percentage([Revenue] for [Year]).

It works year vise revenue percentage. And each year percentage total is 100% (In this report don't have prompt page).

Drill through child report is bar chart report. Measure is Revenue %. From the query  is percentage([Revenue] for [Product type]). Dimension is Product type. No legend.

My requirement is, Ex:  Report 1: Parent report,
                                                        2010     2011    2012    2013
                               PLine1                40%      30%    50%      45%
                               PLine2                 60%      70%    50%      65%
                               Total                   100%     100%   100%   100%
Report 2: (If i click PLine2, Year 2011, Revenue is 70%.) Total of product type is need to 70.
But i get 100% for each product type. For your reference i am attached image.

How to solve this?

Please help me.

Thanks & Regards,
Chandrasekar.

How is Revenue% being calculated in the report you are drilling to?

MF.

[Chandrasekar]

Hi MFGF,

Sorry for my communication. In my first report is ok. Problem in my second report.
Ex:  Report 1: Parent report,
                                                        2010     2011    2012    2013
                               PLine1                40%      30%    50%      45%
                               PLine2                 60%      70%    50%      65%
                               Total                   100%     100%   100%   100%

If i select 70% from Product type after it is drilled its shows like below in chart report:(i need this answer)

                           PType1       30%
                           PType2        10%
                           PType3        30%
                           Total            70%

I get the wrong answer like this,
                          PType1    42.5%
                         PType2     15%
                         PType       42.5%
                          Total        100%
In my query is percentage([Revenue] for report)
Please help me. How to solve this?

Thanks and Regards,
Chandrasekar.

[MFGF]

Hi MFGF,

Sorry for my communication. In my first report is ok. Problem in my second report.
Ex:  Report 1: Parent report,
                                                        2010     2011    2012    2013
                               PLine1                40%      30%    50%      45%
                               PLine2                 60%      70%    50%      65%
                               Total                   100%     100%   100%   100%

If i select 70% from Product type after it is drilled its shows like below in chart report:(i need this answer)

                           PType1       30%
                           PType2        10%
                           PType3        30%
                           Total            70%

I get the wrong answer like this,
                          PType1    42.5%
                         PType2     15%
                         PType       42.5%
                          Total        100%
In my query is percentage([Revenue] for report)
Please help me. How to solve this?

Thanks and Regards,
Chandrasekar.

Ok - thanks for the clarification. How are you calculating the percentage in that second report, though? Are you using an expression? If so, what expression are you using?

MF.

[Chandrasekar]

Hi MFGF,

Revenue_by_report query expression is:     percentage ([Revenue] for report)    I assumed this query get the % values from 1st report.
Revenue_by_ptype query expression is:      percentage ([Revenue] for [Product type])
Revenue% query expression is:                 [Revenue_by_Report]/[Revenue_by_ptype]

I am use this quries in my report 2. How to solve this?

Thanks & Regards,
Chandrasekar.

[MFGF]

Hi MFGF,

Revenue_by_report query expression is:     percentage ([Revenue] for report)    I assumed this query get the % values from 1st report.
Revenue_by_ptype query expression is:      percentage ([Revenue] for [Product type])
Revenue% query expression is:                 [Revenue_by_Report]/[Revenue_by_ptype]

I am use this quries in my report 2. How to solve this?

Thanks & Regards,
Chandrasekar.

Percentage([Revenue] for report) will base percentages on the overall report total, which is why everything is adding up to 100%

I think you need to base your percentages on the total for the year rather than the total for the report. I'm guessing your report query is filtering on the year and the product line you are passing in?

MF.

[Chandrasekar]

Hi MFGF,

Yes, its have filtering, based on year in 1st report . Query expression is: percentage ([Revenue] for [Year]).

Thanks & Regards,
Chandrasekar.

[Chandrasekar]

Hi MFGF,

In this situation need not change 1st report information. What are all the things add in report 2. How to get the base percentages for total year and product line.

How to solve this?

Thanks & regards,
Chandrasekar.

[MFGF]

Hi MFGF,

In this situation need not change 1st report information. What are all the things add in report 2. How to get the base percentages for total year and product line.

How to solve this?

Thanks & regards,
Chandrasekar.

The source report is fine as it is.

In your target report, I'd suggest you add a second query containing the year and revenue items. Filter this on the passed year parameter. This will give you the revenue total for the year passed in the drill through definition.

Change your report to allow cross product queries, and you can then use this as the denominator in your percentage calculations.

MF.

[Chandrasekar]

Hi MFGF,

Can you give the query expression.

Thanks & Regards,
Chandrasekar.

[MFGF]

Hi MFGF,

Can you give the query expression.

Thanks & Regards,
Chandrasekar.

What query expression?

MF.

[Chandrasekar]

Hi MFGF,

Now i am clear, My child report new data item name is Revenue %. Data item is like,

          Case when ?pval_productline? = [Productline] and ?pval_year? = [Year] Then
          percentage([Revenue] for [Year])
          else
          [Revenue]*0
          end

It works fine in cross_tab and list. When i use chart it works only for 2010. Other 2011, 2012, 2013 years revenue % not displayed. Here i am using suppression for remove zero values.

For your reference i am attached image.

Please help me and tell where i mistake.

Thanks & Regards,
Chandrasekar.

[Chandrasekar]

Hi MFGF,

Chart can allow only first year of the list report. Filter not worked for other year, but it works in list and cross tab. How to solve this issue Please help me.
         
                  Master Report-No parameters. Product line,Year and Revenue % display directly in cross_tab.
                  Drill Through-Values pass through data item.
                  Detail Report-2 parameters. Product line and Year. Report have chart with product type and Revenue %.

Thanks & Regards,
Chandrasekar.