Combining DataItems Fields in a dataset in RDLC Report

eborggrech

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

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

Thanks for your reply.

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

mohana_cse06

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

eborggrech

Sales_Invoice_Header__Posting_date
Sales_Credit_Memo_Header__Posting_date

to be represented in the dataset as

Posting_Date

mohana_cse06

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

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

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

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

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

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

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

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

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

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>