grouping the Posting date in the G/L entry table

Omar

Hi guys,
can any one help me,
i have a report consist of two data items,Customer and G/L entry with DataItemLink: Source No.=FIELD(No.)
i am trying to group the posting date in the G/L entry, i have the GroupTotalFields:Posting Date and the DataItemTableView:SORTING(Source No.,G/L Account No.,Document Type,Posting Date) ORDER(Ascending), i always get the data grouped by the Source No instead of the posting date ](*,)
i want my data to be grouped by the Posting date,
please help

lvanvugt

How can you expect to get it grouped by Posting Date if you use this key for the sorting: Source No.,G/L Account No.,Document Type,Posting Date?

Omar

Hi Luc,
thanks for your reply,
i cant see anything wrong in this key, though it contains the Posting date as part of it, please advise about the sorting to use in order to get the data grouped as desired,

thanks in advance,

lvanvugt

Omar wrote:

I always get the data grouped by the Source No instead of the posting date

This is due to the key you have chosen, which starts with Source No. Due to this key (=index) everything is ordered (in first place) with respect to Source No ...

vivek4121

Hi Omar,

According to me as well, you are correct, there is nothing wrong with the key as it contains the Posting date as part of it.

Please check with the sections you have created for that report.

GroupHeader section needs to be added for PostingDate (add the caption of PostingDate on Header section of G/L Entry and value on the GroupHeader section of G/L entry)

Hope this works for you.

Thanks
Vivek

kine

Omar wrote:

Hi Luc,
thanks for your reply,
i cant see anything wrong in this key, though it contains the Posting date as part of it, please advise about the sorting to use in order to get the data grouped as desired,

thanks in advance,

Luc is correct. If you want to group by something, you must use sorting key BEGINNING with same fields as you want to group by (and in same order)! That it ends with the field is not enough. Back to school guys... ;-)

Omar

Hi Vivek,
thanks for your paricipation,
i did like you advised but still the same,
i must inform you that i also have a grouping over the Customer group Dimension field in the Customer data item, am putting my data fileds in the G/L entry GroupFooter, so my sections are like this: Customer Groupheader containing the Customer Group Dimension which is the sector in my report and beneath it there is the G/L entry GroupFooter section which contains the Posting Date and Amount form the G/L entry

thanks

vivek4121

Hi Omar,

If your grouping is over "Customer group Dimension" field then, I would say that in the "DataItemTableView" Property select a key which contains the field "Customer group Dimension", if there is no key which contains that field than in that case, you have to create the one in Customer table.

And I think your DataItem structure is like : first Customer DataItem which is indented by G/L Entry right?

Thanks
Vivek

kine

Omar wrote:

Hi Vivek,
thanks for your paricipation,
i did like you advised but still the same,
i must inform you that i also have a grouping over the Customer group Dimension field in the Customer data item, am putting my data fileds in the G/L entry GroupFooter, so my sections are like this: Customer Groupheader containing the Customer Group Dimension which is the sector in my report and beneath it there is the G/L entry GroupFooter section which contains the Posting Date and Amount form the G/L entry

thanks

Hi Omar,

have you read the things me and Luc wrote? It will be still same, if your Order By key is not Beginning with the fields you are grouping on...

Kishorm

Luc and Kine are correct.

However, in addition to this you also need to restructure your report. Because you have Customer as the main dataitem and G/L Entry as an indented (linked) dataitem, your report will always be sorted by customer before any sort key specified on the G/L Entry table. Basically what you need to do is...

1) Make G/L entry the main dataitem
2) If you need to get info from the Customer table (eg customer name) then create a customer var and get the correct customer record in the OnAfterGetRecord trigger - use this customer var on the report
3) The key on G/L Entry needs to start with Posting Date

FDickschat

So your report looks lile this:

Data Items
Customer - , Sort by No., Group Total "Customer Group"
G/L Entry - Filter Source Type = Customer, Source No. -> Customer.No., Group Total "Posting Date"

In this case you need a key Source Type, Source No., Posting Date on G/L Entry.

If you don't have a key on G/L Entry which starts with Source Type, Source No., Posting Date and you don't want to create such a key as it decreases posting performance you could use a Temporary table instead:
per Customer read the G/L entries but don't print them. Instead write them to a temp buffer table thereby grouping the data manually. Add another Data Item after G/L Entry to print it.