Setting up X-Axis on a Line Graph

<p>Hello,</p>
<p> </p>
<p>I am new to the BIRT Report Design so please bare with me. Lets say I want to set up my X-Axis based off a value I pull from my database. For example say the value I have access to is a date 3/13/2014 9:28 pm. And say I also have access to a duration value. Lets say 32. Is there a way I can start my first x-axis value at 3/13/2014 9:28 pm and have 32 additional points each incremented a minute more than the other so the last value I have displayed on my x-axis is 10:00 pm? Does this make sense? I have been playing around with this all morning and I have gotten no where so any help would be greatly aprpeciated.</p>
<p> </p>
<p>Thank you very much,</p>
<p> </p>
<p>Dave</p>

Find more posts tagged with

Comments

<p>Hi Dave,</p>
<p> </p>
<p>You are better off working on the data side to create a table structure that can be used directly by the chart engine. In other words, you should have at least as many rows in your data set as you want to display on the X-Axis. In the simplest case, your data set will have two columns: one for the time value (X-axis), one for the series value (Y-axis).</p>
<p> </p>
<p>I think you need to give us a bit more information about your data before proposing a solution!</p>
<p> </p>
<p>Regards,</p>
<p> </p>
<p>P.</p>

<p>Hello Sir,</p>
<p> </p>
<p>What I am trying to do is graph the number of participants that are in a given conference call at different points throughout the call. My graph (bar or line) would be setup dynamically based on the data that I pull from my database.</p>
<p> </p>
<p><strong><u>X-Axis</u>:</strong></p>
<p> </p>
<p>My x-axis would be the duration of the call. To accomplish this I can pull a start time of the particular conference from my database. This would be the start of my x-axis. Say this value is 10:00 o'clock. Now I also have a duration value for the conference call. Say this value is 30. Along the bottom of my x-axis I want to break it up into 2 minute increments. So it would start at 10:00, then next would be 10:02, then 10:04, all the way until the end which would be 10:30.</p>
<p> </p>
<p><strong><u>Y-Axis</u>:</strong></p>
<p> </p>
<p>My y-axis would be the total number of participants that were in the call. This is another value I can pull from my database. So if I have 5 participants, my y-axis would start at 1 and then next would be 2, then 3, all the way up to 5 or maybe a little higher so the number isn't at the very top of the graph.</p>
<p> </p>
<p><strong><u>Logic</u>:</strong></p>
<p> </p>
<p>What I am trying to do is graph how many of these participants were in a call at a given time. To accomplish this, I have connect and disconnect times for each particiapnt in the call. So if the first participant joins at 10:02, and they are the only one in the call, for 10:02 it would plot to 1 on the y-axis. If two more callers join at 10:04, then it would be up to 3 on the y-axis. If everyone is joined at 10:10, then I would be up to 5 (the total number of participants) on my y-axis. This would stay the same until the first participant disconnects, which would drop the graph down a participant on the y-axis. And when another person disconnects, it would drop another point on the y-axis. </p>
<p> </p>
<p>Does this make sense to you? I am going to attach a .pdf file that is pretty much exactly what I want and it should help you understand what I am trying to do a little better. I am new to BIRT so I have setup my database and I am pulling the right information but that is pretty much as far as I have gotten. I am confused on how to setup my graph to dynamically size like this based on the values I pull from my database and where exactly my logic goes to setup it up how I want it. </p>
<p> </p>
<p>If anyone has any ideas or can point me in the right direction or help me out to get started I would greatly appreciate it as this interface is very new and confusing to me at this time. I just started a new job and this was a request so I would love to figure it out to make management happy. Again I would very much appreciate it.</p>
<p> </p>
<p>Thank you!</p>
<p> </p>
<p>*I will attach the .pdf file of the graph I want to emulate</p>

<p>Annnnddddd I have no idea how to attach something to a forum post lol how exactly can I do this?</p>

<p>I keep trying to post the graph but it tells me "You are not allowed to use that image extension on this community." Any thoughts?</p>

<p>

