logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
scott9677  
#1 Posted : Monday, March 16, 2020 5:36:55 PM(UTC)
scott9677

Rank: Advanced Member

Groups: Registered
Joined: 5/18/2018(UTC)
Posts: 40

Thanks: 2 times
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?

epf  
#2 Posted : Tuesday, March 17, 2020 9:53:01 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
The error comes probably from the JavaScript when it tries to build the chart...
Difficult to say, it seems that the type of either Axis or Measure is not consistent, if you manage to reproduce this on Northwind, I can have a look.
scott9677  
#3 Posted : Tuesday, March 17, 2020 5:29:43 PM(UTC)
scott9677

Rank: Advanced Member

Groups: Registered
Joined: 5/18/2018(UTC)
Posts: 40

Thanks: 2 times
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>
epf  
#4 Posted : Wednesday, March 18, 2020 7:41:40 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
I just checked, I got the error (not in JavaScript),
if you tranform the Employees.HireDate element which is a DateTime date into Day(Employees.HireDate),
you must also change the element type from Default to Numeric...then the chart is fine.

Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.