Avatar of NickHoward
 asked on

Having last 3 years in my crosstab even if no data


I am having sales values based on Customer Hierarchy and these sales values are shown by last 3 YEARs (YEAR is string in my database). Please note that data will be always for last 3 years (so no filter required in Crystal 2011 Designer). I want to see performance of current year to last year, last year to previous last year. For this, I created 2 calculated members rows in cross tab.

It works perfectly if I have last 3 years data (2010,2011,2012) for any of my Products. If no product was sold in any of last 3 years for particular customer, then my caculated rows in cross tab do not work.

Solution I want is that even if there is no data for any of year I always display last 3 years so that my formulas in calculated rows and rows header keep working. Year is my string field (YYYY).

Please advise how I can achieve it with details solution as I am not very expert in coding. I have attached complete example in attached MS Word (97-2000) file.

If not possible, then advise how I revise my formulas in calculated rows header/ values to overcome this.

Many thanks.
Crystal Reports

Avatar of undefined
Last Comment
Mike McCracken

8/22/2022 - Mon
Mike McCracken

Are you filtering by products?

How are you getting the totals for each year?


Hi mlmcc,

I am using SAP BW Query as my datasource in CR which is filtering the data based on Years. No filter is applied in CR.

I then use simply 0CalanderYear field in my Crosstab row and Amount is being summed in crosstab. Due to some compatibility issue of CR with SAP BW, 0CalanderYear is appearing as string in CR 2011 Designer.

Could be creating a formula field on 0CalenderYear ...

Mike McCracken

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck