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

Crosstab Report of Joined DMR Queries - Invalid Coercion

Started by colt, 16 Sep 2015 05:01:14 AM

Previous topic - Next topic

colt

I have a crosstab Report for Management showing figures out of different Areas ( Production, Sales,...) . Therefore I decided to do this by joining multiple queries. Each of them covering the numbers from 1 Area. The Report has a Date Prompt to select a Report date. As columns in the crosstab, there are 13 weeks before selected day. The day when week Ends is dependant from Chosen Report date. The column Header Shows the date of last day of that week. That means if I Chose '2015-08-31' as Report date, then the Report Shows:


                                     .......     '2015-08-10' | '2015-08-17' | '2015-08-24' | '2015-08-31'
Production Qty
Production kg
Production €

Sales Qty
Sales €

The column '2015-08-31' in this case accumulates number from '2015-08-25' up to '2015-08-31'.
As I wrote before, displayed on the Report are 13 weeks (=columns).

So far the Report went well.

Now there is a new request to add a % column for Production Qty and Production kg which should show the  Production Qty / max(Production Qty ever). Sames for Production kg.
To solve this, I opened the General filter of the Report to not only include last 13 weeks, but weeks of last 5 years.
The dataitem that includes then all weeks is [AllDates].
The % column is done by dataitem  with Definition:

[Value%]=total(currentmeasure within set filter([AllDates];[AllDates]='2015-09-10')) / Maximum(currentmeasure within set [AllDates])

The problem here is: As it is solved now, I have to Change the value '2015-09-10' above to the Report date. I have tried to use a calculated dataitem instead, but whenever I insert that, I've got an Invalid coercion from string to member set error.

Also when trying to filter [AllDates] to get only the last 13 weeks, it only worked for me when doing this by

[DisplayDate]=filter([AllDates];[AllDates] >='2015-06-18')   

with fixed date. All efforts to replace the fixed date by calculated data item resulted in an error too.

So mainly the problem is, that [AllDates] are used as memberset, as well as property. But if I try to use instead roleValue('_businessKey';[AllDates]) I've got an error too.

Any idea to solve the problem is welcome.