Specify Group Order

[AnnieRider]

Is there a way to set a specific order like you can in Crystal?

I have 4 groups, 1, 2, 3 and NULL and I want them ordered as such. Setting the sort to Ascending puts the NULL first. I gather that if this is possible at all then it's probably an expression but I can't find any info on how to do this.

Regards
Annie

 

[xlbo]

I would tend to do this either in the SQL dataset or as the result of an expression in the sort area

Sort on:

=IIF(Fields!FieldName.Value is null, 4,Fields!FieldName.Value)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnnieRider]

Hi

I get a red line under the word null as unknown identifier. I've tried moving it round, putting the is null before the field etc but it still doesn't like it.

Any thoughts?

Thanks
Annie

 

[xlbo]

Always get null syntax issues with different apps - maybe try:

=IIF(isnull(Fields!FieldName.Value), 4,Fields!FieldName.Value)

alternatively, in your SQL:

Select a,b,c, Group, isnull(Group,4) as OrderBy
FROM ......

and then sort on "OrderBy"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnnieRider]

Hi Geoff

It still doesn't like the isnull but doesn't error if I use isnothing. However it still won't order correctly. I'm not sure I can put it in the SQL, I'm using a Stored Procedure.

Thanks
Annie

 

[AnnieRider]

Incidentally the =IIF(isnothing(Fields!Caseload_Ref.Value), 4,Fields!Caseload_Ref.Value)

works in a seperate textbox by setting the textbox value to 4 if null but when I try to sort on the textbox I'm told I can't sort on Report Items, I don't suppose there may be a way around that is there?

Thanks again
Annie

 

[SgtJarrow]

Using SQL, you should be able to use the following where you define the numeric value:

CASE WHEN FieldName IS NULL THEN 100 ELSE FieldName End 'SortOrder'

I set the null to 100 because you only have 4 levels TODAY, but that more than likely could change....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[AnnieRider]

I'm sorry I'm quite new to SSRS, where exactly do I put that? In the Stored Procedure or in the code in the report?

Thanks

 

[SgtJarrow]

That would be part of the stored procedure...

So if your stored procedure looked like this:

SELECT
    Field1
    , Field2
    , Field3
    , thesortedfield 'SortOrder'
FROM sometable

You would make it look like this:

SELECT
    Field1
    , Field2
    , Field3
    , CASE WHEN thesortedfield IS NULL THEN 100 ELSE thesortedfield END 'SortOrder'
FROM sometable

Then in your SSRS report, you only have to have it ordered by the field becuase SQL will return 100 as the sort value for anything with a NULL.

Does that explain a bit better?

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[AnnieRider]

Brilliant! Thanks ever so much
I thought it was going to drive me mad!