Grouping Sorting String and Numerical Fields

I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.

Patient Date Albumin
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9

However, when I try to group by Albumin, Patient, it just sorts by Albumin.
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Adams, John 01/28/2007 4.1
Barker, Mark 01/22/2007 4.1
Adams, John 12/30/2007 3.9
Barker, Mark 01/05/2007 3.9
Adams, John 01/15/2007 3.2

What I'm looking for is this:
Patient Date Albumin
Barker, Mark 01/18/2007 4.3
Barker, Mark 01/22/2007 4.1
Barker, Mark 01/05/2007 3.9
Adams, John 01/28/2007 4.1
Adams, John 12/30/2007 3.9
Adams, John 01/15/2007 3.2

Is this something that can be done with grouping and sorting?

Thanks,
Chad

Hi,

guess that you did not want to group you wanted to just sort, right ? For getting the results pasted below you will have to Sort by Patient Desc, Albumin Desc. If that is not your intention, please post the RDL and the way you want to display the information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Grouping by Age

I have a table Age and need to create Report by Grouping Salesfigures according to Age.
I put the following expression into Grouping and Sorting Properties/General/Filter /Sorting Expression... as well as in Textbox Properties/ values..

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20")

Errormessage: Value expression for textbox "Age" error: Argument not specified for parameter 'FalsePart' of 'Public function IIF(Expression as Boolean, TruePart as Object, Falsepart As Object) As Object'.

Question 2 .
How to return Month as January, February... In correct order?
"DATENAME(mm, Sales.time) AS Month" (Ascending ) returns starting with April, August...
DATEPART(mm, Sales.time) AS Month (Ascending ) returns starting with 1, 10, 11 ...

Answer 1

Your expression is incomplete. As the error message says, you are missing the FlasePart of the nested Iif function, as well a parentheses

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20", ">20" ) )

Can you clarify Quaestion 2. E.g. where are you putting this code, can you paste your query?

|||

For Question 1 you don't have a false path to follow in your second iif.

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20",""))

|||Thank you so much!

SELECT SUM(Cd.Price) AS Sales, DATENAME(mm, Purchase.time) AS Month, Staff.Name
FROM Staff INNER JOIN
Purchase ON Staff.Staff_id = Purchase.Salesperson_id INNER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY DATENAME(mm, Purchase.time), Staff.Name
ORDER BY Month Asc

The outcome is starting with April...
(not with January)?|||Thank you.. still, "Argument not specified for false part". Something I've misunderstood?

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20"),IIF(Fields!Age.Value <31,"21-30"), IIF(Fields!Age.Value < 41,"31-40"),IIF(Fields!Age.Value <51,"41-50",">50"))

I put this value expression In textbox "Age"/Expression, as well as in Grouping Sorting properties /General/Filter/ Sorting|||

Yes you have misunderstood this slightly. The definition of the Iif function is

Iif(<<condition>>, TruePart, FalsePart)

Your expression puts many Iif's all passed into a single Iif. You actually have to nest the Iif's as the FalsePart of the previous Iif

Your expression:
=IIF
( Fields!Age.Value < 16 <- condition
, "<16" <- TruePart
, IIF(Fields!Age.Value <21,"16-20") <- FalsePart nested IIF
, IIF(Fields!Age.Value <31,"21-30") <- Error 4th argument
, IIF(Fields!Age.Value <41,"31-40") <- Error 5th argument
, IIF(Fields!Age.Value <51,"41-50",">50") <- Error 6th argument
)

Correct Expression:
=IIF
( Fields!Age.Value < 16 <- condition
, "<16" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 21 <- condition
,"16-20" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 31 <- condition
, "21-30" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 41 <- condition
, "31-40" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 51 <- condition
, "41-50" <- TruePart
, ">50" <- FalsePart
)
)
)
)
)

just make sure you get the parentheses right and you remove the comments

|||

As far as your query goes, display the name but order by number. for this to work you must include both in the GROUP BY clause

SELECT SUM(Cd.Price) AS Sales, DATENAME(mm, Purchase.time) AS Month, Staff.Name
FROM Staff INNER JOIN
Purchase ON Staff.Staff_id = Purchase.Salesperson_id INNER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY DATENAME(mm, Purchase.time)
, DATEPART(mm, Purchase.time)
, Staff.Name
ORDER BY DATEPART(mm, Purchase.time) Asc

|||

Would it be possible to create a field from a select e.g.

SELECT Age, AgeGroup =

CASE

WHEN (age >= 1 and age <= 3) THEN 'Age 1-3'

WHEN (age >= 4 and age <= 5) THEN 'Age 4-5'

WHEN (age >= 6 and age <= 7) THEN 'Age 6-7'

