Learn how to a build a cloud-first strategyRegister Now


ssrs 2008 sorting

Posted on 2012-08-30
Medium Priority
Last Modified: 2012-09-05
I Have created a ssrs summary report with a volume column when i  try sort by the volume its does not sort

Is the a way i can create an expession to sort the column or is there another way i can sort by volume.

I would also like to  highlight the top 10 using condition formatting

select Users.Department
			,CONVERT(VARCHAR(11), IA.INDEX_END_DT, 106) AS Index_Date
			,month(IA.INDEX_END_DT) as MonthNo
			,Users.Name + ' ' + Users.[Surname] as UserName
            ,lastname + ' ' + firstname as Username1
			,COUNT(DISTINCT Batch_ID) AS Volume
		,task = 'Indexing' 
from IA..ia_mysql_etl IA
            left join IA..Combined_Interval_User_Lookup Users on Users.[fNumber] = RIGHT(IA.INDEX_OPERATOR,8)
            left join QUICKSELL.orgman.[public].org_user PUser on Puser.Username =  RIGHT(IA.INDEX_OPERATOR,8)
where IA.INDEX_END_DT   between @StartDate and @EndDate
            and IA.INDEX_START_DT IS NOT NULL    
            and puser.active = '1'      
GROUP BY Users.Department
            ,Users.Name + ' ' + Users.[Surname]
            ,lastname + ' ' + firstname 

Open in new window

Question by:Davesm
LVL 11

Expert Comment

ID: 38352550
how did you try the sorting?

Author Comment

ID: 38353182
Hi  I have got the sorting right

I am just trying to  use conditional formating to format the top 10 rows would u be able to help me
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 38353627
See the attached example. I'm using the expression
=iif(RowNumber("DataSet1")<=10,"Yellow","No Color")

Open in new window

for the BackgroundColor property of the vendor text box.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 38353702
Hi I am getting this error when i try that code  

The BackgroundColor expression for the text box ‘Volume2’ has a scope parameter that is not valid for RunningValue, RowNumber or Previous.  The scope parameter must be set to a string constant that is equal to the name of a containing group within the Tablix ‘Tablix2’.
Build complete -- 1 errors, 0 warnings
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 38353771
Replace DataSet1 in my code with the name of your dataset.

Author Comment

ID: 38353844
My Dataset is Dataset1 Nicobo
LVL 22

Accepted Solution

Nico Bontenbal earned 2000 total points
ID: 38353881
Are you using 2005 or 2008. You title says 2008, but the Topic is SQL 2005.

I just found at that
=iif(RowNumber(Nothing)<=10,"Yellow","No Color")

Open in new window

also works for me. Maybe it works for you too.

If you are still having problems, can you upload the report so I can have a look.
LVL 37

Expert Comment

ID: 38360639
Please note that the scope parameter is case sensitive.  So "Dataset1" and "DataSet1" are not the same.  If the name does not match exactly with the actual name of the data set, it will throw the above error.

Using Nothing should indeed work as well.  Or, another possibility is specifying the name of the tablix, for instance RowNumber("Tablix2").

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Integration Management Part 2
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question