Grouping by a report item?? Please read. Any help appreciated!!

Question

Grouping by a report item?? Please read. Any help appreciated!!

shiv179

SSC Rookie

Points: 32
More actions
January 8, 2009 at 1:55 am

#217314

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!!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TKD-BB SSC Eights! Points: 993 More actions · Answer 1

Would it work to, instead of doing the calculation for the report item in a text box, as I assume you are doing now, add a new field in the dataset that is a calculated field instead of a field from the database, and perform your calculation there? Then the calculated value would be a field in each row of data, and you could group based off of it that way.

Haven't tried this myself, but thought I would suggest it.

shiv179 SSC Rookie Points: 32 More actions · Answer 2

chujohnson (1/8/2009)
Would it work to, instead of doing the calculation for the report item in a text box, as I assume you are doing now, add a new field in the dataset that is a calculated field instead of a field from the database, and perform your calculation there? Then the calculated value would be a field in each row of data, and you could group based off of it that way.
Haven't tried this myself, but thought I would suggest it.

I'm using the Oracle database.

The VB code makes numerous calls to the database, accessing stored procedures and data during processing.

TKD-BB SSC Eights! Points: 993 More actions · Answer 3

shiv179 (1/8/2009)
chujohnson (1/8/2009)
Would it work to, instead of doing the calculation for the report item in a text box, as I assume you are doing now, add a new field in the dataset that is a calculated field instead of a field from the database, and perform your calculation there? Then the calculated value would be a field in each row of data, and you could group based off of it that way.
Haven't tried this myself, but thought I would suggest it.
I'm using the Oracle database.
The VB code makes numerous calls to the database, accessing stored procedures and data during processing.

OK, not sure how that relates exactly to what I suggested. I was not referring to making any changes in how you retrieve data from the database. Simply, in SSRS, go to the datasets tab and right click on the dataset you are trying to group by this calculated value. Choose Add and when the window comes up for adding the new field select Calculated field instead of Database field and use th expression dialog window to do your reference to the VB code for doing the calculation.

shiv179 SSC Rookie Points: 32 More actions · Answer 4

chujohnson (1/8/2009)
shiv179 (1/8/2009)
chujohnson (1/8/2009)
Would it work to, instead of doing the calculation for the report item in a text box, as I assume you are doing now, add a new field in the dataset that is a calculated field instead of a field from the database, and perform your calculation there? Then the calculated value would be a field in each row of data, and you could group based off of it that way.
Haven't tried this myself, but thought I would suggest it.
I'm using the Oracle database.
The VB code makes numerous calls to the database, accessing stored procedures and data during processing.
OK, not sure how that relates exactly to what I suggested. I was not referring to making any changes in how you retrieve data from the database. Simply, in SSRS, go to the datasets tab and right click on the dataset you are trying to group by this calculated value. Choose Add and when the window comes up for adding the new field select Calculated field instead of Database field and use th expression dialog window to do your reference to the VB code for doing the calculation.

thanks man, its working now!!

i forgot that u can add calculated values to a dataset.

works perfect now!!!!

thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

TKD-BB SSC Eights! Points: 993 More actions · Answer 5

TKD-BB

SSC Eights!

Points: 993

January 8, 2009 at 7:20 am

#923298

No problem. Glad I could be of assistance.