ssrs 2008 sorting

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
            ,Username
            ,IA.INDEX_OPERATOR
			,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
			,CONVERT(VARCHAR(11), IA.INDEX_END_DT, 106)
			,month(IA.INDEX_END_DT)
            ,Users.Name + ' ' + Users.[Surname]
            ,lastname + ' ' + firstname 
            ,Username
            ,IA.INDEX_OPERATOR
            

Open in new window

DavesmAsked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
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.
0
 
srikanthreddyn143Commented:
how did you try the sorting?
0
 
DavesmAuthor Commented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Nico BontenbalCommented:
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.
Top10.rdl
0
 
DavesmAuthor Commented:
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
0
 
Nico BontenbalCommented:
Replace DataSet1 in my code with the name of your dataset.
0
 
DavesmAuthor Commented:
My Dataset is Dataset1 Nicobo
0
 
ValentinoVBI ConsultantCommented:
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").
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.