7 problems with automated PDF reports
If you want several automated PDF reports, there …Read more
We are often asked what is the best way to get figures from tables created in MRDCL, QPSMR or Reflect into Excel charts. The solution is remarkably simple as long as you follow five steps.
If you are using MRDCL, you will need to add the run control parameter CSV2 to the control stage of your MRDCL run. If you want rounded percentages, use CSV2R. This will give you the table you want to use for charting in a CSV file. If you want to chart percentages rather than figures (this is normally the case), you will need to turn off figures by using format NPRC. If you are using Reflect or QPSMR, you do not need to make the equivalent CSV2 setting as it is set for you by default - in other words, don't turn the option off! You will usually need to set your table formats to include NPRC to remove figures.
If you have used Quick Tables in Reflect or QPSMR, you will need to preserve the CSV file generated by selecting File > Preserve View File and Exit. If you don't, the CSV will disappear if you close the table in view.
From here, it's the same process regardless of whether you use MRDCL, Reflect or QPSMR. Open the Tables CSV file that is generated in the same folder as the rest of your project and save it as a XLSX file. You can produce a chart in a CSV file, but it will disappear if you try to save it.
You should have a file like the one below.
Now. comes the clever bit. Choose Data > Filter. Then from the filter on column A, deselect all the options and only check VT, CL, PV as shown below.
Having clicked OK, you are now ready to select just the cells you need and insert the chart you want. You may want to switch the rows and columns depending on the chart you are producing. You should get an Excel worksheet like the one below.
And, that's it. Your figures from MRDCL, Reflect or QPSMR are now in an Excel chart. You can also prepare charts in PowerPoint in the same way.
If you need any further help, please use the You Ask, We Answer form on our home page.