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 IamaSherpa 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
Aug 3, 2001
374
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
date time prompt not working in arabic language in cognos
Replies
0
Views
233
tushargaurav
tushargaurav
Goutham3835
  • Locked
  • Question
Dateset in Cognos framework
Replies
0
Views
198
Goutham3835
Goutham3835
Emperatriz Stredel
  • Locked
  • Question
Two Cognos reports with two Models (Framework Manager)
Replies
0
Views
215
Emperatriz Stredel
Emperatriz Stredel
NileshDhande
  • Locked
  • Question
Can I use web api as data source to cognos?
Replies
0
Views
253
NileshDhande
NileshDhande
aortiz268
  • Locked
  • Question
Congos table of contents help pls
Replies
0
Views
198
aortiz268
aortiz268

Part and Inventory Search

Sponsor

Back
Top