Distinct and grouping problem

can someone look at the statement for me, it's not giving me distinct names,

SELECT DISTINCT TOP 10 leaderboard.name, Last(leaderboard.date_cr) AS date_cr, Last(leaderboard.handicap) AS handicap, Max(leaderboard.total) AS total, Last(leaderboard.cpsa) AS cpsa, Last(leaderboard.rbss) AS rbss, Max(leaderboard.visible) AS visible, Last(leaderboard.score) AS score
FROM leaderboard
WHERE (((leaderboard.visible)=True) AND ((leaderboard.rbss)=True))
GROUP BY leaderboard.name, leaderboard.total
ORDER BY Max(leaderboard.total) DESC , Last(leaderboard.date_cr);

if I take the second grouping off, I get distinct names, but the information isn't correct. i.e. the date, handicap, score, is from different record than the total field.

any help much appreciated.

thanks

That is because you are grouping on more than one column. The total column will get you additional values / rows in the Grouping section.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks for your reply.

So if I take the second grouping out how can I make the information 'line up' correctly?

i.e. have the correct date, handicap, score with the right total. As at the moment they are the wrong values for the Total.

|||That is hard to tell, because you only told that the query "is not working". Depending on your business case, you might want to get the max(total) or not ? That depends on the logic you want to put in the query.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

ok, these are the rules

1. If somebody has played more than once, only the highest score as calculated in one above should be displayed
2. the results should display correct details by total score in descending order
3. In the case where there are two or more equal total scores by different competitors, these should be sorted by date in ascending order (e, i - January 31 before Feb 12)

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

Display None if field is empty

I am grouping my report on a field called Crew. This field is sometimes empty and so the group header doesn't show anything. I want that if this field is empty the report should display 'none' instead of just leaving it blank. How can I do that?
ThanksUse a formula to display the group header. Ie, if Crew is Group #1 :

