So far, we have seen how we can display static data and make calculated fields that Excel can understand and modify.
Now it's time to make the data dynamic: We shall pull it form a MySQL database and we shall see the modifications we have to add to the code written by DWMX.
This is our sample table:
1.- Create the Recordset:
2.- Make the table, give it some format and insert the Recordset Fields:
3.- Now, select the row where Recordset data is displayed and add a Repeat Region:
4.- When you have finished, hit F12: You should see how Excel is opened by the browser and will display something like this:
5.- Now let's start with the tricky part: We shall display the results for SUM and AVERAGE functions, which will need a range of cells to work: So far, we know the starting point of the range (look again at the image above: It will be B2, which is the coordinate for the first cell of the recordset, because B1 is filled with the header).
However, we don't know yet where one the end point will be. So, before we continue, we can put it as in the image below:
6.- Now, think about it: If we have 4 records in our example, the last cell of the results will be B5, if we had 6 records, it would be B7 and so on... That is:
The last row number will be the number of total records plus the number of rows we've got above the first Recordset row!!
Then, let's add the Server behavior "Display Record Count->Display Total Records":
7.- Now, just modify the code to add "1" to the total number of records. If you select the translated part in design view, it will be shown in code view. See below:
8.- Now, simply add "+1" to the code:
It's done!! our spreadsheet is ready to display the values!! Just remember that in the previous step you must add the number of rows you've got before the first row of the repeat region. In our example there was just one, but it could have been more.
You will also note that DW will display a couple of errors in the Server Behaviors panel. Don't worry about them: They simply mean that the code isn't the one DW expects to find for the "Display Total Records" Server behavior (we've added some code to it), nothing more.