<p>Hi,</p>
<p> </p>
<p>As I said before, the effort here is on creating the query, not building the chart. The chart you are creating is very simple and requires only two columns of data: time and number of persons on the call at that time. It could look like this:</p>
<p> </p>
<p>Time, Persons<br>
10:00,2<br>
10:02,3<br>
10:04,4<br>
10:06,5<br>
10:08,6<br>
10:10,5<br>
10:12,6<br>
10:14,6<br>
10:16,6<br>
10:18,6<br>
10:20,2</p>
<p> </p>
<p>Then when you create the chart, you map Time to the X-Axis without grouping, and Persons to the Y-Axis. Obviously, the toughest part will be to transform the data from your database into a result set ready for charting.</p>
<p> </p>
<p>Regards,</p>
<p> </p>
<p>P.</p>

<p>Thank you very much for your reply. I understand what you are saying about the table. Once I create this table I just point my graph to it and setup my axes. Thank you for the clarification. What I am having trouble with figuring out is where do I actually write the logic to setup this table? Do I do this within BIRT report design? If so, where at within BIRT? I apologize for my ignorance but I am new to this reporting engine and I am having trouble getting started. Thanks again your help is much appreciated!</p>
<p> </p>
<p>Best,</p>
<p> </p>
<p>Dave</p>

<p>Is there a way I can setup the graph with hard coded data just to see how it would look? I tried creating a table with hard coded text but it needed a data set and I can't figure out how to use the Time and Persons columns to setup my x and y axes</p>

<p>Hi,</p>
<p> </p>
<p>Sorry for the late reply...</p>
<p> </p>
<p>Here's an example that uses a CSV file as a data source. The CSV file has only two columns, Time and Persons. The chart uses Time for the X-Axis, and Persons for the Y-Axis.</p>
<p> </p>
<p>As for creating a query that will return the data the appropriate way, there might be more than one way to do it depending on your original data source. Post a quick sample and we can help you!</p>
<p> </p>
<p>Regards,</p>
<p> </p>
<p>P.</p>

<p>That CSV worked perfectly. It should me how I can set up a chart and it looks really good. A table like that is exactly what I need to setup my chart how I want it. Thank you very much! Now comes the hard part, getting a table of data like the one in your .CSV file. </p>
<p> </p>
<p>I am basically pulling in a value from my database that will represent the start time. So based on this start time, and a duration value which I also pull from my database, I can set up the "Time column". From a logic standpoint, I could this no problem. But being that I am new to this BIRT interface, where can I do the coding to setup this time column? For example, where could I have a for loop that loops through on my duration value and sets the next row in my table to say two minutes later like in your example. If I could figure where to do this logic at to setup my table I would be able to set up my "Time" column. <u>I don't know if this makes sense or not but somewhere within BIRT I need to be able to do some logic on the values I pulled from my database and setup my table that will be used for the chart with this logic. Is this possible?</u></p>
<p> </p>
<p>The harder part will be figuring out how many participants would be in the call at the particular time but we can save this for after I get my "Time" column.</p>
<p> </p>
<p>Again, I thank you very very much as you've been a tremendous amound of help!</p>
<p> </p>
<p>Thank you!</p>
<p> </p>
<p>Dave</p>

<p>I just have no idea how to take the start time value that I pulled from my database, and generate a table with a time column that is based off my duration value. Say I pull 10:00 from my database and the duration I pull is 20 mins. I am not sure how to create a table with a time column that starts at 10:00, and goes up to 10:20 in increments of 2. This table would have to be generated dynamically because each conference will have a different start time and duration. I dont know if this is even possible or if there is another good way to do this using the BIRT report design interface.</p>

<p>If I could somehow start my table off with the start time as the only value in the time column.</p>
<p> </p>
<p>Then loop through based on my duration.</p>
<p> </p>
<p>var newTime = startTime;</p>
<p> </p>
<p>for(var i=0; i < duration; i++)</p>
<p>{</p>
<p> table.addRow(newTime + 2 mins);</p>
<p>}</p>
<p> </p>
<p>Obviously this is just pseudocode but I am curious if something like this is possible. And if it is, where/how would I write this code to setup my table that can then be pulled from to create my chart. </p>

