Transcript Business Application Reporting

Professional Distributed
Reporting
Using Crystal Reports, SQL Server, and .NET
By Kevin S. Goff
Who is Kevin S. Goff?
Founder and Principal Consultant: Common Ground Solutions
18 years experience - NET/SQL Server/Crystal Reports/Visual FoxPro
2005 Microsoft .NET MVP for C#
Author of “The Baker’s Dozen” series in CoDe Magazine
(Productivity Tips for .NET, Crystal Reports, and T-SQL)
Speaker at 2005 DevTeach, MSDN CodeCamp, .NET User Groups
Common Ground Framework for .NET (free framework)
Awards/citations from the Dept. of Agriculture/large corporations
www.commongroundsolutions.net / [email protected]
My Blog: http://www.TheBakersDozen.net
7/17/2015
Distributed Reporting - Kevin S. Goff
Steps to building a report
1-Define the overall output
2-Define the General requirements
3-Define the data requirements (input parameters/output results)
4-Build the result set
5-Review and update data requirements with any changes
6-Build the architecture and data access layer to return result sets in a
distributed environment
7-Design the report from the data requirements
8-Final integration
9-Additional Considerations/Comments
7/17/2015
Distributed Reporting - Kevin S. Goff
Output:“Begin with the end in mind”
7/17/2015
Distributed Reporting - Kevin S. Goff
2: General Requirements
1-Must be able to Preview/Print/Generate a PDF
2-Must run for a variable # of Locations, and a Date
Range
3-Must be able to generate in a distributed
environment (run inside corporate headquarters, also
run for users who are travelling)
4-Must be able to display data in a different color if
values exceed a threshold
5-Must display annotations for who ran report, and
against which database
7/17/2015
Distributed Reporting - Kevin S. Goff
3: Data Requirements
Define all user inputs for the report
– Date Range
– Variable number of Accounts, Products, etc.
– Miscellaneous criteria (Include/exclude certain data
elements)
Define report result set
– Define all tables, columns, relationships
– Use VS.NET DataSet designer to construct typed dataset
7/17/2015
Distributed Reporting - Kevin S. Goff
VS.NET Typed DataSet
We can refer to elements of the result set as objects
Form of self-documentation
7/17/2015
Distributed Reporting - Kevin S. Goff
4) Build Result Set
Construct stored procedures to return
result sets
– Stored procedure should document all result sets
– Use CSVToTable to convert variable list of selections into a
table variable that can be used in subsequent JOIN statements
Use Query Analyzer to test result sets
7/17/2015
Distributed Reporting - Kevin S. Goff
Stored Procedure
7/17/2015
Distributed Reporting - Kevin S. Goff
Query Analyzer - test result sets
7/17/2015
Distributed Reporting - Kevin S. Goff
5) Update data requirements
From interactive testing in query analyzer, you may find that additional
columns are necessary. Update your typed datasets as needed
7/17/2015
Distributed Reporting - Kevin S. Goff
6) Architecture and data access layer
Supported architectures: Web services and TCP Remoting
–
–
–
–
–
Startup option, determine which connection method
Determined by options in Login.XML
Simple stored proc that runs to validate User ID
This simple login process demonstrates mechanics of calls to the back-end
This example will use TCP remoting
Program against back-end objects via Interfaces!
–
–
–
–
In Masonry.DataAccess, we have GetCostGraphReportData, to call stored procedure
In Masonry.Interfaces, we have ICostReportGraph
Defines a function to return an XML string, based on a set of parameters
Client piece will not have this code – but it can access back-end via an agreed interface
Complete walkthrough of running stored procedure to get data
–
–
–
–
Create instance of remote communication manager (explain functionality….factory/façade
pattern)
Set properties for communication object
Created typed Interface reference to ICostReportGraph oReportGraphObject;
Using interface, we can discover PEMs
–
Very important – we get result set as XML string – we must convert to typed DS
7/17/2015
Distributed Reporting - Kevin S. Goff
7) Construct the report
Provide report data definition from XML schema
Create “preview data” to test with
MyDataSet.WriteXml("Ds_ReportAndGraph.xml",
System.Data.XmlWriteMode.WriteSchema);
7/17/2015
Distributed Reporting - Kevin S. Goff
Provide report with data definition
From Database expert, specify a new connection
7/17/2015
Distributed Reporting - Kevin S. Goff
Select specific XML file
Point to XML or XSD file
In this example, we’re using the XML file that contains both the
schema, as well as data (for prevewing)
7/17/2015
Distributed Reporting - Kevin S. Goff
Select tables from XML
Click the SELECT ALL button to retrieve all tables
7/17/2015
Distributed Reporting - Kevin S. Goff
Database Linking Expert
Crystal Reports does not always default links correctly. Review the
links in the database linking expert, and make any necessary changes.
7/17/2015
Distributed Reporting - Kevin S. Goff
Linking options
By default, links are defined as inner joins.
7/17/2015
Distributed Reporting - Kevin S. Goff
Report Display options
You can use the Crystal Formula editor to change run-time attributes
7/17/2015
Distributed Reporting - Kevin S. Goff
8) Integrating Report
Add the report to the VS.NET project. This creates a “stronglytyped” reference
Add a reference to the CGS.CrystalReports project
Previewing/printing/exporting a report can be done with the
following:
DsReportGraph = SomeFunctionToReturnTypedDataSet()
RPT_ReportGraph oReport = new RPT_ReportGraph();
CGS.CrystalReports.cgsCrystalManager oCrystal = new
CGS.CrystalReports.cgsCrystalManager();
oCrystal.ShowReport(DsReportGraph, oReport, “Preview Title”, “PREVIEW”);
oCrystal.ShowReport(DsReportGraph, oReport, “ ”, “PDF”, ”MyPDF.PDF”);
oCrystal.ShowReport(DsReportGraph, oReport, “ ”, “PRINT”);
7/17/2015
Distributed Reporting - Kevin S. Goff
Final comments/tips/thoughts
Treat the report as you would any UI component (i.e. don’t add business
logic/calculations). Always make them part of the data definition. Use the report as a
presentation vehicle, nothing more.
Unless a report is (and always will be) part of an internal application, keep database
references out of a report. In a distributed environment, the report will likely reside
on a different physical boundary. The report should not contain back-end references
(e.g. stored procedures) – only XML definitions
Standardize report layout in your organization. Adopt a reporting style guide. Strive for
consistency.
Focus on getting the data requirements correct. Reports are about three things: data,
Data, and DATA!
Reports database concept
User profile4 – rights to acocunts in query
Reports are often the most critical parts of an application. They must be correct, they
should look appealing…and they must be correct!
7/17/2015
Distributed Reporting - Kevin S. Goff
THE END!!!
7/17/2015
Distributed Reporting - Kevin S. Goff