ELSE 'Over age'

END

FROM tblAge

|||In fact, I prefer this option, give RS less work to do.

grouping and sorting in matrix control

I have a dataset returned from sql server that can be represented for the
purpose of this discussion with 2 columns. From the server all the data is
sorted first by column 1 and then by column 2 so that the resultset looks
like the following:
column1, column2, column3
a, 1/1/2007, 10
a, 2/1/2007, 30
a, 3/1/2007, 15
b, 10/1/2006, 5
b, 11/1/2006, 1
b, 12/1/2006, 100
b, 1/1/2007, 10
b, 2/1/2007, 9
c, 11/1/2006, 22
c, 12/1/2006, 33
c, 1/1/2007, 44
When I put this data into a matrix with the dates making the columns and
column1 values for each row I get the following
1/1/2007 2/1/2007 3/1/2007 10/1/2006 11/1/2006 12/1/2006
a 10 30 15
b 10 9 5 1
100
c 44 22
33
what I want is the following:
10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007
a 10
30 15
b 5 1 100 10 9
c 22 33 44
With the dates sorted. I know I can do it by changing the stored proc but
that opens up all sorts of issues with other things. Is there any way to get
the data looking like I want using reporting services and not modifying the
stored proc?
thanksOn Feb 28, 2:11 pm, Brian <B...@.discussions.microsoft.com> wrote:
> I have a dataset returned from sql server that can be represented for the
> purpose of this discussion with 2 columns. From the server all the data is
> sorted first by column 1 and then by column 2 so that the resultset looks
> like the following:
> column1, column2, column3
> a, 1/1/2007, 10
> a, 2/1/2007, 30
> a, 3/1/2007, 15
> b, 10/1/2006, 5
> b, 11/1/2006, 1
> b, 12/1/2006, 100
> b, 1/1/2007, 10
> b, 2/1/2007, 9
> c, 11/1/2006, 22
> c, 12/1/2006, 33
> c, 1/1/2007, 44
> When I put this data into a matrix with the dates making the columns and
> column1 values for each row I get the following
> 1/1/2007 2/1/2007 3/1/2007 10/1/2006 11/1/2006 12/1/2006
> a 10 30 15
> b 10 9 5 1
> 100
> c 44 22
> 33
> what I want is the following:
> 10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007
> a 10
> 30 15
> b 5 1 100 10 9
> c 22 33 44
> With the dates sorted. I know I can do it by changing the stored proc but
> that opens up all sorts of issues with other things. Is there any way to get
> the data looking like I want using reporting services and not modifying the
> stored proc?
> thanks
>From your results, it looks like column2 is sorting aphabetically (I'm
assuming that column2 is not defined as a datetime field in the report
or dataset). You should be able to use the conversion function CDate()
in your sort expression. Something like this should work: CDate(Fields!
column2.Value) and the direction should be ascending. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Group Sorting in a Table

Good morning,
I am having a little bit of an issue with the sorting of data in a report.
The report works great, but the sorting could be better.
I have the data in a Table.
I have a list of data that is grouped into three groups. The groupings are
by Project, Phase, and CostCode, in that order. I have set the Sorting in
the Table to be by Project, then Phase, and then CostCode. All of them
Ascending sort.
Project sorting and CostCode sorting is working great but the middle one,
Phase, is sorting completely opposite. It is sorting Descending. I cannot
figure it out.
Anyone have any ideas or a tip? Is this a known bug?Try going into the properties of the table pick groups edit group(the one
thats not working correctly) and try setting the sorting within the group. I
think that that should work for you.
"abillmeier" wrote:
> Good morning,
> I am having a little bit of an issue with the sorting of data in a report.
> The report works great, but the sorting could be better.
> I have the data in a Table.
> I have a list of data that is grouped into three groups. The groupings are
> by Project, Phase, and CostCode, in that order. I have set the Sorting in
> the Table to be by Project, then Phase, and then CostCode. All of them
> Ascending sort.
> Project sorting and CostCode sorting is working great but the middle one,
> Phase, is sorting completely opposite. It is sorting Descending. I cannot
> figure it out.
> Anyone have any ideas or a tip? Is this a known bug?
>
>|||Thank my friend, you nailed it spot on. I found it myself a little bit ago.
"KimB" <KimB@.discussions.microsoft.com> wrote in message
news:6C6199FB-8330-4F1A-AC1E-3FA302E7879D@.microsoft.com...
> Try going into the properties of the table pick groups edit group(the one
> thats not working correctly) and try setting the sorting within the group.
> I
> think that that should work for you.
> "abillmeier" wrote:
>> Good morning,
>> I am having a little bit of an issue with the sorting of data in a
>> report.
>> The report works great, but the sorting could be better.
>> I have the data in a Table.
>> I have a list of data that is grouped into three groups. The groupings
>> are
>> by Project, Phase, and CostCode, in that order. I have set the Sorting
>> in
>> the Table to be by Project, then Phase, and then CostCode. All of them
>> Ascending sort.
>> Project sorting and CostCode sorting is working great but the middle one,
>> Phase, is sorting completely opposite. It is sorting Descending. I
>> cannot
>> figure it out.
>> Anyone have any ideas or a tip? Is this a known bug?
>>

