Select query for creating Pivot table

(1) Share

Report

Posted on by Teevo

395

Hello guys,

When I have table, lets say we named it TableA that contain these kind of records :

Department	Transdate	Status	Error	Amount
HQ	1/1/2022	Posted	No	10
HQ	1/1/2022	Posted	Error	10
HQ	1/1/2022	NotPosted	Error	10
HQ	1/2/2022	NotPosted	No	10
HQ	1/2/2022	Posted	No	10
BS	1/1/2022	Posted	No	10
BS	1/2/2022	NotPosted	Error	10

And I want to create a pivot table, and because it will be for a report, so I gonna need to insert the pivot table in another table, may I know how to do it, with X++ ?

I have to admit, I'm clueless what I'm gonna do about that record and what's needed to accomplish this in X++ select query.

So, the pivot will be like this:

Department	Transdate	Status: Posted	Status: Not Posted	Error
		Count	Amount	Count	Amount	Count
HQ	1/1/2022	2	20	1	10	2	20
HQ	1/2/2022	1	10	1	10	0	0
BS	1/1/2022	1	10	0	0	1	10

Basically, it will to capture and summarize, per Department and dates about :

1. How many are Posted and the amount

2. How many are Not Posted and amount

3. How many are Errors (provided it is posted or not), and the amount

Can someone help me ?

Thanks in advance.

I have the same question (0)

All responses (5)

Answers (0)

Layan Jwei 8,118 Super User 2025 Season 2 on at

Like (0)

Report

Hi Teevo,

Can you re-write the tables. I got lost alittle in the first table as there is a field value 'No' but there is no column for it

Also the 2nd table, the labels are close to each other. So if you can please maybe take screenshots from excel (it might be better)

Also what is countAmountCount, how was this filled?

And I didn't understand the question, what do u want to do in x++?

Was this reply helpful? Yes No
AR-01121610-0 4,967 Moderator on at

Like (0)

Report

Hy,

as the F&O is mostly transcational oriented I would not do this within the ERP instead use any external tool like Excel or PowerBI for this kind of analyze.

Was this reply helpful? Yes No
MuthukumaranAX 2,903 on at

Like (0)

Report

do you want to summarize the data and insert into table or show in report?

how do you want to execute the summarization on this table by user action or through periodic job?

Was this reply helpful? Yes No
Kevin Xia Microsoft Employee on at

Like (0)

Report

Hi,

Can you provide more information (such as business logic or what you want to achieve) to help us solve the problem better? At this time, the information you have provided is not sufficient to support us in proposing a solution.

Best regards,

Kevin

Was this reply helpful? Yes No
Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (0)

Report

Hi Teevo,

Do you want to show those fields on SSRS report? OR Power BI report? Can you give us more details?

Thanks

Waed Ayyad

Was this reply helpful? Yes No