Combining DataItems Fields in a dataset in RDLC Report

eborggrech · 2011-06-10

Hi,
Is it possible to combine values from different tables into one DataSetFieldName for an RDLC Report?

My aim is to combine Sales Invoice Header and Sales Cr.Memo Header to produce a dataset with the following data from both tables:

Posting Date
Payment Method Code
Amount Including Vat

The report will then group and filter the data provided in the Dataset (from both tables) as required.

I followed these steps:
- created 2 DataItems (with no indentation)- One for Sales Invoice Header, One for Sales Cr.Memo header
- created textboxes for the respective fields
- set the DataSetFieldName property of the similar fields ("Posting Date") the sections the same BUT I get an error when compiling: The Control property DataSetFieldName value <datasetfieldname> is already used.

Is there some way to populate a dataset in a similar manner?

Thanks for your time!

mohana_cse06 · 2011-06-10

You cant create same dataset name 2 times..
best is to just add fields in Sections and click suggest layout..it will create dataset names to fields automatically with table name prefix.

ex:

Sales_Invoice_Header__Posting_date
Sales_Credit_Memo_Header__Posting_date

eborggrech · 2011-06-10

Thanks for your reply.

That would give me the result in seperate columns whereas I require them to be in one.

mohana_cse06 · 2011-06-10

Can you explain more what do you need in one column?

eborggrech · 2011-06-10

Sales_Invoice_Header__Posting_date
Sales_Credit_Memo_Header__Posting_date

to be represented in the dataset as

Posting_Date

mohana_cse06 · 2011-06-10

If you have Sales_Invoice_Header__Posting_date = 01/01/11 and
Sales_Credit_Memo_Header__Posting_date = 12/12/11
then how do you need it?

eborggrech · 2011-06-10

There is no link between the Sales Invoice header and Sales Cr. Memo header. Both data items will be filtered by posting date and executed one after another.

When Sales_Invoice_Header__Posting_date has a value, Sales_Credit_Memo_Header__Posting_date should have no value, and vice versa

mohana_cse06 · 2011-06-10

eborggrech wrote:

There is no link between the Sales Invoice header and Sales Cr. Memo header. Both data items will be filtered by posting date and executed one after another.

When Sales_Invoice_Header__Posting_date has a value, Sales_Credit_Memo_Header__Posting_date should have no value, and vice versa

you mean both posting dates will have same date at a time?

Then you can use either Sales_Invoice_Header__Posting_date or Sales_Credit_Memo_Header__Posting_date

Troubles_In_Paradise · 2011-06-10

eborggrech wrote:

There is no link between the Sales Invoice header and Sales Cr. Memo header. Both data items will be filtered by posting date and executed one after another.

When Sales_Invoice_Header__Posting_date has a value, Sales_Credit_Memo_Header__Posting_date should have no value, and vice versa

so... do you want that in a field in Report Layout will be showed Sales Credit Memo Header Posting Date or Sale Invoice Header Posting Date?

eborggrech · 2011-06-10

exactly, whichever one is filled in the follwing:

Sales_Invoice_Header__Posting_date
Sales_Credit_Memo_Header__Posting_date

will be stored in the dataset field:
Posting_Date

mohana_cse06 · 2011-06-10

Then you can calculate posting date from Sales_Invoice_Header__Posting_date or Sales_Credit_Memo_Header__Posting_date in one variable and pass it to report dataset.

Troubles_In_Paradise · 2011-06-10

mohana_cse06 wrote:

Then you can calculate posting date from Sales_Invoice_Header__Posting_date or Sales_Credit_Memo_Header__Posting_date in one variable and pass it to report dataset.

Good idea :thumbsup:

kamranshehzad · 2011-06-10

If I understood what you need then this may be a solution for you.

why dont you write a function in C/AL that returns you one posting date and assign it to variable.
this is what i did
Defined a textbox on report and then entered GetEEDocNo in sourceExpr field
and wrote a c/al function that used to give me th required document.
I did it as i couldnt get Flow fields on rdlc reports...

what you can do is define a field and call a function that will return you once posting date ( either from sales invoice or credit note based on your logic )..

GetEEDocNo() : Code[20]

CASE "VAT Entry".Type OF
  "VAT Entry".Type::Sale:
    BEGIN
      IF SalesInvHeader.GET("VAT Entry"."Document No.") THEN
        EXIT(SalesInvHeader."Pre-Assigned No.");
    END;
  "VAT Entry".Type::Purchase:
    BEGIN
      IF PurchInvHeader.GET("VAT Entry"."Document No.") THEN
          EXIT(PurchInvHeader."EE BI No");
    END;
END;

eborggrech · 2011-06-10

Thanks to everyone!

I resolved the issue by creating 2 seperate fields in the dataset (no choice), then in the RDLC Layout I set the source of the text field to:

=IIF((Fields!Sales_Cr_Memo_Header_Posting_Date.Value= Nothing),Fields!Sales_Invoice_Header_Posting_Date.Value,Fields!Sales_Cr_Memo_Header_Posting_Date.Value)

Troubles_In_Paradise · 2011-06-10

eborggrech wrote:

Thanks to everyone!

I resolved the issue by creating 2 seperate fields in the dataset (no choice), then in the RDLC Layout I set the source of the text field to:

=IIF((Fields!Sales_Cr_Memo_Header_Posting_Date.Value= Nothing),Fields!Sales_Invoice_Header_Posting_Date.Value,Fields!Sales_Cr_Memo_Header_Posting_Date.Value)

=D>

Howdy, Stranger!

Quick Links

Categories

Combining DataItems Fields in a dataset in RDLC Report

Comments