Excel Reporting via SQL Queries Custom AX reporting
- Slides: 29
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
- Complex sql join queries
- Basic retrieval queries in sql
- Hotel database sql queries
- Sql insert update delete query
- Sql queries for banking database
- Sql dml
- Ssms intellisense not working
- Decimoquinta estacion del via crucis
- La via negativa
- Vialusis
- Marcha hicopoda
- Via erudita e via popular
- Difference between sql and plsql
- Pl/sql unit testing
- Using subqueries to solve queries
- Standing query
- Action queries in access
- Dimensional modeling basics
- Multirelation queries
- Wild card queries in information retrieval
- Thank you any queries
- Answering my queries
- World importers
- Conjunctive queries
- Canned queries
- Ingres algorithm
- Teradata ordbms
- For any queries
- Scdl student centre login
- Suggestions and queries