If IsNull(Group#1Name) then
"None"
else
Group#1Name

Group Header names are available through formula editor the same as all other report/database fields.|||Thanks for replying kristyw. I am new to Crystal. Where should I put this formula?|||Create a new formula and drag it onto your form.|||It gives me an error saying "The ) is missing" and highlisghts Group on the first line.

If IsNull(Group#6Name) then
"None"
else
Group#6Name|||Here's my working formula :

If IsNull(GroupName ({VRP_CUST_DETAILS.ACCOUNTNUMBER})) then
"None"
else
GroupName ({VRP_CUST_DETAILS.ACCOUNTNUMBER})

Learn how to use CR Formula Editor - it's very handy when it comes to creating these types of formulas as you can drag the field names into it and easily find all the available functions.|||Thanks, that works just fine.

Display Multiple Records In One Grouping Line

Is it possible to display three different record values in one grouping
header? For instance, I have End of Year values for three different records
for a grouping called Burbank. I am grouping by Burbank, but I want to show
the Year End value for Burbank in 2005, 2006, and a 2007 projection, but they
are three separate records in my query. If statements will only grab the
first year and subreports slow down the report a ton just for one value.
Thanks,
BJYou could always use a case statemnt for each year in your select statement.
"bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
news:D0704272-4328-44F9-8902-55CE36C704A8@.microsoft.com...
> Is it possible to display three different record values in one grouping
> header? For instance, I have End of Year values for three different
> records
> for a grouping called Burbank. I am grouping by Burbank, but I want to
> show
> the Year End value for Burbank in 2005, 2006, and a 2007 projection, but
> they
> are three separate records in my query. If statements will only grab the
> first year and subreports slow down the report a ton just for one value.
> Thanks,
> BJ|||How would I interact with the select statement in my report though? I have
the say 3 separate records in my SELECT statement of my dataset, but in the
report I want to display them all in one row. It is almost like a Matrix,
but I don't want to grow across. I want to preacknowledge that I have a
value from 2005, 2006, and 2007 that I want to display from three separate
records, but on one line of the report.
"Ben Watts" wrote:
> You could always use a case statemnt for each year in your select statement.
> "bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
> news:D0704272-4328-44F9-8902-55CE36C704A8@.microsoft.com...
> > Is it possible to display three different record values in one grouping
> > header? For instance, I have End of Year values for three different
> > records
> > for a grouping called Burbank. I am grouping by Burbank, but I want to
> > show
> > the Year End value for Burbank in 2005, 2006, and a 2007 projection, but
> > they
> > are three separate records in my query. If statements will only grab the
> > first year and subreports slow down the report a ton just for one value.
> >
> > Thanks,
> >
> > BJ
>
>|||You can put a sub report in a cell on a line. I have done this on the main
detail line and I have done this in an additional detail line below. That
might work for your needs. Takes a little playing around with the subreport
to make it look correct when embedded in the main report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
news:AA264F47-3040-40C6-9C87-9805596376B0@.microsoft.com...
> How would I interact with the select statement in my report though? I
> have
> the say 3 separate records in my SELECT statement of my dataset, but in
> the
> report I want to display them all in one row. It is almost like a Matrix,
> but I don't want to grow across. I want to preacknowledge that I have a
> value from 2005, 2006, and 2007 that I want to display from three separate
> records, but on one line of the report.
> "Ben Watts" wrote:
>> You could always use a case statemnt for each year in your select
>> statement.
>> "bjkaledas" <bjkaledas@.discussions.microsoft.com> wrote in message
>> news:D0704272-4328-44F9-8902-55CE36C704A8@.microsoft.com...
>> > Is it possible to display three different record values in one grouping
>> > header? For instance, I have End of Year values for three different
>> > records
>> > for a grouping called Burbank. I am grouping by Burbank, but I want to
>> > show
>> > the Year End value for Burbank in 2005, 2006, and a 2007 projection,
>> > but
>> > they
>> > are three separate records in my query. If statements will only grab
>> > the
>> > first year and subreports slow down the report a ton just for one
>> > value.
>> >
>> > Thanks,
>> >
>> > BJ
>>

Display group data on every page

Hi!
I have two groupings placed in on list object and I'm trying to
grouping data to be displayed on every page but without luck. The
grouping data is displayed correctly at the beginning of the group but
since it might be a long list of items in one group I would like to
display the group header on every page. Is this possible? I've
searched the net and found answers that it should be possible to do
this but I can't figure out how. HELP Please.
Cheers ErikOn Feb 14, 10:16 pm, e...@.liffner.se wrote:
> Hi!
> I have two groupings placed in on list object and I'm trying to
> grouping data to be displayed on every page but without luck. The
> grouping data is displayed correctly at the beginning of the group but
> since it might be a long list of items in one group I would like to
> display the group header on every page. Is this possible? I've
> searched the net and found answers that it should be possible to do
> this but I can't figure out how. HELP Please.
> Cheers Erik
When editing group in designer, there should be checkboxes called
'Repeat Group Header', Repeat Group Footer'. These will cause the
group header/footer to be repeated on every page.|||On 15 Feb, 01:56, "Rowen" <rowen...@.gmail.com> wrote:
> On Feb 14, 10:16 pm, e...@.liffner.se wrote:
> > Hi!
> > I have two groupings placed in on list object and I'm trying to
> > grouping data to be displayed on every page but without luck. The
> > grouping data is displayed correctly at the beginning of the group but
> > since it might be a long list of items in one group I would like to
> > display the group header on every page. Is this possible? I've
> > searched the net and found answers that it should be possible to do
> > this but I can't figure out how. HELP Please.
> > Cheers Erik
> When editing group in designer, there should be checkboxes called
> 'Repeat Group Header', Repeat Group Footer'. These will cause the
> group header/footer to be repeated on every page.
Hi!
Is this true even if I use a list as the grouping object. I'm unable
to find the checkboxes in my rdl file.|||On Feb 16, 2:04 am, e...@.liffner.se wrote:
> On 15 Feb, 01:56, "Rowen" <rowen...@.gmail.com> wrote:
> > On Feb 14, 10:16 pm, e...@.liffner.se wrote:
> > > Hi!
> > > I have two groupings placed in on list object and I'm trying to
> > > grouping data to be displayed on every page but without luck. The
> > > grouping data is displayed correctly at the beginning of the group but
> > > since it might be a long list of items in one group I would like to
> > > display the group header on every page. Is this possible? I've
> > > searched the net and found answers that it should be possible to do
> > > this but I can't figure out how. HELP Please.
> > > Cheers Erik
> > When editing group in designer, there should be checkboxes called
> > 'Repeat Group Header', Repeat Group Footer'. These will cause the
> > group header/footer to be repeated on every page.
> Hi!
> Is this true even if I use a list as the grouping object. I'm unable
> to find the checkboxes in my rdl file.
The checkbox is visible when editing a report in design mode (i.e. in
Visual Studio). There will not be any checkbox in the rdl file.
If you want to add the property to the rdl file via a text editor, it
should be like this:
<TableGroup>
<Header>
<TableRows>...
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="...">
<GroupExpressions>
...
</GroupExpressions>
</Grouping>
<Sorting>
...
</Sorting>
</TableGroup>

display data without auto grouping in report builder

I have a report built by report builder. When I run report several column
data did not show repeated data on each row. Instead it just show one time
and the other repeated data field just auto merged with the first cell. Is
there any way to show each row data no matter it is the same as the one in
the cell above?
Example now I see:
Type City State
HSLA PHILADELPHIA PA
NORCROSS GA
PAOLI PA
PAOLI PA
What I want to get is:
HSLA PHILADELPHIA PA
HSLA NORCROSS GA
HSLA PAOLI PA
HSLA PAOLI PA
Is it possible to do that in "design report" or after hit "Run Report"? I
don't want the auto grouping in adhoc report. Please HELP! I really
appreciate it.I figured it out. The column is shown in the group if you click 'sort and
group'. Just drag all the fields you want and drop them on the table so that
the group is created in entity name instead of the individual field names.
"Daisy" wrote:
> I have a report built by report builder. When I run report several column
> data did not show repeated data on each row. Instead it just show one time
> and the other repeated data field just auto merged with the first cell. Is
> there any way to show each row data no matter it is the same as the one in
> the cell above?
> Example now I see:
> Type City State
> HSLA PHILADELPHIA PA
> NORCROSS GA
> PAOLI PA
> PAOLI PA
> What I want to get is:
> HSLA PHILADELPHIA PA
> HSLA NORCROSS GA
> HSLA PAOLI PA
> HSLA PAOLI PA
> Is it possible to do that in "design report" or after hit "Run Report"? I
> don't want the auto grouping in adhoc report. Please HELP! I really
> appreciate it.
>