crosstab formatting

[Former Member]

Hi,

I have a report in this format,

cp1-reg1-tr1

SNO Name tot 2010-05-01 2010-05-02 2010-05-03

001 abc 8 1 3 4

002 def 5 1 1 3

total 13 2 4 7

cp2-reg2-tr2

SNO Name tot 2010-05-01 2010-05-03

003 hgt 5 1 4

004 rty 4 1 3

total 9 2 7

I have 3 groups cp,reg,tr, i placed the crosstab in "tr" group. in row field i wrote a formula for sno as

whileprintingrecords;

numbervar i;

i:=i+1;

if i<10 then

' 'totext(i,'00#')'.'' ' currentfieldvalue

else if i>=10 then

' 'totext(i,'0##')'.'' ' currentfieldvalue

else

' 'totext(i,'#')'.'' ' currentfieldvalue

the column dates will be given by user as startdate and enddate. In first group, data is there for 01,02,03 dates and in next group data is there only for 01, 03.

Now my problem is i want the crosstab as

SNO Name 2010-05-01 2010-05-02 2010-05-03 total

cp1 reg1 tr1

001 abc 1 3 4 8

002 dff 1 1 3 5

tot 2 4 7 13

cp2 reg2 tr2

001 fgh 1 0 4 5

002 rty 1 0 3 4

tot 2 0 7 9

a) the sno,name,etc heading i want to repeat once only in the page header

b) sno should be starting from 1 in every group

c) If data is not present dat should be zero.

d)Total should come in the ending

CAn anyone please help me?

Thanks,

sweish

[Former Member · Accepted solution]

I have the answer for your dates issue...

Data will only show up if it's actually in the database. In order to get every date, even if there is no data for that date, you need to create a table in your database that has all of the dates in it. You then make that the base table of the report and left join from it to the table(s) with your data. To display the dates, you then use the date field from the dates table instead of from the rest of the data.

-Dell

[Former Member · Accepted solution]

I have used left join and did it, but in the dates which has no data- it is not getting displayed in the crosstab.

[Former Member · Accepted solution]

In your cross tab, is the date comming from the left outer joined date?

[Former Member]

Dates are coming from the tables which is containing all the dates, in the prieview first crosstab is containing all the dates with no data and remaining crosstabs are coming with the dates whicha re having data.