We have a simple qvw feeding an Nprinting app/connection/report and in the excel template for the report, we are populating a pivot table. The report has a title tab, a data tab and then on the third tab, we've created a pivot table that refreshes upon open of file (thus works beautifully when the task cycles on a couple hundred values of a variable in the data model).
But we are having an issue when trying to use the "create as a data model" feature in excel. We are doing this to add the distinct count calc into the pivot table (value field settings > summarize values as ..sum, avg, etc.). When using the pivot table from a data model build, the pivot table reads a currency value as text and thus won't sum. We've tried the formatting options for the data column in designer but it hasn't helped.
And even in just testing the data model pivot without the sum, adding in the blank pivot then previewing... we get an error saying the "data source is not supported."
Bottom line, we are aiming to perform the distinct count on the excel side of the report vs. in the qvw table before exporting. Any thoughts or advice are greatly appreciated!
