I am having trouble converting a table that looks like this (see below) to a crosstab:
Job Stg Time
a 1 x
a 2 y
a 3 z
b 1 x
b 2 y
b 3 z
When I convert it to a crosstab with Job down the side, Stg across the top, and Time in the body, instead of getting 1 row per Job, I still get multiple rows, but with empty cells, like this:
The problem you have actually isn’t a BusObj problem but a SQL one. Rather than describing in detail what causes it, here’s one solution. In the object definition change the object type to a Measure and set the aggregation to Max (remember to set the datatype back to a date as this will automatically change to a number when you change the object to a measure). This way you will get the results on one line. We deal with loads of dates in crosstabs so had the same problem. The 'Max’ing makes no difference to the object use in normal reports. The only limitation is in use of automatically generated date hierarchies.
At least we BusObj users are lucky - Oracle Discoverer2000 can’t use dates in the body of a crosstab at all!
Hi Linda, I read your problem, and Jonathan’s response and I have to reiterate how important it is to change the object to measure with the max applied.
I have had several heated confrontations with my users based on your same dilemma. I know that in 3.1 you could achieve the report you are looking for because there was no intelligence in the software around the concept of “Cross table” or “matrix” reports. Logically an object that you are trying to insert into the “body” of a cross table report should be such that you can perform some function such as sum across or down. I spent hours trying to convince my users that this was the logical reason for Bus Obj taking away the functionality in 4.1.
If Bus Obj is listening - there should be functionality to put a report together without “Tricking” the logic.
Thanks, Laurenf@bellatlantic.net
Linda Caron wrote:
I am having trouble converting a table that looks like this (see below) to a crosstab:
This problem has been termed “Water Falling.” It happens when a dimension or a measure whose projection function is None has been placed in the body of a xtab. This often happens to users who have converted from Version 3.1, because this type of report was possible then.
Robert
Schmidt Interactive Software, Inc.
Linda Caron wrote:
I am having trouble converting a table that looks like this (see below) to a crosstab:
Job Stg Time
a 1 x
a 2 y
a 3 z
b 1 x
b 2 y
b 3 z
When I convert it to a crosstab with Job down the side, Stg across the top, and Time in the body, instead of getting 1 row per Job, I still get multiple rows, but with empty cells, like this:
If Bus Obj is listening - there should be functionality to put a report together
without “Tricking” the logic.
Anyway, just to let you know that I logged this as an enhancement request quite a ago. As I’m currently about 3000 miles away from my desk I can’t off-hand tell you the enhancement request number but I’m sure it’s worth a few more people leaning on BusObj to get them to include it in the next release etc…
Robert answered with something that is also true for numerical measures but as Linda’s mail indicated that the body object was a time object the solution is as Lauren and I presented.
If Bus Obj is listening - there should be functionality to put a report together
without “Tricking” the logic.
How should this work? There are, for sure, alot of other people out there, who want it behave as it is now!
The solution is: You need not change your object definitions. Actually it depends what you want to display in the body of the report. If your “time” value is a dimension, then you have to use the local min/max function within the report, or, any aggregation function which can deal with your time values.
Second solution is the one already proposed: changing the qualification of the object to be a measure, and adding the min/max/whatever aggregation function to the properties (NOT in the SQL).
IF you converted this report from BO V3, which was also mentioned in the discussion, redefine the objects qualifications in designer BEFORE you upgrade your reports.
Hope, this helps.
Walter.
Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
Thanks to everyone who helped me with my “waterfall” problem when I converted a table to a crosstab. Just to clear up any confusion: This was not a 3.1 to 4.1 conversion problem. I created the report in 4.1. Also, the “time” object in the body was already a measure - an amount of time, not a date. But I needed to fix some date fields also so that I would not have a similar problem with other reports. I followed your advice and the crosstab now looks like I expected it to.
I want to review what I did to ensure it was the correct thing to do. I have cycle time objects at three levels in my universe: Job, Stage, and Task. In the database, Task cycle times are aggregated into a Stage table. But rather than aggregate Stage cycle times to Job level, we calculate it directly based on the difference between the Start and Stop dates. 1) The aggregate function for Job Cycle Times has been changed from NONE to MAX.
2) The aggregate function for Stage Cycle Times has been changed from NONE to MAX.
3) The aggregate function for Task Cycle Times has been changed from NONE to SUM so that it will aggregate properly in reports. 4) The date objects have been changed from DIMENSIONS to MEASURES with the aggregate function set to MAX.
I am concerned that changes 1) and 2) will cause problems with user’s reports that use those objects. Should I be? Other than each user verifying that their reports still work correctly, does anyone have a suggestion on how to ensure there are not any problems?