Cablecast has some pretty cool reporting features built in. However, it doesn’t do everything and that’s when people turn to Excel to fill in the gaps.
Here are some tips that might be helpful.
Getting the Data into Excel as XML
The best way, that I have found, to get data from Cablecast and into Excel is through XML. Excel has a nifty interpreter, which does a passable job presenting your data in a table within Excel.
First, go to the reporting section of Cablecast and select the report that you want. In our example, we use the Scheduled Shows report.
Don’t bother with any of the breakdown check boxes or charts, because Cablecast won’t generate those when outputting XML.
Make sure that you have the Output Format radio buttons set to XML. Click the go button. You’ll get a page that looks something like…
If you’re using FireFox, right-click on the XML data and select This Frame > Save Frame As…
Save the file into a directory of your choice, leaving the XML extension in place.
Next, open Excel and open the file (I like to drag the file into Excel). If your Excel is like mine, you’ll be asked how to open it. I like to open it as a table, but you can try your own options. For this example, just pick As an XML Table and click OK.
Excel will complain that there is no schema for the XML source and that it will make its best guess. That’s what we want, so click OK.
[A side note here: My setup has Excel crashing every time I do this. Once it restarts, the XML file is in place, so no harm and no foul. But it's still weird. Does anyone else have this happen to them?]
Your new table will appear in a table within Excel!
What’s in the Table?
It turns out that Excel is pretty good at showing the data from your XML export. It does have some weirdness that needs addressing.
First, columns A-L are pretty much garbage. They could be reduced to a single cell in the table, but because of the translation, they get put into columns. Copy the data down (if you need it) and just delete those columns, since they repeat the same data all of the way down the table.
Note that “TotalNewShows” and “TotalOldShows” refers shows that are played during that date range that have an event date that falls within. Old shows have event dates before the report’s date range, new shows are within.
Dealing with Times and Dates
The fields that hold the show lengths need to be formatted. By default, Excel treats them as text, which looks OK, but can not be calculated. We can fix this by using Excel’s format feature.
Select the Length column. Choose Format Cells… This will bring up the format window, which you can use to modify how Excel displays your column.
When the Format Cells… dialog box opens, select Time from the Category list.
From the Type list, select the “37:30:55” option.
Note: This option is telling Excel to treat the entry as a length and not as a day of the month. There is a neato post on Excel’s treatment of time at: http://www.cpearson.com/excel/datetime.htm
Click the OK button.
You shouldn’t notice any changes in your table but behind the scenes, Excel translated your show’s length into a number that can then be calculated. Let’s do that now!
If you have Excel 2003 or 2007, you can activate a totals row by right clicking anywhere within the table and selecting Table > Totals Row.
Now we can go to the bottom of our table and select the new cell that is just below the last entry in the Length column.
Click the arrow down button and select Sum.
You’ll now see the sum of all the lengths in the Length column!
Summing the Lengths Without an Excel Table
If you do not have a table, you can still accomplish the same task by using Excel’s SUM function.
Choose the cell directly below the last entry in the Length column. Type…
=SUM(F2:F13)
…where F2:F13 is the range of lengths that you are summing.
Note: The equals sign (=) is important here! It’ is what tells Excel to make a calculation instead of just printing “SUM(F2:F13)” to your display.
Note2: F2:F13 is an example of a range. If you click and drag a selection of cells in Excel, it makes a range. Ranges are explained quite well in this post: http://spreadsheets.about.com/od/r/g/range_def.htm
Converting Seconds to HH:MM:SS
For convenience, there is a TotalSeconds column that gives you the show’s length in seconds. For some, using this as a length is easier to understand than the trickery we just went through, especially when you get to more advanced Excel operations. You can use SUM and other Excel functions on the TotalSeconds column and then convert it into the more readable HH:MM:SS format of the Length field.
There is a quick trick to doing this.
First, select the field that you want in the HH:MM:SS format and change it to the time format that we used in the above steps by going to the Format Cell… menu.
Next, make the calculation and divide it by the magic number of 86400. This magic number converts seconds into a time that Excel understands so that its formating features work as expected.
We illustrate this in the next picture.
In the above example, we’ve summed the values in H32 through H38 and divided by our magic number, 86400.
If we did not change our formatting, we would get a result like this…
…which is the actual number that Excel uses for calculating dates, times and lengths.
Since we did change the format, we get the prettier…
…which is 6 hours, 31 minutes and 58 seconds.
Note: For fun, the next time that someone asks, “Do you have a minute?” reply, “For you? I have 0.00138888888888889!” Which is like saying that you have 2 minutes, but in a really funny way! 
That’s it for now. In the future, I may post on how to generate charts in Excel and how to do sorting and filtering. Let me know if you have any questions or if there are any other Excel tricks that you’d like to see.
Thanks!
Excel and Reports in Cablecast
Cablecast has some pretty cool reporting features built in. However, it doesn’t do everything and that’s when people turn to Excel to fill in the gaps.
Here are some tips that might be helpful.
Getting the Data into Excel as XML
The best way, that I have found, to get data from Cablecast and into Excel is through XML. Excel has a nifty interpreter, which does a passable job presenting your data in a table within Excel.
First, go to the reporting section of Cablecast and select the report that you want. In our example, we use the Scheduled Shows report.
Don’t bother with any of the breakdown check boxes or charts, because Cablecast won’t generate those when outputting XML.
Make sure that you have the Output Format radio buttons set to XML. Click the go button. You’ll get a page that looks something like…
If you’re using FireFox, right-click on the XML data and select This Frame > Save Frame As…
Save the file into a directory of your choice, leaving the XML extension in place.
Excel will complain that there is no schema for the XML source and that it will make its best guess. That’s what we want, so click OK.
[A side note here: My setup has Excel crashing every time I do this. Once it restarts, the XML file is in place, so no harm and no foul. But it's still weird. Does anyone else have this happen to them?]
Your new table will appear in a table within Excel!
What’s in the Table?
It turns out that Excel is pretty good at showing the data from your XML export. It does have some weirdness that needs addressing.
First, columns A-L are pretty much garbage. They could be reduced to a single cell in the table, but because of the translation, they get put into columns. Copy the data down (if you need it) and just delete those columns, since they repeat the same data all of the way down the table.
Note that “TotalNewShows” and “TotalOldShows” refers shows that are played during that date range that have an event date that falls within. Old shows have event dates before the report’s date range, new shows are within.
Dealing with Times and Dates
The fields that hold the show lengths need to be formatted. By default, Excel treats them as text, which looks OK, but can not be calculated. We can fix this by using Excel’s format feature.
Select the Length column. Choose Format Cells… This will bring up the format window, which you can use to modify how Excel displays your column.
When the Format Cells… dialog box opens, select Time from the Category list.
From the Type list, select the “37:30:55” option.
Note: This option is telling Excel to treat the entry as a length and not as a day of the month. There is a neato post on Excel’s treatment of time at: http://www.cpearson.com/excel/datetime.htm
Click the OK button.
You shouldn’t notice any changes in your table but behind the scenes, Excel translated your show’s length into a number that can then be calculated. Let’s do that now!
If you have Excel 2003 or 2007, you can activate a totals row by right clicking anywhere within the table and selecting Table > Totals Row.
Now we can go to the bottom of our table and select the new cell that is just below the last entry in the Length column.
Click the arrow down button and select Sum.
You’ll now see the sum of all the lengths in the Length column!
Summing the Lengths Without an Excel Table
If you do not have a table, you can still accomplish the same task by using Excel’s SUM function.
Choose the cell directly below the last entry in the Length column. Type…
=SUM(F2:F13)
…where F2:F13 is the range of lengths that you are summing.
Note: The equals sign (=) is important here! It’ is what tells Excel to make a calculation instead of just printing “SUM(F2:F13)” to your display.
Note2: F2:F13 is an example of a range. If you click and drag a selection of cells in Excel, it makes a range. Ranges are explained quite well in this post: http://spreadsheets.about.com/od/r/g/range_def.htm
Converting Seconds to HH:MM:SS
For convenience, there is a TotalSeconds column that gives you the show’s length in seconds. For some, using this as a length is easier to understand than the trickery we just went through, especially when you get to more advanced Excel operations. You can use SUM and other Excel functions on the TotalSeconds column and then convert it into the more readable HH:MM:SS format of the Length field.
There is a quick trick to doing this.
First, select the field that you want in the HH:MM:SS format and change it to the time format that we used in the above steps by going to the Format Cell… menu.
Next, make the calculation and divide it by the magic number of 86400. This magic number converts seconds into a time that Excel understands so that its formating features work as expected.
We illustrate this in the next picture.
In the above example, we’ve summed the values in H32 through H38 and divided by our magic number, 86400.
If we did not change our formatting, we would get a result like this…
…which is the actual number that Excel uses for calculating dates, times and lengths.
Since we did change the format, we get the prettier…
…which is 6 hours, 31 minutes and 58 seconds.
Note: For fun, the next time that someone asks, “Do you have a minute?” reply, “For you? I have 0.00138888888888889!” Which is like saying that you have 2 minutes, but in a really funny way!
That’s it for now. In the future, I may post on how to generate charts in Excel and how to do sorting and filtering. Let me know if you have any questions or if there are any other Excel tricks that you’d like to see.
Thanks!