6 Replies Latest reply: May 15, 2014 10:08 AM by Data Kruncher RSS

    Monarch 10.5 Excel Add-In

    xboxremote _

      Hello all,

       

      I posted this message last week in the thread regarding the release of the new version of Monarch. I have to get a reply, so I figured I would re-post it in its own thread. Any help would be very greatly appreciated, as I'm reaching a proposal deadline, and I'm not finding a clear answer elsewhere. Thanks!

       

      So this whole Excel Context Add-In only works when you export from the table view in Monarch? I'm pushing to get this update at my work because of this add-in, but it won't be very useful if this is the case. Unless I'm just understanding it incorrectly.

       

      I understand that seeing "summarized" data with the context add-in would be useless because often times you're summing or counting or doing some sort of function on the data (therefore, you cannot pinpoint one point in the report from which a particular number comes from). However, at my work, we use the summaries for a different purpose:

       

      We have reports for a 12-month period from which we grab all sorts of data. However, no one here (or anywhere, I'd imagine) likes looking at data with the date as a field itself. If you want to analyze a number throughout a fiscal period, you would have to find 12 different rows within the table. Even with sorting, it's still a pain. So, using summaries, we pivot (crosstab) that data, so that each month is across the top as their own fields, and each row contains 12 months of data, one for each field kind of like this...

       

      ACCOUNT # | ACCOUNT NAME | REVENUE | OCTOBER | NOVEMBER | DECEMBER | JANUARY |

      1234567        Xboxremote         $1,500.20   $1,200.20   $987.86      $800.00       $765.20

      5382968        Data Kruncher      $45,201.50 $43,110.58  $40,080.90  $39,399.21  $35,521.50

      /QUOTE

       

      (That didn't turn out as well as it looked when I was typing it, but you get the idea)

       

      However, we don't actually SUM anything. There is only one occurence of each month per ACCOUNT/ACCOUNT NAME combination. Everyone here is very familiar with Excel, so we export our summaries to Excel for analysis, and for uploading to our databases. So, while they are analyzing the numbers in Excel, I want them to be able to click a button that will take them to the exact number they are looking for in the source report. I was hoping this was the solution, but it doesn't appear to be, unless somebody has an idea... Any ideas? The help is very much appreciated. And again, I may just be misunderstanding, so if I am and someone could explain further, that would be perfect. Thanks!

        • Monarch 10.5 Excel Add-In
          Data Kruncher

          So this whole Excel Context Add-In only works when you export from the  table view in Monarch? I'm pushing to get this update at my work because  of this add-in, but it won't be very useful if this is the case. Unless  I'm just understanding it incorrectly.[/quote]

           

          100% correct. And only to brand new Excel 2007+ file formats. No appending to existing workbooks.

           

          I understand that seeing "summarized" data with the context add-in would  be useless because often times you're summing or counting or doing some  sort of function on the data (therefore, you cannot pinpoint one point  in the report from which a particular number comes from).[/quote]

           

          Again, exactly correct.

           

          However, we don't actually SUM anything. There is only one occurence of  each month per ACCOUNT/ACCOUNT NAME combination. Everyone here is very  familiar with Excel, so we export our summaries to Excel for analysis,  and for uploading to our databases. So, while they are analyzing the  numbers in Excel, I want them to be able to click a button that will  take them to the exact number they are looking for in the source report.[/quote]

           

          Ideas... I'd begin with the mandatory Table export using the Context option to an .xlsx file. Then add the summary export to that file. Use project exports to manage the proper sequence.

           

          Now I haven't actually tried building any of this, but...

           

          For the summary, remember that you can include hidden key fields. I'd use a duplicate of the Account Name as the first key field, and set it to hidden.

           

          Now for the pie-in-the-sky, dreaming part...

           

          You'll notice that the hidden column in the summary actually does get exported to Excel, but it's (correctly) formatted as a hidden column.

           

          I'd create a macro, not within this Monarch-managed workbook, but in your Personal Macro Workbook, that makes hyperlinks out of those hidden account names, so that when the user clicks on a name it moves them to the proper row in the Table export, at which point they can activate the Monarch Context Add-in.

           

          The first step would be to invoke the hyperlink-creating macro by clicking an icon in the Quick Access Toolbar (which in turn is associated to the proper macro). Then it would unhide the hidden Account Name column and create the hyperlinks.

           

          Again, just an idea.

           

          Kruncher

           

          PS I don't think that I've been sample data before. :cool:

            • Monarch 10.5 Excel Add-In
              Data Kruncher

              In retrospect, the whole duplicate/hidden account name isn't necessary, as you could just make the regular account name into links.

               

              I was thinking more along the lines of another distinct key value that wasn't part of the summary fields, but again, in this case it doesn't sound like that's necessary.

                • Monarch 10.5 Excel Add-In
                  Data Kruncher

                  Further thoughts...

                   

                  Where you have multiple records for activity in multiple periods for a given period, you could make the Summary values themselves links to the proper Table row, instead of the account name value.

                    • Monarch 10.5 Excel Add-In
                      xboxremote _

                      Wow! Thanks so much for the answers and ideas, Data Kruncher. I appreciate it very much. I'm not quite yet a Monarch guru, so I only understood parts of your proposed solution, but I think I get the general idea. The only thing I guess I don't understand is the hidden field part.

                       

                      I have never actually exported anything with a hidden field from Monarch (assuming I can in version 8?). And then I'm not sure why exactly I would need it (why would I duplicate a field and hide it, when I already have that field shown?). That did give me an idea, which is very heavily based on the rest of your idea...

                       

                      So I could export the data directly from the table view. This table would be linked to the original reports from which the data came. Then, I build a summary of that data using Access (either through the use of a crosstab query, or since I've found crosstab queries to be excruciatingly slow, my own query that accomplishes the same thing but more efficiently). Then I can export that summarized table into a new Excel file. This new Excel file would contain a macro that, when activated, would pinpoint in the original table whichever number the user has selected in the summarized table (using a multi-part field identifier). After pinpointing it in the original table, the macro would activate the Excel add-in which would pinpoint it in the original report. I could even throw in some error handling, just in case there was another occurence of that multi-part field combination.

                       

                      Whew -- So is my idea too far off? Is it even possible (for example, to activate the add-in through use of a macro)? Do you think this will be too sluggish? Any opinion or suggestions would be greatly appreciated, as would an explanation of the hidden field thing. Thank you!!!

                        • Monarch 10.5 Excel Add-In
                          xboxremote _

                          PS - I'm glad you enjoyed your moment in my sample data

                            • Monarch 10.5 Excel Add-In
                              Data Kruncher

                              Then, I build a summary of that data using Access[/QUOTE]

                               

                              No, there's no need to do that. Use Monarch's Summary window You need at least one defined Key field, then an optional Item field (many people rarely employ Item fields), and then at least one Measure field (actually, Measure fields are optional too).

                               

                              Creating a single key summary field provides the same result as a "SELECT DISTINCT MyField FROM MyTable;" SQL clause. This is a terrific way to be able to populate lists that will be used by Excel's data validation feature on other worksheets.

                               

                              Then I can export that summarized table into a new Excel file.[/QUOTE]

                               

                              Again, the right idea, but the wrong execution.

                               

                              Export from the Summary window to the same Excel file that you created by exporting the Table window using the Context feature. Then you'll have it all in one place, and it'll be much easier to create those links to the worksheet with the Table data.

                               

                              I could even throw in some error handling[/QUOTE]

                               

                              I like how you think. :cool:

                               

                              Do you think this will be too sluggish? /QUOTE

                               

                              I really doubt it. Well mannered VBA code can run amazingly quickly. Use the best data types when declaring variables and use the object model to your best advantage. You'll be shocked at how well your own creations execute.

                               

                              an explanation of the hidden field thing[/QUOTE]

                               

                              I think that the ability to hide a key field in summary may not have been introduced until Monarch v9. My memory is a bit fuzzy on v8 features now.

                               

                              My favorite thing to do with hidden fields is use them to essentially enable a custom sort of the next key field. Experienced, OK, that's a bit restrictive, let's say "some" Monarch users know that you can use the "Specified values" feature on the Matching tab in conjunction with the Sort tab's "Position in Specified Values List" to create custom sort orders, but that can take too much hands on management.

                               

                              In my typical usage, I want to list the various Key operations groups, and then report the "Corporate" group last in all cases. So in the Table, I use a lookup calculated field named MasterSort to give the Corporate group a sort value of say 999 and the others 1, 2, 3, etc.

                               

                              Then when building summaries, the MasterSort becomes the first key value, but it's a meaningless value in and of itself, so I hide it from view, but sort it numerically. Then the regular Operations groups appear in the right order, and the Corporate group is always last. No hand-holding required.