Novice Question

[Terry007]

I have a report that is in the following format (simplified) where A represents some text and the x's, y's, z's and t's represent numbers....

Being a novice, I selected the entire "area" as a detailed type and then selected each of the groups of numbers individually. The output form is not what I need (data extracted is fed into another app). I've shown the output desired at the bottom...can someone suggest how to achieve this?

Thanks



A

x1 y1
x2 y2
x3 y3
. .
. .
. .


B

z1 t1
Z2 t2
z3 t3
. .
. .
. .


Output table desired

A x1 x2 X3 . . .
A Y1 y2 y3 . . .
B Z1 z2 z3 . . .
B t1 t2 t3 . . .

[OllyInMunich]

Hello Terry,

That looks like a classic multi-column region. Define an append template for A & B. Then turn on Multicolumn region (Report window, template menu). Define 2 columns to fit. Define a detail template and select one and only one of x1 or y1.

There are other tricks to cope if the data doesn't line up in columns nicely, or has other anomalies. And to get your output, you might need some tricks with a sort order and then a summary, possibly needing a two stage process.

But yes, it is possible.

Best wishes,

Olly

[Terry007]

I was thinking about the multicolumn but I was concerned about selecting only one of the sets of data.....not sure how to deal with the other set? Would I create another template? How then to run both templates against the data and get the one set output with multiple rows as desired?

[Terry007]

I need a strategy for getting the desired row based output in one output file. Not sure how to make this happen.....never done multiple templates and had them work together to get a combined result...not sure that even works....help

thanks

Terry

[OllyInMunich]

Hello Terry,

Email me at olly@greenbar.info with the report file and I'll send you back an example model.

Best wishes,

Olly

[Grant Perkins]

Terry,

I would go with Olly's offer but in the meantime can you tell us whether the columns have any headings and the rows have any descriptions,

As it stands it looks a bit bare. If it is bare with no further helpful information for grouping purposes I think I would be tempted to avoid MCR and just extract each column as a block of text into a single field and then present that at an output or, if that is too 'raw', use a calculated field and probably the Textline function to create a new representation of the data via concatentation of each line. If the data are for distinct column (perhaps a name tag to the left of the row?) there would be no need to concatenate.

The thing is that the total report usually provides some help for this where as the data fields of interest, standing alone, often do not.

Now, these ideas probably move you well beyond the realms of 'novice' rather quickly but that is not unusual we find! Indeed it is almost normal if you look back through forum posts .... :)

Fear not, the individual parts that go to make up the whole solution are not difficult and having someone walk you through the steps will provide a rapid learning exercise that should illuminate a lot that is most likely currently looking a little dark to you. Life then gets a lot easier and a deal more interesting, or so many people remark.


HTH.



Grant

[Data Kruncher]

Greetings all,

As Grant suggested, I was able to develop a solution, albeit moderately inelegant but workable, without using MCR. If you had v10 available with its Column() function I suspect that this would be much easier, but alas such does not appear to be the case.

Here's the sample report I used:

A
1 4
2 5
3 6

B
7 10
8 11
9 12



Here are the steps:

Build a one line detail template, using Advanced End field on none of the above. I creatively named the fields Col1 and Col2. Make sure that they're Character fields.
Append your A/B Labels field.
This should net you 2 rows in the Table, based on your limited sample.


Now in the Table:

Create a new calculated Numeric field, Field1 with this formula:

val(textline(Col1,1))
Duplicate Field1 to create fields Field2 and Field3 changing the ",1" in the formula to ",2" and ",3" respectively.
Hide the Col1 and Col2 fields.
Define a project export which will export the table to an Excel file if you have Monarch Pro, or a text file if you have Standard. Elect to overwrite the file when the export is performed.
Save the model and the project files.
Run the project export.
Next up: handle the other column. Repeat the steps above, substituting Col2 for Col1 in the calculated fields. Replace the existing project export with one that appends data to the first export. Again, save this as a new model and a new project, and run the project export.

Now in a third Monarch model, open the text file and model it as you would normally, or better, open the Excel file as a database source (Pro version). Now, if necessary, go to the Table window and sort it based on the Label, and you're done; ready to export to its final destination. Save the third model and the third project.

My final Table window looks like this:


Label Field1 Field2 Field3
A 1 2 3
A 4 5 6
B 7 8 9
B 10 11 12


Of course in the real world, you'll have more than 3 fields to define to build it out horizontally. Keep in mind that Monarch only allows 255 fields in the Table, so after all this, it might not be a useful approach for you, and in that case it's back to the drawing board.

But if it works for your data, great. Make your life easy and automate the process with a little batch file which runs all three Monarch processes for you.

[Terry007]

Wow, Olly, Grant and Kruncher......thanks for all the help and suggestions. I'll spend some time this weekend with all of your suggestions and let you know how it goes.....thanks a lot for the suggestions and offers of help...you guys are the greatest!!

Terry

[Terry007]

Kruncher

I tried your suggested approach. It worked....however I have a couple of questions:

: after I exported the first col1, I saved the project and model as Test1 and opened it up again but modified the fields as you suggested and made an Export2 and ran it. After running it, I saved it as Test2.....

THEN TO TEST IT AGAIN:
I opened up Test1 project again and did the associated Export1, closed it and opened up Test2 and did the associated Export2...closed it and reviewed the file which was perfect.
1) IS THIS HOW YOU ENVISIONED Running it by having two projects each with a model and running one after another? or was there a simplier process where there is actually one project with two models?

Terry

[Data Kruncher]

Yes, this is a multiple pass solution, meaning that you'll need to run the projects one after another to get the desired end result.

A small batch file will let you execute this easily, something like:


"C:\Program Files\Monarch\Monarch.exe /prj:"C:\Projects\Test1.xprj" /pxall
"C:\Program Files\Monarch\Monarch.exe /prj:"C:\Projects\Test2.xprj" /pxall
"C:\Program Files\Monarch\Monarch.exe /prj:"C:\Projects\Test3.xprj" /pxall


A Monarch project file can only employ one model file. Thus the need for multiple projects.

[OllyInMunich]

Hello Terry, hello Kruncher,

Forgive me for butting in, but I'm a bit perplexed. This works OK if you have a known (small) maximum to the number of rows in your detail trap, as you only have to build val(textline(Colx,y)) for y values from 1 to n.

Surely using a one line detail for each row and MCR would be a more flexible approach, in case the number of rows varies next time the report is run?

Best wishes,

Olly

[Data Kruncher]

I certainly won't disagree Olly. I don't believe that I said that this was a particularly "good", robust, solution for potentially larger data sets.

[Terry007]

Kruncher/and others

Yes...I set up a batch file and it works great....thanks for all your all and thanks to all others that had suggestions and input here.

Terry

[Terry007]

Olly/Kruncher

Ok....I'll look into the more stable approach as well.....thanks for the feedback...

Terry

[Grant Perkins]

Olly/Kruncher

Ok....I'll look into the more stable approach as well.....thanks for the feedback...

Terry

Terry,

If you have something to the LEFT of the columnar rows that describes what they are (most reports would) that the MCR can be a much more elegant solution (as Olly points out) for reports with variability.

On the other hand if there is nothing much presented to help ID the row (or indeed the columns) and the number of rows has a know limit and order (if appropriate), both of which can be 'hard coded' into a monarch model then making it visual and simple may suffice for your purposes.

Both approaches are valid and useful to have in the Monrach User Toolkit. Whichever way you go you are winning by learning bit do test for the likelihood of change or divergence nullifying the strategy you adopt. Inputs can be very capricious things.


Grant