asked on
Having last 3 years in my crosstab even if no data
Hi,
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.
CrossTab97.doc
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.
CrossTab97.doc
Crystal Reports
Last Comment
Mike McCracken
ASKER
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 ...
Thanks.
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 ...
Thanks.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
How are you getting the totals for each year?
mlmcc