expression sum and iif help?

Hi All,

SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))

i write this expression inside the group footer row, it give a #error when generating report.

please help

Cheers

Nick

Hi Nick,

The possible reason for this error is that the Sum function can only add fields of the same data type. It is quite possible in this case that the data types of the fields in the TRUE and FALSe part of the If statement is different. Try making the data types same of both the fields by usinf the Conversion functions available.

Hope this helps.

Regards,

Pradeep

|||

Hi,

I think ure iif() is returning varying datatypes. 0 being a integer and Fields!tsHours.Value returning some other datatype.

Use a conversion function such as CDbl() on both fields.For eg: CDbl(Fields!tsHours.Value) and CDbl(0).This makes either of the values returned as Double.

eg:

Sum(IIf(Fields!NO.value>=1,CDbl(Fields!tsHours.Value),Cdbl(0)))

Somiya

|||

Thanks Chaubey and Somiya,

You guys are right, after i explicitly convert to same type , then it works.

Cheers

Nick

Expression Problem - GrandTotal

In my SSRS 2005 report I have a column in my table that I cannot get a SUM in my table footer (I'm not referring to report footer!!) due to the way it is calculated. Here's the low down:

I will focus just on the columns in my table unique to this problem. Remember, I'm using a Table Control in SSRS 2005, not just a Body section!!!

In my table I have:

Commission % ProjGross ProjFee

.25 1000 ProjGross * Commission %

.50 2000 ProjGross * Commission %

.7 3000 ProjGross * Commission %

...

ProjFee = ProjGross x Comission %

the values above are in a Group. The column's group values render fine and are correct using this formula in my ProjFee's group textbox:

=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) +

(SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)

So, now for the problem. I cannot just create an expression in my table footer like the one this below because it would only take the first commission %25 and wouldn't represent a true some of the column ProjFee. YOu can't total up Commission % or you'd get a wacky % somthing like 500%!

=ProjGross * Commsion %

I don't want to do this totaling in SQL first either...and can't; there's a lot more behind this calculation which is why and the SQL would be horrendous. I'm not quite sure how I'm gonna handle the Commission % in my table footer Total for this column.

Since I cannot just reference the Group column's name in my footer and do a SUM on that field (it's out of scope) I don't know what else to try

check this out:

http://www.photopizzaz.biz/footersum.jpg

http://www.photopizzaz.biz/footersum2.jpg

http://www.photopizzaz.biz/footersum3.jpg

All I want to do is get my Footer GT to work. The formulas I have in both the group field and footer field for ProjGross are as follows:

Group:

=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) +

(SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)

Footer (right under group)

=sum((((Fields!PostedAmount_InHouse.Value + Fields!NewPDs_Check.Value + Fields!NewCCs_Check.Value) / Fields!CurrentPostingDay.Value)

* (Fields!TotalPostingDays.Value - Fields!CurrentPostingDay.Value)

+ (Fields!PostedAmount_InHouse.Value + Fields!OldPDs_Check.Value + Fields!NewPDs_Check.Value + Fields!OldCCs_Check.Value + Fields!NewCCs_Check.Value))

* (Fields!FeeSchedule.Value)) / 100

I’ve played around with moving the SUM in the Footer field since that works sometimes but no luck so far. If I could only refrence the Group field and just do SUM(groupfieldname) but that’s something I have asked and nobody seems to know why you can’t do that in a SSRS 2005 table when you’re trying to do that in a Footer field to sum a Group field by referencing the Group field’s label name. SSRS 2005 gives you an error when you try doing that saying that you must stay in scope when referencing names and you can’t reference a Group name forma footer textbox in a table. That would sure make my life easier than trying to fiddle around with the SUM I have in my footer textbox to try to get it to work

|||am I just not explaining myself or is SSRS 2005 ending up to be just like Crystal where you have to figure out quirky ways to get things done? The fact of the matter is, I can't simply do a sum on a column by referencing a group's textbox name from the table footer expression. If you are dealing with very complex expressions and you m ust only use an expression and cannot reference a group field directly from the footer, you are left with what I have tried to do above...which is a nightmare because my GT is not coming out and I don't know if it can because of the Commission % (Feesched) being a % and you can's sum a % in the footer like this for an overall GT

exporting to a pdf problem

HI everybody, my problem is that when i export to a pdf file i get on first page cut of info and the second page I just get a header and footer. Is there anything to do to fix this problemWould you please post your RDL and the PDF file. If you are using SP1, would
you also post the <ReportName>.rdl.data file.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"J-man" <J-man@.discussions.microsoft.com> wrote in message
news:4F0ECF86-1C7B-4B53-ACA7-0F39C7EEC81F@.microsoft.com...
> HI everybody, my problem is that when i export to a pdf file i get on
first page cut of info and the second page I just get a header and footer.
Is there anything to do to fix this problem|||Just checking - are you running SP1?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"J-man" <J-man@.discussions.microsoft.com> wrote in message
news:4F0ECF86-1C7B-4B53-ACA7-0F39C7EEC81F@.microsoft.com...
> HI everybody, my problem is that when i export to a pdf file i get on
> first page cut of info and the second page I just get a header and footer.
> Is there anything to do to fix this problem|||Well, if you didn't apply SP1 you are probably not running it. When you
browse to http://servername/reportserver, what version do you see? If it
says 878, you are running SP1. If it says 743, you are running RTM. You can
install SP1 from the link off http://www.microsoft.com/sql/reporting.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"j-man" <j-man@.discussions.microsoft.com> wrote in message
news:A76A0B7C-3A01-4CFB-B43A-1D0485E847E0@.microsoft.com...
>I have no clue, I'm just a dumb kid. How would i find this out?
> "Brian Welcker [MSFT]" wrote:
>> Just checking - are you running SP1?
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "J-man" <J-man@.discussions.microsoft.com> wrote in message
>> news:4F0ECF86-1C7B-4B53-ACA7-0F39C7EEC81F@.microsoft.com...
>> > HI everybody, my problem is that when i export to a pdf file i get on
>> > first page cut of info and the second page I just get a header and
>> > footer.
>> > Is there anything to do to fix this problem
>>