Query from inside an rdlc Report

[johnisotank]

Hi,

was hoping someone could tell me how to do this please..

I have an rdlc report with a table on it. Now, in this table there is a textbox called 'CustomerCode'. When the report runs this might be populated with 'A001' for example.

How can I get a different text box to query my Customers table and pull in the address details please?

Thanks
John

 

[ousoonerjoe]

if i may suggest, rather than trying to poll the field and pull more information, try writing a SELECT statement that JOINs the Customer and Address tables and just pull all the info down at one time.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[johnisotank]

Hi thanks for the reply,

Not too sure what you mean. If it helps this is my situation:

This report is for an invoice and I have the following tables:

tblInvoice:
Fields = InvoiceNo, CustomerCode

tblJobs:
Fields = JobNumber, InvoiceNo

tblCustomers:
Fields = CustomerCode, Address1, Address2...

So basically, I need my report to pull in all the information for one invoice. Lets say the invoice number is 12345 - There is only one tblInvoice record, but there may be a hundred tblJobs records with this invoice number.

So, if there was 100 tblJob records I didn't think it would be best to pull in the customer address (With a join) 100 times..

Does that make sense? Hope so!

Thanks
John

 

[johnisotank]

Hi again, maybe I overcomplicated my request.

Lets say I have a tblJobs which has the following fields and values:

JobNo, CustomerID, InvoiceNo
1, A000, INV1
2, A000, INV1
3, A000, INV1
4, B000, INV2


And I do a SELECT statement:

SELECT JobNo, CustomerID
FROM tblJobs
WHERE InvoiceNo = 'INV1'

That would populate my report with 3 records, I would then like the address details for customer 'A000' to appear only once in the header of the report.

Hope that makes more sense.

Thanks
John

 

[Sorwen]

Try:

SELECT JobNo, CustomerID
FROM tblJobs
WHERE InvoiceNo = 'INV1'
GROUP BY JobNo, CustomerID;

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[johnisotank]

Hi Sorwen, sorry I still don't think Im making myself clear..

tblJobs:
JobNo, CustomerID, Value, InvoiceNo
1, A000, £10.00, INV1
2, A000, £15.00, INV1

tblInvoice:
InvoiceNo, CustomerID, InvoiceDate
INV1, A000, 28-Jun-2008

tblCustomer:
CustomerID, CustomerName, Address1
A000, ABC Limited, 1 London Street

And then my report needs to look like a proper invoice with the customer address at the top, Invoice Number below and then then each job for the invoice number listed underneath that.

ABC Limited
1 London Street

Invoice Number: INV1

JobNo, Value
1, £10.00
2, £15.00

Do I need to do something with subreports?

thanks

John

 

[ousoonerjoe]

yes... sub reports is the key you're looking for.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[johnisotank]

I have my main report and the subreport, but I am massively struggling to see how I can get the customer address to appear for the specific customer.

are there any decent tutorials out there that deal with this please?

Thanks

John