1 of 1 people found this helpful
SQL with the delimited data source option would be my suggestion for how to graph them.
2 of 2 people found this helpful
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.
Thank you philmodjunk.
I've tried to follow your suggestion, however without success.
This is SQL query I am using on the Y axis:
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);
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?
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.
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.
REPORT__GRAPH_CPD (5 criteria)
Two global fields being populated with previous desired dates.:
REPORT__GRAPH_CPD_previous_year (5 criteria)
Two global fields being populated with previous financial year dates.:
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 second series Y axis would point for the second relationship.
The chart's data source:
Quick note: the second relationship (REPORT__GRAPH_CPD_previous_year) works fine if I put it in a separate line graph.
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.
data.fmp12.zip 72.2 K
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:
The previous financial years are also being calculated taking into account the current date.
Thank you once again.