Axapta View: how to make group by in vertical mode (Pivote table)

(0) Share

Report

Posted on by Community Member

Is possible to replicate this query (from sql):

SELECT Customer,ISNULL([2012],0)as [2012],ISNULL([2013],0)as [2013]
FROM (
    SELECT 
       Customer,[Year] as [year], Amount
    FROM Mytest
) as s
PIVOT(SUM(AMOUNT)
      FOR Year IN ([2012], [2013])) AS PVTTable


to Axapta view?

I can create this in the Axapta:

SELECT SUM(T1.RENTALMONTHS) AS RENTALMONTHS,
T1.CHARGETYPE AS CHARGETYPE,T1.CUSTTRANSCYLINDER AS CUSTTRANSCYLINDER,
T1.TRANSTYPE AS TRANSTYPE,T1.DATAAREAID AS DATAAREAID,1010 AS RECID 
FROM CYLCUSTTRANSPAYMENT T1 
GROUP BY T1.CHARGETYPE,T1.CUSTTRANSCYLINDER,T1.TRANSTYPE,T1.DATAAREAID

But i need that this group by in Vertical Mode, in diferentes columns and not in the default mode that group by works, in
horizontal. How can i do it in AX?

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Denis Macchinetti 16,444 on at

Like (1)

Report

Hi Maximiliano

In this case, I suggest you to create a "phantom" view in AX that refer to the SQL view.

I mean:

1-Create an AX Temporary table with the fields (name and type) returned by your SQL Query

2-Create an AX View linked to the Temporary table named TESTVIEW

3-Create the SQL view with the syntax above named TESTVIEW (as the AX view)

That's it!

In this case you can create also complex Query (Pivot, etc.) directly in SQL.

Finally, you can create an View static method that create the SQL View as above and call this method directly from the DBSynchronize method of the Global class.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I understand what you mean, but, could you give me an example how to create the static method, and how to call the with the global class DBSyncronize?, another question, what will happend when AOT re-sync database, it will destroy the sql changes made before with the static method?

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (1)

Report

Hi Maximiliano Pinto,

If you will open Application.dbSynchronize() you can find examples how it's done in standard AX e.g. InventCostPreCloseNonFinTransferHelper::syncDBStoredProcedure() creates store procedure but logic is the same.

This method is executed on each sycn so AX will wipe your view than execute this method to create it again.

Was this reply helpful? Yes No
Denis Macchinetti 16,444 on at

Like (0)

Report

Hi Maximiliano

Ievgen is right!

In your case, the method will create a View and not a Store Procedure.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

thanks, really helped, now i am worried when we will upgrade to a new axapta version (Currently ax 2012 R2), but otherwise all right

Regards.

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

You would be surprised but in AX 7 we have couple of classes to create SQL object in run time so you will be able to migrate it.

Was this reply helpful? Yes No