Not Totaling Properly

Hello. I am building a very simple report. I have a date field and a count of accounts field. All I have for the filter is a date range and whether the account number has a classification of IN. The count field is not working properly though. It shows the date multiple times with a count of one instead of one date and 5 under the account number. Any thoughts on why this is not counting properly? I have another report that is almost identical but it has location and count of account number as the fields.

So this is how it is working:

3/1/2010         1
3/1/2010         1
3/1/2010         1
3/1/2010         1
3/2/2010         1
3/2/2010         1
3/2/2010         1
3/2/2010         1

When It should be doing this:
3/1/2010         4
3/2/2010         4

The formula is COUNTDISTINCT(Account Number)

The filter is All of (date on or after, date on or before, STATUS = "IN"

Thanks!
dminx13Asked:
Who is Participating?
 
lammy82Connect With a Mentor Commented:
I'm not sure as I don't usually use Report builder either, unfortunately!! If you could modify the reporting model to include just the date portion of the field then this would be the easiest way forward (e.g. create a view of the table with the time portion removed and then report from that view) ... but you've already said you don't have access to the SQL server.

Looking on the web, I think  you may be able to define a new field using a forumla (http://msdn.microsoft.com/en-us/library/aa337532(SQL.90).aspx) which makes use of the DATEONLY function (http://msdn.microsoft.com/en-us/library/aa337219(SQL.90).aspx) and then use this new field on your report?  
0
 
Chris LuttrellSenior Database ArchitectCommented:
You have to be grouping by your date field, the CountDistinct is a group function that will use its enclosing group.  It sounds like you are not grouping but just showing the detail lines therefore only 1 row per "group" as far as that function goes.
0
 
dminx13Author Commented:
I am new to Report Builder. How do you group on Report Builder? In Crystal it is very simple. You pick something and make it a group. It does not appear to be that easy in Builder. I am tring to understand just dragging and dropping to get it to group but that isn't working. Both of the fields are in a group named Abstract Data because that is the entity that the fields are coming off of. How do I get the date to be a group and the account number a count within that group?

Thanks
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Chris LuttrellSenior Database ArchitectCommented:
Sorry but I don't use the report builder interface enough to know the specifics off hand, I tested it early on but I do everything through the BIDS designer tool where you have much more control.  If no one else can answer, I might be able to test something in report builder later when I have more free time.  Good luck.
0
 
dminx13Author Commented:
Thanks. We'll see if anyone else has any ideas. I'd prefer working in something else too but not my choice!
0
 
SharathData EngineerCommented:
Post your query. or try like this.
select [Date],sum(Cnt_Account_Number)
from ( your query) as t1
group by [Date]
0
 
dminx13Author Commented:
Not sure how to do a query in builder or where for that matter in Report Builder. All I have access to is the interface. I don't have access to SQL at all.........
0
 
SharathData EngineerCommented:
In order to get what you want, you need to modify the back end query.
Having access to interface only, you cannot get your expected result as a report.
0
 
Chris LuttrellSenior Database ArchitectCommented:
Sharath,  this is not really a query question although it is just in SQL Server zones, they are trying to use a Group Function in the Report Builder tool that is part of SSRS.  Yes, you could write a query to get the group totals straight from the database and then just display them as detail rows in the report, but that is not the real solution they are after as they would still not know how to do grouping in the report when that becomes the only right way to solve a reporting problem.
0
 
dminx13Author Commented:
And what baffles me is that I have another report that works just fine. Instead of date it is location. Like this:

ABC           4
DEF            5
GHI             2

Instead of
ABC     1
ABC     1
ABC     1
ABC     1
DEF      1
DEF      1
DEF      1
DEF      1
DEF      1
GHI       1
GHI       1

So i don't know what the problem is with the date.

Deanna
0
 
lammy82Commented:
Do your dates have a time portion in the database which you are removing using formattong on the report?  Just a thought....
0
 
dminx13Author Commented:
That is a good point. If it doesn't print out the time, then the time is most likely different on each piece. I will see if there is a way I can drop the time from the format entirely and see if that work. Any thoughts on how to do that would be appreciated while I am playing with it too.
Thanks!
0
 
dminx13Author Commented:
Hadn't thought about the date being formatted as a time. Once I found that out and used the DATEONLY function I was able to group accordingly.
0
 
dminx13Author Commented:
That worked and I found the DATEONLY function. Thanks!
0
All Courses

From novice to tech pro — start learning today.