Excel Reporting via SQL Queries Custom AX reporting

Excel Reporting via SQL Queries Custom AX reporting with the Excel user interface

Excel Reporting via SQL Queries • • • Why – Pros & Cons Setup Security How Wrap-up

Pros & Cons • • + One point source for code management + Restricted access, reduced exposure + Fast Flexible querying - Additional security setup - AX enums - AX logic not available +/- Still in Excel

Setup • User ODBC connection • SQL Roles • AD group membership

Security using SQL Roles • • Create a AD group, add user membership Add AD group to SQL Security Logins Create Role in Database Roles Assign SQL Security login to new role • Assign stored procedure to securables (later)

Add AD group to SQL Logins in SSIS

Create Database Roles

Assign AD group to Role

How to do it • Modeling the query

Recommend start with Test database instance

Need to select at least 1 field from 1 table Next next Finish

Choose how data will be presented, then click Properties to build query

Preliminary Data, validation & proving

Make a copy of the query

Open SSIS, create a new stored procedure and name it appropriately

…follow directions…

Add the query

Paste copy of query, clean up

Execute to update stored procedure

Add security

Select the role

Grant Execute permission

Add to Excel

Finalize • Finish testing • Get sign-offs • Move to Live – Create usp in Live database – Assign to roles – Connection in Excel • Release

Move to Live • Create usp in Live database • Assign to roles • Connect in Excel

Excel refresh options

Prevent saving pivot table source data