I have a report that uses a stored procedure to capture the result set. When looking at the results data, the result set looks great! When I try to format the information into a cross-tab on the report, it automatically aggregates the information. I have made sure that the option on the table/crosstab has been checked to ‘avoid duplicate rows aggregation’. Any other suggestions?
Tim K Heuer
PacifiCare Health Systems
(714) 825-5702 - office
(800) 946-4645 pin 1404017 - pager heuer_tk@exchange.phs.com
I have a report that uses a stored procedure to capture the result set.
When looking at the results data, the result set looks great! When
I
automatically aggregates the information. I have made sure that the option on the table/crosstab has been checked to ‘avoid duplicate
rows
aggregation’. Any other suggestions?
Coming from a stored proc, you have the option to modify your object types.
The object is coming back as a measure. Try to change it to a dimension. This will probably work, but if your data requires aggregation your report will look pretty ugly! If this doesn’t work you will probably have to do some additional work in your stored proc to make the rows unique.
I’ve got this exact problem occuring on a Desktop Intelligence report right now. I’m reporting off a stored procedure, and one of my columns in just a single value repeated over and over again. When I create the report, it trys to aggregate the value which I don’t want it to do. Since I’m in a crosstab, I can’t use “avoid dup row agg”.
I tried to cast the value to a varchar, but DeskI is more intelligent than I and already converts this to a measure. Any ideas on how to update the stored procedure to make the rows unique?
Here is an example of my result set:
Bob 1000 5.5
Bob 1000 6.7
Bob 1000 8.9
Jack 2000 3.2
Jack 2000 4.9
Jack 2000 7.2
What I’m trying to do is to get just the value in column two to show up next to the name in the report:
Bob 1000
Jack 2000
But instead, I always get
Bob 3000
Jack 6000
I’m thinking there should be a way to create an ID column that would number the unique fields per name… Like this:
Bob 1000 5.5 1
Bob 1000 6.7 2
Bob 1000 8.9 3
Jack 2000 3.2 1
Jack 2000 4.9 2
Jack 2000 7.2 3
That way, I can select where ( = 1).
Any thoughts?
just a thought:
What if you convert that value (that is displayed in the 2nd column in your example) from number to string in your stored procedure? Now if it is of a number type then BO takes it as a measure. If you change the type to string then BO should take it as a dimension.
However, if you do that then you may lose some properties of a measure object for this new dimension object - like aggregation (which you dont want here but maybe in any other report it is necessary) or any other.
So try that and test the whole report/document whether it gives you what you need and nothing else was broken.
(BOB member since 2003-11-27)