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?

[Webinar] Streamline your web hosting managementRegister Today

hackman122Connect With a Mentor Author 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.
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?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
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
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?
All Courses

From novice to tech pro — start learning today.