Clever Geek Handbook

Grouping by report item in SSRS 2005 - textbox - any workarounds?

I want to group a report item, but this is not allowed. So I tried to create a parameter ... not allowed. Tried link from footer ... failed again.

It's a little tricky. Let me explain:

I have textbox22, this is the value:

=Code.Calc_Factor(Fields!xx.Value, fields!yy.Value...)

This is inline VB code in the report that calls each line to calculate the standard ratio.

Now, to calculate the deviation from the standard coefficient, I use textbox89, whose value is:

=(Fields!FACTOR.Value - ReportItems!textbox22.Value)/ReportItems!textbox22.Value

Do not confuse between Fields!FACTOR.Value

and textbox22.Value

, they are different. Fields!FACTOR.Value

- the factor used textbox22.Value

is what it should (standard factor).

Now I want to create a group that separates the variances into 2 groups,> 1% or not. So I tried to create a group:

=IIF(ReportItems!textbox89.Value > 1,0,1)

... But then SSRS complains about using report items.

I had a similar problem using report items in the past, but this is a new case!

Any help is greatly appreciated.

+1


source to share


2 answers


Have you tried adding a calculated field to your dataset?

This is how it works:



  • While you're in the report layout, open the tool datasets window (in my environment, it's on the left).

  • Right click on the DataSet you are working with and add a field, you can use the calculated field and plot your formula correctly

  • Then you should be able to group in this field

Dan

+3


source


I'm not 100% sure someone won't have a magical solution for this, but I've run into similar problems in the past. I believe (but may not be the case) that the problem with Reporting Services is that it only renders once, and what you are asking is to render the data before rendering the grouping, which it does not do.

The only way I can ever get the exact results I want is to do the data processing purely in SQL (usually using table variables) and then use Reporting Services only as a display platform. This will require your factoring algorithm to be expressed in T-SQL in a stored procedure, which you will probably have to write to get the data in the form. This appears to be the only way to achieve the end result.



It has the bonus feature of separating report design and presentation from data manipulation.

Sorry I couldn't provide an SSRS solution, maybe someone else will know more.

+1


source






More articles:


All Articles