| Author |
Message |
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
  
Topic: Graph Help
Posted: 30 Sep 2014 at 10:18pm |
|
Good morning,
I am currently producing a 12 month trend by 'counting' the amount of records for each month.
The issue with the current format is it will only graph the months that data is available for (understandably).
Is there a way to force the graph to plot all months, regardless of available data?
|
 IP Logged |
|
|
|
z9962
Senior Member
Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
|
Posted: 30 Sep 2014 at 10:45pm |
yes... what is your data source? SQL? Is it always 12 months prior to today?
|
| IP Logged |
|
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
Posted: 30 Sep 2014 at 10:50pm |
Hello. It is SQL, yes.
It will always be 12 months prior (Select Expert formula below).
{table.creationdate} >= DateAdd("yyyy",-1,DateSerial(year(CurrentDate),month(CurrentDate),1)) and
{table.creationdate} < DateSerial(year(CurrentDate),month(CurrentDate),1) Edited by Dr4ke - 30 Sep 2014 at 10:51pm
|
| IP Logged |
|
z9962
Senior Member
Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
|
Posted: 30 Sep 2014 at 11:08pm |
you can create a command that will give you 12months (below) There are different ways to use this for what you need. This creates the date as the 1st, therefore if its the 2nd it does not link. Depending on how much data and what access you have over the db depends on best solution. though this should be a start? IF
OBJECT_ID('tempdb..#TempDate') IS NOT NULL DROP TABLE #TempDate
CREATE
TABLE #TempDate (tDate DateTime)
DECLARE
@i INT
SET
@i = 1
WHILE
@i <= 12
BEGIN
INSERT INTO #TempDate (tDate) Values (DATEADD(month,-@i,getdate()))
SET @i = @i + 1
END
SELECT
tDate FROM #TempDate
|
| IP Logged |
|
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
Posted: 30 Sep 2014 at 11:16pm |
|
Thank you.
So I see this is creating a temporary table. Currently I have 9 tables feeding my report data; with the creation of a new table I am assuming it will need to be linked in some way.
By the looks of the formula this is going to output integer values (1 - 12); how would this link into my other tables when the values are date/time fields?
I.e. if I have the following entries
01/01/2014 04:45:13
04/04/2014 05:04:15
06/07/2014 12:53:14
how would these link to the new tables for the following entries:
1
4
7
that's assuming I have understood the suggestion correctly of course :-)
|
| IP Logged |
|
z9962
Senior Member
Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
|
Posted: 30 Sep 2014 at 11:21pm |
This is where it will depend on a few things. Do you have access to write views on your db?
|
| IP Logged |
|
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
Posted: 30 Sep 2014 at 11:31pm |
|
I don't currently have the ability to write views to the database, but it is something I could request.
|
| IP Logged |
|
z9962
Senior Member
Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
|
Posted: 30 Sep 2014 at 11:40pm |
The easiest way I could think of is to have a
new field in the view for that table which shows the first day of the month.
Therefore you can do a left outer join to it. If it was just date, you could
modify the command to do it daily, though little slower it would work, though
as you have time it makes it a little harder.
|
| IP Logged |
|
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
Posted: 30 Sep 2014 at 11:45pm |
|
Sorry... I'm not sure I understand exactly what I would need to do?
|
| IP Logged |
|
z9962
Senior Member
Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
|
Posted: 30 Sep 2014 at 11:52pm |
in a SQL view your creation date needs to be set to first day of the month, you could use this in the view dateadd(month,datediff(month,0,creationdate),0) you can then link on this field.
|
| IP Logged |
|
|
|