GridView update, with SqlDataSource UpdateCommand set from Code-behind. (C#)

Hi all

I have a GridView on an aspx page, that is enabled for editing, deletion and sorting.

In the Page_Load event of the aspx page, i add a SqlDataSource to the page, and bind the source to the GridView.

When i click the update, or delete button, it makes a PostBack, but nothing is affected. I'm sure this has got something to do with the parameters.

First, i tried having the GridView.AutoGenerateColumns set to True. I have also tried adding the columns manually, but no affect here either.

The code for setting the commands, and adding the SqlDataSource to the page are as follows:

string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
string selectCommand = "SELECT * FROM rammekategori";

SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand);
ds.ID = "RammeKategoriDS";
ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = @.Kategoribeskrivelse WHERE (Kategorinavn = @.Kategorinavn)";
ds.DeleteCommand = "DELETE FROM rammekategori WHERE (Kategorinavn = @.Kategorinavn)";

Parameter Kategorinavn = new Parameter("Kategorinavn", TypeCode.String);
Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse", TypeCode.String);
ds.UpdateParameters.Add(Kategorinavn);
ds.UpdateParameters.Add(Kategoribeskrivelse);
ds.DeleteParameters.Add(Kategorinavn);

Page.Controls.Add(ds);

SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource;

if (m_SqlDataSource != null)
{
this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID;
}

As mentioned - no affect at all!

Thanks in advance - MartinHN

It turned out, that the SQL-statements where wrong. I got it all to work now, by using a ?-mark, instead of @.Parametername in the SQL.

So this works:

string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
string selectCommand = "SELECT * FROM rammekategori";

SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand);
ds.ID = "RammeKategoriDS";
ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = ? WHERE Kategorinavn = ?";
ds.DeleteCommand = "DELETE FROM rammekategori WHERE Kategorinavn = ?";

Parameter Kategorinavn = new Parameter("Kategorinavn");
Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse");
ds.UpdateParameters.Add(Kategorinavn);
ds.UpdateParameters.Add(Kategoribeskrivelse);
ds.DeleteParameters.Add(Kategorinavn);

Page.Controls.Add(ds);

SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource;

if (m_SqlDataSource != null)
{
this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID;
}

I was working on a MySQL server, and not a MS-SQL server, as I normally do...

|||Is there any particular reason why you are adding the SqlDataSource dynamically rather than declaring it in your .aspx code?|||

>>Is there any particular reason why you are adding the SqlDataSource dynamically rather than declaring it in your .aspx code?

Yes - there sure is. I want to define alle data-access information, such as SQL-statements in a lower-tier-layer, so i would be able to remove the GUI, and change it with another GUI. It just gives a better architecture to it...

|||

martinhn wrote:

Yes - there sure is. I want to define alle data-access information, such as SQL-statements in a lower-tier-layer, so i would be able to remove the GUI, and change it with another GUI. It just gives a better architecture to it...

It sounds like the ObjectDataSource is more suited for what you are trying to do.

HTH,
Ryan

Gridview Sorting

I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got

ORDER BY
CASE WHEN @.sortBy='' THEN DateCreated END,
CASE WHEN @.sortBy='DateCreated' THEN DateCreated END

and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @.sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?

If your select is actually returning a datatype of int, it should sort normally. If it's returning an int in a varchar, then well...|||

That's not what I was going for. I am using Atlas to do live searching/filtering of the gridview, and in order to use LIKE in my WHERE clause, I had to convert all datatypes to string. I've solved the problem by using

DECLARE @.sort AS varchar(63)
IF (RIGHT(@.sortBy,4)='DESC') BEGIN
SET @.sort=LEFT(@.sortBy,LEN(@.sortBy)-5)
END
ELSE BEGIN
SET @.sort=@.sortBy
END

with what I had before. Then in my code-behind, if "DESC" is part of the param, I start at the bottom of the table and insert rows into a new table.

If

sortBy.EndsWith("DESC")ThenDim dt2As DataTable = dt.CloneFor iAsInteger = dt.Rows.Count - 1To 0Step -1Dim rAs DataRow = dt.Rows(i)

dt2.ImportRow(r)

Next

dt = dt2

EndIf