i have a Report having 2 groups table3_Group1 for username filed, group name table3_Group2 based on calldate filed. i have column called LH i used the calculation to display like below =IIF( (sum(Fields!Vendor_DurationSeconds.Value)/3600)=0,"0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2))) now i need row count numbers based on above expression, those are fall in in above scope my sample is like below Username Call date LH number rows +Jain 5 01/11/10 20 1 40 1 50 1 60 1 70 1 2 (need to avoid 00) +Mark 02/11/10 00 13 1 26 1 00 I tried to get Number rows by using runningvalue,row number, countrows but no luck my expressions are: =RunningValue(IIF( (sum(Fields!Vendor_DurationSeconds.Value)/3600)=0,"0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2 ) ) ),Count,"table3_Group2") in above table3_Group2 is my second group and expression is used for LH calculation. So i need rowcount based on group expression and aswell total row count. please help me on this its very urgent i didn't get any proper stuff in internet. if you need any info tell me i will provide u. Thanks in advance Jacks

your expression =IIF( (sum(Fields!Vendor_DurationSeconds.Value)/3600)=0,"0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2))) could be simplified to =IIF( (sum(Fields!Vendor_DurationSeconds.Value)=0,"0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2)))My Blog "Karl Beran's BI Mumble"

can you also submit your dataset query. I think you are trying to solve something using reporting expressions that could be done in the t-sql/mdxMy Blog "Karl Beran's BI Mumble"

Thanks for ur reply for tricky question I have 2000 server and 2005 client so while using rownumber, not supporting to do. so i need to do capture those row counts based on expression and group by using SSRS 2005. Now iam trying to capture rowcounts based on expression and group by using runningvalue function, i have already tried by using rownumber,countrows but no one is working. finally iam using runningvalue expression is =RunningValue(IIF( (sum(Fields!Vendor_DurationSeconds.Value)/3600)=0,"0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2 ) ) ),Count,"table3_Group2") in above table3_Group2 is my second group and expression is used for LH calculation. iam getting error like below: (running value or rownumber functions). Aggregate functions cannot be nested inside other aggregate functions. Now help me how to capture above sample row counts structure by using runningvalue or is there any SSRS functions. sample rowcount structure Username Call date LH number rows +Jain 5 01/11/10 20 1 40 1 50 1 60 1 70 1 2 (need to avoid 00) +Mark 02/11/10 00 13 1 26 1 00 help me Thank you in advance

the above isn't your datset query. Add a new column to your dataset with: CASE WHEN LH = 0 THEN 0 ELSE 1 AS 'number rows' Then in the detail level have an expression =Fields!number_rows.Value and in the group rows =sum(Fields!number_rows.Value)My Blog "Karl Beran's BI Mumble"

<form id="aspnetForm" action="edit" enctype="application/x-www-form-urlencoded" method="post"> <script type="text/javascript"></script> <script type="text/javascript"></script> <noscript></noscript> </form>

· Thanks first, I have got the row count like 1 or 0 by using CASE WHEN LH = 0 THEN 0 ELSE 1 condition. Now i getting problem to sum the condition. to do 'number rows' that to single column by using sql i need to change the business logic a very lot. so i trying to get those sum by using SSRS in 2nd group table3_Group2 i have given condition to get 1 or 0 as below =IIF((IIF( (sum(Fields!Vendor_DurationSeconds.Value)/3600)=0, "0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2))))=0,0,1). by using above condition iam getting 1 or 0 based on LH calculation.this is fine. so iam giving below code with in group 1 to get sum value as below. =Running value(IIF((IIF( (sum(Fields!Vendor_DurationSeconds.Value)/3600)=0, "0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2 ))))=0,0,1), SUM,"table3_Group1") but iam getting error like "Running value or rownumber ,Aggregate functions cannot be nested" so please can suggest me how get those sum values by using SSRS. Thanks in advance

I'm very confused. 1) Can you get a column coming back from your SQL query that shows a value of 1 or 0? 2) If you can, there should be no issue with applying a sum at each group level, this is what the SSRS wizard does automatically when you select a column to be a group rather than detail level. 3) If you can't, post the t-sql and I'll put it in there for you.My Blog "Karl Beran's BI Mumble"

Hi Beran, Actually to adding new column in t-sql i need to change lot of businesslogic. so i wrote the if condition in SSRS textbox expressoin as below. IIF((IIF( (sum(Fields!Vendor_DurationSeconds.Value)/3600)=0,"0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2 ))))=0,0,1) by using above code iam getting 0 or 1 values in group 2. now i need to capture the sum in group 1 for above 0 or 1's so iam using below thing Running value(IIF((IIF( (sum(Fields!Vendor_DurationSeconds.Value)/3600)=0, "0.00", ( ROUND(sum(Fields!Vendors_loans.Value) / ( sum(Fields!Vendor_DurationSeconds.Value)/3600),2 ))))=0,0,1), SUM,"table3_Group1") but iam getting error like "Running value or rownumber ,Aggregate functions cannot be nested" so please can suggest me how get those sum values by using SSRS. I will be in still working on it through online, can u provide ur suggestions,Thanks in advance

The error "Running value or rownumber ,Aggregate functions cannot be nested" is exactly what it says. The reason you are getting this error is that it is not possible to do what you are attempting. Put it in the SQL, this sort of logic shouldn't be in the report layer. If you submit your query I'll show you how to add the row count.My Blog "Karl Beran's BI Mumble"