Although I haven't tried it myself, you ought to be able to run a couple of Monarch project exports to be able to do this. I don't think that you can do this with a single Monarch model/project.
The first export would append your data output to an existing header text file. The next would append the trailer text file, or footer, to the result of that export, resulting in a final file.
Barring that you could always use a DOS batch file to tie it all together. The following would concatenate three individual files (A, B, and C text files) into a final file D text file:
If you want to output data with header and footer, you can do this using the Summary window.
Define the first key column as the field you want in your header, and check the boxes for this to be hidden column. Also check the box on the general summary tab to show a total - this will give you your footer.
If you have particular requirements to force particular data into these positions, it can be done using some calculated fields and filters prior to the summary.
I do have monarch pro for version 10, since I never attempted something like this, what is the best way to add the stuff together from your point of view?
basically the data is in a table,
is it possible to do a total count of records too or is that asking too much also within the header trailer?
apologies for my noviceness.
It's easy to make a count appear in the footer line of your export - simply add the function Count() as a Measure in your summary.
Without seeing your data and having a clear understanding of what you want to do with it, I can't give you practical advice on how best to approach it.
What I can give you, if it's any help, is reassurance that you can do it in Monarch. I've not seen any data yet that couldn't be handled.
Thanks for the feedback Olly
Olly not using summary table, basically i transformed the database data, from a csv format into table did some simple field insertions with some formulas based on what the business user wanted.
That was all that was needed
so you are telling me that i need to create a summary to do this which I was trying to avoid.
You're correct: external lookups add fields to the table.
I suppose what we should have asked originally is: What type of content do you wish to include in the header and footer blocks? Will they just be descriptive text - like regular paragraphs - or will they be a mix of labels and statistical or summarization amounts, based on the data in the body of the report? Or something else?
The header & Trailers are below,
unfortunately gets skewed some in this web page but here is what it looks like.
Field Name Format Rules Col Notes
Header Control Record
Rec Type Char (3) Man-1 1 HDR
COB Date Date Man-1 2 YYYYMMDD actual Business date
Data Source Code Char (6) Man-1 10 i.e. CMKTC6
Trailer Control Record
Rec Type Char (3) Man-1 1 TLR
Feed Control Count Number (5) Man-1 2 Total # of Data Records/rows
Data Source Code Char (6) Man-1 10 i.e. CMKTC6
Best equip yourself with your favorite caffeinated beverage now, I'm afraid.
The good news is that by using only Monarch and a little batch programming, you can do what you want.
There are a few things that you'll need to lay the ground work for this.
You'll need three individual Monarch projects to export the COB Date, the data source code and the number of data records. Each of these will export only the relevant value, without the field name, to a text file. Export to cobdate.txt, sourcecode.txt and recordcount.txt respectively.
For the COB Date and the Source Code I imagine that you can export from the table window easily enough. For the record count, you'll want to create a summary. You may have to turn around and import the summary export again so that you can export a single value to a text file, this time from the table window. Maybe filter on the total, and hide the total name, whatever it is. The end result that you're after is a text file that contains only a number.
Next, create a text file that contains a single word. It doesn't matter what it is. You'll use this as the record source, but you'll hide the word in the table, so it's irrelevant. You're only using it to create a single record for a model.
This becomes the input source for the first model, the "Header" model/project. Capture the word, then go to the Table. Add a calculated runtime parameter field named CodeChar. For now give it a value of "TEST".
Create a calculated Memo field named "Header", and give it this formula:
"Field Name Format Rules"chr(13)
"Header Control Record"chr(13)
"Rec Type Char (3) Man-1 1 HDR"chr(13)
"COB Date Date Man-1 2 YYYYMMDD"chr(13)
"Data Source Code Char (6) Man-1 10 i.e. "+CodeChar
Notice that I didn't work on the YYYYMMDD; I'll leave that for you to tackle for now. Hide the captured field from the report, and hide CodeChar.
Go to the Options, Export and Clipboard and turn off the option to export field names as the first row of output.
Create a new project export to output this field to a file named "Final Report.txt", fixed-length text, and set the overwrite option. Save the model and the project. Header.xmod and Header.xprj.
Now for the next project file, Body.xprj, use your existing model to output all of your data to the "Final Report.txt", fixed-length text, using the append option.
For the last project file, create Footer.xprj using your single word text file as the input source. Again, that value will be ignored.
In the Table window, create two runtime parameter fields, RecordCount and CodeChar. Now create a Memo calculated field with this formula:
"Trailer Control Record"chr(13)
"Rec Type Char (3) Man-1 1 TLR"chr(13)
"Data Source Code Char (6) Man-1 10 "+CodeChar
Set the option to not export the field names, and define a project export to again append to the "Final Report.txt" file.
That's the hard work done.
Now, nobody wants to jump through all of those hoops every time an update is required, so let's automate it.
Fire up Notepad, and assuming you stored all of these files in C:\mytest (edit the script below as necessary), paste in this bit:
start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:cobdate.xprj /pxall
start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:sourcecode.xprj /pxall
start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:recordcount.xprj /pxall
set /p sourcecode=<datasourcecode.txt
start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:header.xprj /set:codechar=%sourcecode% /pxall
start "Pass1" /wait "C:\program files\monarch\program\monarch.exe" /prj:body.xprj /set:codechar=%sourcecode% /pxall
set /p records=<recordcount.txt
"C:\program files\monarch\program\monarch.exe" /prj:footer.xprj /set:sourcecode=%sourcecode% /set:recordcount=%records% /pxall[/CODE]
Now save the file as "FinalReport.bat".
Provided everything's been built and each of the exports defined properly then executing the batch file should build your report for you. You may have to edit the program paths to suit your installation.
Each of the "set /p" commands creates a variable in memory using the values that are exported to the small text files. Those variables are then passed to Monarch using the /set command line parameter.
It is a number of steps, but the requirements you've laid out are somewhat complex as far as using Monarch goes, IMO.