Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Unanswered: Displaying same datafield twice on report

    Hi All,

    I need to display a same datafield twice on my report with different conditions. When i drag and drop the same field twice on the report. I want a way to define different conditions for both these field.

    I am using MS Access and Crystal reports 8.5 version. the front end of my project is Visual Basic 6

    eg: I have three tables: BookMaster (Table 1) with bookId as primary key and bookName as additional field, AuthorMaster (Table 2) with authorId as Primary Key and authorName as additional field; bookAuthor (Table 3) which consists of bookId and authorId as foreign keys and authorType as text field (the field authorType is given because i need to show who is the primary author and who is secondary author). The bookAuthor table has two authorId for the same bookId. One primary author and other secondary author. (Real life scenario where same book have multiple author).

    I need to display the names of primary and secondary authors for all the books in BookAuthor table. Like: book id, bookName, primaryauthorid, primaryauthorname, secondaryauthorid, secondaryauthorName row by row. Each row displays one books

    I did the following steps: I created the relationships in visual table linking and then I created a group with bookId from bookAuthor and then draged the datafield authorId, authorName twice for primary and secondary author. But the report displays the same author name for the book.

    I am in deep trouble due to this.

    Can some one help me how to achieve this.

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    You could use the grouping expert to make a group based on authorType. Then leave the AuthorName in the details.

  3. #3
    Join Date
    Jun 2007
    Posts
    5
    It does not work As the output will not give me the list of all books and all its authors in one line

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    you are correct, I gave you bad info on that. Group by book with author in the details. That should give you:

    Book
    Author
    Author

    You could then sort by type.
    btw, why is your type a string rather than a number?

  5. #5
    Join Date
    Jun 2007
    Posts
    5
    String is not a concern for me. I can easily change it to integer.My concern is to show book and all its author in one line. Now that may be on the group header line or single line in Detail section.

    When I add one authorName field in detail section, I do get the list of all authors one beneth other but that is not what is required.

    I want to create a tabular structure which display a list of all bookNames and their primary, secondary authors.

    I also tried creating two formula field and place them in header section.

    @primary_auth
    if authortype = "primary" then authorname else ""

    @secondary_auth
    if authortype = "secondary" then authorname else ""

    But the report displays only primary authorName and keeps the secondary blank. what i concluded was, may be in groupHeader section, the value for authorType is selected once (i.e. primary) and then both the formulae are evaluated using same value for authorType i.e. "primary". Hence the other formula returns blank.

    Some one suggested to use formulas that reference the next record (i.e. the Next() function) but it just gives the immediate next and not all authors (say a tertiary one)

    Sangram

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    If you want them all on one line, either do a calculated field with both authors concatonated in it or use the underlay following section feature of the section expert and pull you authors in seperate groups.

  7. #7
    Join Date
    Jul 2007
    Posts
    54
    How about linking your tables like this:

    BookMaster (Table 1) linked to bookAuthor (Table 3) by bookid and then 2 copies of the same table AuthorMaster (Table 2) linked to the bookauthor (Table 3) table by authorid. The first copy is to show the primary author and the selection criteria and/or formulae specify this. Assuming some books may not have 2 authors, the 2nd copy of AuthorMaster (Table 2) will use a left join and the selection criteria and/or formulae will select it only for the secondary author (NB you may need to use an IsNull() statement).

    This is clear in my head, but tough to write out, so I hope you can see where I am coming from here.

    Good luck!

    Caspar

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    2 copies of one table?
    You can do all that with one properly built author table.

  9. #9
    Join Date
    Jul 2007
    Posts
    54
    I agree, however I had assumed that the tables were a given and that tinkering with the database design is not an option.

    I use the 2 copies of one table trick when printing out a Bill of Materials (for instance) where the tables are BillOfMaterials and PartsMaster, with 2 copies of the PartsMaster, one to describe the parent part and one to describe the child part(s) (and this is again a scenario where I have no control over the database design).

    HTH
    Caspar

  10. #10
    Join Date
    Feb 2007
    Posts
    348
    hmm, I tried to do this and found it to not work at all as I expected. I'll have to think about it some more but I would say go with whatever works for now.

  11. #11
    Join Date
    Jul 2007
    Posts
    54
    I agree with starkmann that bt_sangram should choose whatever works.

    Thinking about it some more, I think my suggestion to use 2 copies of the same table was wrong (sorry) and that bt_sangram's suggestion in Post #5 of 2 formulae was right, but just needs an extra bit:

    1/ Group on bookid
    2/ Insert the 2 formulae in the detail section in separate columns
    3/ In the group footer insert a Maximum summary on each formula
    4/ Suppress the header and the detail

    Now the footer can have the bookid, book title, primary author and secondary author all in separate columns.

    HTH
    Caspar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •