Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Footers 1

Status
Not open for further replies.
UcfMike

UcfMike

IS-IT--Management
Joined
Mar 29, 2007
Messages
184
Location
US
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.
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
[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]
Click to expand...

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

 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
You were exactly right.

Enjoy your trip.

Happy Easter.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
Code: 
 #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:

Code: 
[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

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

Frustrating.
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JOEYB99
  • Locked
  • Question Question
Exporting queries to Excel without headers/footers
Replies
3
Views
323
blom0344
blom0344
oxl
  • Locked
  • Question Question
Impromptu - Subtotal on same field in different Footers
Replies
1
Views
80
griffindm
griffindm
XTechie
  • Locked
  • Question Question
Add footers/summaries with Powerprompts
Replies
0
Views
70
XTechie
XTechie
BogTrotter
  • Locked
  • Question Question
Summaries in page footers: Impromptu 6
Replies
1
Views
142
jbecher
jbecher
baca
  • Locked
  • Question Question
Report footers and generic IF function
Replies
1
Views
157
DoubleD
DoubleD

Part and Inventory Search

Sponsor

Back
Top