Omit body & group same field twice

[LaRetta]

I have two examples where I would like a result and I've never been able to figure out a good way. Some of you are incredible on reporting so I want to check in for ideas please:

1) Can same report be used with or without a body? I create reports with many leading parts and then by sorting, it only groups and summarizing those parts. This is a great way to use the same layout for multiple reports. But if someone doesn't want the body, it seems I must duplicate the report and delete the body and maintain the two layouts. It doesn't sound like a big problem but I would rather keep it lean without the duplicity if possible.

2) Along those same lines, I have many leading parts for example:

• Source
  
• Category
  
• Type
  
• Year
  
• Category (new field?)
  
• Age
  

... but to allow maximum flexibility for the report, I want to add the same field again further down the line (example red). All I can think is to create a calculation of the field so that, even though they both will sort the same, FM will consider them differently and it will group them separately.

I'm just wondering if anyone has considered these ideas and if there are better ways of handling them?

[LaRetta]

Never mind. It was dumb to even think there might be a clever trick. A body is part of layout design and a report either has one or it doesn't. And reports group and sort by FIELD so if I want to include the same field again, I would need to create a calculation. And in that case, it would probably be less resource-wasteful to create the second report layout.

I'm still glad I asked. Have you ever thought something and YEARS goes by and then you find out there were much easier ways? I was just checkin'. :laugh:

[Rick Whitelaw]

I routinely find "legacy" code I wrote years ago for which there are much easier ways of accomplishing a task. I saved a Text Edit file of a calculation that started out as many paragraphs worth of code, reduced it to a third of that, then on the third iteration reduced it to perhaps six or seven lines of code! I learn, hopefully, as I go.

[comment]

Re #1: it's true the body part either is or isn't there; but if it contains only a calculation field - a sliding calculation field, that is - it could be reduced to zero size.

I don't quite understand #2: are you trying to re-use the same layout with a different sort order?

[LaRetta]

Sliding! For 'removing' the body dynamically, that will work if we can preview and don't need to modify in browse mode. Thank you! I'm glad I asked!

Michael said, "are you trying to re-use the same layout with a different sort order"

Yes, I want to provide User with checkbox where they can select their sort (group) order for summarizing by varying fields. It seems there are field groups that always sort in sequence. For instance, we always sort and summarize by Year and then by Quarter and then by Month (if we select those fields at all). Same with sorting & summarizing with addresses ... we group first by state then city. So there are usually only a handful of other fields which could be sorted in different ways; fields such as Type, Category and Gender.

This need to summarize in various configurations and groups mostly applies to large tables such as LineItems or Activities but the body is usually the same information and columns - the only difference is their grouping. I can use conditional formatting to display whatever I need for column headers etc.

Those unused parts don't hurt anything so actually we could put even more in different orders. This I learned from you that parts don't show unless they are sorted. So this file shows 4 different sorts (groups).

This is what I want to do without adding Source2 and Gender2. Checkbox omits body but then puts one into Preview of course. And yes the file is ugly. This is my test file and pretty is as pretty does. :^)

UPDATE: replaced with more functional file.

SortReportOrder2.zip

[LaRetta]

I routinely find "legacy" code I wrote years ago for which there are much easier ways of accomplishing a task.

LOL, it's like when I work all night on my whiteboard and then look at it in horror the next day when not a single word is legible. :-)

I learn but it seems my memory leaks back out my backside or something ... :*)

[Lee Smith]

LOL, it's like when I work all night on my whiteboard and then look at it in horror the next day when not a single word is legible.

What kind of wine are you drinking? LOL

[LaRetta]

Obviously Lee, I wasn't drinking the good stuff!

The more I play with this the more I like it but then I'm a bit strange. Those 'duplicate' fields (which are calculations) can be unstored and display values only when the second Source2 field is requested in script so they shouldn't bloat file size. Script name can be used to determine the sort order; I need to tie the pieces together for display, calculation, script name and sort (so User can specify selection and order in checkbox).

I've replaced the prior file since I've enhanced the functionality further. And I think I'd want to pretty-up the Groups using conditional formatting. I think I've talked myself into like the idea as long as those duplicate sort fields aren't going to dawg-down the solution.

I cannot tell you how many reports must normally be created to allow for different groupings of data by different parts to generate summary totals. Well, most of you know. Once the User determines group order then it is just a matter of finding the correct records. And maybe this will bite me in the behind yet but so far I think it has potential!

[comment]

I think you're walking a fine line between more fields or more layouts. IMHO, more fields will eventually grow to be the more complicated solution - for example, if you have GetSummary() calcs, you'll have to duplicate these too. But perhaps I am influenced by the fact that layouts have folders and fields don't...

[LaRetta]

Yes certainly a fine line. Also, the second occurrence would be unstored if I didn't display it unless referenced in the script. I could always just store the value but that would increase file size. I am not sure (if I had to choose) which of those two would be best choice - probably to go ahead and store it.

Well, I am dealing with some very complex reporting requirements which need to be grouped and summarized every way possible based upon four fields and it seems I will need a temp table anyway (version 11 and I need left outer joins). Now in the temp table, the 'duplicate group' seems to fit nicely since the fields are simple text1, text2, num1 etc placeholders and then I label them with merge variables. So I can take advantage of the secondary 'duplicate' fields quite easily and since temp table is unstored anyway, I do not see any added issues there, do you? That GetSummary() example was of course yours (a great technique I use often) and I would not count unique on a full string as in the example - that was simply for a test.

Thanks again for your clear-headed and logical perspective! :-)

[comment]

I do not see any added issues there, do you?

Other than speed, no - but that doesn't mean there won't be any, only that I can't foresee them (I am doing this in my head).

[LaRetta]

I routinely find "legacy" code I wrote years ago for which there are much easier ways of accomplishing a task.

LOL, this happens to me with code I wrote last week. The thing is ... we will never be as good today as we will be tomorrow so we might as well accept where we are right now ( learned from a friend and oh so true). Understanding that sure takes the pressure off. :-)