How do I handle multiple rows in SSRS report?

Camillia
Camillia used Ask the Experts™
on
I'm building a report...it's actually an invoice letter. On top, there's a field, Customer PO.

There's a main table, table A, and a second table, Table B. This field is in Table B and this table can have several rows with CustomerPO that relates to the main table, table A

I'm displaying CustomerPO on top of the report.

Is there anyway to have 2 SQL statements in a dataset? one to bring the customerPO and one to bring the rest of the data? I don't think because I tried it and "query designer" threw an error. Looks like there can only be one select statement.

Do I need to create a second dataset?? (I just thought of that)

report
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>Is there anyway to have 2 SQL statements in a dataset?
No.  Use two datasets to return two different sets.
So, for the "customer PO", I can use Dataset1 and for the rest of the report, dataset 2?
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Can't really say based on the info/images you provide.  If CustomerPO is the same for all rows you could include that in the detail set, and in the expression do a First() to display the first CustomerPO value, knowing it's the same for all values.  Anything beyond that would better be served by two sets.
Thanks, Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial