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

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

DO you have further column headers?

mlmcc

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

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

can you post the rpt with data

find attached rpt with data.
Report1.rpt

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

do you want service month verses process month?

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

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

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

look at these cx tabs


Report2-modified.rpt

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

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.

service month on columns  process date on rows.  

see this
Report3-modified.rpt

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

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

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

so you are following a fiscal year

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

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

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

Report5-modified.rpt

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

fixed extra zero in process yearmonth
Report6-modified.rpt

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.

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

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

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.

Column header = service date
Row header = process date
sum(payment) as a summary operation for cross tab data.

mimcc

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

Will

Briallant ! Thats exactly what I was looking for mlmcc.

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:
https://www.experts-exchange.com/questions/27238996/Crystal-report-Blank-Rows-in-Excel-Export.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.

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

mlmcc

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