Link to home
Start Free TrialLog in
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.
Avatar of Mike McCracken
Mike McCracken

Are you filtering by products?

How are you getting the totals for each year?

Avatar of NickHoward


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 ...

Avatar of Mike McCracken
Mike McCracken

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial