Grouping Query for Crosstab

[mixim]

Hello Guys,

I have a query, which has:

Product Code
Ordered Amount
Order Week

What I would like to do, is to create a Crosstab, where we have data data comming in as Order Lines.

Product Code as Rows
Order Date Week as Columns
Ordered Amount as Measure

If i just pull in the values to a crosstab, of course i get the Product rows and Ordered Amount looking like its repeating values.
The same Product can be ordered (and orderline created) several times, and at several different weeks.

Question is then, how would i go about to create a query to suffice the crosstab. In essence I would like to achieve:

Rows concisting of Distinct Articles for all Order Lines.
Columns concisting of Distinct Weeks for all Order Lines.
Measure concisting of Aggregated "Ordered Amount" for the corresponding combination of Articles/Weeks.

Im working with a Dimensional Package. All items are from the same level as a fact.

When trying right now I get the following:


And it is not possible to select Group By in the UI on neither of the axis.


I seem to miss something out here...

[MFGF]

Hello Guys,

I have a query, which has:

Product Code
Ordered Amount
Order Week

What I would like to do, is to create a Crosstab, where we have data data comming in as Order Lines.

Product Code as Rows
Order Date Week as Columns
Ordered Amount as Measure

If i just pull in the values to a crosstab, of course i get the Product rows and Ordered Amount looking like its repeating values.
The same Product can be ordered (and orderline created) several times, and at several different weeks.

Question is then, how would i go about to create a query to suffice the crosstab. In essence I would like to achieve:

Rows concisting of Distinct Articles for all Order Lines.
Columns concisting of Distinct Weeks for all Order Lines.
Measure concisting of Aggregated "Ordered Amount" for the corresponding combination of Articles/Weeks.

Im working with a Dimensional Package. All items are from the same level as a fact.

When trying right now I get the following:
https://dl.dropboxusercontent.com/u/2552010/SCR.6951.jpg

And it is not possible to select Group By in the UI on neither of the axis.


I seem to miss something out here...

Hi,

I'm not sure I'm quite understanding what your issue is? If the same product is ordered multiple times in the same week, a crosstab should aggregate those multiple amounts to a single intersection for the week and the product.

Is it possible some of your week members are displaying the same week number as their caption but actually have different MUNs? Is it possible you have multiple members with the same caption and different MUNs for a product?
 
Regards,

MF.

[mixim]

Hi,
I'm not sure I'm quite understanding what your issue is?[...]
 
Regards,
MF.

Well, I guess I get the message because cognos already sees it as grouped. As you say, I would expect it to actually group these values.

However, when pupulating a regular list, it does actually group the values and calculate sum for measure. This would indicate same MUN's right?

https://dl.dropboxusercontent.com/u/2552010/Untitled.jpg

So it is only in crosstab I see the above behaviour. That is why I am thinking of ways to force it with a query. Or by any other means.
Is there any way I could see the MUN for e.g. each row to verify if its repeating or unique?

[mixim]

Some more hints to throw into the puzzle:

1. When in the crosstab node member setting Text Label as "Member Caption" for both Week and Product I get the following names, are these the MUN's?


2. While poking around i tested some settings, and if i go to the Query properties, and set
"Auto Group & Sum" to NO ,
"Override Dimension Info" to NO
"Define Member Sets" to Yes

I get the following error:
OP-ERR-0144 Grouping on a property or property expression: dataItem="WEEK",expression="[__ns_0].[OLine].[OLine].[OLine0].[WEEK]" in valueSet="0" is not supported.

But maybe this is normal when setting Auto G & S as NO?

[MFGF]

Well, I guess I get the message because cognos already sees it as grouped. As you say, I would expect it to actually group these values.

However, when pupulating a regular list, it does actually group the values and calculate sum for measure. This would indicate same MUN's right?

https://dl.dropboxusercontent.com/u/2552010/Untitled.jpg

So it is only in crosstab I see the above behaviour. That is why I am thinking of ways to force it with a query. Or by any other means.
Is there any way I could see the MUN for e.g. each row to verify if its repeating or unique?

Building a list means that the "relationalqueryprovider" is used, so probably you will be able ot group successfully on captions. Don't be fooled by this - it doesn't necessarily mean your members have the same MUN.

To see what the MUNs look like, expand the member tree in your package (on the left), right-click on a week member, and select "Properties". One of the displayed properties will the the Member Unique Name.  Find another week and do the same. Tell us what the MUNs look like. If you expand two different years, are there weeks under each with the same captions? Do thet have the same MUNs?

You could try the same with products too. Do you have what appears to be the same product under different parents in the member tree?

MF.