You can change the report request records using the "Set Process Options" function from CDSREQ. This allows you to add a start/stop condition to the program, set the default paging, breaking and sorting parameters. Choosing the "Add Option" button will add a new environment variable to the request record, like PUB-CDE, ITM-NUM, or PMO-CDE.

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'))

Examples:

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 

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.