BEx Query total summarized

[Former Member]

I need to fine tune a query.

Basic in structure.

Column is defined as Revenue.

My "Row" definition needs to be summarized.

At issue is the following.

I have dimemsion "Sold to" and dimension "Ship To" defined as objects.

I need Ship To only for the purpose of identifying my Ship To attribute "Country".

Ship To - has display properties as "No Display" and Results Row >> Always Suppress.

Requirement: Results of query to state my Sold To with value.

I have a one "Sold To" to many Ship-To relationship.

I need to have my "Sold To" result listed as a total .... one value,

Currently result separately lists each (hidden - Ship To).

Example:

Sold To "A" ...... Ship To USA (NY)

Sold To "A" ...... Ship To USA (NJ)

Sold To "A" ...... Ship To USA (Pa)

Sold To "A" ...... Ship To Canada (Toronto)

Sold To "A" ...... Ship To Canada (Quebec)

I need only two totals .. 1. Country USA and 2. Country Canada.

Not 5 lines as I currently get.

Thank you .... Dan

Edited by: Dan Shemanski on Oct 3, 2008 9:53 PM

[Former Member]

Bob,

I understand you point clearly.

I went into the InfoProvide-Sales Cube 0SD_C03 and opened up the Navigational Attributes node.

0SHIP_TO_0COUNTRY is listed with ON/OFF field checked.

I apoligize for the confusion. All the configuration appeared Ok.

In re-reviewing your point I realized that my query is being written off a Multi-Provider ( I may need COGS ) and just realized that within the MP,0SHIP_TO_0COUNTRY is not active.

My error ..... excellent point you reinforced.

I didn't realize the navigational attribute would not have the same active on setting as with the cube.

I'll test in BW1.

[Former Member]

Bob,

I can not remove the second drill-down of(ship_to). The reason is I need the the country key of ship_to.

My result must accomplish two different scenarios.

1. Single Sold_to with multiple Ship_to's represented with one Sold_to value.

2. Another Sold_to many have multiple Ship_to counties.

This example would display the the Sold_to multiple times

(by country key). With the flexibility of query design I hope to be able to develope this flexibility within the query.

[Former Member]

Dan,

One more time...

You do not need to drilldown by Ship-To to get "the country key of ship-to" in your report.

If you make the Country attribute of Ship-To navigational in your InfoProvider for this query, then it will become another independent characteristic that can be added to the report (without adding Ship-To !!). That will allow you to drilldown by the navigational attribute Country itself (not the attribute of Ship-To !!), which will then only show one row per country for each sold-to, instead of one row per ship-to for each sold-to (which is what you have now).

Hope this helps...

Bob

[Former Member]

Dan,

Turn on Ship-To Country as a navigational attribute and add it your query, then drill-down by that instead of Ship-To.

Hope this helps...

Bob

[Former Member]

Bob,

I already have Country as a Ship_To navigational attribute defined in my object and query.

My query definition is as follows.

Row definition: Sold_To

Advanced Tab: Access Type for Result Values: Posted Values

Use characteristic settings

Filter Value Selection: Only Posted Values for Navigation

Display: Key & Text

Results Row: Always Display.

Row definition: Ship_to

Advanced Tab: Access Type for Result Values: Posted Values

Use characteristic settings

Filter Value Selection: Only Posted Values for Navigation

Display: No display (I want this hidden)

I already have "Country Key" as a navigational attribute with display as text.

My issue is that with a display of Sold_To "AAA" I may have a listing of many different Ship_To's.

My query will hide ship_to key/text but disply the same Sold_to

many times. I want to see a single value for "Sold_To".

I need my many values rolled into and displayed as one value.

[Former Member]

Dan,

The point is that you should be drilling down by the navigational attribute for Country (from Ship-To), and *NOT* by Ship-To. When you drilldown by Ship-To and hide the Ship-To values, you are still going to get one row in the report for each Ship-To. However, if you drilldown by the navigational attribute for Country, it will only show one row for each unique value of Country for each Sold-To, which I believe is what you originally said your requirement was.

If you want everything to rollup by Sold-To only, as your reply indicates, then you should remove the second drilldown from the report entirely.

Hope this helps...

Bob