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

Question

Hi,

I want to group by a report item, but that's not allowed. So I tried creating a parameter...not allowed as well. Tried referencing from footer...failed again.

This is somewhat complicated. Let me explain:

I have textbox22, it's value is: =Code.Calc_Factor(Fields!xx.Value, fields!yy.Value...) This is embedded VB code in the report that's called for each row to calculate a standard factor.

Now to calculate the deviation from the standard factor, I use textbox89, whose value is: =(Fields!FACTOR.Value - ReportItems!textbox22.Value)/ReportItems!textbox22.Value

Don't get confused between Fields!FACTOR.Value and textbox22.Value, they are different. Fields!FACTOR.Value is the factor used, textbox22.Value is what it should be (standard factor).

Now I want to create a group which splits deviations into 2 groups, > 1% or not. So I tried creating a group: =IIF(ReportItems!textbox89.Value > 1,0,1) ...But then SSRS complains about using report items.

I have run into a similar problem of using report items in the past, but this is a new case!!

Any help greatly appreciated.

Thanks!!!!

Answer 1

I'm not 100% that someone won't have some magic solution for this but I have run across similar problems myself in the past. I believe (but could be wrong) the problem Reporting Services is having is that it only renders once and what you're asking it to do is render the data before rendering the grouping which it doesn't do.

The only way I have ever been able to produce the exact results I need is to make the data rendering happen exclusively in the SQL (through the use of table variables usually) and then use Reporting Services merely as a display platform. This will require that your factoring algorithm gets expressed in the T-SQL within the stored procedure you will likely have to write to get the data in shape. This would appear to be the only way to achieve your end result.

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

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

Answer 2

Have you tried adding a calculated field to your dataset?

Here is how it works:

• While you are in the layout view of the report, open "datasets" tool window(in my environment it is on the left).

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

• Then you should be able to group on this field

-Dan