If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

PowerCube data source used in a crosstab

Started by rjhankey, 20 Jan 2012 02:14:04 PM

Previous topic - Next topic

rjhankey

Here's hoping there's more experts out there that have dealt with a similar solution to the one I've been banging my head over for some time now.

I have a PowerCube that contains data for delivery carriers (FedEx, DHL, etc.), the loss claim amounts that were filed, and the type of incident that occurred (Fire Damage, Carrier Mishandled, etc.)  These loss claims can also be sliced by year, geo, and brand.

In Report Studio, I need to create a report that will calculate carriers' scores for each type of incident that occurs in the overall set of claims, even if the carrier didn't have any claims in that incident type, or their total claims for that incident type total $0.

I selected a crosstab as a starting point, and I have it working where I can show the carriers and the various incident types along the left side (rows), and total claim amount in the first column.  That's working well.

I also went on to add a calculated measure to the crosstab that converts any $0 or null total claim amounts for each carrier/incident pair to a small epsilon value (0.0001).  Let's call this the Adjusted Claim Amount.

Now that I have that, I need to divide the Adjusted Claim Amount by the "average" claim size observed for each incident type.  In my PowerCube, in the incident type dimension, I have associated the calculated average as a second level so that each incident has a non-aggregated average value tied to it.  We'll call this the GMCV.

Here's the problem -- when I want to add a new column to the crosstab (I've tried calculated member, calculated measure, even data item), and I want to define it as Adj Clm Amt (calculated measure) divided by the GMCV (level 2 member of the rpt inc type dimension).  Everything I try results in a runtime error that the two operands cannot be converted to a consistent dimensional object.

Is that a sign that I'm doing something illegal with measure and dimension data?  Is there a better approach to accomplish what I'm trying to do?  The trouble I have is that I don't know what the adj clm amt aggregate will be until the user has selected the desired data slices at report run time, something I don't know in the cube or in Framework Manager.  The only thing I can calculate are the average claim amounts for each incident type (GMCV) since that spans all claims in the data set and not just across the selected data slice.  So it seems like I need to tackle this in the Report Studio layer.

Any suggestions or helpful hints would be greatly appreciated!!

Thanks,

-Russ