I'm trying to create a chart that has 'month to date' information for the year using a SQL Server source.

It has a date field for the day,an int field for the number of transactions that month up to that date, and a money field for transaction volume.

I've built up a chart.
In Drop Table Elements I have a custom Month(datefield) as a splitter. Day(datefield) in the Drop Row elements as an axis, and TransactionCount in drop data elements as an NVD3 Series Line on one chart and volume in drop data elements as an NVD3 series line on another chart.

It's giving an error "Specified Cast Not Valid" on the chart, but if I look at the CSV output, it exports correctly. There are some missing dates in the report where nothing happened that day.

It used to show the full sql statement so I could use that to troubleshoot with but I don't see that. I've tried casting the money field as float and that didn't help.

Any thoughts?

Here's a bit of a crazy example, but it does duplicate the issue. Basically it's trying to give a trend chart with the separate lines for each month, the day of month for the x axis, and the amount on the Y axis. I used EmployeeID because it is an int.

It gives the error when you do the view but if you export it to CSV there is data.

Thank you for your time in looking into this. I used the 32 bit version 5.1.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<GUID>9364273a-a19c-4596-ab63-34bc1b209eba</GUID>
<ViewGUID>9d30aa90-d4ee-4891-bb6f-40b56a28afa7</ViewGUID>
<Sources>
<ReportSource>
<GUID>ee10ec21-3245-4c76-8324-be3b4086fcc2</GUID>
<Name>Audit (Repository)</Name>
<ConnectionGUID>1</ConnectionGUID>
<MetaData />
<MetaSourceGUID>3d80412e-03a4-4a96-818a-a2aecef4c813</MetaSourceGUID>
</ReportSource>
<ReportSource>
<GUID>03a8262e-091f-4392-8b2e-cef2cb4659d0</GUID>
<Name>Northwind (Repository)</Name>
<ConnectionGUID>1</ConnectionGUID>
<MetaData />
<MetaSourceGUID>52833575-11ae-4b7d-8b5e-0f9b29d1267a</MetaSourceGUID>
</ReportSource>
</Sources>
<Models>
<ReportModel>
<GUID>caa2fa69-18ef-4c58-b976-8e041bd3a38f</GUID>
<Name>Model</Name>
<SourceGUID>03a8262e-091f-4392-8b2e-cef2cb4659d0</SourceGUID>
<Elements>
<ReportElement>
<GUID>4213ee93-a4aa-4b3d-9eae-8ac5573b5014</GUID>
<Name>Employees.HireDate</Name>
<DisplayName>Hire Date Month</DisplayName>
<Format>d</Format>
<PivotPosition>Column</PivotPosition>
<AggregateFunction>Max</AggregateFunction>
<TotalAggregateFunction>Max</TotalAggregateFunction>
<SerieDefinition>Splitter</SerieDefinition>
<MetaColumnGUID>b4a7f5d4-1045-4c67-b724-578e116ceff5</MetaColumnGUID>
<SQL>Month(Employees.HireDate)</SQL>
</ReportElement>
<ReportElement>
<GUID>0b864f6e-3ae9-46a6-aff7-1f2c6ddfa8d4</GUID>
<Name>Employees.HireDate</Name>
<DisplayName>Hire Date Day</DisplayName>
<Format>d</Format>
<PivotPosition>Row</PivotPosition>
<AggregateFunction>Max</AggregateFunction>
<TotalAggregateFunction>Max</TotalAggregateFunction>
<SerieDefinition>Axis</SerieDefinition>
<MetaColumnGUID>b4a7f5d4-1045-4c67-b724-578e116ceff5</MetaColumnGUID>
<SQL>Day(Employees.HireDate)</SQL>
</ReportElement>
<ReportElement>
<GUID>bfa416f8-048c-439c-af4b-96362dfd97a3</GUID>
<Name>Employees.EmployeeID</Name>
<DisplayName />
<Format>N0</Format>
<PivotPosition>Data</PivotPosition>
<Nvd3Serie>Line</Nvd3Serie>
<MetaColumnGUID>34215f49-e6a5-45e8-bfb8-d51c3a8f0033</MetaColumnGUID>
</ReportElement>
</Elements>
</ReportModel>
</Models>
<Views>
<ReportView>
<GUID>9d30aa90-d4ee-4891-bb6f-40b56a28afa7</GUID>
<Name>View</Name>
<Views>
<ReportView>
<GUID>62cd3188-e7f1-44c9-9948-db8a3c844f6b</GUID>
<Name>Model</Name>
<Views>
<ReportView>
<GUID>1f72504e-6196-4d7f-ae06-152d99aa07ab</GUID>
<Name>Model Container</Name>
<Views>
<ReportView>
<GUID>9601c66e-f231-48f9-8136-27de934fc174</GUID>
<Name>Page Table</Name>
<TemplateName>Page Table</TemplateName>
<SortOrder>1</SortOrder>
</ReportView>
<ReportView>
<GUID>203fa1d3-a7b8-4ee5-97ef-104267812700</GUID>
<Name>Chart JS</Name>
<TemplateName>Chart JS</TemplateName>
<SortOrder>2</SortOrder>
</ReportView>
<ReportView>
<GUID>5532cd98-1f72-4251-813e-5d5c2acc4a53</GUID>
<Name>Chart NVD3</Name>
<TemplateName>Chart NVD3</TemplateName>
<SortOrder>3</SortOrder>
</ReportView>
<ReportView>
<GUID>0028da1d-3764-4f91-a92c-12f524da791f</GUID>
<Name>Chart Plotly</Name>
<TemplateName>Chart Plotly</TemplateName>
<SortOrder>4</SortOrder>
</ReportView>
<ReportView>
<GUID>e12c163e-1d37-4699-b4fd-538ef20f56fa</GUID>
<Name>Data Table</Name>
<TemplateName>Data Table</TemplateName>
<SortOrder>5</SortOrder>
</ReportView>
</Views>
<TemplateName>Model Container</TemplateName>
<SortOrder>1</SortOrder>
</ReportView>
</Views>
<TemplateName>Model</TemplateName>
<ModelGUID>caa2fa69-18ef-4c58-b976-8e041bd3a38f</ModelGUID>
<SortOrder>1</SortOrder>
</ReportView>
</Views>
<TemplateName>Report</TemplateName>
<SortOrder>1</SortOrder>
</ReportView>
</Views>
</Report>