Hi, gang. I often load multiple reports in to Monarch (6.0 Pro) to extract similar data. If there is a data point that does not exist on one of the reports, Monarch populates the table with the value from the previous report. For example, if I look for "Cancelled" on a set of reports, if one of the reports does not have any "Cancelled" accounts, the template doesn't find it. (If there are no "Cancelled" accounts on a report, there is no line for "Cancelled" at all, and the word does not appear) I want a "0" to appear, but instaed I get the number of cancelled accounts from the report just before. Sorry if this is rambling but any help would be greatly appreciated.[/b][/quote]Hi Jim, first, I assume your reports might look something along these lines:
REPORT FOR SEPT 1 2002
(etc.)[/font][/quote]But some reports may just read as follows:
REPORT FOR SEPT 2 2002
xxxxx xxxxxxxxxxx[/font][/quote]because no accounts were cancelled. You still want to see a line showing 0 (zero) accounts cancelled for that day. I think you can do what you want using the Monarch Summary view.
First, be sure you are also capturing the report date or report period from each of your reports. That's an important piece of information. You wrote: "I want a "0" to appear, but instaed I get the number of cancelled accounts from the report just before" which suggests to me maybe your model is not picking up a report date/period.
Next, create a Calculated Field (in Monarch Table view), something along these lines:
Suppose you have a data field (column) called "Account Status" (which reads: "New", "Cancelled", etc.). We will call our calc. field "Cancelled Count":
If a data line in our Table View contains "Cancelled" in the Account Status field, the "Cancelled Count" field will display a 1, otherwise, it will display a zero.
Now, define a Summary view in Monarch as follows:
Specify your Report Date or Report Period data field as the first "Key Field" (Sidenote: you can add more Key Fields if you want; perhaps Account Number, Account Name, etc.)
Specify your calc. field Cancelled Count as the "Summary Field" (calculation: SUM)
You should now see a Summary view looking something like this:
1 SEPT 1 2002 2
2 SEPT 2 2002 0
3 SEPT 3 2002 5
(etc.)[/font][/quote]Notice how you get that "0" count you are looking for, for those reports that have no Cancelled accounts.
Hope this helps...let me know!
Product Marketing Manager
More than likely you are extracting this data using an Append Template.
It is a characteristic of the Append to attach to the lines of Detail below until it reaches another instance of itself.
There are two ways to work around this depending on the consistancy within your report file.
First you can try adjusting the Template.
Try, if possible to extract using a multi-line sample that appears as often as the Detail. Many times this can be accomplished if there is a label to use as a Literal Trap.
If this is not possible.......
Build a Summary.
Depending on the Drill Level of the field in the Summary Window, you can Suppress the Duplicates in the field properties Display tab.
Technical Support & Model Building