Report Generation Tool Error with Excel

[Murph]

Thank you and please excuse my ignorance. I am obviously a little out of my league here.

I will try this keeping in mind that what I sent you is a simulation.

What I am actually doing is indeed a DAQ gathering data on a counter which interrupts an optical sensor once/rotation.

It is to read the speed of a flying insect in a circle. The bar the insect is attached to is 365mm and I calculate the speed based on the counter interruptions.

[Bob_Schor]

---

@Murph wrote:

Thank you and please excuse my ignorance. I am obviously a little out of my league here.

I will try this keeping in mind that what I sent you is a simulation.

What I am actually doing is indeed a DAQ gathering data on a counter which interrupts an optical sensor once/rotation.

It is to read the speed of a flying insect in a circle. The bar the insect is attached to is 365mm and I calculate the speed based on the counter interruptions.

---

Then you have definitely been wasting all of our times.  If you are going to simulate something, especially if you are going to show someone your simulation, be sure to simulate what you are doing.  When you simulate a calculation of rotary movement using a "once-per-cycle" signal and a counter (as you seem to be doing), if the measurement interval is long with respect to the revolution time, you can simply "count" revolutions, but if the revolution time is relatively long, one frequently measures the time between rotation "ticks" and reports rotation (in that variable time frame) as 1/(time between ticks).

This does, of course, have some bearing on your simulation, as it gets to the notion of the nature of your measurement -- is it at uniform time intervals or "something else".

So forget about the simulation.  Go look up that reference (it's here in the Forum) that shows, by means of a better (or "Revised") Excel Example, how to use the RGT to create a fairly nice-looking Report in Excel, with entries for Labels (like "Operator" and "Date"), Tables (of data), and Graphs, and how to put them in the cell addresses you want to use, doing away with the need for Templates.

Bob Schor

[Murph]

I thought these forums were to help.

I have already written everything else and it works very well. I figured I would not include all that and make it simpler as the data set ends up being the same

A data point and the time it happened.

I use the template because I do fill it with everything else I need like Operator, Subject name, min, max, whatever.

I 1st started using Waveform Graph but could not get the time to be reflected in Excel the same way that I can't with an XY graph.

So very simply.

Why does the graph on the vi display time correctly and when it ti sent to Excel it does not.

[Bob_Schor]

---

@Murph wrote:

I thought these forums were to help.

Why does the graph on the vi display time correctly and when it ti sent to Excel it does not.

---

Murph,

     I apologize for wasting your time (and my own).  I promise that this will be my final response on this thread.  I have tried to "teach" you, and have given you correct "hints" and guidance along the way (I've just re-read this entire thread).

     As I explained in the 8th entry in this thread, you are saving an approximation of LabVIEW's "TimeStamp" value.

The LabVIEW timestamp is a 128-bit data type that represents absolute time. You can interpret this data type as a signed, 128-bit fixed-point number with a 64-bit radix.

{
   (i64) seconds since the epoch 01/01/1904 00:00:00.00 UTC (using the Gregorian calendar and ignoring leap seconds),
   (u64) positive fractions of a second
}

When you convert this to a Dbl, you lose some precision (since Dbls have only 64, not 128, bits).  But it still represents the number of seconds since the epoch.

LabVIEW "knows" this, and can "translate" this rather unwieldy "number of seconds since a date over a century ago" into a Date/Time string if (as is the default) you say that the Graph/Chart X axis represents Time (either Absolute or Relative).  Excel doesn't "know" that.  You can tell Excel to interpret these data as Time, but Excel saves Time in a different Format than LabVIEW, so the numbers won't be compatible.

There are several solutions. 

• Use a compatible way of saving the data (like a String)
• Use "Relative Time" and make the X axis Time in Seconds (or minutes, or hours), saving values such as 1, 2, 3, understandable by both LabVIEW and Excel.

At least twice, I pointed you to a "Solution" to a similar problem that didn't use Templates (which "lock you in" to a Format that doesn't easily allow flexibility if needs change).

Here is a Front Panel for an Excel Report, with Controls for items that would be a "fill-in-the-blank" on a Template:

Here is the section of the program that "builds" the Template, creating boxes for these data, and generating (for purposes of testing) three simulated temperature readings from 0 to 10 seconds at quarter-second intervals:

Finally, here is part of the Excel Report, showing the Date/Time/Operator section, the Test/Humidity/Pressure section, part of the Data table, and the Graph:

Bob Schor

[Murph]

No time wasted here. Sorry if you think it wasted yours. I am obviously not as trained as you. We newbies look to experienced people like you for help.

You Excel demo was excellent and as I mentioned. I have the rest operating quite like yours. It looks awesome.

The ONLY thing I wanted was to figure out a way to display Time in the excel graph the same way it is displayed in LV, not a 1,2,3. I believe you have answered my question with

"You can tell Excel to interpret these data as Time, but Excel saves Time in a different Format than LabVIEW, so the numbers won't be compatible."

[RavensFan]

Excel only know what you are plotting as your X axis.  In your case you are starting with 0 and every step is a .25 second.

Excel treats time as number of days.  (Bob told you how LabVIEW treats time as number of seconds.).   So every 1 second is 1/86,400 of a day.

If you want the  X axis to look like time (hh:mm:ss), then you need to set the x-axis as a time format, and convert your seconds to fractions of a day.

If you want your X-axis to be absolute time, so the first data point show up as 5/26/20 8:25 am, then you will have to create an X column that adds that base time to every one of your x values.  So that would be =$B$1 + $B$2 + A6/86400   (and A7, A8, ......)

What is often better, is to do the work in LabVIEW, format each X data point to be a string that corresponds to the time (so 5/26/20 8:25am) and have that get placed into Excel as a string.  Then have Excel convert that string to a time field.

And the first time you set up that formula in Excel, something will probably be wrong, so you'll have to experiment with it and adjust.

[cstorey]

The reason is you are not plotting the same things.  The LabVIEW graph is plotting the timestamp, the Excel graph is plotting the array of floating point numbers that is incremented by 250m on each loop iteration.  Bob demonstrated its easier to have the RGT plot x-y data as doubles in a single step.

You could send Excel all the data in a separate step as strings - Column #1 (X) the timestamp (as a string), Column #2 (Y) your measurement data as a double (string).  Then have the template setup to plot the data in those columns.  Your Excel column formatting should be setup with the cells properly formatted for the timestamp to display in a plot correctly.  I've attached the Excel file showing an example of the Excel format.

LabVIEW could use - %Y-%m-%d %H:%M:%S%3u

Excel would use custom format - yyyy-mm-dd hh:mm:ss.000 

Craig

[Murph · Accepted solution]

Thank you so much.

Excellent advice.

I ended up implementing the suggestion to send strings to Excel then wrote a macro in Excel to generate the graph.

This is now incorporated into the full application using templates, it's awesome!

It works perfectly.