Custom Report issue

Hello i got this custom report

Am receptionat marfa de la 

[!Nota intrare marfa:1,1,1,1,1,1,1,1,1]

>Denumire Prod.|U/M|ID|[N]Cantitate|[C]Pret fara T.V.A.|[C]T.V.A.|[C]Pret cu T.V.A.|[C]Pret Total cu T.V.A.|[N]T.V.A. (%)

@@inv

and here is my sql

SELECT  

/*

MIN(CONVERT(nvarchar,InventoryTransactions.Date,105)) AS DATE,

*/

InventoryItems.Name,

InventoryTransactions.Unit,

InventoryTransactions.InventoryTransactionDocumentId,

convert(decimal(5,0),round(InventoryTransactions.Quantity,0)),

convert(decimal(5,3),round((InventoryTransactions.TotalPrice)/(InventoryTransactions.Quantity),3)) as PretUnitar, 

convert(decimal(5,4),round(((InventoryTransactions.TotalPrice)/(InventoryTransactions.Quantity)*InventoryItems.DefaultBaseUnitCost),4)) as TVA, 

convert(decimal(5,4),round(((InventoryTransactions.TotalPrice)/(InventoryTransactions.Quantity))+((InventoryTransactions.TotalPrice)/(InventoryTransactions.Quantity)*InventoryItems.DefaultBaseUnitCost),4)) as PretTVA, 

convert(decimal(20,2),round(((InventoryTransactions.TotalPrice)+(InventoryTransactions.TotalPrice*InventoryItems.DefaultBaseUnitCost)),3)) as Total,

convert(decimal(5,0),round(InventoryItems.DefaultBaseUnitCost*100,0)) as tviw

FROM InventoryItems

INNER JOIN InventoryTransactions

ON InventoryItems.Id=InventoryTransactions.InventoryItem_Id

WHERE InventoryTransactions.Date > '{Start}' AND InventoryTransactions.Date < '{End}'

/*

'%' + convert(decimal(5,2),round(InventoryItems.DefaultBaseUnitCost*100,2)) as tviw

GROUP BY InventoryItems.Name,InventoryTransactions.Unit,InventoryTransactions.Quantity,InventoryTransactions.TotalPrice

*/

ORDER BY InventoryTransactions.Date

and here is my problem: when selecting date 24-09-2015 to 08-10-2015 the reports works corectly and its displaying me the info that i need

and when i select date between 21-09-2015 to any other date that is grader than 21 it is displaying me this error message

What should be the problem that is causing this ? Please help me

Is it SQL Server CE? Most google results for the error message points to a SQL Server CE bug.

Yes it is SQL Server CE … Can i fix that ? Or do you got any solution for my problem ?

If removing Order By solves the issue that may mean you hit a bug reported here.

You can search google if a fix exists for that. I’ll strongly recommend to upgrade CE to SQL Server Local DB or SQL Server Express.

1 Like

Here is a guide to install SQL Server Express 2014 it also includes instructions to migrate your CE database to SQL.

You will get much faster performance with SQL Server vs CE. You also gain redundancy and greater control over the database.

1 Like

the order by didn’t fixed the problem … there was other mistake that i didn’t noticed … i modified convert(decimal(5,0),round(InventoryTransactions.Quantity,0)), to convert(decimal(10,2),round(InventoryTransactions.Quantity,2)), and now its working fine … there were some value in my stock bigger than 5 digits that was the problem … thanks

oh and another thing that i was curious… in my pivot table is there any setting for the numeric field to show me letters ? i got there U/M that that should show me the measuring unit but that is a numeric field and i want to change it to a text field … is there any way i can do that ? but i want my pivot table to remain the same way

You can use SQL’s CASE WHEN structure. Google brings a lot of tutorials but this one is close to what you need. See how it selects yes or no depending on table values.