netc08 gl & rs
DESCRIPTION
This powerpoint accompianied a presentation and demo of the LSU AgCenter's reporting application and use of ad-hoc reportingTRANSCRIPT
PUTTING REPORTING INTO THE HANDS OF THE DATA OWNERS
Make your own d@$% report!
A CASE STUDY OF ACCOUNTING DATA
Why Accounting?
Known problem area Pre-existing reports Simple starting point to explore options for future,
more complex data
In the beginning… (and for a long, long time…)
There was paper.
Paper Is Bad, M’Kay?
Generated on a fixed schedule at a central location Mailed to extension offices - Out of date by time of
receipt Easy to misplace, stain with coffee, etc. Not a usable format for other reporting needs Takes up storage space
A New Age Dawns
Microsoft releases Reporting Services as part of SQL Server
Web-based, on-demand access Easily exportable to PDF, Excel and other formats Drill-through ability provides detail without searching
But it is a Cloudy Beginning…
Security A user is granted access to specific account codes PI’s on a grant should also see their grant accounts without
having to request access
Usability Where to put documentation??
Development & Extensibility IT has to create all new reports – requires development time
and understanding of data
Addressing Security
What level to add it? RS? DB? SProcs? Granted domain users sproc access Added a function to all stored procedures that
matches a user’s access to the requested account codes and strips out any part of the request that is unauthorized
Created an application allowing Accounting Services to grant access to users on an account code or account mask
Usability & The Default Installation
No instructions or help Users unsure of their own access Users unsure of some reports’ function/purpose
No parameter verification Bad parameter entry = blank report = support phone call
No connection to the rest of our intranetBottom Line: Lots of IT Time providing support for
very basic usability issues
The Sun Comes Out!
Created a Web Wrapper Application to integrate: Instructions Report descriptions Account access display Parameter verification Connected to our
intranet
Adding New Reports to the Wrapper
How to add a new report without modifying code? Financial Reports have a standard set of parameters –
build a user control for each Use an XML document to define report info Dynamically build parameter entry screen Dynamically load report Add new report by updating XML document
A Light Bulb Moment
What if I could give users the tools to make their own reports?
Enter: Report Builder
Report Builder Overview
Uses pre-defined data model to create a user-friendly “view” of data
Data Model allows for security/access restrictions Data Model handles common statistical functions Drag and Drop fields and filters to create reports on
demand Intelligent grouping and subtotaling is automatic but
configurable Create new fields based on calculations - math or text
Building the Report Model
Add common calculations as fields Give user-friendly names Define relationships between tables Set up common look-up values Define selection type (when field is used as filter)
Security Filters are Finicky!!! Not-so-great documentation Bypasses existing security model**
Security Concerns
Row-Level security needed to be added Modify security model
Can’t use SQL Function with Current User to limit results Can’t match to account masks – must be direct match
Enable a security filter by: Exploding security table to a explicit user: explicit account set
of records Add filters on sensitive entities based on current user and
exploded records
Using the Model in Report Builder
Access to the model is granted to Accounting Services and Power Users
The data that the model pulls is limited based on the current user’s access
Users can create table, matrix or chart report formats Grouping and subtotaling is automatically added
based on the order of the fields in a table or matrix
Publish Reports
One-time reports can be discarded Regular reports can be saved to report server Now: IT can add a server report to the wrapper
application for all users or for a unit-based group Later: The user will be able to fill out an online form
to add the server report to the wrapper application
Lessons Learned
Start with the model I created views, sprocs, security tables, etc. for direct data
source connections early on All this can be encapsulated in a report model, which can be
used for both ad-hoc and tech-designed reports
Back up and test every step when working with security filters
Spend the time to train or write on-demand help If your data will be consumed by other applications,
investigate database row-level security
Lessons Learned, cont.
Determine all access methods early on Direct database querying Report model querying Stored procedures only
??Can report models be built off of stored procedures? If so, can we remove domain user read access from tables??
Questions for Future Growth
How do we address the same types of concerns in more diverse fields where parameters are not standard?
How do we control the proliferation of new user-generated reports, maintain organization and prevent duplication?
Future Directions
In accounting: Projections – state budget or sponsored program budget Different Fiscal Years Historical perspectives
Other data areas: Human Resources Grants/Sponsored Programs Research Data Extension Reporting
Delivery through MOSS (LSU AgCenter Intranet)
