Crystal reports - Cross tab Each row starts with a new zero

CrystalReports2011
CrystalReports2011 used Ask the Experts™
on
In crystal reports 2008.
I have inserted a crosstab
I get a zero in each new row - so the cross tabs dont match.

see example below:
expected output:
      01      02      03      04
20807      127,069      66,479      7,446      1,685
20808      79,696      70,110      16,868      2,635
20809      121,018      59,016      9,566      5,264      
20810      100,463      115,018      8,118      -297      

Instead I get output:
      01      02      03      04
20807      127,069      66,479      7,446      1,685
20808      0                   79,696      70,110      16,868      2,635
20809      0                   0                    121,018      59,016      9,566      5,264      
20810      0                   0                     0                  100,463      115,018      8,118  -297      

how do i get rid of these zeros ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
WHat do they 01 - 04 in the top row mean?

DO you have further column headers?

mlmcc

Author

Commented:
those are the months 01 (jan) 02(feb).... till 12(dec)
and 20807 (july 2008)
20806 (june 2008 )
till july 20011

so the row and column headers go a lot further

Author

Commented:
the calculations are correct to the last decimal point
so the formula's and sql's seem correct

but strangely they are all shifted by a zero for each new row
WillOwner

Commented:
can you post the rpt with data

Author

Commented:
find attached rpt with data.
Report1.rpt
WillOwner

Commented:
The reason its shifting is that you are using the DT for the rows and the columns.  So  when it gets to the next one it would naturally be one over one down.  What do you want to show
WillOwner

Commented:
do you want service month verses process month?

Author

Commented:
I didn't understand. I am new to CR Cross tab. what does 'DT' mean ?

Author

Commented:
yes thats right. service month verses process month? sum(payment) in summarised fieldl
WillOwner

Commented:
use the process dt or service dt  as the column or row, depending on what you want to show
WillOwner

Commented:
look at these cx tabs


Report2-modified.rpt

Author

Commented:
You have exchange row header with the column header.
but I need output like one attached.
Sample-Output--2-.xlsx

Author

Commented:
The output file has a different format for row and column headers.
I need to change them as well:
1) left hand size row header: date: is in format 200807 (july 2008)
   i need it in format 20807 (yyymm) - is this possible ?

2) top column header is date 'mm' format.
but my report decodes if july(07) then '01'
                                     if august ('08') then '02'

You will understand it when you compare my report output with sample output.
WillOwner

Commented:
service month on columns  process date on rows.  

see this
Report3-modified.rpt
WillOwner
Commented:
take a look. If this is what you want,  and you are confused about the solution.  let me know and I will explain
Report4-modified.rpt
WillOwner

Commented:
i took a closer look at the row label  you want the year as 2009 =209 ?
WillOwner

Commented:
if thats the case modify the dateform formula.  you will need to parse out the year and then concatenate it . let me know and I will give it a whirl

Author

Commented:
yes
year as 2009 =209
and
but my report decodes if july(07) then '01'
                                     if august ('08') then '02'

may be after that changing the format it will be easier to understand
WillOwner

Commented:
so you are following a fiscal year

Author

Commented:
ok i'll google dateform formula and try converting it by then.

i am still not clear with the data mis match in the cross tab.

can you explain why
                      01
20807           has a zero in your report instead of 127069 as per the same report

Author

Commented:
if  you have considered 01 (as jan since you have used a formula month(servie month)

then why is
                                     01 (jan as per your report)
20901 (jan 2009)          107,814  - this is correct if month is july not jan

Author

Commented:
let me correct the month and year format ... i'll try and then may be it;s easier to understand
WillOwner

Commented:
I made the july the starting month  on the service month so 7=1 8=2 1=7
WillOwner

Commented:
fixed extra zero in process yearmonth
Report6-modified.rpt

Author

Commented:
you have done an excellent job with the formula for converting the date and month format. now that the comparison is easier.
let me clear the confusion with respec to the cross tab data.

output in your report:
      1.00      2.00      3.00      4.00
20807      127,068.80      0.00      0.00      0.00
20808      66,479.36      79,696.28      0.00      0.00
20809      7,446.37      70,110.27      121,017.65      0.00
20810      1,685.27      16,868.10      59,015.99      100,463.10
20811      -1,407.95      2,634.82      9,566.39      115,018.23
20812      -740.53      2,485.24      5,264.41      8,118.15
20901      341.25      232.95      2,168.28      -296.78
20902      355.89      634.01      216.73      5,632.71

but sample output is
                       01      02      03      04      
20807      127,069      66,479      7,446      1,685      
20808      79,696      70,110      16,868      2,635      
20809      121,018      59,016      9,566      5,264      
20810      100,463      115,018      8,118      -297      
20811      107,395      113,787      10,221      2,474      
20812      68,774      110,417      6,530      580      
20901      107,814      82,679      8,492      7,673      
20902      113,909      71,052      3,585      2,036      

correct me if i am wrong but striaght comparison does not  match the data.



Author

Commented:
If this discrepency b/c the sample output has months from 01 till 30.
so the @month_of_service formula should consider month(servicemonth) >=6.(for a particular year) and also next year as well. Month >12 is jan of next year. to get the month number 13
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
His converts all months to a 1-12

What do you want as the rows?
    Process or Service date?

What do you want as the columns?
    Process or Service date?

Do you want the 1 across the top to be based on the service date?
  so a service date of 15 Jul 08
     Month 1 - process date in Jul 08
     Month 2 - process date in Aug 08

  so a service date of 15 Aug 08
     Month 1 - process date in Aug 08
     Month 2 - process date in Sep 08

Is that what you are looking for?

mlmcc
WillOwner

Commented:
The table is correct based on the data set, unless you have a different rule to group by.  I  actually performed a summary operation on the data in the report and the values tie.

Author

Commented:
Column header = service date
Row header = process date
sum(payment) as a summary operation for cross tab data.
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Is this what you want?

mlmcc
Report6-modifiedRev1.rpt
WillOwner

Commented:
mimcc

I think you got , he appears to be looking for service month after process.  I missed that,.

Will

Author

Commented:
Briallant ! Thats exactly what I was looking for mlmcc.

Author

Commented:
Thanks for your help mlmcc and wkrasner for the formulas for the month and the year.
I would be happy to accept both the solutions.

Before I end this question, can anyone of you please have a look at this open question as well:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27238996.html

mlmcc did reply back but it didn;t help. my entire department is working on it but we haven;t found a solution yet. I need to find a way to remove those extra rows while exporting into excel (data only) from crystal report. Let me know if you want me to send you the rpt with the data.  It would save my weekend.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I really don't think there is a way to remove those rows.

mlmcc

Author

Commented:
I have uploaded the report and the data. wonder if you can give a last try.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial