none
In-Memory Dataset and Count() using relation...? RRS feed

  • Question

  • Hi,

    I have an in-memory dataset I load at runtime from an xml file. The dataset has several tables, but for this report I am using Customers and Invoices tables.
    The Customers and Invoices have a relationship, using column "cid" - for customer id.

    I am trying to create a report that will list all customers including total number of invoices per customer. My report works partially - it lists the customer name and company, but I can't get the number of invoices per customer to work. I have tried different variations of the Count() function, but with no success. I am completely lost as to how to get it to work.

    Am I missing something? Can someone please, please help me? I am deserate to get this working.

    The report uses two bindingSources:

    CustomersBindSource. DataSource => myDataSet
    CustomersBindSource.DataMember => Customers (the table)

    and

    CustomersInvoicesBindingSource.DataSource => CustomersBindSource
    CustomersInvoicesBindingSource.DataSource => Customers_Invoices (the relationship)

    It looks like everything is correct, and should work, but the Count() function returns "12" for every customer.

    The report should look like:

    NAME   COMPANY   NR. INVOICES
    blah      blah ltd        12
    blah      blah ltd        8
    blah      blah ltd        5
    ..

    Many thanks,
    Michael.
    Wednesday, June 24, 2009 6:35 PM

All replies

  • It sounds like you just have a plain list, but no list grouping.  If you want to show detail records besides the grouped data per customer, I recommend to actually use a table:

    1. create a table
    2. add a table group, group the data by Name or Company, and also add a group header
    3. use the Count function in the group header to get the invoice count
    4. show the detail data in the table detail section of the table


    HTH,
    Robert
    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, June 27, 2009 11:04 PM
    Moderator
  • Robert,

    Thanks for your reply. I actually have a table, with columns, etc. It is true I don't have any grouping because none of the grouping I tried actually worked. More than likely I didn't group correctly.

    I am sorry to be a pain, but I would really appreciate if you could expand the above step-by-step (as a walkthrough), just to make sure I am doing it right. I tried your suggestions, but it's still not working, and I am assuming I am still doing something wrong.

    There are several things I haven't figured out yet, like:

    1. Do I need a table group and/or Details Grouping?
    2. What is the difference between the two?
    3. If I add a count() expression in a group, I get this "Error 1 A group expression for the table 'table1' includes an aggregate function.  Aggregate functions cannot be used in group expressions." - why?
    4. You say: "use the Count function in the group header to get the invoice count" - how do I do that?
    5. You say: "show the detail data in the table detail section of the table" - how do I do that?
    6. How do I access a field value from the second table?

    Is it possible to send you some screenshots of the report for you to correct perhaps? This is the first time I've attempted an rdlc report, so please excuse by ignorance in this area. Than you for your understanding.

    Many thanks,
    Michael.
    PS: I can't group by name or company, it must be by "cid".
    Sunday, June 28, 2009 6:57 AM