How to add an additional value to a data item using dimensional data

[annegirl]

I've created an active report using dimensional data.  I'm a newbie to cubed data, so I'm hoping someone can help me figure out a method to accomplish the following:

I have a data item, "Revenue Type" which has 3 values: Trans, Stream, and SaaS.  I created a Data Iterator off of this data item, which filters the data based on the Revenue Type that a user selects.

I want to add another value to the "Revenue Type" data item, Total, which is the sum of Trans, Stream, and SaaS. I know with relational data I can create a union to accomplish this, but when I try that with the dimensional data I get the following error, so I'm guessing I shouldn't be doing this with dimensional data:

RQP-DEF-0177

      An error occurred while performing operation 'sqlOpenResult' status='-28'.

     Details
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-SQL-0449 An error occurred when allocating memory during the "APICursor::Open" operation.RSV-SRV-0042 Trace back:RSReportService.cpp(717): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(848): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(305): QFException: CCL_CAUGHT:

So my question is, is there some kind of function that anybody knows of that I can use to add "Total" to the Revenue Type data item, using dimensional data?

I also don't want to have to create a deck to accomplish this, because that will quadruple my output size, and my file size is already quite large.

Thanks!

[MFGF]

I've created an active report using dimensional data.  I'm a newbie to cubed data, so I'm hoping someone can help me figure out a method to accomplish the following:

I have a data item, "Revenue Type" which has 3 values: Trans, Stream, and SaaS.  I created a Data Iterator off of this data item, which filters the data based on the Revenue Type that a user selects.

I want to add another value to the "Revenue Type" data item, Total, which is the sum of Trans, Stream, and SaaS. I know with relational data I can create a union to accomplish this, but when I try that with the dimensional data I get the following error, so I'm guessing I shouldn't be doing this with dimensional data:

RQP-DEF-0177

      An error occurred while performing operation 'sqlOpenResult' status='-28'.

     Details
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-SQL-0449 An error occurred when allocating memory during the "APICursor::Open" operation.RSV-SRV-0042 Trace back:RSReportService.cpp(717): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(848): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(305): QFException: CCL_CAUGHT:

So my question is, is there some kind of function that anybody knows of that I can use to add "Total" to the Revenue Type data item, using dimensional data?

I also don't want to have to create a deck to accomplish this, because that will quadruple my output size, and my file size is already quite large.

Thanks!

Hi,

You need to think of this in slightly different terms. You don't have a data item with values in your package - you have a level of a hierarchy with members. Each of the Revenue Types you are mentioning here is a separate member.

What is the structure of the hierarchy Revenue Type is defined within in your package? Is there a level above this with a member "All Revenue Types"? If so, you can create a set of four members - set ([All Revenue Types],[Trans],[Stream],[SaaS]) and use this as the expression that drives your data iterator. You'd need the same expression in the items you are filtering, too.

Cheers!

MF.

[annegirl]

That worked brilliantly, thanks so much!!

[annegirl]

Ok, so I have another issue that arose with the addition of this "Total"

As you suggested, I added a new data item called "Rev Type" and defined it as "set ([Revenue type], [Transaction], [SaaS], [Stream])", where "Revenue type" is the total.  I wanted to rename [Revenue type] to [Total], so I created another data item, defined as:
   case
      when caption([Rev Type]) = 'Revenue type'
      then 'Total'
      else caption([Rev Type])
   end

For certain calculations, however, the report failed (where it ran successfully previously), and gave me an Arithmetic Overflow error:

UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-SQL-0144 An arithmetic exception was detected.UDA-EE-0060 Overflow occurred on a "floating point" overflow operation.UDA-SQL-0460 A general exception has occurred during local processing.RSV-SRV-0042 Trace back:RSReportService.cpp(717): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(848): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(305): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl():

The bizarre thing is that if I remove the case statement, it runs just fine - any ideas where I'm going wrong here?

Thanks again!

[MFGF]

Ok, so I have another issue that arose with the addition of this "Total"

As you suggested, I added a new data item called "Rev Type" and defined it as "set ([Revenue type], [Transaction], [SaaS], [Stream])", where "Revenue type" is the total.  I wanted to rename [Revenue type] to [Total], so I created another data item, defined as:
   case
      when caption([Rev Type]) = 'Revenue type'
      then 'Total'
      else caption([Rev Type])
   end

For certain calculations, however, the report failed (where it ran successfully previously), and gave me an Arithmetic Overflow error:

UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-SQL-0144 An arithmetic exception was detected.UDA-EE-0060 Overflow occurred on a "floating point" overflow operation.UDA-SQL-0460 A general exception has occurred during local processing.RSV-SRV-0042 Trace back:RSReportService.cpp(717): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(848): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(305): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl():

The bizarre thing is that if I remove the case statement, it runs just fine - any ideas where I'm going wrong here?

Thanks again!

Hi,

It looks to me like you're still thinking of this in terms of relational reporting, but here you're reporting off a dimensional package. "Revenue Type" is a member, and as such it has an ID and a caption. You can't arbitrarily decide to change the caption of a member as you are trying to do here. You are attempting to use a relational construct - a case statement - on a set of members, and it is fundamentally incompatible. At best your expression is returning a string value - either 'Total' or the caption of a member, but this is not the same as the set of members, and it will result in the kinds of errors you see here.

Short answer - change the caption of the Revenue Type member in your cube to be something relevant to your reporting requirement. You're going to fail spectacularly to do this with relational constructs in your reports.

Cheers!

MF.