Sorting on a Variable 4

[GShort]

I have a report that shows customers service calls for remote offices. They consist of Customer Number, Start Date & Time, Address, Zip.

Several of the offices have a large amount of tickets and would like to sort the report by zip code for planning reasons. By default the report is sorted by the start date and time of the service.

Is there a way to allow the user to choose what to sort the report by? I have tried to place a variable in the where clause but the report would not declare the date variable correctly until I took it out.

Thank

 

[AlexCuse]

if it's only one option you could do something like this:

order by case when @var = 1 then ZipCode
else OtherColumn end

If you want to get more fancy with it, you may need to use a stored procedure that uses dynamic SQL (bascally builds a query (with a order by based on your parameters) and executes it)

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[xlbo]

Alternatively, use a report parameter to control the sort order in the report

Set param up with 2 harcoded values "Default" and "Zip"

Call it "SortBy"

Set the sort property on the table / list to expression and test for which value hsa been selected

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

 

[AlexCuse]

That is good Geoff. I am just starting to get into SSRS but that will be very helpful.

for you

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[xlbo]

cheers - been "getting into" it for a year or 2 now. It's one of those apps where there's generally about 3 different ways of doing anything and about 10 different opinions about which way is best !

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

 

[AlexCuse]

Well, the case statement will only work with one column, and dynamic SQL sucks, so I will jump on your side of the fence on this one ;-)



I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[GShort]

Xlbo,
I was unable to get the parameter to working with the table properties.

In the parameter I have the following in the Available values:
Label = Account Number
I have tried serveral things on values
Value = Fields!CUSTNMBR.Value or =Fields!CUSTNMBR.Value or "=Fields!CUSTNMBR.Value" or CUSTNMBR

Of those four none of the formats seemed to work correctly (I had the same formats for the zip code).

I did do what AlexCuse suggested
"If you want to get more fancy with it, you may need to use a stored procedure that uses dynamic SQL (bascally builds a query (with a order by based on your parameters) and executes it)" but it is not very dynamic so it is not ideal though it does work.

Thanks for the help so far

 

[xlbo]

I don't think you quite get what I mean

You create a parameter - call it "SortBy" and make the label read "Sort By"

You give it default values for the user to choose from

Label: Start Date & Time
Value: Date

Label: Zip
Value: Zip

In the sort property of the table, rather than picking a field, chhose "Expression" which will take you to the expression editor. In here enter:

=iif( Parameters!SortBy.Value="Date", Fields!Date_Field_Name.VALUE, Fields!Zip_Code_Field.Value)

This works - I have it in several reports.


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

 

[xlbo]

Alex - interested to understand why "dynamic SQL sucks"

Whilst I would suggest there's no need in this instance as it would mean re-running the data to get the new sort order, I have quite a few stored procs that use dynamic SQL - building queries based on parameters passed through. Would certainly like to know if there are any issues with this methodology !

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

 

[AlexCuse]

Don't get me wrong, I have a proc or two out there that uses dynamic SQL (sometimes it is the only way), but I think that it is over used.

I think it sucks because it is a security risk, doesn't allow for caching of execution plans, and has scope issues that many people don't recognize.

Here is a great article on the subject, he explains it much better than I ever could

http://www.sommarskog.se/dynamic_sql.html

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[GShort]

Thanks for the dull crayon explnation .

I have it working the way that you discribed and I find it much nicer then the dynamic sql since I can change the query very easily.

 

[xlbo]

Thanks for the link alex - will have a thorough read of that. I thought most of the issue would be to do with sql injections and execution plans but this should give me a lot more detail on that. I have come at SQL from a reporting history rather than a database / DBA history so some of the security concepts are relatively new to me.

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