Small and medium business | Business Central, N...

Results from DataSet in AL Report

(0) Share

Report

Posted on by Knofan

Hi Guys,

Im developing a report. Nothing fancy but i have some trouble getting the data right.

The dataset looks like this:

        dataitem(Sales_Line; "Sales Line")
        {
            column(ItemNo; "No.") { }
            column(Variant_Code; "Variant Code") { }
            column(Quantity; Quantity)
            {
                // Method = Sum;
            }
            column(Sell_to_Customer_No_; "Sell-to Customer No.") { }
            dataitem(PItem; Item)
            {
                DataItemLink = "No." = field("No.");
                column(ItemDescription; Description) { }
            }
            dataitem(PurchaseLine; "Purchase Line")
            {
                DataItemLink = "No." = field("No."), "Variant Code" = field("Variant Code");
                //SqlJoinType = FullOuterJoin;

                column(PurchQuantity; Quantity)
                {
                    //Method = Sum;
                }

            }

        }
    }

The problem is that i get rows without some data in some fields. Like this:

ItemNo	Variant_Code	Quantity	Sell_to_Customer_No_	ItemDescription	PurchQuantity
001CAN	Medium	1	7987	Item A - red	NULL
001SAN	Medium	1	7987	NULL	NULL

This should only have been one row and it seems that the PurchQty from the Purchase Line table forces it to be another row added. Is this fixable?

If i use a query i get the right data but then i cant use report parameters, and i need to use it.

Thanks in advance!

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Nitin Verma 21,708 Moderator on at

Like (0)

Report

Please use grouping on Item No. in RDLC design.

Thanks

Was this reply helpful? Yes No
Knofan 49 on at

Like (0)

Report

Hello Nitin

Thank you for your answer. That wont work since there are twice as much data as it should. Qty should only be one, when its grouped and summed it double from what it should.

By only grouping on Item No the size "medium" apears twice for each Item.

Was this reply helpful? Yes No
Suggested answer

Nitin Verma 21,708 Moderator on at

Like (0)

Report

It would not total the Quantity unless you put the SUM method, it will group the Item No. so that It display only one time of each time, its up to you if you dont want to sum of the other columns.

Thanks.

Was this reply helpful? Yes No
Knofan 49 on at

Like (0)

Report

Yes, i need the sum on two columns. Is there anyway to change the dataset so that it outputs the data in a way that it can be used?

It works great with a Query but within a report it does not work.

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (0)

Report

I would add the type field to the data item link so you only handled lines of type item.

Is your real problem that you get a NULL where there are no purchase lines matching your data item link?

Was this reply helpful? Yes No
Suggested answer

Amit 2,559 on at

Like (0)

Report

Hi,

You can write code on onAftergetRecord of Sales Line Dataite.

Purchase.reset;

Purchase.Setfilter("Document Type",'%1',PurchaseLine.Document Type" :: Order);

Purchase.Setrange("No.","No.")

Purchase.Setrange("Variant Code",Variant Code");

if PurchaseLine.findfirst then

PurchaseQty := PurchaseLine.Quantity.

In this way way you achive the functionality.

Regards

Amit Sharma

www.erpconsultors.com

Was this reply helpful? Yes No
Knofan 49 on at

Like (0)

Report

I had some trouble to get it to work with your example. Perhaps i have made a misstake somewhere.

Here is what i have:

trigger OnAfterGetRecord()

begin

PurchaseLine.Reset;

PurchaseLine.SetFilter("Document Type", '%1', PurchaseLine."Document Type"::Order);

PurchaseLine.SetRange("No.", "No.");

PurchaseLine.SetRange("Variant Code", "Variant Code");

If PurchaseLine.FindFirst() then begin

PurchaseLine.Quantity := PurchaseLine.Quantity;

end;

end;

Was this reply helpful? Yes No
Suggested answer

Nitin Verma 21,708 Moderator on at

Like (0)

Report

Hi,

trigger OnAfterGetRecord()

begin

PurchaseLine.Reset;

PurchaseLine.SetFilter("Document Type", '%1', PurchaseLine."Document Type"::Order);

PurchaseLine.SetRange("No.", "No.");

PurchaseLine.SetRange("Variant Code", "Variant Code");

If PurchaseLine.FindFirst() then begin

QtyVariable := PurchaseLine.Quantity;

end;

end;

Was this reply helpful? Yes No
Knofan 49 on at

Like (0)

Report

I don not se any difference in my report from this.

Is there any way to manipulate the records in the OnPostDataItem trigger for example. Group them there and use that data instead? The problem is that i have at least two rows on each item, it should idealy be just one row. With all data.

This is easy to do with querys but then it seems i can not use filters when i run the report. For example date filters.

Was this reply helpful? Yes No
Suggested answer

Amit 2,559 on at

Like (0)

Report

Send the AL and rdlc file.

Regards

Amit Sharma

www.erpconsultors.com

Was this reply helpful? Yes No