How to develop a SSRS report using two sql queries

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi,

I have a requirement to put two sql queries(i can say two union select statements) which is providing the infromation what i need in the Sql server management studio but i am not sure how we can develop a SSRS report using two SQL select statments and run it in AX2012.

Please let me know if someone come across this scenario.

Thanks,

Maruthi

2

I would have appreciated if you actually asked a question, because now I’m not sure what you need.

If you don’t know how to use two queries, simply add one more data set to your report.

If you don’t know how to create reports at all, note that you can based data sets on AOT queries, for example. You’ll find more information in the documentation: Defining Report Data.

3

Check the UNION fuction.

Thanks

Renjith

4

Hi Martin,

This is what exactly i am looking for, If i have two union select statements which will be a combination of multiple tables. If i created two different Datasets with two select statements and i can’t combine these two datasets in a single Dataset which will be combination of two select statements.

There is a way by selecting union in the Dataset properties but this will be used for extracting the data from single table.

I am following the same way what mentioned in the link you provided.

Below is the query i am using to generate a SSRS report.

select b.ACCOUNTINGDATE Date,
a.TRANSACTIONNUMBER,
f.DISPLAYVALUE,
c.PROJID,
c.AMOUNTCURCREDIT,
c.AMOUNTCURDEBIT ,
null vendornumber,
null vendorname
from ADVANCEDLEDGERENTRYHEADER a,
GENERALJOURNALENTRY b,
ADVANCEDLEDGERENTRYLINE c,
PROJTRANSPOSTING d
,GENERALJOURNALACCOUNTENTRY e,
DIMENSIONATTRIBUTEVALUECOMBINATION f
where a.LEDGERVOUCHER = b.SUBLEDGERVOUCHER
and a.RECID = c.ADVANCEDLEDGERENTRYHEADER
and d.VOUCHER = b.SUBLEDGERVOUCHER
and a.RECID = e.GENERALJOURNALENTRY
and c.PROJID = d.PROJID
and f.RECID = c.LEDGERDIMENSION
union
select a.ACCOUNTINGDATE Date,
a.DOCUMENTNUMBER,
b.LEDGERACCOUNT,
c.PROJID,
b.TRANSACTIONCURRENCYAMOUNT ,
b.REPORTINGCURRENCYAMOUNT,
e.ACCOUNTNUM,
f.NAME
from GENERALJOURNALENTRY a
,GENERALJOURNALACCOUNTENTRY b
,PROJTRANSPOSTING c
,VENDINVOICEJOUR d
,PROJLISTLEDGERUPDATES g
,VENDTABLE e
,DIRPARTYTABLE f
where a.RECID = b.GENERALJOURNALENTRY
and c.VOUCHER = a.SUBLEDGERVOUCHER
and d.LEDGERVOUCHER = c.VOUCHER
and e.ACCOUNTNUM = d.INVOICEACCOUNT
and e.PARTY = f.RECID
and g.TRANSID = c.TRANSID

5

Aha, so you actually don’t have two queries - you have a single query. It internally consists of multiple parts, but that’s not important - it’s a single query a returning a single set of records, therefore you need just a single data set in your report.

If you want to build your data set upon a query, simply design a union query in AOT. It’s supported since AX 2009.