Former Member

### How to count year wise no of column in cross tab report

Dear Experts ,

I have created a cross tab report , which display year wise and month wise data.

In 2017 we have data for 4 months (jan , feb , mar & apr ), where in 2016 having 12 months data.

In Year wise total i want to calculate ( total / No of months of the year ) .

Total value for the year 2017 is 380 & 2016 is 1139 . Now i want to display 380/4 and 1139/12 .

In Right click -> Total value -> format filed -> display string -> i have written condition //cstr(CurrentFieldValue / distinctcount({@Month_Order},{@Year}) ) .

I am getting correct value for year 2016 , but it showing wrong value for 2017 .

When i place a formula distinctcount({@Month_Order},{@Year}) in detail section , it showing value 4 for 2017 and value 12 for 2016.

cross-tab.jpg (232.4 kB)
10|10000 characters needed characters exceeded

May 24, 2017 at 04:31 PM

Hi Sri,

Here's what you need to do:

1. Create a formula (@DC) with this code:

`DistinctCount({@MonthYear},{@Year})`

I believe 'Year' and 'Monthyear' are the columns in your crosstab?

2. Go to the Crosstab Expert and add this DC formula field as the second summary. Set its summary function as 'Maximum'.

3. Go to the Preview tab and right-click the 'Total' cell header and select 'Insert Child'.

4. A new blank column will be added with zero values at the end of the first year on your crosstab.

5. Right-click this blank header cell and select 'Calculated Member' > Edit Insertion formula and use this code:

`GetColumnGroupIndexOf(CurrentColumnIndex) = 1`

6. Right-click the blank white space to the top left of the crosstab and select Advanced Calculations > Calculated Member.

7. In the 'Properties' section to the right, for 'Group Level', select 'Before'.

8. You should now see the blank column for ALL years beside the original "Total" column.

9. Right-click this blank columns header cell > Calculated Member > Edit Header formula > Type in:

`"Total"`

10. In this new "Total" column, you should see two summary cells with zero values one below the other.

11. Right-click the Second summary cell in this Total Column > Calculated Member > Edit Calculation Formula > use this code:

`GetTotalValueFor("@MonthYear",1`

12. Right-click the First summary cell in this Total Column > Calculated Member > Edit Calculation Formula > use this code:

`If GetTotalValueFor("@MonthYear",1) = 0 then 0 else GetTotalValueFor("@MonthYear",0)/GetTotalValueFor("@MonthYear",1)`

13. Highlight the Second Summary cell and change its text colour to White.

14. Reduce the height of this second summary cell as small as possible.

15. Go to the Crosstab Expert > Customize Style tab > Under "Columns", highlight "Year".

Under "Group Options", select "Suppress Subtotal"

Let me know how this goes.

-Abhilash

10|10000 characters needed characters exceeded
• May 23, 2017 at 04:46 PM

Hi Sriram,

What totals do you get? In the screenshot, your example in red is correct for 2017 but 2016 should be 94.95.

Also, your example shows 2017 rounded to a whole number but 2016 to a decimal. Your formula looks like it is not rounding.

Can you show us what numbers you expect to see for 2017 and 2016?

Thanks,

Brian

10|10000 characters needed characters exceeded
• Former Member

Yes Brian , I am getting 4 in both years . How to solve this , is there any other way to get . Please suggest .

• May 25, 2017 at 07:48 AM

Hi Sri,

Adding a second summary does not insert a new row.

Could you check if you've inserted a Calculated Row as well?

Right-click the blank white space to the top left of the crosstab > select Advanced Calculations > Calculated Member.

Under 'Calculated Members', how many entries do you see?

-Abhilash

10|10000 characters needed characters exceeded
• Former Member

Hi Abhilash ,

I seen two entries in calculated formula , 1. Sum of value and 2. Max of DC .

In Row filed , i had used formula @Change_rate_Name .

if {change_request.dv_u_substate}="Closed Successful" then "Change Success" .

I think i am getting new row where conditions not matched with formula . Please suggest . 15.jpg14.jpg

15.jpg (251.1 kB)
14.jpg (278.5 kB)