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!

Cognos 8 Report Studio Percent Of Total In A Crosstab

Status
Not open for further replies.
Nuffsaid

Nuffsaid

Technical User
Joined
Aug 3, 2001
Messages
374
Location
CA
Hi All,

We’re currently in the process of migrating from ReportNet to Cognos 8 and I’ve come across a few issues that have made this migration a little painful to say the least. Here’s the latest one. In Report Studio I have a cross tab report that calculates percent of total. This report is based on a relational package. When I migrated the report and package from ReportNet to C8 following Cognos’ recommended path, the report bombs when I try to run it, even though it validates.

Some observations I made while messing with this;

In ReportNet when you create a cross tab Report Studio creates dimensions automatically.

In C8 when I open the report and check the query using the query explorer a “dimension tab” appears at the bottom of the report beside the “Projected Data Items” tab, however when I select the tab a blank page appears and the report locks up.

I tried to recreate the report from scratch in 8 but can’t figure out the expression editor syntax for the formulas to do the calculations. (The documentation was EXTREMLY helpful)

I noticed there is an “Override Dimension Info” query property and started messing with that but I’m not having much success. I’m including a simple cross tab using the gosales_goretailers package below. If someone would be so kind as to add a few percentage calculations and post back so I can rip it apart to see what makes it tick, it would be much appreciated. BTW I’m still in a C8 test environment with the RTM version so if you post code that was created with MR1 or MR2 (which is now available btw) I won’t be able to run the report.

Thanks to all who take the time to participate.
Code: 
<report xmlns="[URL unfurl="true"]http://developer.cognos.com/schemas/report/2.0/"[/URL] expressionLocale="en-ca"><!--RS:8.1-->
	<modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
	<queries>
		<query name="Query1">
			<source>
				<model/>
			</source>
			<selection><dataItem name="Order year" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order year]</expression></dataItem><dataItem name="Quarter"><expression>CASE
WHEN [gosales_goretailers].[Orders].[Order month] between 1 and 3 THEN 'Q1'
WHEN [gosales_goretailers].[Orders].[Order month] between 4 and 6 THEN 'Q2'
WHEN [gosales_goretailers].[Orders].[Order month] between 7 and 9 THEN 'Q3'
ELSE 'Q4'
END
</expression></dataItem><dataItem name="Product line" aggregate="none"><expression>[gosales_goretailers].[Products].[Product line]</expression></dataItem><dataItem name="Product type" aggregate="none"><expression>[gosales_goretailers].[Products].[Product type]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><dataItem name="Total(Product line)"><expression>total(currentMeasure within set [Product line])</expression></dataItem><dataItem name="Total(Product type)"><expression>total(currentMeasure within set [Product type])</expression></dataItem><dataItem name="Total(Order year)"><expression>total(currentMeasure within set [Order year])</expression></dataItem></selection>
		</query>
	</queries>
	<layouts>
		<layout>
			<reportPages>
				<page class="pg" name="Page1">
					<pageBody class="pb">
						<contents>
							<crosstab class="xt" refQuery="Query1" rowsPerPage="500">
								<crosstabCorner class="xm"><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></crosstabCorner>
								
								
								<style>
									<CSS value="border-collapse:collapse"/>
								</style>
							<crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Order year" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Order year)" class="ol"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCellStyle class="ov"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product type" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Product type)" class="il"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCellStyle class="iv"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Product line)" class="ol"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCellStyle class="ov"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Quantity"/><crosstabFactCell class="mv"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents></crosstabFactCell></crosstab>
						</contents>
					</pageBody>
					<pageHeader class="ph">
						<contents>
							<block class="ta">
								<contents>
									<textItem class="tt">
										<dataSource>
											<staticValue/>
										</dataSource>
									</textItem>
								</contents>
							</block>
						</contents>
						<style>
							<CSS value="padding-bottom:10px"/>
						</style>
					</pageHeader>
					<pageFooter class="pf">
						<contents>
							<table class="tb">
								<tableRows>
									<tableRow>
										<tableCells>
											<tableCell>
												<contents>
													<textItem>
														<dataSource>
															<reportExpression>AsOfDate()</reportExpression>
														</dataSource>
													</textItem>
												</contents>
												<style>
													<CSS value="vertical-align:top;text-align:left;width:25%"/>
												</style>
											</tableCell>
											<tableCell>
												<contents>
													<textItem>
														<dataSource>
															<staticValue>- </staticValue>
														</dataSource>
													</textItem>
													<textItem>
														<dataSource>
															<reportExpression>PageNumber()</reportExpression>
														</dataSource>
													</textItem>
													<textItem>
														<dataSource>
															<staticValue> -</staticValue>
														</dataSource>
													</textItem>
												</contents>
												<style>
													<CSS value="vertical-align:top;text-align:center;width:50%"/>
												</style>
											</tableCell>
											<tableCell>
												<contents>
													<textItem>
														<dataSource>
															<reportExpression>AsOfTime()</reportExpression>
														</dataSource>
													</textItem>
												</contents>
												<style>
													<CSS value="vertical-align:top;text-align:right;width:25%"/>
												</style>
											</tableCell>
										</tableCells>
									</tableRow>
								</tableRows>
								<style>
									<CSS value="border-collapse:collapse;width:100%"/>
								</style>
							</table>
						</contents>
						<style>
							<CSS value="padding-top:10px"/>
						</style>
					</pageFooter>
				</page>
			</reportPages>
		</layout>
	</layouts>
