Get Month Wise data Using Date Table

Business Central/NAV Developers Forum
1

hii,

i have to design the report monthwise from value entry table.

how can i use date table for this?

let me know if any way of using date table in this report.

2

You can set Group expression in report based on Posting Date/Document date. The expression will be something like Month(Fields!PostingDate.Value).

3

But my report id Processing Only…there is no layout…So is it any way of using Date Virtual table for that?

4

Hi,
In that case i think you want to use the Date Table for filtering value entries? If that is the case then you can do something like this -

StartDate = 01012016D (Assume Value )
EndDate := CalcDate(‘1M-1D’,StartDate); (Give you month End Date)

FOR I = 1 To 12 DO BEGIN (IF you plan to run for 12 months)
//Code Whatever you require with
ValueEntry.Setrange(“Posting Date”,StartDate,EndDate);
StartDate := CalcDate(‘1M’,StartDate);
EndDate := CalcDate(‘1M-1D’,StartDate);
END;

Hope it helps.

5

Hi [mention:d37e849211a549bdb70a00b394567ad1:e9ed411860ed4f2ba0265705b8793d05],

Yes you can use the Date table to do this.

Then you need to create a DataItem in your report using the Date table. Here you can filter on the Period Type (Date, Week, Month, Quarter or Year). Remember always to set a filter on the Period Start and End, or the report will run for a very long time…

You can then put the code to filter on the value entry on the OnAfterGetRecord trigger of the data item.

ValueEntry.SETRANGE(“Posting Date”,“Period Start”,“Period End”);
ValueEntry.CALCSUM(Quantity);

Saurav’s suggestion is basically doing the same thing. Just without the date table. His solution is great if you need to have a report with 12 columns (one for each period) and typically a line per item (if quantity). Then you would store the values in an array variable within the FOR-statement.

6

Ok.Thank You for Your Reply…Now i got It…