| Author |
Topic |
|
abhit_kumar
Posting Yak Master
147 Posts |
 Posted - 2010-04-06 : 04:51:34
|
| Hello Frnds,i have made report in tabular format but im getting wrong total in table footer .In table i have inserted one Group and one Table details.at query part data is displaying like as:-InvNO InvTot PayNo. PayRecvd100 110 55 90100 110 66 20200 150 22 100200 150 33 50So i have displayed InvNO and InvTot in Group level and at detail level i have taken PayNo and PayRecvd. So Single InvNo 100 is coming with multiple PayNo and Payrecvd, whcih is good.But the InvTot is coming as 520, but its should come as 260.In report data presentation is correct but in footer total its coming double.Please guide me.Regards,abhi |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-06 : 05:23:58
|
| You need to include an expression like follows=Sum(IIF(RowNumber("YourInvNOgroupNameHere")=1, Fields!InvTot.Value,0))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
abhit_kumar
Posting Yak Master
147 Posts |
Posted - 2010-04-06 : 05:38:35
|
| its gives error that aggregate function can not be nested inside other aggregate functionthis expression needs to add at Row group or table Details or footer part? |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-06 : 06:18:54
|
The problem is your table layout. You're using one table, where you should be using 2. The invoice total for invoice No 100 is 110. This should be stored only once. You're storing it twice. That's a good indication that you're tables aren't normalized correctly. If you need multiple payment records per invoice, then they should be stored in a separate table. That way, you're not storing repeating invoice data for each payment. However, it's quite possible that you can't do anything about that for now. So here's what you can do. Use a sub query to select the distinct invoice no and total (assuming that each Invoice No only has one total. If this assumption is false, then you've got a lot more problems).The inner query should return each invoice No only once, along with its total. The outer query sums them together.SELECT SUM(InvTot)FROM ( SELECT InvNO, InvTot FROM table -- Put any where clause you want to use here GROUP BY InvNO, InvTot ) z There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-06 : 06:55:34
|
quote:
Originally posted by abhit_kumar
its gives error that aggregate function can not be nested inside other aggregate functionthis expression needs to add at Row group or table Details or footer part?
at row footer. I'm not nesting any aggregate function------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-06 : 07:33:52
|
quote:
Originally posted by visakh16=Sum(IIF(RowNumber("YourInvNOgroupNameHere")=1, Fields!InvTot.Value,0))
I don't think you can use Row_Number (I assume that's what you meant) inside a SUM() aggregate. And IIF is not a T-SQL function.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
abhit_kumar
Posting Yak Master
147 Posts |
Posted - 2010-04-06 : 08:20:38
|
| yes becoz of it, its give me error, is there any way to handle it in desing layout,bcoz i dont want to touch the query part |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-06 : 08:24:48
|
quote:
Originally posted by abhit_kumar
yes becoz of it, its give me error, is there any way to handle it in desing layout,bcoz i dont want to touch the query part
Did you try the one I posted?There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
abhit_kumar
Posting Yak Master
147 Posts |
Posted - 2010-04-06 : 08:32:26
|
| Yeah from query we can do the things, but if possible can we do it from layout part itself? |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-06 : 08:57:04
|
quote:
Originally posted by abhit_kumar
Yeah from query we can do the things, but if possible can we do it from layout part itself?
I'm not really sure. SSRS isn't really my thing. I'm not sure if you can filter the data there. Is it always double? If so, you could divide it by 2 when displaying it. However, if the problem is a query that returns bogus data, then fixing the query is the best solution.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-06 : 11:53:00
|
quote:
Originally posted by DBA in the making
quote:
Originally posted by visakh16=Sum(IIF(RowNumber("YourInvNOgroupNameHere")=1, Fields!InvTot.Value,0))
I don't think you can use Row_Number (I assume that's what you meant) inside a SUM() aggregate. And IIF is not a T-SQL function.There are 10 types of people in the world, those that understand binary, and those that don't.
This is Reporting services forum and what op's asking for is report expression.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-06 : 11:53:32
|
quote:
Originally posted by abhit_kumar
Yeah from query we can do the things, but if possible can we do it from layout part itself?
Can you show your actual expression used?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
abhit_kumar
Posting Yak Master
147 Posts |
Posted - 2010-04-07 : 01:45:05
|
| Currently at Table footer i have used expression as:-=Sum(Fields!InvTot.Value) |
|
|
|
|
Total is doubling