<p>Hi,</p>
<p> </p>
<p>To create a table with a dynamic number of rows, you need a Scripted Data Set. With this type of data set, you add code to the fetch method to add rows to your set.</p>
<p> </p>
<p>In the attached example, the first data set (Data Set) is used to retrieve one row from a flat file. This is a data set that will give the start time and duration. In order for that data set to be executed, it needs to be bound to a table, hence the table that is above the chart. The table's visibility property is set to false, so it will not appear when the report is run. To store the start time and duration in variables, I have added code to the onCreate event at the row detail level of the table.</p>
<p> </p>
<p>Now that we have the start time and duration, we can generate the rows in the scripted data set. In this data set, I have defined two columns, Time and Persons. In the Open method of the scripted data set, I initialize 3 variables. These 3 variables are used in the Fetch method to create a loop that adds rows to the data set. The Fetch method is called endlessly until you tell it to stop, in this case, when we hit the maximum number of records to be created based on Duration.</p>
<p> </p>
<p>Finally, the chart is now bound to the Scripted Data Set.</p>
<p> </p>
<p>This should help you understand how to create rows dynamically.</p>
<p> </p>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>

<p>Hello Sir,</p>
<p> </p>
<p>Thank you very much for your help. I now see how I can setup my table dynamically. My only question would be do you think you could set it up so that it would use the StartTime and Duration pulled from an already setup database instead of an excel file? I tried to emulate the way you did it with the excel file only I used a StartTime and Duration value that I pulled from my database but I keep getting errors. I will tell you how I set it up and then give you the errors and maybe you will have an idea of what I am doing wrong.</p>
<p> </p>
<p>1. I set up a new Scripted Data Source</p>
<p> </p>
<p>2. I set up a new Data Set that I named Scripted Data Set and I created output parameters for the data set. The paramters I created were "Time" as a Time, and "Persons" as an Int.</p>
<p> </p>
<p>3. I then set up a table in report with two columns and one detail row. In the first column I put "ConfStart", which is a value I pull from my database that gives a date as a day, month, year, and time. In the second column I put "ConfDuration" which is the duration value(INT) that I pull from my database. This should be similar to how you pulled in the values from the data source you set up from the excel file so I don't see how this would make a difference.</p>
<p> </p>
<p>4. Just like in your table in the sample report above, I highlighted the detail row, and in the script onCreate I put:</p>
<p> </p>
<p>vars["vConfStart"] = this.getRowData().getColumnValue(880);<br>
vars["vConfDuration"] = this.getRowData().getColumnValue(882);</p>
<p> </p>
<p>where the numbers are the ID of my element. I tried with a string as well but this didn't work either.</p>
<p> </p>
<p>5. Then I highlighted my Scripted Data Set and in the Open script I put:</p>
<p> </p>
<p>myRows = 1;<br>
maxNbOfRows = vars["vConfDuration"];<br>
startTime = vars["vConfStart"];</p>
<p> </p>
<p>6. I then changed to the Fetch script and put:</p>
<p> </p>
<p>if (myRows > maxNbOfRows) {<br>
   return false;<br>
}<br>
else {<br>
   startTime = BirtDateTime.addMinute(startTime, 2);<br>
   row["Time"] = startTime;<br>
   row["Persons"] = myRows;<br>
   myRows++;<br>
   return true;<br>
}</p>
<p> </p>
<p>7. I then went to my Chart and set it to use the Scripted Data Set for its Data Source, and row["Time"] for the X-Axis and row["Persons"] for the Y-Axis.</p>
<p> </p>
<p>This setup gave me the following errors:</p>
<p> </p>
<p> </p>
<div>The following items have errors:</div>
<br><div>
<div>ReportDesign (id = 1):</div>
<div><span>+ </span>There are errors evaluating script "myRows = 1;<br>
maxNbOfRows = vars["vConfDuration"];<br>
startTime = vars["vConfStart"];":<br>
Fail to execute script in function __bm_OPEN(). Source:<br>

