9 Replies Latest reply on Aug 7, 2017 7:21 AM by luis.ecohaus

    Line graph with several data sources

    luis.ecohaus

      Hi all,

      I have a line graph working perfectly showing the number of leads per month. It is also dynamically according to a start date and end date, and the lead status I choose. It is working using a relationship.

      What I want to achieve? I would like to have one or two additional lines on the graph referring to the last two financial years. The issue is I don't know how to show more than a set of data in the same line graph.

       

      As I said I can make it work for a single set of data.

      This is my current relationship (5 criteria):

      Screen Shot 2017-08-04 at 12.28.06.png

      .. and the last line:

      Screen Shot 2017-08-04 at 12.28.11.png

      gStartDate, gEndDate and gStatus are global fields which I can manipulate, giving them different values.

       

      This is my graph Data source settings:

      Screen Shot 2017-08-04 at 12.32.29.png

      This is my chart settings showing both X-Axis and Y-Axis values:

      Screen Shot 2017-08-04 at 12.33.19.png

       

      It might be possible to achieve what I want with SQL queries. I don't think I can achieve the goal using relationships as the graph itself only lets me choose one data source. However, I might be wrong.

       

      Thank you.

        • 1. Re: Line graph with several data sources
          philmodjunk

          SQL with the delimited data source option would be my suggestion for how to graph them.

          1 of 1 people found this helpful
          • 2. Re: Line graph with several data sources
            weaverd

            Clicking on the Add Y series button will allow you to add another set of Y data for the same set of X values. You will need to either use another set of relationships, SQL, or scripts to gather the data you require for the 2nd, or 3rd etc Y data series.

            2 of 2 people found this helpful
            • 3. Re: Line graph with several data sources
              luis.ecohaus

              Thank you philmodjunk.

              I've tried to follow your suggestion, however without success.

              This is SQL query I am using on the Y axis:

               

              ExecuteSQL (

               

              "SELECT count(\"_pk_lead_id\")

              FROM REPORT

              WHERE \"date_creation_no_timestamp\" >= ? and \"date_creation_no_timestamp\" <= ? and \"lead_status\" = ? and \"lead_source\" = ?

              GROUP BY \"r_Year_Month\"

              ORDER BY \"r_Year_Month\"";

              "|" ; "¶";

              Date(4; 1; 2016);

              Date(3; 31; 2017);

              "Active";

              "CPD"

              )

               

              It returns the correct data for each month if I use the Data Viewer. However, applying that to the graph it only gives the value for one month (single dot in the graph).

              Am I using the SQL int he correct way?

              • 4. Re: Line graph with several data sources
                luis.ecohaus

                Thank you weaverd.

                I've tried to use another set of relationship for the second Y axis series, but it gives me a straight line in the graph, which not corresponds to the data I was expecting.

                 

                The second relationship is the same as the one I described in my original post, with the only difference in the dates.

                With regards to the data source, it is getting and sorting the data from the first relationship, as it is impossible to have more than one data source.

                • 5. Re: Line graph with several data sources
                  weaverd

                  luis.ecohaus can you post an image of your relationships graph showing the two relationships along with images of the relationship criteria? I assume you have created 2 new global date fields (say gStartDate1, gEndDate1) to use in this second relationship, and then populated those fields with appropriate dates for a previous financial year? Also it is interesting that you are matching all _pk_lead_ID's between the table occurrence as part of your relationship criteria. This may not be helping depending on what _pk_lead_id refers to. More than happy to look at your file if needed.

                  • 6. Re: Line graph with several data sources
                    luis.ecohaus

                    Both relationships:

                    Screen Shot 2017-08-07 at 10.39.02.png

                     

                    REPORT__GRAPH_CPD (5 criteria)

                    Screen Shot 2017-08-07 at 10.39.56.png

                    Screen Shot 2017-08-07 at 10.40.00.png

                    Two global fields being populated with previous desired dates.:

                    • gStartDate
                    • gEndDate

                     

                    REPORT__GRAPH_CPD_previous_year (5 criteria)

                    Screen Shot 2017-08-07 at 10.40.07.png

                    Screen Shot 2017-08-07 at 10.40.11.png

                    Two global fields being populated with previous financial year dates.:

                    • gPreviousFinancialYear_Start
                    • gPreviousFinancialYear_End

                     

                    The cartesian relationship on the field _pk_lead_id might not be the problem. It a cartesian relationship, it can be any field really. The field is the primary key itself.

                     

                    The chart:

                    Screen Shot 2017-08-07 at 10.45.55.png

                    The second series Y axis would point for the second relationship.

                     

                    The chart's data source:

                    Screen Shot 2017-08-07 at 10.46.32.png

                    • 7. Re: Line graph with several data sources
                      luis.ecohaus

                      Quick note: the second relationship (REPORT__GRAPH_CPD_previous_year) works fine if I put it in a separate line graph.

                      • 8. Re: Line graph with several data sources
                        weaverd

                        luis.ecohaus see attached file for example line graph using several data sources. I have tried to mimic your data structure approximately using some dummy data. Use the popup menus on the report layout to choose a source, status, and set start and end dates. The data for this year and last year is generated using Execute SQL. The Execute SQL calculations are in the chart itself for each of the data series, rather than in a field, but you could use a field if you wanted. You could be more sophisticated with calculations to choose the previous data that you wanted to compare to the current year, rather than using globals to set the start and finish values for the date range. For example, you could set a value of -1, for the previous year, or -2 for 2 years previous etc. Hope this helps.

                         

                        regards

                        2 of 2 people found this helpful
                        • 9. Re: Line graph with several data sources
                          luis.ecohaus

                          Thank you weaverd.

                          It worked like a charm. The only disadvantage I see is the number of SQL queries to be executed which can cause performance issues. However, I will not have my staff running this type of report every time, and it doesn't consume many time (in my case).

                          Regarding the global fields, I am actually using calculation and storing the date calculation value on those global fields (gStartDate and gEndDate). The user can choose between the below dates, and the calculated dates will be store on those global fields:

                          Screen Shot 2017-08-07 at 15.20.48.png

                          The previous financial years are also being calculated taking into account the current date.

                          Thank you once again.