Error after joining 2 queries

[RudiHendrix]

I have an issue that I have also sent to the Cognos helpdesk. They're still working on it, but no solution yet.

What I am looking for is a graph. The graph should be a combination of Volume and ACQ
Both values should be displayed as running totals. The Volume in a bar chart and the ACQ in a line chart (in the same graph).
The Volume is available for every month and it was pretty easy creating a bar chart containing a running total of the volume. (That is a graph for only query 1)
This is the tabular data of Query 1:



However, the ACQ (annual contracted quantity) is a value that has a start and stop date. It is valid for a specific period. The value in it is the value for the whole year. So if I got the value the first thing I do with it is divide it by 12 to get the monthly figure.
That's done in Query 2:


Because I need to depict the monthly value against every month in the year I'm looking at I have to create a separate query to retrieve the months of that year.
Query 3:


I've added a data item containing "1" to both queries 2 and 3 so I can join them in Query 4
That is also working out fine. In that query I have to create a running total of the ACQ.
If I create a chart of only query 4 this works as well.
Query 4:


Now both queries 1 and 4 have a date in the Dutch language format "Mon yy". So in the last query I want to join those two queries on the date. That results in the following error:
GEN-ERR-0016

The 'member' function is not supported in the context in which it is used.

GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.GEN-ERR-0009 Data source type(s) 'OR' - Function 'member' is not supported in 'RelationalQueryProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'subQuery' is not supported in 'TabularFuncProvider'.GEN-ERR-0009 Data source type(s) 'OR' - Function 'member' is not supported in 'RelationalQueryProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.GEN-ERR-0009 Data source type(s) 'OR' - Function 'member' is not supported in 'RelationalQueryProvider'.GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'subQuery' is not supported in 'TabularFuncProvider'.GEN-ERR-0009 Data source type(s) 'OR' - Function 'member' is not supported in 'RelationalQueryProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.GEN-ERR-0009 Data source type(s) 'OR' - Function 'member' is not supported in 'RelationalQueryProvider'.GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'subQuery' is not supported in 'TabularFuncProvider'.GEN-ERR-0009 Data source type(s) 'OR' - Function 'member' is not supported in 'RelationalQueryProvider'.GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.GEN-ERR-0009 Data source type(s) 'OR' - Function 'member' is not supported in 'RelationalQueryProvider'.GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.QFS-ERR-0130 The query is not supported.


The even stranger thing is, that our Acceptance environment returns a different error! But as this is the same error as on Production I'll go for Development.

Perhaps you know a different way of achieving my goal. Obviously that is fine as well!

[MFGF]

That is a very odd error message - it seems to be complaining about usage of the member() function in a relational query.

Is your package a relational or DMR package?  Are you using a member() function in a calculation anywhere?

MF.

[RudiHendrix]

According to a Cognos consultant that was here once we have something in between DMR and relational. But in my opinion we have a DMR package.

We have a nice star schema as basis of the package. Connected all of the tables in FM. Next created hierarchies etc. So, I would say DMR.

I've opened the xml of the report and I don't see a member() function. I do find <chartNodeMembers>, </memberCaption>
And for example stuff like this:
<dmMember><MUN>[Facturering].[Factureringfeiten].[Gefactureerd Volume m³ 35,17]</MUN><itemCaption>Gefactureerd Volume m³ 35,17</itemCaption></dmMember>
But that sort of stuff shouldn't be a problem, right?

This is really a pain, because the only thing that is left for me now is to rebuild the entire query in SQL developer and use that in Report Studio. But, by doing so I also ignore my entire Cognos framework (and therefore security and all other features!)

[blom0344]

I would not expect the member() function in the report xml, but in the SQL/MDX that the report tries to generate.

The running totals would require you to write Cognos SQL (RSUM(XSUM..)) I guess, since a database will not have running aggregate functions

AFAIK the Cognos server would be the place where your queries would be joined

[RudiHendrix]

When creating the running totals I can just add a data item running-total([data item that I want totalized]

That part seems to be working fine. Isn't that done on the Cognos server then?

Or should I create a running total in my FM model?

[blom0344]

Compare native SQL against Cognos SQL for a simple running total. You will notice that in the native SQL the running total is not defined.
AFAIK the SQL is first executed against the database and the result set is processed by Cognos to add the running totals.

In your case I would bild a little test with the simplest sets possible and check whether you get the proper results without an error message

[RudiHendrix]

So you think it does have something to do with the running total?

I have removed all running-totals and recreated the last join and query where the ACQ and the Volume are coming together. However as soon as I pull a data item from either one of the queries and put them in the query that is a result of the join I get the error again.

So, for some reason it cannot perform the join very well. All queries before the last two are getting joined work perfectly!

[MFGF]

Hi,

You have a dimensionally modelled package, and you are using relational reporting techniques against it, which may be confusing the query generation somewhat.

PaulM posted up an elegant technique for producing running totals against an OLAP package - given that you are using DMR, it's definitely worth trying this rather than struggling with the running-total aggregate, which is for relational packages.

You can find the post here.

Regards,

MF.

[RudiHendrix]

Okay, I was not aware of the running-total function being a relational function. I can take a look at the link that you posted.

However, if I remove all occurrences of running totals the joining of the two queries doesn't work either. That still seems very strange. I have two working queries. They both have a field that is formatted in the same way and I would like to join them. That should be possible right?

Right now I'm also working on a workaround. I've created a SQL statement on my datamart and thus I am working completely separate from my FM model. So far it is working out fine. I have all the data I need in a a query that uses the Oracle pivot function.
I have outer joined that query with a query that returns all months of the year. In SQL developer it looks good. In Cognos it looks good as well. The only downside is that the query suppresses all rows where I do have a date, but no data for the rest of the row.

[RudiHendrix]

After some querying I have found a workaround. I have a huge query that retrieves all data that I need and with that I can work in Report Studio.

However, I'm still curious to see if Cognos support finds a solution for the error that I had. If this means the running total must be taken out, that is fine. But I am still curious to the solution that they find.

If they come up with something I'll post it here as well.