<br>
" + myRows = 1;<br>
maxNbOfRows = vars["vConfDuration"];<br>
startTime = vars["vConfStart"]; + "<br>

<br>
A BIRT exception occurred. See next exception for more information.<br>
Error evaluating Javascript expression. Script engine error: Report variable"vConfDuration" does not exist<br>
Script source: /report/data-sets/script-data-set[@id="874"]/method[@name="open"], line: 0, text:<br>
__bm_OPEN(). (Element ID:1)</div>
<div><span>+ </span>There are errors evaluating script "if (myRows > maxNbOfRows) {<br>
return false;<br>
}<br>
else {<br>
startTime = BirtDateTime.addMinute(startTime, 2);<br>
row["Time"] = startTime;<br>
row["Persons"] = myRows;<br>
myRows++;<br>
return true;<br>
}":<br>
Fail to execute script in function __bm_FETCH(). Source:<br>

<br>
" + if (myRows > maxNbOfRows) {<br>
return false;<br>
}<br>
else {<br>
startTime = BirtDateTime.addMinute(startTime, 2);<br>
row["Time"] = startTime;<br>
row["Persons"] = myRows;<br>
myRows++;<br>
return true;<br>
} + "<br>

<br>
A BIRT exception occurred. See next exception for more information.<br>
Error evaluating Javascript expression. Script engine error: ReferenceError: "maxNbOfRows" is not defined. (/report/data-sets/script-data-set[@id="874"]/method[@name="fetch"]#1)<br>
Script source: /report/data-sets/script-data-set[@id="874"]/method[@name="fetch"], line: 0, text:<br>
__bm_FETCH(). (Element ID:1)</div>
<div><span>+ </span>Data Set script method "Fetch" returned null; expected a Boolean value. (Element ID:1)</div>
</div>
<br><div>
<div>Row (id = 16):</div>
<div><span>+ </span>There are errors evaluating script "vars["vConfStart"] = this.getRowData().getColumnValue(880);<br>
vars["vConfDuration"] = this.getRowData().getColumnValue(882);":<br>
Report variable"vConfStart" does not exist (Element ID:16)</div>
<div> </div>
<div> </div>
<div>I have been messing with this all day and I cannot seem to gain an inch of ground:(</div>
<div> </div>
<div>What you have shown me has been very promising and I have indeed learned a lot about the BIRT interface but if you have anymore insight it will be greatly appreciated. Thank you very much!</div>
<div> </div>
<div>-Dave</div>
</div>

<p>By the errors I receive it almost looks like when I assign the variables in the Open script in my Scripted Data Set, that vars["vConfDuration"] and vars["vConfStart"] don't exist even though I assign them to the values from my table in the onCreate script in the lines:</p>
<p> </p>
<p>vars["vConfStart"] = this.getRowData().getColumnValue(880);<br>
vars["vConfDuration"] = this.getRowData().getColumnValue(882);</p>
<p> </p>
<p>Could this be because these values get pulled from my database at a later point or something like that? Just trying to brainstorm ideas as to why it works with your example when you are pulling from a flat file, and why it doesn't work with mine when I am pulling these values from my database.</p>
<p> </p>
<p>I changed the table from hidden to visible to see what values get displayed when my report is generated and ConfStart and ConfDuration are "Mar 13, 2014 9:28 PM" and "32" respectively. The only thing I can think of is that when those scripts are executed my values haven't yet been pulled from my database? Is there a way to make this work the way I am trying to do it?</p>
<p> </p>
<p>Again, thank you for all your effort. I know you don't have to help me out with this problem and you are taking away from your own time in order to do so and I appreciate that alot!</p>
<p> </p>
<p>Thanks,</p>
<p> </p>
<p>Dave</p>

<p>Hi Dave,</p>
<p> </p>
<p>The two report variables you use in your script, vConfStart and vConfDuration, need to be defined before you can use them. Report variables are defined in the Data Explorer section. (In my report sample, you will see two variables already created there. You could make your life simple by simply re-using them...)</p>
<p> </p>
<p>In fact, all you need to do if you start from my sample is to replace the Data Source/Data Set based on the flat file with your own Data Source/Data Set that gets its data from a database. If you use the same naming convention, there is nothing in the code you need to change.</p>
<p> </p>
<p>P.</p>

<p>Thank you very much for your help. I have figured this setup out and have successfully gotten my X-Axis working the way I want it. You have been such a great help!</p>
<p> </p>
<p>And if I may, I have just one more question about setting up my Y-Axis haha</p>
<p> </p>
<p>Okay so my Y-Axis will be the number of people in the call at the time on my X-Axis. To figure how many people will be in the call at that time I have connect and disconnect times for each participant. So I setup my database to pull these values and just like for the X-Axis I setup a table:</p>
<p> </p>
<p> </p>
<p>*See Attachment connectTable</p>
<p> </p>
<p> </p>
<p>ColumnValue9 is the connect time and ColumnValue10 is the disconnect time for a participant. </p>
<p> </p>
<p> </p>
<p>I set this table to visible so it would get displayed with my report so that you can get an idea of the data I am working with:</p>
<p> </p>
<p> </p>
<p>*See Attachment displayedTable</p>
<p> </p>
<p> </p>
<p>I delcared my vars for each of the values much like StartTime and Duration from the X-Axis so the setup should be the same.</p>
<p> </p>
<p>My problem is trying to figure out the logic in the fetch script that you helped me create so that I can add the correct number to row["Persons"] that corresponds to if a person was connected at that time on the X-Axis. I hope this makes sense it is somewhat confusing to try to explain.</p>
<p> </p>
<p>So if the time on the X-Axis was 9:28:34 PM, based on the data above only one person is connected at that time, the participant who connected at 9:28:00 PM. So at that time on my X-Axis, it would correspond to 1 on my Y-Axis. If two people were connected at 9:28:00 PM, it would correspond to 2 on my Y-Axis. I hope this gives you a good idea of what I am trying to do. Being that I am new to BIRT, and this being the most confusing part of my report, I could really use the help.</p>
<p> </p>
<p>I know I will have to loop through all those ConnectTimes from the data above and if the X-Axis time >= the ConnectTime, I would increment a counter for every instance and that number would get added to my row["Persons"]. I am just confused at how I would access each of those values in the table and just the overall setup of this procedure. If you could offer any insight it would be greatly appreciated. Also, if you need anymore information please feel free to ask me so that you can get a better understand of what I am trying to do.</p>
<p> </p>
<p>Thank you very much Sir,</p>
<p> </p>
<p>Dave</p>

<p>When I plot the value of ColumnValue9 it only gives me 9:30:00 PM which is the last value in that data set. It looks like when I do</p>
<p> </p>
<p>vars["vConnectTime"] = this.getRowData().getColumnValue("ColumnValue9");</p>
<p> </p>
<p>it only returns the last value in that data set because that was the last value to populate that column. Is there a way to get all those connect times as an array of values so I can loop through and check them against the time on the X-Axis?</p>

<p>Okay so I have the logic figured out for comparing the times along the X-Axis with the connect times to see if someone is in the call, but my problem falls within the post above. I only have a handle to the last connect time which if you look at the attached thumbnail "dataTable.png" is 9:30:00. When I do:</p>
<p> </p>
<p>vars["vConnectTime"] = this.getRowData().getColumnValue("ColumnValue9");</p>
<p> </p>
<p>it just grabs the last column value (at least I think) which is the 9:30:00 value to populate vConnectTime.</p>
<p> </p>
<p>I need to grab all of those connect time values (not just the last one) and put them into an array so that I can loop through them each time my fetch executes so that I can compare them to the current value along the X-Axis to see if someone is in the call or not. If I could figure out how to access all of those values I feel like I would be good to go.</p>

<p><u><strong>MORE CLEAR EXPLANATION:</strong></u></p>
<p> </p>
<p>I'm just trying to explain this even clearer so its more understandable in case its not very clear from my previous posts.</p>
<p> </p>
<p> </p>
<p>So my table I've created looks like this:</p>
<p> </p>
<p><strong>*see attached connectTable.png</strong></p>
<p> </p>
<p>where "ColumnValue9" represents connection times to a conference call that I pull from my database.</p>
<p> </p>
<p> </p>
<p>I have set up a variable in the onCreate script to grab the data from the column in the table depicted in the screenshot above, and it looks like this:</p>
<p> </p>
<p><strong>vars["vConnectTime"] = this.getRowData().getColumnValue("ColumnValue9");</strong></p>
<p> </p>
<p> </p>
<p>When I display the above table depicted in connectTable.png, I get the following output in my report:</p>
<p> </p>
<p><strong>*see attached dataTable.png</strong></p>
<p> </p>
<p>When you look at dataTable.png, you see that it has multiple connection times, one for each participant in the call.</p>
<p> </p>
<p>In my Scripted Data Set, in the "open" script, I set <strong>vars["vConnectTime"] </strong>to a variable like this:</p>
<p> </p>
<p><strong>connectTime = vars["vConnectTime"];</strong></p>
<p> </p>
<p> </p>
<p>Then in the same Scripted Data Set, but in the "fetch script", I tried to loop through <strong>connectTime</strong> but I figured out that <strong>connectTime</strong> only holds 1 value, which is 9:30:00 PM, the last value in the attached <strong>dataTable.png</strong> file.</p>
<p> </p>
<p>Basically, what I need to figure out is how to access each of those connection times depicted in that attached <strong>dataTable.png</strong> file. It seems like the column in my table that holds "ColumnValue9" is overwritten or something like that every time, and I am only left with the last value, 9:30:00 PM. Even though when I display the table it shows them all, which I don't quite understand. </p>
<p> </p>
<p>So if I could figure out how to get each of those connection time values into an array, then figure out how to access that array from my Scripted Data Source "fetch" script, my problem would be solved.</p>
<p> </p>
<p> </p>
<p>I hope I have given you enough information to understand what I am talking about. I just want to thank you again for your time as it has helped me out immensely!</p>
<p> </p>
<p>Thank you,</p>
<p> </p>
<p>Dave</p>

<p>Hi,</p>
<p> </p>
<p>I have made an attempt at creating a chart based on a table where you have 2 columns: Time call started and time call ended. That first table is still in a flat file, but could be stored in a database. The first step is to read that data and transform it so I can get 2 columns: call time and number of persons on the call at that time. This transformation happens in the onCreate event of the detail row of the first table. In this step, I store the data in arrays. Then, I create a Scripted data Set where I copy the values of the arrays. This way, I can use the scripted data set as a source to my chart.</p>
<p> </p>
<p>I have put some comments in the different scripts to help you figure it out.</p>
<p> </p>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
<p> </p>
<p>PS: I will be away for the next week, so you are on your own!</p>
<p> </p>
<p> </p>

<p>Hello,</p>
<p> </p>
<p>Thank you again for your help! I know you are away this week but I just wanted to keep you updated. I set my project up like the one you provided in the .zip file but with no luck. It seems that it does work perfectly up to the point that I was talking about before. I still only have access to the last connect time and the last disconnect time that I pulled from my data base. It seems to work fine when pulling from a flat file (the excel file you provided), but when I pull the data from my database, all I have access to is that last time. I am not able to get a handle to the rest of the connect and disconnect times.</p>
<p> </p>
<p>To deter this discussion from becoming too long, I have rephrased my question and posted a new discussion:</p>
<p> </p>
<p><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/35185-reading-values-from-a-table-into-an-array/'>http://developer.actuate.com/community/forum/index.php?/topic/35185-reading-values-from-a-table-into-an-array/</a></p>
<p> </p>
<p>If you have any input please feel free to comment as it will be greatly appreciated. </p>
<p> </p>
<p>Thank you,</p>
<p> </p>
<p>Dave</p>

<p>I figured it out! If you are curious as to what I did I posted my solution to the new post that I created. You should be able to access it from the link I included in my last post. Feel free to take a look and offer any feedback you may have. Thank you again for all of your help!</p>
<p> </p>
<p>-Dave</p>