I am a newbie and I have designed a report in CR2008 and my datasource for the report is an XSD file. There are 2 tables Table A and B.

Table A is as follows:

AID     Name     Reference     Period          Manager
5       Name 1     Ref 1          July 2009     User Name 1
8     Name 2     Ref 2          August 2009     User Name 2
9     Name 3     Ref 3          March 2009     User Name 9
45     Name 4     Ref 4          May 2008     User Name 4
88     Name 5     Ref 5          June 2009     User Name 1


There are other coloumns too in the datatable, but that shouldnt matter.

Table B is as follows:

ID     A_ID     DateKey        DateValue                         Name
1      5          Date1        2009-09-29 00:00:00.000        Start Date
2      5          Date2        2009-08-11 00:00:00.000        Fieldwork Complete
3      5          Date3        2009-08-14 00:00:00.000        Report Issued
4      5          Date4        2009-07-17 00:00:00.000        Report Finalised
5      5          Date5        2009-07-10 00:00:00.000        Close Date
6      8          Date1        2009-08-11 00:00:00.000        Start Date
7      8          Date4        2009-09-29 00:00:00.000        Report Finalised


My report is a joining of the 2 tables as follows:

ID   Name   Reference    Period     Manager      Start Date   Fieldwork    ReportIssued     ClosedDate

I have written a formula, which matches the AID and the DateKey and then displays the date value. So for each date I want, I have written a different formula (I am not sure if this is the effective way to do this.) When I run the report each of the date is appearing in a different line (they appear correctly under the respective heading.) So I have the ID, Name, Ref, Period, Manager and Start Date on on line and then the other dates, each one on separate lines.

How do I get the dates to appear in the same line.

I really would like your input on this.

Thanks

Hi

In my design, I have placed each of the field next to each other in the details section. The corresponding date formulae is also placed in the same line.

I have suppressed blank rows.

But when I try to run the report, each of date appear in a separate line. So instead of getting all the information relating to A_ID in one row, I am getting 5 rows. I do not understand why each of the date would come in a separate line instead of next to each other.

This is the formula I wrote for one date.

If {Table2.AuditID} = {Table1.ID} AND {Table2.DateKey} = "Date1" Then formula = Cdate({Table2.DateValue})


Hope this has made sense.

Thank you for your reply...

Tanya
            

The report comes out like this (This is only for one row of TABLE 1):

ID NAME    REF    PERIOD           MANAGER    DATE 1     DATE2     DATE 3    DATE 4
5   Name1   Ref1   February 2008 UserName1 14/02/2008
                                                                                15/02/2008
                                                                                                19/02/2008     
                                                                                                                20/02/2008



I have one row for Table1 and then each of the date appearing in a separate line.

I am really struggling to find where there could be a problem. I tried changing the type of join (in the Links section of DataBase Expert), but it still doesn't make a difference.

Is there a difference in how crystal behaves when using XSD files as the data source?


Thanks...

I have tried, to move the fields apart, but the dates still appear in different line.

The formula that I wrote, is as follows:

If {TableB.A_ID} = {TableA.ID} AND {TableB.DateKey} = "Date1" Then formula = Cdate({TableB.DateValue})

Even if my XML file has only one row in Table A, the dates still seem to appear in separate lines.

Is there something wrong with the formula I have written? because, if I take out all the date formulae, the data appears fine.