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

[Former Member]

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.

Please suggest .

[abhilash_kumar · Accepted solution]

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

[abhilash_kumar]

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

[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

[former_member292966]

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

[Former Member]

Dear Brian ,

Thanks for your reply . Expectation value for the year total 2017 95.04 & 2016 94.95 .

But i am getting total for the year 2016 in report 284.86 . Please suggest .

[former_member292966]

Hi Sriram,

I see the problem now. 2016 is using the number of months from 2017 instead of the number of months for 2016. If you replace the total in the Display String with:

cstr (distinctcount({@Month_Order},{@Year}));

Do you will see 4 for both years?

[Former Member]

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