Skip to Content
0

I need to take out duplicates from date column in cross tab in crystal report

Apr 26, 2017 at 07:40 PM

43

avatar image
Former Member

In cross Tab:

One column is date. I need to show only month in following order fiscal year order:

April

May

june

July

August

September

october

november

december

january

febuary

march.

It should not be more than one time. While I am trying I am getting duplicates months in the column. So month should not be more than one time. I am using crystal report 8.0

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Brian Dong Apr 26, 2017 at 10:56 PM
0

Hi,

When you group your date, are you grouping for each month?

Are you selecting a specific fiscal year?

My guess as to why you are getting repeating months is because you are including multiple years, that is if you are grouping by month.

Have a look at the Group Options in the Crosstab Expert for the month field.

Brian

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Now I am getting this one using cross tab:

2006 2007 20008 2009 2010 2011 2012 2013 2014 2015 2016 2017

September

october

november

december

january

febuary

march

september

january

november

,,

etc

But I need this one: Our Fiscal Year starts from April to March. Based on our fiscal year it should give the result :

FY 2006 FY2007 FY 2008 FY2009 FY2010 FY2011 FY2012

April

May

June

July

August

September

October

November

December

January

Febuary

March

0

Hi,

Create your FY as:

If Month ({Orders.Order Date}) In [4 to 12] Then 
    Year ({Orders.Order Date}) 
Else Year ({Orders.Order Date}) - 1; 

Create your FM as:

Select Month ({Orders.Order Date}) 
    Case 4: 1 
    Case 5: 2 
    Case 6: 3
    Case 7: 4 
    Case 8: 5 
    Case 9: 6 
    Case 10: 7 
    Case 11: 8 
    Case 12: 9
    Case 1: 10 
    Case 2: 11 
    Case 3: 12 
    Default: 0; 

To display the month as a name instead of a number, go into the Group Options for the FM formula and in the Options tab select Use a Formula as Group Name as use this formula:

["April", "May", "June", "July", "August", "September", "October", "November", "December", "January", "February", "March"] [{@FM}];

That should do it.

Brian

0