Query For SSRS Report

(0) Share

Report

Posted on by Community Member

Hi,

In AX4 I use this query as part of a SSRS report:

select 'Invoice' AS Indicator, c.purchid as [Purchase Order], c.invoiceaccount as [Vendor Ac], c.purchname as [Name], c.dimension as [Site], a.costamountphysical as [Amount], a.packingslipid as [Delivery Note], a.recid from inventtrans a inner join ledgertrans b on a.voucher = b.voucher inner join purchtable c on a.transrefid = c.purchid where a.dataareaid = @dataareaid and b.dataareaid = @dataareaid and c.dataareaid = @dataareaid and b.accountnum = '34050' and b.transdate <= @date and b.voucher like 'PI-%' and a.voucherphysical like 'GRN-%'

Can anybody suggest how I might rewrite this so the query runs in AX2012?

Cheers

Paul

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report
This might be a bit more readable:

select * from InventTrans a inner join LedgerTrans b on a.voucher = b.voucher inner join PurchTable c on a.TransRefId = c.PurchId where a.DataAreaId = @dataareaid and b.DataAreaId = @dataareaid and c.DataAreaId = @dataareaid and b.AccountNum = '34050' and b.TransDate <= @date and b.Voucher like 'PI-%'
and a.VoucherPhysical like 'GRN-%'

Now the question is what you want to do with it. Are you trying to create an SSRS report based on an AOT query and you don't know how to model the AOT query?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Martin.

This is a query pulled from the AX4 database and is used in a SSRS report. My company is upgrading to AX2012 so I want to write the same query to work in AX2012. There is no LEDGERTRANS and several field names have been moved, renamed or excluded from AX2012.

I'm not familiar with the tables in AX2012 and how they are related so I thought I'd ask here in case somebody is familiar with them.

Thanks

Paul

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

Are you sure that you don't want to use SSRS reports integrated in Dynamics AX? As you know, all reports in AX 2012 are SSRS reports, so it would make sense to use the same framework instead of just blindly continuing with something created at time when AX didn't support SSRS. You would lose the integration with AX.

Regarding LedgerTrans, look at the following white paper: Mapping the LedgerTrans table to General Journal Tables.

Was this reply helpful? Yes No
Girija Shankar Beuria 690 on at

Like (0)

Report

You have to check this :

http://technet.microsoft.com/en-us/library/hh292603.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have a similar question. I have been developing SSRS reports for quite a few years, normally using Business Intelligence Development Studio or SQL Server Data Tools 2008,2008r2, or 2012. This my first AX SSRS report, how do I insert the SQL query that I wrote for my SSRS 2012 report version into the VS 2013 AX report model? I have found out that it appears that I must use VS 2013 with the AX report model to get the report into the AOT ( and AX). Is this assumption correct that I can't use a .rdl developed in SSDT 2012? I am a SQL developer not a .net developer.

Thanks for you help.

Rob

Was this reply helpful? Yes No