There are two different Microsoft SQL Server Reporting Services (SSRS) Tools that can be used for writing reports for FASTER Web:
1. Report Builder
a. Requires Information Technology/Computer Services approval.
b. Free tool that requires client installation.
c. FASTER has provided a link to download Microsoft Report Builder in the Reports Module.
2. Business Intelligence Development Studio (BIDS)
a. Requires Information Technology/Computer Services approval.
b. Requires a client install and appropriate licensing from Microsoft.
c. More robust tool than the Report Builder and requires a little more expertise in report development.
The design of your custom report can vary greatly depending on your desired results, your skill set, and the amount of time you would like to put into creating your custom report. However, there are a few main report items that you will generally see in almost every report.
1. Page Header and Footer
a. In the Page Header, you may want to include the report title, date range, page number, and any other information that you would want to be displayed at the top of each page when the report is run.
2. Report Body
a. Include data columns that you would like displayed in your report.
i. If you would like to interactively sort data, you can do so by editing the text box and setting the Interactive Sorting appropriately. This works best when done with the column header for the data you would like to sort by.
ii. When number or date data fields wrap into two or more lines it is difficult to read the data, so always make sure your report has enough space to display the numbers and dates without wrapping.
b. Add groups if desired.
i. Adding groups will allow you to include subtotals for costs and counts.
c. Add report summary information if desired, including totals for costs and counts.
d. It is always a good idea to display the parameter values which are used to filter the data in the report. If a report is exported and shared without displaying the parameter values it will be difficult for the end user to understand the data.
Customer written reports should be created using a non-FASTER Schema and database object name. Any schema that is part of the FASTER Web product, such as the “faster” schema or the “reports” schema may be changed during an upgrade and any objects in those schemas that are not created by FASTER are removed by default.
1. It is recommended that you use your agency name as the Schema. For example, Fasterville, North America would create and use the Schema “Fasterville”.
2. New custom report:
a. It will help your users if the names of views or stored procedures are consistent with FASTER report numbering conventions.
For example:
§ FAS is the abbreviation for Fasterville.
§ A custom Asset report’s database object might be named FAS180_AssetListWithTiresAndEngine.
§ A custom Inventory Item report’s database object might be named FAS280_PartsListForFireApparatus.
b. Further, it is recommended that the report name be consistent with the database object name. This will help you identify the database objects associated with the report you are building.
For example:
§ If the database object name is “Fasterville.FAS180_AssetListWithTiresAndEngine” then the report name would be “FAS180 – Asset List with Tires and Engine”.
§ If the database object name is “Fasterville.FAS280_PartsListForFireApparatus” then the report name would be “FAS280 – Parts List for Fire Apparatus”.
|
Standard Report numbering pattern: 100 Series – Assets 200 Series – Inventory Items 300 Series – Work Order and Direct Charge 400 Series – Fuel 500 Series – Accounting 600 Series – System 800 Series – Motor Pool |
3. New custom report based on the existing standard report:
a. Get the name of the stored procedure by right clicking on the appropriate dataset and viewing the Dataset Properties.
b. It is recommended that the names of views or stored procedures be changed to contain the custom Schema and three character abbreviation.
For example:
§ If you want to create a report similar to the “W104 - Asset Meter Report” which uses the “Report. W104_AssetMeter” stored procedure, the new database object should be renamed “Fasterville.FAS104_AssetMeter”.
§ If you want to create a report similar to the “W407 - FuelUsageDetailReport” which uses the “Report.W400_FuelTransactionReport” stored procedure, the new database object should be renamed “Fasterville. FAS400_FuelTransactionReport”.
|
Be sure to change the name of the stored procedure to something different than the standard FASTER Web name. This will prevent you from overwriting the standard FASTER Web stored procedure |
c. It is recommended that the report name be consistent with the database object name.
For example:
§ If the database object name is “Fasterville. FAS104_AssetMeter” then the report name would be “FAS104 – Asset Meter”.
§ If the database object name is “Fasterville. FAS400_FuelTransactionReport” then the report name would be “FAS400 – Fuel Transaction Report”.
d. If you are adding columns to the report and the fields needed are already being passed into the report through the existing dataset, you can just simply make the changes that you want and resize page if necessary.
e. If you are adding columns to the report and the fields needed are not already being passed into the report through the existing dataset, you will need to modify the existing stored procedure that the report is using, Refresh Fields in dataset, and resize page if necessary.
|
To resize page to FASTER report standard 8.5 x 11 for portrait and 14 x 8.5 for landscape; take the width of displayed area and add 1” for margins. Then use the following formulas to solve for “x” to calculate “InteractiveSize” and “PageSize” in the “Report” properties: Portrait: 8.5”/11” = [Display Width]” + 1”/x” Example: Display Width is 9.5” 8.5”/11” = (9.5” + 1”)/x: Solve for x: x = 13.6”, so portrait page size is 10.5” x 13.6” *
Landscape: 14”/8.5” = [Display Width]” + 1”/x” Example: Display Width is 18” 14”/8.5” = (18” + 1”)/x: Solve for x: x = 11.6”, so landscape page size is 19” x 11.6”
* Portrait page size should never be less than 8.5” x 11” |
1. When using Report Builder through SQL Server Reporting Services or as a desktop application, depending on database you are creating the report from, select the appropriate data source:
a. FasterWEB for Faster Web core product reports.
b. Motorpool for Faster Web Motor Pool product reports.
2. When using Business Intelligence Development Studio, depending on database you are creating the report from, select or create the appropriate data source:
a. FasterWEB for Faster Web core product reports
b. Motorpool for Faster Web Motor Pool product reports.
There are two reasons to use datasets:
1. The primary dataset is used to display data on report. A primary dataset can come from:
a. An existing database object (not recommended; if FASTER makes changes to standard database object it may cause your custom report to no longer work).
b. A renamed existing database object.
c. A customer created database object.
d. An SQL statement within the report.
2. Parameter datasets are used to generate dropdown lists for report parameters. Parameter datasets normally get lists of faster.STL[Data] (Static Tables) or faster.TL[Data] (Dynamic Tables). For example: faster.STLPartStatus or faster.TLOrganization.
Parameters are used to limit what data is displayed in report. Parameters can be used to limit data within the report itself or can be passed into a stored procedure to limit the data being returned to the report from SQL Server (recommended, when practical).
The FASTER Web database stores date and time in UTC format. UTC format is “Coordinated Universal Time”, or what used to be called “Greenwich Mean Time”. If you are designing a report or making changes to an existing report that uses dates or times, you will need to do a convert these to the Time Zone appropriate for the user.
1. In order to have your local time displayed on your report, the UTC value from the database will need to be converted to your time zone. The easiest way to do this to use FASTER’s pre-defined function, Report.fx_ToLocalTime.
2. If you are passing a date through a parameter in your report to the database, you will need to convert this report date to UTC in order to make the correct comparison between this date and the date that is stored in the database. The easiest way to do this to use FASTER’s pre-defined function, Report.fx_Utc.
In FASTER Web, there are certain steps that must be followed in order for your custom report to appear and be functional from the Custom Reports section of the Reports module in FASTER Web. You can deploy reports using the Custom Report Deployment tool provided in the Reports module, or by deploying the report manually.
1. To deploy FASTER Web custom reports using the in-product Custom Report Deployment tool (specific permissions required):
a. Follow the instructions located on the FASTER Web > Reports module > Custom Report Deployment screen.
b. For additional help regarding this deployment option, click on the Help link button from the Custom Report Deployment screen.
2. Custom FASTER Web Motor Pool reports must be deployed manually at this time:
a. In Microsoft SQL Management Studio, run any SQL scripts associated with the report on your FASTER Web Motor Pool database.
b. In the FASTER Web Motor Pool Reports Server, upload the RDL report file.
i. Reports should be placed in the Custom Reports\[Subfolder] folder.
ii. Set the Data Source to the appropriate MotorPool Data Source for the report.
c. In the FASTER Web Motor Pool App Server, Create or modify the existing CustomReports.xml file and copy to the following location: C:\inetpub\wwwroot\MotorPoolWebService.
|
FASTER Web Motor Pool names and associated report series: SUBFOLDER REPORT SERIES Accounting 500 or 800 related to accounting Assets 100 or 800 related to assets Reservations 800 related to reservations Setup 800 related to setup and administration |