</report>

Nuffsaid.
 
Sort by date Sort by votes
Code: 
<report xmlns="[URL unfurl="true"]http://developer.cognos.com/schemas/report/2.0/"[/URL] expressionLocale="en-ca"><!--RS:8.1-->
    <modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
    <queries>
        <query name="Query1">
            <source>
                <model/>
            </source>
            <selection><dataItem name="Order year" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order year]</expression></dataItem><dataItem name="Quarter"><expression>CASE
WHEN [gosales_goretailers].[Orders].[Order month] between 1 and 3 THEN 'Q1'
WHEN [gosales_goretailers].[Orders].[Order month] between 4 and 6 THEN 'Q2'
WHEN [gosales_goretailers].[Orders].[Order month] between 7 and 9 THEN 'Q3'
ELSE 'Q4'
END
</expression></dataItem><dataItem name="Product line" aggregate="none"><expression>[gosales_goretailers].[Products].[Product line]</expression></dataItem><dataItem name="Product type" aggregate="none"><expression>[gosales_goretailers].[Products].[Product type]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><dataItem name="Total(Product line)"><expression>total(currentMeasure within set [Product line])</expression></dataItem><dataItem name="Total(Product type)"><expression>total(currentMeasure within set [Product type])</expression></dataItem><dataItem name="Total(Order year)"><expression>total(currentMeasure within set [Order year])</expression></dataItem><dataItem name="% of total"><expression>percentage([Quantity] at [Quarter],[Product type])</expression></dataItem><dataItem name="Total(Quarter)"><expression>total(currentMeasure within detail [Quarter])</expression></dataItem></selection>
        </query>
    </queries>
    <layouts>
        <layout>
            <reportPages>
                <page class="pg" name="Page1">
                    <pageBody class="pb">
                        <contents>
                            <crosstab class="xt" refQuery="Query1" rowsPerPage="500">
                                <crosstabCorner class="xm"><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></crosstabCorner>
                                
                                
                                <style>
                                    <CSS value="border-collapse:collapse"/>
                                </style>
                            <crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" class="ml" edgeLocation="Quarter"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="% of total" class="cl" edgeLocation="% of total"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell class="cv"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Quarter)" class="il" solveOrder="1" edgeLocation="Total(Quarter)"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell class="iv"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Order year" class="ml" edgeLocation="Order year"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Order year)" class="ol" edgeLocation="Total(Order year)"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell class="ov"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product type" class="ml" edgeLocation="Product type"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Product type)" class="il" edgeLocation="Total(Product type)"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell class="iv"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" class="ml" edgeLocation="Product line"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Product line)" class="ol" edgeLocation="Total(Product line)"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell class="ov"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Quantity"/><crosstabFactCell class="mv"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents></crosstabFactCell><crosstabIntersections><crosstabIntersection row="Product type" column="% of total"><style><dataFormat><percentFormat decimalSize="2"/></dataFormat></style></crosstabIntersection><crosstabIntersection row="Total(Product type)" column="% of total"><style><dataFormat><percentFormat decimalSize="2"/></dataFormat></style></crosstabIntersection><crosstabIntersection row="Total(Product line)" column="% of total"><style><dataFormat><percentFormat/></dataFormat></style></crosstabIntersection></crosstabIntersections></crosstab>
                        </contents>
                    </pageBody>
                    <pageHeader class="ph">
                        <contents>
                            <block class="ta">
                                <contents>
                                    <textItem class="tt">
                                        <dataSource>
                                            <staticValue/>
                                        </dataSource>
                                    </textItem>
                                </contents>
                            </block>
                        </contents>
                        <style>
                            <CSS value="padding-bottom:10px"/>
                        </style>
                    </pageHeader>
                    <pageFooter class="pf">
                        <contents>
                            <table class="tb">
                                <tableRows>
                                    <tableRow>
                                        <tableCells>
                                            <tableCell>
                                                <contents>
                                                    <textItem>
                                                        <dataSource>
                                                            <reportExpression>AsOfDate()</reportExpression>
                                                        </dataSource>
                                                    </textItem>
                                                </contents>
                                                <style>
                                                    <CSS value="vertical-align:top;text-align:left;width:25%"/>
                                                </style>
                                            </tableCell>
                                            <tableCell>
                                                <contents>
                                                    <textItem>
                                                        <dataSource>
                                                            <staticValue>- </staticValue>
                                                        </dataSource>
                                                    </textItem>
                                                    <textItem>
                                                        <dataSource>
                                                            <reportExpression>PageNumber()</reportExpression>
                                                        </dataSource>
                                                    </textItem>
                                                    <textItem>
                                                        <dataSource>
                                                            <staticValue> -</staticValue>
                                                        </dataSource>
                                                    </textItem>
                                                </contents>
                                                <style>
                                                    <CSS value="vertical-align:top;text-align:center;width:50%"/>
                                                </style>
                                            </tableCell>
                                            <tableCell>
                                                <contents>
                                                    <textItem>
                                                        <dataSource>
                                                            <reportExpression>AsOfTime()</reportExpression>
                                                        </dataSource>
                                                    </textItem>
                                                </contents>
                                                <style>
                                                    <CSS value="vertical-align:top;text-align:right;width:25%"/>
                                                </style>
                                            </tableCell>
                                        </tableCells>
                                    </tableRow>
                                </tableRows>
                                <style>
                                    <CSS value="border-collapse:collapse;width:100%"/>
                                </style>
                            </table>
                        </contents>
                        <style>
                            <CSS value="padding-top:10px"/>
                        </style>
                    </pageFooter>
                </page>
            </reportPages>
        </layout>
    </layouts>
</report>
 
Upvote 0 Downvote
Thanks King Crab, I'll have a look when we move to MR2. I can't run it in our RTM version.

Nuffsaid.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tushargaurav
  • Locked
  • Question Question
date time prompt not working in arabic language in cognos
Replies
0
Views
534
tushargaurav
tushargaurav
Goutham3835
  • Locked
  • Question Question
Dateset in Cognos framework
Replies
0
Views
481
Goutham3835
Goutham3835
Emperatriz Stredel
  • Locked
  • Question Question
Two Cognos reports with two Models (Framework Manager)
Replies
0
Views
499
Emperatriz Stredel
Emperatriz Stredel
NileshDhande
  • Locked
  • Question Question
Can I use web api as data source to cognos?
Replies
0
Views
527
NileshDhande
NileshDhande
aortiz268
  • Locked
  • Question Question
Congos table of contents help pls
Replies
0
Views
470
aortiz268
aortiz268

Part and Inventory Search

Sponsor

Back
Top