SSRS Custom Code


Here is my code:
public dim tCount as double

public function AddCount(per as double) as double
      tCount = tCount + per      
      return per
end function

From code, you can see that I want to add 'per'. The code works fine within group. I am calling
from table footer and it is giving me 0.0.

Where did I miss and what?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
Is the report more than one page long? I've found that SSRS re-instantiates its code assembly for each page, which would clear tCount. You can't depend on the custom code to save things for you.
Use a Sum(<expression>) instead. In SSRS 2008 you can use them just about anywhere.
hackman122Author Commented:
Thanks rscowden,

I am generating percertage based on numbers in group. I tried sum but it spits 'scope' error also, can't use Sum  ( Sum (....))
I have hidden groups using toggle item so it shows 1 page.

any other thoughts?
Megan BrooksSQL Server ConsultantCommented:
In SSRS 2005 you might have more trouble with that (I have to go from memory because I no longer have it around). In some cases you might need to specify the scope as Sum(<expression>, <scope>), where <scope> would most likely be the dataset name in quotes, to obtain an overall sum.
If you also need a group sum you can specify the group name instead (or leave out the scope), but in all cases the group has to be active for you to reference it. This means you can only obtain a group sum from somewhere within that group. <scope> is used to point to a group other than the innermost one.
Worst case, if SSRS 2005 won't sum over a dataset scope in a table footer, you can wrap the existing groups with another dummy group that groups on a constant expression (such as =1). Then use the outer group footer to show the table totals.
You shouldn't need to nest Sum() aggregates, I don't think. Just sum at the appropriate scope.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

hackman122Author Commented:

I was also thinking of the same. I had put added one more group on top of all (but for some reason group expression = 1 did not work). My output looks as follows:
Dept.            Emp Count.       %
01                120                   87.29 (=Code.AddCount(expresion))
                                             0.00% (=Code.tCount)
02                69                     83.57%
24               9                        83.84%
I now have 2 problems:
1. Visibility, I tried different values but still not getting correct expression, this works though:
=iif(Fields!Dept.Value = "24", False, True) (24 is our last dept. it can/will change but for now i can live with it. I still want to make it non-static, if you will).

2. the last tCount is not correct. eg, say
the last tCount value do not give total count, it gives 1789.41 but it should have been (1789.41+83.84)

What would be the work around?

I appreciate your help, thanks,
hackman122Author Commented:
Just to make things more clear. Following is my group structure:
T=Table; G=Group; H=Header; F=Footer

TH----- Dept                                       Emp Count                                %
GH1--- Hidden
GH2-- =Fields!Dept.Value                  =CountDistinct(EmpId, "Dept")       =Code.AddCount(Expression)
GH2-- Course Name                          Emp Count                                    %
GH3-- =Fields!Coursename.Value     =Count(EmpId, "Course")              =<expression to calculate %>
GF2                                                                                                         =Code.tCount
GF1                                                                                                         =Code.tCount
TF                                                     =CountDistinct(EmpId)                    =Code.tCount

on last group I get following values for =Code.tCount
GF2: 1873.07
GF1: 1789.24
TF  : 0.00
hackman122Author Commented:
This is what I did:
I stayed in the inner most group and did my calculations. I kept it hidden until the last Dept is shown (I kept it "24", any suggestion is welcome).

It worked for me now. It will not work when the last dept number will change. but i'm happy for now.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I am having the same issue (albeit with different code)

There must be away of calling =Code.tCount from the footer????
I have the exact same issue but couldn't resolve it the same way that hackman did because my last group may change from one run to another.

Any help?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.