We are pleased to announce the latest Advantage module: Business Intelligence. You've told us that people in your organization need to be able to get at the vast amount of data in Advantage; that they want meaningful reports, easily accessed and not requiring detailed technical knowledge or deep familiarity with Advantage. We listened and the BI module is our response. BI offers you 3 main benefits:
- Meaningful Reports Rather than selecting reports and then running them, you simply go to a website to access reports which will include graphs, pivot tables you can modify and standard "canned" reports. You can configure your home page to display specific reports of your choice at the time interval of your choice. Reports include the analysis types you need—snapshot, historical and comparison; the subjects relevant to your business—marketing, financial and operational; and are geared for each user type you have—key performance indicators for strategic decision makers, summarized data for tactical decision makers, and access to detail for front-line decision makers.
- Organized & Accessible Data Your data is already organized for you for easy analysis, stored in a separate data mart. The data is structured around the measures and dimensions you need and summarized data is efficiently presented. There's no need for users to know field names or codes. You can access reports on demand via a SharePoint website, or they can be delivered via email. Reports are also interactive so that users can explore combinations of dimensions, drill down to deep detail or jump to related reports.
- Industry-Standard Solid Foundation The module uses advanced tools with broad acceptance. These include Microsoft SQL Server, Integration Services, Analysis Services and Reporting Services, plus Excel. These tools have been used to provide a suite of core reports with customization options involving web layout, custom data elements and new report development.
The BI module is in beta testing at both Kalmbach and Oakstone.
If you determine a mistake has been made, you can re-set the request record completely back to the default settings and start over. Please be advised that this re-set will REMOVE ALL CUSTOMIZED settings – so use it with care. Alternatively, if you want to change something specific about the option you added (perhaps the prompt is incorrect), you must do so at SYSDCT/PRC.
Adding a process option:
The process option allows you to add an environment variable, and then either use that as the stop/start value or use it in your selection criteria.
Clicking on the "Add Option Button" launches the following Dialog box:
The "Reference Name" field is what shows up in your process log, and that is what you need to use in your selection criteria. Please use capital letters for the reference name and use a dash, not an underscore in the field name.
To reference the environment variable in the selection criteria:
For an Alphanumeric field ->> YYY.RRR-ZZZ = GETENV(‘REF-FLD') For a Date field =>> YYY.RRR-DDD = DATE(GENENV(‘REF-FLD')) For an Integer ==>>YYY.RRR-NNN = INTEGER(GETENV(‘REF-FLD'))
Example 1: For a Subscription report looking for specific Donor type records within a given publication: Reference Name: PUB-CDE Actual Name: PUB-CDE Required = Yes Reference Name: DNR-TYP Actual Name: DNR-TYP
Selection Criteria: SUB.DNR-TYP = GETENV(‘DNR-TYP')
Example 2: For an Inventory report using the CDSIGL-M records, where only a certain item should be selected, and only a certain range of accounting dates should be selected:
The example below uses the INVIGL-V Virtual File – looking at the CDSIGL-M records – which are indexed by Item number, and accounting date. This example will prompt for an item number, and also will prompt for a start/stop accounting date.
Although the field is the same (ACT-DTE) – the reference name is used in the selection criteria, so the reference name needs to be unique for the starting date and the ending date. The prompts were also changed for user clarity at run time. Reference Name: ACT-DTE Actual Name: ACT-DTE New prompt: Beginning Date Required = Yes Reference Name: ACT-DTE2 Actual Name: ACT-DTE New Prompt: Ending Date Required = Yes Reference Name: ITM-NUM Actual Name: ITM-NUM Selection Criteria: IGL.ITM-NUM =GETENV(‘ITM-NUM') AND IGL.ACT-DTE >= DATE(GETENV(‘ACT-DTE')) AND IGL.ACT-DTE <= DATE(GETENV(‘ACT-DTE2'))
Process Log Information:
+ date Mon Apr 13 11:01:36 EDT 2009 + id -urn + id -urn + export USER=TBCDOMAIN\AdvService + set -X + trap exit 1 ERR + export SHELLPID=5364 + umask 000 + export SETENV_FILE=./set5364.env + export VFSTAT=YD + export BEG_STEP=0000 + export TOT_LVL=S + export TOT_ONL=N + export SEL_KEY=KEY-1ST + export SEL_NME= + export SEL_VFL=CDSIGL-V + print STR_VAL = <ITM-NUM> + print STP_VAL = <ITM-NUM> + print PAG_KWDS1 = + print PAG_KWDS2 = + print TOT_KWDS1 = IGL.ITM-NUM + print TOT_KWDS2 = + print SEQ_KWDS1 = IGL.ACT-DTE + print SEQ_KWDS2 = + print SLD_DTA31 = IGL.ITM-NUM =GETENV('ITM-NUM') + print SLD_DTA32 = ANDIGL.ACT-DTE >= DATE(GETENV('ACT-DTE')) + print SLD_DTA33 = ANDIGL.ACT-DTE <= DATE(GETENV('ACT-DTE2')) + export PAGELEN=0060 + export DSB_PRT=KKP_HP + export RPT_CPY=01 + export DTE_OPT=A + export REQ_USR=00004632 + export USR_IDN=KPORT + export REQ_NME=INVWWW + export PRJ_NBR=ALL* + export KEY_1ST=P00000303 + export REQ_CNT=00071278 + export CUR_JSQ=0000 + export ACT_DTE2=20090601 + export ACT_DTE=20070101 + export ITM_NUM=0397512478
Hurry and you might still be able to catch the webinar on Encryption and Decryption of Credit Cards at 11:00 a.m. EST on March 17. Having a good understanding of this functionality is vital to maintaining compliance and providing proper organization of credit card data in Advantage. Philippe Rowland will be presenting on this topic. If you are interested in this topic but missed this time period, please contact Doug Moore.
SQL Reporting Services with Advantage is another popular topic back again this year. On March 25th, Levi Hyssong will demonstrate how to develop reports, create report models, view reports online, export and distribute these reports, and much more. He will be demonstrate this through the use of SQL Server Business Intelligence Development Studio and SQL Server Reporting Services Report Manager. Don't delay this topic fills up fast!
by Tim Zapawa, Vice President of Client Services
There are a multitude of options, tools, and programs available for analyzing and reporting on your Advantage data. Deciding on and then implementing an optimal solution can be challenging for many organizations. And as companies and datasets get larger, the importance and impact of these decisions on data analysis increase as well. I'm often asked questions about how the data is accessed and what type of reporting tools should be used. In this article, I will briefly describe and compare some of the software programs available for data analysis and reporting. In the next article, I'll discuss some of the options for how data can be accessed.
Most of our clients use some combination of Advantage user-defined reports, Excel PivotTables, SQL Server Reports, and Crystal Reports for their data analysis and reporting. Each reporting tool has some benefits and drawbacks that should be considered.
Advantage user-defined reports provide an excellent means for novice users with simple reporting requirements. Report columns can be easily selected and labeled from an array of predefined tables that are linked to one another in what we refer to as a virtual file (note that virtual files are the equivalent of a SQL view; both are just a means of tying together multiple tables into one). For organizations that do not use external software programs for analysis or reporting, the Advantage user-defined report tool is ideal, as IT Operations staff does not have to be concerned about learning and supporting programs outside of the Advantage system. However, like most graphically-designed report writers, the reporting development tool provides only basic report development functionality. We offer more advanced courses for organizations that want to write the reports using our Tools language and this can be a good option for companies that want to maintain all their reports within Advantage.
Excel PivotTable reports are probably the most popular option being used at our client sites. It's not surprising, as PivotTable reports are extremely powerful, yet easy to use. Moreover, most users already have Microsoft Excel installed on their computers, so there is no additional licensing expense. And users are generally already familiar and comfortable with Excel. Building expertise with PivotTable reporting can generally be accomplished with minimal training and practice. Microsoft also continues to enhance the reporting tools in each new release of Excel.
Crystal Reports also provides some powerful tools for report development and analysis. It's also integrated with Advantage, so running a Crystal Report is no different than running an Advantage report. Run-time parameters, such as beginning and ending dates, can be specified and passed to the Crystal Report when requested. However, unlike Excel, Crystal Reports is not as pervasively installed. And while licensing costs are fairly nominal, becoming an expert with this software generally requires more training and practice. This program is also ideal for creating business forms--I don't know of any software that even comes close to matching its capabilities in the same range of software licensing cost.
SQL Server Reports is a relatively new player in the report development marketplace. It provides a lot of the same reporting tools as Crystal Reports, although it cannot hold a candle to the business form development tools in Crystal. However, there's no additional licensing cost if you already have SQL Server installed; it's simply a SQL Server component that needs to be turned on. And like Crystal Reports, SQL Server Reports can be run directly from Advantage. We're making extensive use of SQL Server reports in our Business Intelligence module, as there are so many web-enabled options that provide easy-to-use tools for users to view, subscribe-to, and run their reports through a web page. If you attended any of our BI sessions in the past year, you've seen this impressive program in action.
Most organizations opt to use Excel, given the range of development tools and ease of use. One PivotTable report can easily provide the equivalent of hundreds of comparable reports in Advantage, Crystal, or SQL Reports. However, Excel does not have the same level of web-enabled options as SQL or the powerful form programming tools of Crystal. Of course, you need to weigh multiple factors in choosing the best report solution for your organization.
In the next article, I'll discuss important considerations for implementing reporting and data analysis tools within your organization. Contact me at TZapa@AdvantageCS.com or your ACS representative if you'd like more information about these reporting tools and how they can be best implemented at your company.
Editor's Note: Tim Zapawa is the author of Excel Advanced Report Development and Excel 2007 Advanced Report Development, both published by Wiley.
You can have Advantage save archival copies of all your reports to different directories or even automatically copy and send these reports to various users.
The Printer settings at CDSSYS/PRT can be used to control whether the reports print any actual copies and where the files are saved. You can have the files saved to the run-time user directory, with the date/time stamp included, simply by checking the archive button. If you never want these reports printed out, simply choose "0" as the override number of copies.
You can also specify a directory where the files will be saved in \reports\special_directory\yymm\dd\*.report - by choosing the ‘archive to directory' function, and specifying the new directory name. This can be very useful for all your nightly/weekly/cycle-end related reports.
At 2005r2 and up, you can make use of the OPR300 automatic distribution feature which allows you to send specific reports either via email or ftp, or to copy the reports to a specific directory. This is very useful for clients with users connected over a wide network.
If you need some reports in a given posting to go to an actual printer, while you only want the others to get saved into the usual folder, you can use the SYSRPT screen (at 2007r1, the SYSRPT screen moved to CDSSYS/RPO)to define the specific printer to use for this report. For example, maybe you would like your ARP237 report to print in the accounting department. You would establish ARP237.REPORT at SYSRPT to be printed to ACCOUNTING_HP7NP (or whatever the name of the printer is you have set up as the accounting printer). This printer must be available on the network for Advantage to "see".