Below is the outline of a report we consider moderately complex as it requires researching Dynamics AX and converting the X++ logic over to SQL and SSRS. This is a good example of the requirements I would provide and the level of detail I’d expect an experienced SSRS/Dynamics AX developer to need in producing a great BI report. I’d like to create this report using SSRS external of AX; An SSRS report requirING direct SQL into the AX database, not using the AOS or any business layer of AX.
Report Summary: Produce a GL Summary by Month Report which supports clicking into a detailed report to view the Voucher details and Origin transactions. This example models exactly how AX works, from the Ledger Details screen you can navigate to the Transaction screen and then to the Origin screen. The report I’d like created is simply 2 reports; 1) GL Summary by GL Account and Year/Month and 2) a drill-down report which shows all Ledger Vouchers and the ability to expand/collapse the list of Origin records.
Report 1: This should be a basic matrix report with the following row/column details:
Rows – Create 1 column for each GL Account, sum all transactions for that GL Account. Alternate the row colors from White/LightSteelBlue for each GL Account and Order by GL Account Number.
Columns – Create 1 column for each Year/Month ordered by Year/Month Ascending. Add a Sub-Total for each Year as well.
Drill-Down – Allow drill-down to sub-report from each Row/Column cell. The Drill-down will provide the Voucher and Origin details for all transactions included in the GL Account/Month combination.
Sub-Report: This should be a Table formatted report which allows Expand/Collapse based on each GL Voucher.
Parent Row: List each GL Voucher for the selected GL Account/Month passed into from Report 1. Provide the Voucher Amount on the row which when added for all Vouchers on this sub-report should equal the value clicked on from Report 1.
Child Rows: List each Origin associated to the GL Voucher expanded in the Parent Row. List the Date/Number/Type and Amount for each Origin transaction.
please give the solution ,urgent i am new to ssrs reports.