8 Replies Latest reply: May 15, 2014 10:13 AM by Grant Perkins RSS

    Value Based Exports

    elginreigner _

      Most of the projects/models I set up are completely custom, and usually cannot be reused. I have started to standardize data I return back to our clients, currently the list is small of about 10 clients. This list is about to get large.

       

      My current setup is a filter per client and an export per client. I call the model from a batch file to run all exports (/PXALL). This will be time consuming to open each, add a new filter and export. I could use advice on setting up the model to create an export per value in a certain field and the preferred method to achieve this.

       

      With that said, It needs to be as effecient as my current setup. With my current setup of process all filters at once, instead of one after the other, had cut the process time in half.

       

      Open to all advice and ideas.

        • Value Based Exports
          Olly Bond

          Open to all advice and ideas.[/QUOTE]

           

          Hello elgin,

           

          You don't need to define separate exports, and in fact you may be able to avoid separate filters as well.

           

          You can define one project export, from the table, which exports based on "all filters", and generates either one Excel file with each filtered data set on a different worksheet, or a separate Excel file for each filter.

           

          For this approach, you'd still need to have a filter for each client.

           

          If you can export from the summary, you could build the Client ID as the first key field, and then export the summary so that it breaks the data based on each change in the value of the first key field.

           

          Again, this can be per worksheet or per file. For this approach, no filters are required.

           

          Hope this helps,

           

          Olly

            • Value Based Exports
              elginreigner _

              I like the 2nd option, much less work for me. I can handle all of the file placements via scripting.  I've never tried, but can I have a key field without showing/exporting it?

                • Value Based Exports
                  Olly Bond

                  Hi elgin,

                   

                  Sure - just double click the field in the summary, and set it to "Hide the Key Field". I think it's in the Display tab of the dialog. Watch out for values like "Betty's Music Store" - in Excel, the worksheet with that value would be called "Betty_s_Music_Store". But if you have nice client ID's like "01234" you should be fine.

                   

                  Best wishes,

                   

                  Olly

                    • Value Based Exports
                      elginreigner _

                      This excellent news. Yea, my key values are very basic, just 4 letters.

                        • Value Based Exports
                          Joe Berry

                          I use this option alot.  For departmental summaries it is a great tool.

                            • Value Based Exports
                              elginreigner _

                              I completed this setup, it looks great. However, it won't work for what I need it to do. No big deal , as I have other tasks it will work great on.

                               

                              Issue why it wont work: In the files, I need the tabs to be created even when the data is empty. If there are no values, they key field wont change and wont create the tab.

                                • Value Based Exports
                                  Olly Bond

                                  Hello Elgin,

                                   

                                  Thanks for the post- it's given me the next blog post idea - how to handle situations where you want the header in the summary even though the details are missing.

                                   

                                  Things are a bit hectic at the moment, so it might take a couple of weeks, but I'll gt something up there. In the meantime, you might get some joy in creating a clone report that contains zero value records for all the key values you need, and opening this report in tandem wth your data.

                                   

                                  Best wishes,

                                   

                                  Olly

                                    • Value Based Exports
                                      Grant Perkins

                                      As Olly has suggested one good way to work with reports that don't always have all the  values that might be available is to create a 'dummy record' file that does have all the values and use that as the first 'report' so that all expected fields are populated.

                                       

                                      As an alternative, depending on the nature of the report and its data, it may be possible to pre-define the fields in the model and have them always appear that way.

                                       

                                      I think we had some examples some time (years) back that involved concatenating a number of reports that had variable data content by adding a single record 'file' at the top of the report 'stack' so that Monarch always had a complete format to work with. As a by-product that dummy record also became the pro-forma structure to be changed if the data contents changed in any way (or, of course, or use with other similar reports).  It also offers the benefit of being useful for setting field sizes for reports where the output is modified at run time according to maximum number of field data fill characters. The dummy allows the known data structure sizes to be always defined.