8 Replies Latest reply: May 15, 2014 10:14 AM by Olly Bond RSS

    Hide columns based on total

      I’m trying to create one model that will work for all our customers invoices. Not all customers have the same fees. I would like the summary model to hide the columns that have a total of $0.00. How do I do that?

        • Hide columns based on total
          Olly Bond

          Hello magdah and welcome,

           

          I'm trying to understand exactly what you need - is it, using Lesson11.xmod and Classic.prn as an example, that you want to export a summary of sales for each account, with an across key for media, and that for an account where there are no sales of CDs, or TAPEs, that column should not appear?

           

          If you make Account the first key field and export the summary broken on each change in the value of Account, I think you will still get all the columns. But if you make filters for each account (e.g. Account="Betty's Music Store") then if Betty didn't sell any LPs then there won't be a column headed LP in the summary.

           

          It's a pain if your Accounts change frequently, or if there are more than 254 (the maximum number of filters). Perhaps a future version of Monarch would allow an option on burst summary exports to hide zero sum columns?

           

          Best wishes,

           

          Olly

            • Hide columns based on total

              Thank you for your fast response.

               

              Using Lesson11.xmod and Classic.prn it would be like looking at the summary report and even though there are no sales of CDs, for any customers, that column still shows unless a filter is applied to limit it to one account. I would like that field hidden if there are no sales of a particular media type.

               

              For my purposes, each customer has its’ own report, which includes a list of employees.  I want to use one model for all, or possibly, many of them. All the customers may have Fee A and Fee B, but not all of them have Fee C. I'd like the Fee C column not to show on customers that don't have that fee.

                • Hide columns based on total
                  Olly Bond

                  Hello magdah,

                   

                  Annoyingly, DataPump and Monarch Server would both let you solve this problem - out of the box - in different ways. But with desktop Monarch you can still get a grip on it using File() information.

                   

                  I'm not in front of Monarch right now, but will reply in more detail tomorrow.

                   

                  Best wishes

                   

                  Olly

                    • Hide columns based on total
                      Olly Bond

                      Hello magda,

                       

                      Sorry for the delay. I've opened Classic.prn and created three text files: Betty.prn, BigShanty.prn and Bluegrass.prn.

                       

                      Using Lesson11.xmod, I get a summary of all three that looks like this:

                       

                      CUSTOMER          SHIP DATE     CD     DVD     LP     SACD

                       

                      Betty's Music Store     2010-04-10     119.34     53.91          

                      Betty's Music Store     2010-04-21     233.71     35.94     64.74     

                                                

                      Big Shanty Music     2010-04-05     147.45               

                      Big Shanty Music     2010-04-14     10.78     29.95          86.31

                                                

                      Bluegrass Records     2010-04-10     122.34     53.91          71.92

                                                

                      Total                         633.62     173.71     64.74     158.23[/CODE]

                       

                      What we're looking for is:

                       

                      CUSTOMER          SHIP DATE     CD     DVD     LP

                       

                      Betty's Music Store     2010-04-10     119.34     53.91          

                      Betty's Music Store     2010-04-21     233.71     35.94     64.74[/CODE]

                       

                      ...that is, the export for Betty.prn should not show the SACD field as there are no values.

                       

                      By default, bursting the summary to export based on each value of the first key field gives us:

                       

                      Customer                Ship Date             CD           DVD            LP          SACD

                      Betty's Music Store     10/04/2010        119.34         53.91           ---           ---

                      Betty's Music Store     21/04/2010        233.71         35.94         64.74           ---[/CODE]

                       

                      But if you create a filter that just selects Betty's records, and another that just selects Big Shanty's, and another that just selects Bluegrass, and then define the project export to export the summary based on "all filters", then you get:

                       

                      Customer                Ship Date             CD           DVD            LP

                      Betty's Music Store     10/04/2010        119.34         53.91           ---

                      Betty's Music Store     21/04/2010        233.71         35.94         64.74

                       

                      Total                                     353.05         89.85         64.74[/CODE]

                       

                      This works, as long as you have a small list of customers, (max 250 or so), and they don't change much.

                       

                      Is this enough for your report(s)? If you need something that will cope with more than 254 values, then the filter-based approach won't work. I'm sure that the scripters around here will have ideas that could help in that case...

                       

                      Best wishes,

                       

                      Olly

                       

                        • Hide columns based on total

                          I guess I'm not sure how to apply this. We administer medical insurance plans. We create reports, invoices, for each company we work with. These invoices show a list of their employees and what fees are being charged for each employee. For example, an employee with a family pays more than an employee by themselves. Some of our companies have life insurance, some of them don't. What I want is that the column with life insurance fees is surpressed when the company doesn't have life insurance and therefore the column is all zeros.

                            • Hide columns based on total
                              Olly Bond

                              Hello magdah,

                               

                              It might help if you could send me some samples and also specify how automated you need the process to be. There's one very crude trick of setting colouring so that zero figures are shown in white text on a white background - if you are exporting to PDF or XLS this might help, but you might need to avoid exporting the column headers.

                               

                              Best wishes,

                               

                              Olly

                                • Hide columns based on total

                                  That sounds wonderful. How do I send it to you? I need to modify the data so that I'm not sending real people's information.

                                    • Hide columns based on total
                                      Olly Bond

                                      Hello Magdah,

                                       

                                      There's a useful macro in a spreadsheet called scramble.xls kicking around - DataKruncher and SteveCaiels have both contributed to versions of it. If you email me your address I'll send it to you, then you can replace all the letters with random letters and all the numbers with random numbers while preserving the spacing.

                                       

                                      Best wishes,

                                       

                                      Olly