Error with a Report in ssrs

Question

0.00/5 (No votes)

See more:

SSRS-2008

Good day to you all, please i need a help with the following:

I get the following error: "column is invalid in the select list because it is not contained in either an aggregate function or the group by clause when i ran the query;

SQL

SELECT CardType, Sum(Amount) AS Total
FROM POSTRANS_Cards
WHERE TransDate='22/11/2015'
GROUP BY CardType

My intention is to get a set of record that show the total value for a specific cardtype on a specified date.

Please any will be appreciated.

Thank you
Frank

Posted 4-Dec-15 0:47am

Frankie-10589807

Updated 4-Dec-15 0:55am

CHill60

v3

Add a Solution

Comments

CHill60 4-Dec-15 6:58am

Are you sure that is the error generated by that code? There is nothing wrong with that query (although I suspect there is a problem with your schema looking at that date)

Frankie-10589807 4-Dec-15 9:16am

Yes Mr. Chill60, that is the error message i got while at the ssrs, but when i ran the same querry in sql server, this is what i got: "Conversion failed when converting date and / or time from character string"

Thank you Mr. Chill60 for your assistance, actually i am a beginner in this.Thanks again.

CHill60 4-Dec-15 9:27am

I assume the column TransDate is defined as Date?
In which case try using an unambiguous date format i.e.
WHERE TransDate = '22-NOV-2015'
Once you fix that issue there must be something else in the report that is causing the original error you posted

Frankie-10589807 4-Dec-15 10:10am

Yes Mr. Chill60, the TransDate field is defined as Date, and using this format:
WHERE TransDate = '22-NOV-2015' in the sql server side, the query ran well. thank you very much, but i also want to ask, is it possible to set a parameter that reference that field from the same query?

I really appreciate your assistance Mr. Chill60, you know, You are just a genius. Thanks again.

Frankie-10589807 4-Dec-15 10:29am

Hi Mr. Chill60, Wish to inform you that all is now working correctly even when i used a parameter to reference that field "TransDate" at the ssrs side. I thank you so much and more power to your elbow.

Frank.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-12-04T04:18:00

Solution 1

Posting as solution as OP has confirmed it worked.

Use an unambiguous date format so that SQL does not try to convert the string. i.e.

WHERE TransDate = '22-NOV-2015'

In response to the further comments, you can get the details for all of the dates thus:

SQL

SELECT CardType, TransDate, Sum(Amount) AS Total
FROM POSTRANS_Cards
GROUP BY CardType, TransDate
ORDER BY TransDate

Or if you want to run the report for a single date have a look at Lesson 1 on the following tutorial
Adding parameters to reports[^]

Posted 4-Dec-15 4:18am

CHill60

Updated 4-Dec-15 4:19am

v2

Comments

Richard Deeming 4-Dec-15 11:15am

The only truly unambiguous date literal format for SQL is yyyyMMdd, with no separators.
WHERE TransDate = '20151122'

Frankie-10589807 4-Dec-15 11:37am

Ok, thank you so much Mr. Richard. I appreciate.

Frank.

CHill60 8-Dec-15 10:29am

Very, very good point - not only is this locale-independent (which I wasn't :blush:) but remains unambiguous for datetime in the older versions of SQL Server

Frankie-10589807 15-Dec-15 10:56am

You are right Mr. Deeming, however, the date format provided by Mr. Chill60 for my challenge work well and to me it reduces the complexity of the initial format i used. These things are machines you know, sometimes they behave in a surprising way. thank you for contributing to the solution for my challenges.