Forum Discussion

darpan13's avatar
darpan13
Contributor
3 years ago

Table View - Tab Addition Automation ??

SOURCE: ONESTREAM CHAMPIONS

Hi Team,

Greetings for the Day.

We have a requirement to download the registers data into an Excel Book with all Account as different Sheets and that too from within one stream.

One STream table view is the best option provided to fetch the data from one stream.

The question here is we have 100+ Accounts in there, which we need to bring in the tabs, and to achieve that we need to add all tabs 1 by 1 under Spread Sheet - Table view (Refer to attached screenshot).

Is there any automatic way to append the Table view with all accounts tab names using some macro or inbuild One Stream Functions?

 

 

image.png1114×546 39.1 KB

 

 
Excel Add In
  • JohnMorehead's avatar
    JohnMorehead
    New Contributor III
    3 years ago

    Darpan, I don’t think that Tables Views are actually your best option. You’re probably best off using an Extensibility rule to create an excel document of the exact format you desire, that way it will be flexible about the addition of accounts in the future, but give you control over the formatting of the data as necessary as well.

    image
    image.png1530×758 75.8 KB

     

    This is a screenshot of some example code for creating an Excel document with separate sheets for each data table in a data set. If you need more info, just let me know!

    • JohnMorehead's avatar
      JohnMorehead
      New Contributor III
      3 years ago

      Oh, and you’ll need to paste this into you “Referenced Assemblies” line on the BR properties.

      C:\Program Files\OneStream Software\OneStreamAppRoot\OneStreamApp\bin\DocumentFormat.OpenXml.dll; C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\WindowsBase.dll

      • ckattookaran's avatar
        ckattookaran
        VIP
        3 years ago

        If a choice is given I would’ve gone with EPPlus than OpenXML. That thing is convoluted. Now, to answer your question Darpan Table View cannot do that. If and only IF books support a table view query then it was easier to do this.
        For now as John mentioned you need something that can create an EXCEL file. My choice is EPPlus (it is an external dll). You could use interop however, that means you need EXCEL installed and it is slow.

    • sudarshan's avatar
      sudarshan
      New Contributor III
      3 years ago

      Hey John,

      I can see you are using Visual Studio to write rules, do you mind sharing how to write rules in visual studio I find the br editor a bit cumbersome.

      Thanks,

      Sudarshan

      • JohnMorehead's avatar
        JohnMorehead
        New Contributor III
        3 years ago

        Yeah, so the first thing is you need to get the DLLs or Assemblies for the OneStream API. I get these off the file system when I install a server, like on a VM or personal machine. Once you have those, you simply add them as references in your project, and you should get the full intellisense you need. You can't actually compile or debug rules in VS, it is just a Code Editor, not a full IDE unfortunately. But I still find a ton of value in it! Also, you can easily use Visual Studio Community, you don't need a full license for the work we do.

    • JennyCalvache's avatar
      JennyCalvache
      New Contributor III
      2 years ago

      Hello John, Is it still possible to get those codes you wrote? I am very curious to see and use it.

      Thanks!

      • JohnMorehead's avatar
        JohnMorehead
        New Contributor III
        2 years ago

        What code exactly do you want to see? The code where I generated an Excel document based on a datatable object, using OpenXML?

  • JennyCalvache's avatar
    JennyCalvache
    New Contributor III
    2 years ago

    I would like to know how to create an Excel document using a data table in BR. When I used a table view from Excel, it was too slow since the table view has too many rows and columns. Basically want to see the rest of the codes you posted.

    Thanks!

    • JohnMorehead's avatar
      JohnMorehead
      New Contributor III
      2 years ago

      Sent you the link to the file in a PM. Please let me know if you have any questions 🙂