I'm currently using an RDLC report and ReportViewer control to display a report to the user. The report needs to be formatted in a certain way so just creating a table and iterating through won't work. My database is set up to be pretty normalized. For example, two tables I have are a "PersonalData" table with a ID and a "PhoneNumbers" table with a ContractorID that relate to the first table. The "PhoneNumbers" table has essentially two columns of data: Number and Type. Each contractor in the PersonalData table can have multiple phone numbers (home, cell, work, etc.). I'm also passing the ContractorID into the report so I know which records to grab.
On the report, I need to display each phone number in a separate text box layed out like so:
Home Phone: (555) 555-5555 Cell Phone: (444) 444-4444
Daytime/Local Phone: (333) 333-3333 E-Mail Address: [email protected]
The records are inserted in the database in the following order: Home, Cell, Daytime/Local. I can grab the home phone since I know it will always be the first record in the database (required field) by using:
=First(Fields!Number.Value, "DataSet1_PhoneInformation")
And either the Daytime/Local or Cell depending on the element count by using the Last() function. The problem is grabbing and displaying that Cell Phone value. In my tests I have a person with 3 phone numbers (Home, Cell, Daytime/Local) and I checked by using the Count() function is returning 3 records for the person.
Is there some combination of aggregate functions I could use? Or maybe someway to get around using the scope identifier? I'm pretty sure I have to have it since I have multiple DataSet tables. Any ideas?