5 Replies Latest reply on Nov 6, 2013 8:16 AM by ChrisG

    Dynamic HTML crosstab report

    ChrisG

      Dear FMP gurus

       

      I have been trying with not much success to create a dynamic html crosstab report. I have found some samples and discussions on the net, but at this point it is a bit beyond me.

      I need to build a crosstab report with the following data:

      Y Axis = Customer names

      X Axis = Product names

      Chart = Quantities sold to each customer

       

      All of this data resides in a single table called "distribution"

       

      Up to now I have managed to create the X and Y axis, but getting the quantities to line up with the correct customer / product pairing has got me stumped.

       

      Any help would be greatly appreciated.

       

      Thank you.

       

      Chris G

        • 1. Re: Dynamic HTML crosstab report
          Mike_Mitchell

          Chris -

           

          From your description, I can't be 100% certain, but I suspect the difficulty is coming from cases where a particular customer did not buy a particular product. In such a case, you wind up inserting the value for the next product he did buy in the wrong cell - and the error cascades down through the rest of the table. Am I right?

           

          Assuming I am, what you'll need to do is assemble a return delimited list of all products. You can do this using ExecuteSQL or some other convenient method. Then, as you loop through the records, use an indexing variable to compare the current cell position against the position in the product list against the current record. If they don't all match up, then you'll need to insert a blank cell. I usually use the nonbreaking space (&#nbsp) character because I find it preserves my table cell borders. Of course, you are free to do whatever you like.

           

          HTH

           

          Mike

          • 2. Re: Dynamic HTML crosstab report
            ChrisG

            Hi Mike, thank you for your help.

             

            Yes, you've guessed exactly right. If, for example there are 5 products and 20 customers in my distribution list, I arrange the customers alphabetically in the Y axis and the products alphabetically in the X axis.

            The problem as you correctly pointed out is that customer A bought products 1 and 5 which means there should be a non breaking space for products 2, 3 and 4. In my distribution list, customer A will appear twice because he bought 2 products. Customer B could appear 4 times if he bought 4 products, etc. I am storing all my data in variables using the list function. I need some clever mechanism to correctly line up the data when creating my html code.

             

            Chris

            • 3. Re: Dynamic HTML crosstab report
              Mike_Mitchell

              As I said, create a list of all the product IDs. As you proceed down each customer's list of IDs, compare the current ID against the ID of the current column number. If they match, great; insert the appropriate data. If they don't, insert a blank space.

               

              In other words, loop over the list of product IDs using an incrementing variable. Have a second increment that only increments if you have a match. Otherwise, the current position in the customer's product list stays the same.

               

              Ex:

               

              Set Variable

              Set Variable

              Loop

                If

                  Set Variable [$html ; $html & ""]
              Else
              Set Variable [$html ; $html & "

              " & {insert customer data} & "

              &#nbsp

              "]

                  Set Variable

                End If

                Exit Loop If

              End Loop

               

              HTH

               

              Mike

              • 4. Re: Dynamic HTML crosstab report
                Mike_Mitchell

                Ugh. Looks like my script HTML got munched by the parser. Let me try again:

                 

                Set Variable [ $productCount ; 1 ]

                Set Variable [ $custProductCount ; 1 ]

                Loop

                  If [ GetValue ( $custProductList ; $custProductCount ) = GetValue ( $productList ; $productCount ) ]

                    Set Variable [ $html ; $html & "<td>" & {insert customer data} & "</td>" ]

                    Set Variable [ $custProductCount ; $custProductCount + 1 ]

                  Else

                    Set Variable [ $html ; $html & "<td>&#nbsp</td>" ]

                  End If

                  Exit Loop If [ Let ( $productCount = $productCount + 1 ; $productCount > ValueCount ( $productList )) ]

                End Loop

                 

                Mike

                • 5. Re: Dynamic HTML crosstab report
                  ChrisG

                  Oh yes, I think that's it. I will go ahead and implement that.

                   

                  Thank you Mike.