Open Forum

Expand all | Collapse all

Multi-Tab Excel Export

  • 1.  Multi-Tab Excel Export

    Posted Jul 20, 2020 08:23 AM
    Hello,

    I am currently creating a consolidating set of financials for my company.  There are 3 regions and 10 entities within each region.  The problem I am running into is when I export the report to Excel, there are natural page breaks I can't seem to get rid of because there are too many columns to fit onto one page.  I was wondering if there is any way to remove these page breaks and insert my own where I would like, but if not, is there a way to create a report that generate the first 10 entities on one tab of an Excel document, the next 10 entities on another tab, and the last 10 entities on a third tab?  Thanks in advance!

    #Functional #Accounting​​

    ------------------------------
    Evan Ross
    CCI
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Multi-Tab Excel Export

    GPUG ALL STAR
    Posted Jul 20, 2020 12:20 PM
    @Evan Ross,

    I'm trying to picture where you're exporting from. Is it Management Reporter?

    Also, you said that "​there are too many columns to fit onto one page." Are each of the 10 entities per region arrayed across the page horizontally?

    If you're using Management Reporter, you ought to be able to create a Reporting Tree with your three regions as "branches" on the tree. Then when you run the report, you can export it into Excel and have each of the regions on separate Excel pages.

    As far as page breaks in Excel, you're seeing the "natural" page breaks because your default page size and orientation is probably 8-1/2" x 11" (if you're in the U.S.) and portrait orientation. You can change the page width (page "breaks" refer mostly to the page length) by reducing the font size, changing the page orientation, and (if your printer is capable) the page size to legal paper or tabloid-sized paper.

    Does this help?

    Sincerely,


    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Multi-Tab Excel Export

    GPUG ALL STAR
    Posted Jul 20, 2020 11:04 PM
    Evan,
    >>  Is there a setting I have to select to have each branch print to a separate tab in Excel? <<
    I just output one of our Income Statements to Excel from Management Reporter. Here's what
    the "Export to Microsoft Excel" dialog box looks like:
    I think that the one that's giving you grief is the "Export to single worksheet". I've
    got that checkbox UN-checked. Management Reporter exported each of the Tree
    nodes to a separate tab in the workbook:
    Here's what (part of) our Tree looks like:
    What does your Reporting Tree look like? If you've got it organized as I suggested in
    my first message, you'd have a separate tree branch for each Region, and thus, each
    Region would take up its own tab in Excel... as long as you UN-check "Export to single
    worksheet"!
    >> I will play with the page settings and see if I can get the system generated page
     breaks to go away <<
    Management Reporter isn't the most flexible when it comes to layout and formatting.
    Since you're exporting it to Excel anyway, you might as well take advantage of the
    formatting options there.
    I know, I know... it would be better if the output in Excel matched the output in Mgmnt.
    Reporter! Good luck with that! I don't mean that in a mean way... it's just that font
    settings in Management Reporter just don't translate perfectly into Excel. You're going
    to continue to be frustrated, I guarantee it!
    You might try setting Management Reporter to output to legal-sized paper. At my last
    company I made a quite complicated Income Statement that I formatted for 11"x17".
    I'm going to post this on the Forum page, too, so that others may see our chat.

    On Mon, Jul 20, 2020 at 11:44 AM Evan Ross via
    Dynamics GP User Group (GPUG) <Mail@connectedcommunity.org> wrote:

    Message From: Evan Ross

    Hi Steve,


    This is my first post in GPUG, and I am fairly new to the Great Plains/Management Reporter software so please forgive my unclear description.  I built a set of financials in Management Reporter, and need to export it into Excel each month (pictured below).



    I would like to have 
     a page break instead of where I have the fill column, but Management Reporter inserts a page  break between entity 7 & 8 of region 1.  Ideally, I would like to have a page break instead of the  fill column.  I will play with the page settings and see if I can get the system generated page  breaks to go away, but regarding the Reporting tree, I already have one set up, but it all prints  to the same tab.  Is there a setting I have to select to have each branch print to a separate tab in Excel?
    ------------------------------
    Evan Ross
    CCI
    ------------------------------

    Regards,


    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Multi-Tab Excel Export

    Posted Jul 21, 2020 09:37 AM
    Steve,

    I do not have the Generate to single worksheet option "checked".

    I also believe I set up my Reporting tree correctly, but this was the first time I have ever created a tree so there's potential I messed something up there.
    Do you see something I am missing?

    Evan



    ------------------------------
    Evan Ross
    CCI
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Multi-Tab Excel Export

    GPUG ALL STAR
    Posted Jul 22, 2020 12:05 PM
    @Evan Ross,

    Based on the layout of your Reporting Tree, I would have expected MR to have exported each entity to a separate tab in the Excel workbook.

    Could you post a screen shot of your Column Definition? If you've placed each Entity into a column, then I think the Reporting Tree might conflict with your Column Definition. It would also help to see a screen shot of your Row Definition.

    That is, typically when you create a Reporting Tree like the one you've created, the Column Definition is "generic" and only shows data for a single Entity at a time. The "rollup" Tree segments that represent the Regions would sum all of the activity for all of the Entities for that Region into a single column, not x columns, one for each Entity. Similarly, the Summary branch of the Tree would total both Regions into a single column.

    That's why I want to see the Column and Row Definitions so that I can see what you're attempting.

    (I'd also put a call to @Noah Moseley and @Derek Krebs at MSX Group. They are the most facile Management Reporter authorities around.)

    What's happening here, I think, is that your expectation of how Management Reporter handles your Entities within Regions doesn't jibe with the way you want it to come out in Excel. Just a guess: it looks like the mockup you sent me Monday at 11:44 is from Excel, yes? That's the way you want it to look.

    Sincerely,

    ​​​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Multi-Tab Excel Export

    Posted Jul 24, 2020 09:41 AM
    Steve,

    You are correct.  I created my column definition with 1 column for each entity.  Can you please share a screen shot of how the column definition should look?  I have also reached out to Noah Moseley as you suggested, but if the issue is just the column definition, it might be a quick fix if I can see how a proper column definition is set up with the reporting tree.  Thanks!

    ------------------------------
    Evan Ross
    CCI
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Multi-Tab Excel Export

    GPUG ALL STAR
    Posted Jul 24, 2020 11:33 AM
    @Evan Ross,

    That's a bit of a tall order! ​I don't have reports set up that are analogous to your prototypes.

    The main thing to note, IMHYDAO (In My Humble Yet Deadly Accurate Opinion), is that your Reporting Tree includes all of the Entities for each of your Regions. It's laid out just like the Reporting Tree sample I posted that we use for our Income Statements. However, the Column Definition also includes all of the Entities as individual columns. So what will happen is this: If you run the report, the "Summary" branch will show all of the Entities in their proper columns. Currently it'll spill over the page width, but let's leave that for the time being.

    If you choose one of the "Region" branches you'll see all of the Entities for that Region, but you'll also see blank columns for the Entities in the other Region(s).

    If you choose one of the "Entity" branches, you'll see the numbers for that Entity, but all of the other Entity columns will be blank.

    To go back to one of your other questions about why you only see one tab in the Excel export, I believe it's because you didn't "Select All" when you Generated the report.

    My feeling is that you could safely eliminate all of the Entity branches in your Reporting Tree while keeping all of the Entities intact in your Column Definition. That would be a place to start.

    Sincerely,

    I think

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Multi-Tab Excel Export

    Posted Jul 29, 2020 10:11 AM
    Hi Steve,

    I believe I figured out my problem.  I had to adjust the settings in my report definition to include more levels down from my starting unit.  Once I did that and exported to Excel, I had a workbook that had a summary tab, and one tab for each parent level of my reporting tree.  I am still having trouble with the page size on each tab (I tried legal and it still isn't big enough haha), but this was definitely a big step in the right direction to get the end result I am looking for!  Thanks for the guidance!

    Evan

    ------------------------------
    Evan Ross
    CCI
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Multi-Tab Excel Export

    GPUG ALL STAR
    Posted Jul 29, 2020 10:43 AM
    @Evan Ross,

    >> I had a workbook that had a summary tab, and one tab for each parent level of my reporting tree. <<​

    OK! That's the way it's supposed to work!

    >> I am still having trouble with the page size on each tab (I tried legal and it still isn't big enough haha) <<

    Page size in Management Reporter doesn't translate into Excel; I think mainly because Excel has its own notions of what page widths, etc., default to in a new workbook, which is what you're creating when you export an M-R report to Excel. I don't think that M-R exports printer settings.

    Good to see that you've made progress, though!

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Multi-Tab Excel Export

    Posted Jul 29, 2020 11:16 AM
    Steve,

    Now that I have the multiple tabs, I have a new question, and I was wondering if you might be able to at least tell me if it is possible.  The first tab of my workbook has all the location and entities side by side as my original report had it, and then each tab after that has one location with all the entities associated with that location.  Is it possible to have the first tab show each location rolled up as in just show the location as a sum of all the entities, but does not show the entity within each location?  Then each tab after would have the location and still show the entities associated with it.

    First tab rolled up:

    Location specific tab:


    ------------------------------
    Evan Ross
    CCI
    Chicago IL
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: Multi-Tab Excel Export

    GPUG ALL STAR
    Posted Jul 29, 2020 11:35 AM
    @Evan Ross,

    I don't believe that this can be done in a single M-R report. It's your Column Definition that's the kicker.

    How would you feel about exporting a few different reports with alternate Column Defs?​ One ColDef would have columns just for your Locations, and all the Entities would roll up under each one. Another ColDef would only show one Entity; which one would be determined by your TreeDef. Another would have all the Entities for one of your Locations. Another would have all the Entities for the other Location.

    As I said, M-R cannot shuffle columns around once you've picked a Column Definition for a report. But you can simulate what you want by creating multiple reports that use different Column Definitions.

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: Multi-Tab Excel Export

    Posted Jul 29, 2020 11:39 AM
    Steve,

    If I did this, then I would be able to create a report group, and run the group once to get all the reports in the group correct?

    Evan

    ------------------------------
    Evan Ross
    CCI
    Chicago IL
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: Multi-Tab Excel Export

    GPUG ALL STAR
    Posted Jul 29, 2020 11:49 AM
    @Evan Ross,

    Yes! I'm not sure if you could get automatic Excel exports for each one, though. It's been a while since I've worked day-to-day with M-R.

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 14.  RE: Multi-Tab Excel Export

    TOP CONTRIBUTOR
    Posted Jul 29, 2020 01:09 PM
    Hi Steve. Hi Evan.

    All report tiles that display in the web browser for a Report Group can be exported to Excel all at one time. They will export to a single Excel file on different tabs.

    ------------------------------
    Noah Moseley, MSX Group Senior Consultant
    Academy Instructor for Management Reporter
    Noah.Moseley@MSXGroup.com

    View our GPUG Partner MSX Group listing: https://www.gpug.com/engage/findacompany/companyprofile?UserKey=275d1f3a-73b4-4a58-b0d8-7395d682dbc3
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 15.  RE: Multi-Tab Excel Export

    GPUG ALL STAR
    Posted Jul 29, 2020 02:19 PM
    @Noah Moseley,

    Now that is useful to know!​ I stopped using the web viewer sometime back because printing was ... problematical.

    Regards.

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of June 10, 2020
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 16.  RE: Multi-Tab Excel Export

    TOP CONTRIBUTOR
    Posted Jul 22, 2020 12:18 PM
    Hi Evan.

    You have a lot going on here. Email me and we can look at this together online and then post the solution here on this post. Thanks.



    ------------------------------
    Noah Moseley, MSX Group Senior Consultant
    Academy Instructor for Management Reporter
    Noah.Moseley@MSXGroup.com

    View our GPUG Partner MSX Group listing: https://www.gpug.com/engage/findacompany/companyprofile?UserKey=275d1f3a-73b4-4a58-b0d8-7395d682dbc3
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role