Footers 1

[UcfMike]

I apologize for all the questions but so far the migration from Impromptu to Report Studio isn't going smoothly.

I've added a list footer to my report with the goal of summarizing data for that group. However whenever I add fields I don't get the results I want. I either get totals for the whole report, or the last value from the group that the list footer is based off. What am I missing. In Impromput I just created a new query calculation that totaled the field that I wanted it and associated it to the proper group.

 

[UcfMike]

It looks normal, but if I set the aggregate and rollup aggregate function to normal for each field, the price field appears to total for the entire report.

 

[blom0344]

[small]I would expect this if your calculation is totalling a measure for a grouped item. If you include it in the List Page Footer, it will give you the total for the last group. If you include it in the group footer for the relevant grouped item, it will work correctly.
[/small]

Yes, it matters which column is selected when creating the footer. Problem is that the footers are identical (it does not matter how you create them, they are displayed over the whole list box)

Ties Blom

 

[mfgf]

Hi Mike,

What do you mean by "it looks normal"?

In the Cognos SQL, are you seeing the relevant "group by" clause(s)? How are the measure values being summarised? (XSUM?, XSUM(XSUM?, XMIN(XSUM?). Is there an "order by" clause?

MF.

 

[UcfMike]

Well, I can't tell you what I mean by normal, because, without paying attention well, I deleted the folder that housed the report. So I'm going to have to start over.

 

[UcfMike]

Here's a question as I rebuild the report.

If the price table joins to the product table via a one to many relationship, ie one product can have many prices.

Products hooks to Store via a many to many relationship with the junction table Product to Store.

If the relationships aren't dimensionally modeled, is FM going to be able to properly handle the ER relationships that I've put together in FM and get to the proper Store information.

 

[mfgf]

Hi,

My feeling here is that you will end up with an unwanted query split (stitch query where not required).

The Cognos SQL will show this once you have rebuilt your report (look for COALESCE functions and a FULL OUTER JOIN).

I'm off for an Easter break now and then a week working abroad, so it may be a while before I can check back to see how you get on.

Good luck!

MF.

 

[UcfMike]

with
D as
(select
PRODUCT.PRODUCT_CODE as PRODUCT_CODE,
PRODUCT.PRODUCT_NAME as PRODUCT_NAME,
PRODUCT.PRODUCT_NAME as PRODUCT_NAME3,
PRICE.PRICE_VALUE as PRICE_VALUE,
XSUM(PRICE.PRICE_VALUE ) as PRICE_VALUE5,
XSUM(PRICE.PRICE_VALUE ) as PRICE_VALUE6,
RSUM(1 for PRODUCT.PRODUCT_CODE order by PRODUCT.PRODUCT_CODE asc local) as sc
from
"MVCI-SOLAR"..SOLAR.PRODUCT PRODUCT
join
"MVCI-SOLAR"..SOLAR.PRICE PRICE
on (PRODUCT.UNIQUE_KEY = PRICE.PRODUCT_UNIQUE_KEY)
filter
(rank() over ( partition by PRODUCT.PRODUCT_CODE order by PRICE.UNIQUE_KEY desc nulls last ) = 1)
order by
PRODUCT_CODE asc
),
D3 as
(select
RESORT.RESORT_NAME as RESORT_NAME,
PRODUCT.PRODUCT_CODE as PRODUCT_CODE,
PRODUCT.PRODUCT_NAME as PRODUCT_NAME,
PRODUCT.PRODUCT_NAME as PRODUCT_NAME4,
RSUM(1 at PRODUCT.PRODUCT_CODE,RESORT.RESORT_NAME for PRODUCT.PRODUCT_CODE order by PRODUCT.PRODUCT_CODE asc,RESORT.RESORT_NAME asc local) as sc
from
"MVCI-SOLAR"..SOLAR.RESORT RESORT
join
"MVCI-SOLAR"..SOLAR.PRODUCT_TO_RESORT PRODUCT_TO_RESORT
on (RESORT.UNIQUE_KEY = PRODUCT_TO_RESORT.RESORT_UNIQUE_KEY)
join
"MVCI-SOLAR"..SOLAR.PRODUCT PRODUCT
on (PRODUCT.UNIQUE_KEY = PRODUCT_TO_RESORT.PRODUCT_UNIQUE_KEY)
order by
PRODUCT_CODE asc,
RESORT_NAME asc
)
select
D3.RESORT_NAME as RESORT_NAME,
coalesce(D.PRODUCT_CODE,D3.PRODUCT_CODE) as PRODUCT_CODE,
coalesce(D.PRODUCT_NAME,D3.PRODUCT_NAME) as PRODUCT_NAME,
D.PRICE_VALUE as PRICE_VALUE,
RMIN(D.PRICE_VALUE5 order by D3.RESORT_NAME asc,coalesce(D.PRODUCT_CODE,D3.PRODUCT_CODE) asc ) as PRICE_VALUE5,
RMIN(D.PRICE_VALUE6 for D3.RESORT_NAME order by D3.RESORT_NAME asc,coalesce(D.PRODUCT_CODE,D3.PRODUCT_CODE) asc ) as PRICE_VALUE6,
XMIN(coalesce(D.PRODUCT_NAME3,D3.PRODUCT_NAME4) for D3.RESORT_NAME,coalesce(D.PRODUCT_CODE,D3.PRODUCT_CODE) ) as PRODUCT_NAME7
from
D3
full outer join
D
on ((D3.PRODUCT_CODE = D.PRODUCT_CODE) and (D3.sc = D.sc))
order by
RESORT_NAME asc,
PRODUCT_CODE

 

[UcfMike]

You were exactly right.

Enjoy your trip.

Happy Easter.

 

[UcfMike]

I get this when I run it against the package where one of my one to many relationships is reversed.

select
RESORT.RESORT_NAME as RESORT_NAME,
PRODUCT.PRODUCT_CODE as PRODUCT_CODE,
PRODUCT.PRODUCT_NAME as PRODUCT_NAME,
PRICE.PRICE_VALUE as PRICE_VALUE,
XSUM(PRICE.PRICE_VALUE at PRICE.UNIQUE_KEY ) as PRICE_VALUE5,
XSUM(PRICE.PRICE_VALUE at PRICE.UNIQUE_KEY ) as PRICE_VALUE6,
PRODUCT.PRODUCT_NAME as PRODUCT_NAME7
from
"MVCI-SOLAR"..SOLAR.RESORT RESORT
join
"MVCI-SOLAR"..SOLAR.PRODUCT_TO_RESORT PRODUCT_TO_RESORT
on (RESORT.UNIQUE_KEY = PRODUCT_TO_RESORT.RESORT_UNIQUE_KEY)
join
"MVCI-SOLAR"..SOLAR.PRODUCT PRODUCT
on (PRODUCT.UNIQUE_KEY = PRODUCT_TO_RESORT.PRODUCT_UNIQUE_KEY)
join
"MVCI-SOLAR"..SOLAR.PRICE PRICE
on (PRODUCT.UNIQUE_KEY = PRICE.PRODUCT_UNIQUE_KEY)
filter
(rank() over ( at PRODUCT.PRODUCT_CODE,PRICE.UNIQUE_KEY partition by PRODUCT.PRODUCT_CODE order by PRICE.UNIQUE_KEY desc nulls last ) = 1)
order by
RESORT_NAME asc,
PRODUCT_CODE asc

 

[blom0344]

In most cases I would expect that a price is an attribute of a product. It is not a fact (even though numerical), so basically I do not understand why you would want to summarize prices.

The calculation

 #products*price

would be considered a fact ,since summarizing would yield revenue.

If I paid due attention during the fasttrack course I would say that the only way for cognos to create a proper stitch query is when the datamodel is dimensionally modelled. In that case the stitch query is triggered when 2 facttables are used in one query.

What really makes me wonder is:

[COLOR=red]PRICE.PRICE_VALUE  as  PRICE_VALUE[/color],
       XSUM(PRICE.PRICE_VALUE  at PRICE.UNIQUE_KEY )  as  PRICE_VALUE5,
       XSUM(PRICE.PRICE_VALUE  at PRICE.UNIQUE_KEY )  as  PRICE_VALUE6

price_value being summarized (twice) against it's own.
That is absolutely pointless

Ties Blom

 

[UcfMike]

So it still seems to be coming down to the problem that they don't want to dimensionally model their data.

Frustrating.

 

[blom0344]

Just a reaction on your last remark.
Even with a very simple datamodel I run into strange behavior if I do not model into a dimensional set.
Cognos seems to rely very heavy on the cardinalities set and it is directly influencing the SQL generated.

Perhaps you should experiment by setting all cardinalities as 1:1 (as suggested by mfgf in another topic)

Ties Blom