10 Replies Latest reply on Mar 5, 2013 4:03 PM by nkolios

    Tool tips with calculated results

    nkolios

      I have created a cross tab report, image. This is 12 months sales volumes by product line. What I am trying to acheive is when the mouse is hovered over Draught Lager in October, the tool tip will show the previous 5 years information for this product, to compare the same month in previous years. I just dont know how Im going to acheive this. If anyone has any ideas please let me know. Thanks for reading.

       

      Niko

       

       

       

       

       

      Cross tab.jpg

        • 1. Re: Tool tips with calculated results
          nickchapin

          Are using FMP12?

          • 3. Re: Tool tips with calculated results
            taylorsharpe

            Tool tips work in FM11... it is under the inspector.  Click the pencil to the right of it to enter the calculation engine.  

            Untitled-1.jpg

            1 of 1 people found this helpful
            • 4. Re: Tool tips with calculated results

              nkolios wrote:

               

              What I am trying to acheive is when the mouse is hovered over Draught Lager in October, the tool tip will show the previous 5 years information for this product, to compare the same month in previous years. 

              Hi Niko,

               

              That seems like a lot of numbers to display in a tooltip which, unless one presses and holds while reading it, disappears almost instantly.  I would suggest instead that you open a small  window so the User can view and comare at their leisure.  Selecting a field from the report can switch to that 'record view' in the window.  With script triggers, it can easily be kept together.

              • 5. Re: Tool tips with calculated results
                nickchapin

                Since you're not using 12 (in which I would have suggested using SQL), you might want to set a global var to the data while you're in your script getting the cross-tab data. You would, I think, want to base tha var on the id of the row and the column. However, LaRetta is correct in that it may be too much data for a quick look. Perhaps a comnination of the two?

                • 6. Re: Tool tips with calculated results
                  nkolios

                  Hi,

                  Thanks for the response, I only need to show about the last 3 years so it will only be 3 numbers.  The tricky bit for me is to get information back from the cell where the mouse is hovering over, once I have that then think I can find the data I need.  Most of the functions will work but only when you click into the field.

                  • 7. Re: Tool tips with calculated results
                    nickchapin

                    Have you looked at the Self function? It's often the "go to" function for use in ToolTips.

                    • 8. Re: Tool tips with calculated results
                      nkolios

                      Hi Nick, thanks for the response, I just tried using that and it just gives me  value but the value represents the summed value of the column not just the single field, (I am using this on a report layput).  I have created a calculation in the tooltip but it doesnt work if I just hover the mouse over the field only if I click into it.  Then if I hover the mouse over the second column in the crosstab report it gives me the same result as if I was hovering over the first column.  Its all a bit confusing!

                      • 9. Re: Tool tips with calculated results
                        Mike_Mitchell

                        Niko -

                         

                        The issue isn't the tooltip per se, but how to draw out the data you want. We need to know a bit more about the data structure of the report. You say you want the previous 3 years' data for a particular cell. How are those data stored or referenced in your database? You need to write a calculation that can fetch that information. The tooltip is a small part of the issue; the calculation that fetches the data is the real issue, and we need to know how your data are being stored in order to figure that out.

                         

                        Nick's suggestion of setting a variable may be in the right direction if you're using summary fields. You may also want to look at the GetSummary ( ) function. Alternatively, in order to accomplish what you want, you might need to back up completely and calculate previous years' totals and store them in a separate table to make them "fetchable". But again, the problem isn't the tooltip; it's the method for fetching the data that should be your focus.

                         

                        Mike

                        • 10. Re: Tool tips with calculated results
                          nkolios

                          Hi Mike,

                          Thanks for taking the time to reply.  I agree, the calculation that fetches the data is what is giving me my first headache. The data itself is imported monthly from my EPOS system.  My Cross tab report shows monthly figures for 24 product groups.  Our financial year run from Oct to Sept so I have had to create month numbers based on this and year numbers to keep the financial years together. 

                           

                          On my start up page I have a button which will produce a cross tab report for a particular financial year, using script parameters and a global field to give me the data I need. You will see from looking at the fields that I use the field name convention Volume_Month_1, Volume_Month_2 etc.  I have been trying to experiment with trying to extract the correct information from the field that I am hovering over and then displaying it in the tooltip but the information I get back is very random. I can extract the month number from the field name and the year number from the global field.  There is one main issue, when i move the mouse over the fields from left to right, i.e. from one month to the next, i get the results only from the first column regardless of what column I am hovering over.  I hope I am making sense.

                           

                          I will attach a file showing the fields a bit later as I am having trouble uploading it.