Any Sage Line 50 Report Designer experts out there?

Any Sage Line 50 Report Designer experts out...

Didn't find your answer?

Working on my own fairly simple report for Job Costing which shows each job and the number of hours worked by each employee on that job from timesheets entered, with a total no of hours worked for the job.

Each job is attached to one of 3 offices, Manchester, Leeds or Liverpool
and these are set as job analysis codes

I am attempting to create a filter on this report that will only include jobs attached to the Manchester office, by going into Format/Filters and picking the table/field and using the operator = with the office name, which shows in the filter builder as
'tblJob'.'JobAnalysisName1'=Manchester

I'm obviously doing something wrong because without the filter the report runs fine albeit with all the jobs for all offices included, but when I try to run the report with this filter, it throws up an error message

ODBC SQL Execute call failed [Microsoft][ODBC Microsoft Access Driver]Too few parameters. Expected 5.

Can anyone help me please? Sage helpline won't help me because we don't have Sage Cover - was considered an unnecessary expense by my predecessor!

Thanks in advance

Lesley Anderton

Replies (7)

Please login or register to join the discussion.

avatar
By listerramjet
17th Nov 2004 12:48

an Office solution!
if the underlying database is Access, then you could use Access to create appropriate queries, and link directly to them in Excel, or use the Access report writer, or both.

the downside is that you would need to garner an understanding of the data dictionary.

Thanks (0)
avatar
By User deleted
17th Nov 2004 11:25

An alternative
Mamut Software, which just won the Small business Software of the Year, has this functionality as standard. If it is a report that you require regularly you may find some benefits both in time and satisfaction by changing software.

Thanks (0)
avatar
By lesleyanderton
17th Nov 2004 12:34

reply to Tom
Although I've had the misfortune to use Line 50 for several years, I'm a newcomer to Sage Job Costing. Inherited it 3 months ago when I started with my new employer, an architects practice. My predecessor bought it and then couldn't get to grips with it, so I've started from scratch really with a blank piece of paper so to speak, and have to say I'm quite proud of what I've set up! Just gone live and started inputting transactions this month (timesheets, purchase and sales invoices)

Not impressed with the standard reports, none of them seem to meet our needs so I'm busy designing my own. Find it really frustrating that the most useful of the standard Sage reports cannot be opened to tweak and be saved as a different name.

Not quite reached the work in progress side of things yet and not sure whether it will be of any use to me anyway - all my jobs have been set up as fixed price because we do not bill based on time or cost plus etc. At the moment I use a good old excel spreadsheet to calculate work in progress, but if I do use Sage's WIP I'll let you know how I get on.

Thanks (0)
Routemaster image
By tom123
17th Nov 2004 10:27

Glad you got sorted. I too have had cause to look in the access tables to try and understand how it works

Do you use the work in progress reports?

We struggle to get anything useful from ours.

Thanks (0)
avatar
By lesleyanderton
17th Nov 2004 10:20

thanks for the responses
based on your comments and a bit of detective work last night, I have solved the problem

Sage Job Costing is really one big Access Database, so I went into Access and opened the table called Job. It would seem that in report designer a field called 'tblJob'.'JobAnalysisName1' will print the office, ie Manchester, Leeds or Liverpool on my report, but there is not actually a column called JobAnalysisName1 in the table, instead there is a column called StandardJobType1 with values of 1, 2 or 3 in it. These I discovered equate to the 3 offices and 3 = manchester. So in my report designer I deleted my previous filter and replaced it with 'tblJob'.'StandardJobType1'=3, and hey presto it worked and now I can print a report with just the jobs relating to Manchester office on it.
Many thanks once again for the pointers that helped me sort the problem

Thanks (0)
avatar
By listerramjet
16th Nov 2004 13:31

?
It is a long time since I have looked at the (infamous) sage line 50 report designer, but the error message suggests it is having a problem in passing the parameter to the database engine.

adding quotes around manchester may solve it, but if not, and if you can get into the filter builder, then you might try substituting

tblJob.JobAnalysisName1="Manchester"

i.e. remove the single quotes around the text to the left of the equals sign.

It may be that the problem is in how you are setting up the parameter in the report - it may be a simple syntax problem. I recall that the help files were sometimes helpful, so you could try help when in the report designer.

A quick search on the web indicates that there are lots of options if you want a training course, but little useful online help. However you might find someone who has had the relevant training and is willing to let you have a copy of the notes?

Thanks (0)
Routemaster image
By tom123
16th Nov 2004 12:45

Quote marks?
Working on the basis that there might be a sinmple answer, should't Manchester be in quotation marks (try ' or ")

Let us know how you get on, I also have the misfortune to struggle with sage JC.

Thanks (0)