Solve Order Property in Report studio

[coggie01]

Has anyone used the Solve Order Property and does it work as expected? I have a crosstab where I want the calculation of column to be executed last or after the row calculation and it does not seem to work. Basically, I am trying to calculate percentages and in the total column it is summing all the percentages versus the aggregated percentage. For example:

Actual Variance %
Oakland 50 20 40%
San Diego 100 -10 -10%
Total 150 10 30%**

** but the total% should be 10/150=6.67%

I placed a Solve Order of 0 in the Total row to sum the records first then Solve Order of 1 in % column to get the percentage of the total variance / total actual.

But it does not appear to work Any suggestions??

 

[JGirl]

I'm not 100% sure that I understand your problem, I dont know what the "Solve Order" property is...

You should be able to get it to aggregate properly...Look at the following example (copy the code below, and in report studio use Tools --> Open report from clipboard)

This example is reportstudio 1.1 using the gosales and retailers query

<report xml:lang="en-au" xmlns="[URL unfurl="true"]http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->[/URL]
	<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
	<querySet xml:lang="en-au">
		<BIQuery name="Query1">
			<cube><dimension name="Order year"><level name="Order year"><item refItem="Order year" aggregate="none" key="true" isHeader="true"/></level></dimension><factList><item refItem="Revenue" aggregate="automatic" isHeader="true"/><item refItem="Planned revenue" aggregate="automatic" isHeader="true"/><item refItem="Variance" aggregate="automatic" isHeader="true"/><item refItem="Varaince %" aggregate="automatic" isHeader="true"/></factList></cube>
		<tabularModel><dataItem name="Order year" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order year]</expression></dataItem><dataItem name="Revenue" aggregate="total"><expression>[gosales_goretailers].[Orders].[Revenue]</expression></dataItem><dataItem name="Planned revenue" aggregate="total"><expression>[gosales_goretailers].[Orders].[Planned revenue]</expression></dataItem><dataItem name="Variance"><expression>[Revenue] - [Planned revenue]</expression></dataItem><dataItem name="Varaince %"><expression>[Variance] / [Revenue]</expression></dataItem><filter use="required" summary="false"><condition>[gosales_goretailers].[Countries].[Sales territory] = 'Americas'</condition></filter><filter use="required" summary="false"><condition>[gosales_goretailers].[Products].[Product name] = 'Aloe Relief'</condition></filter></tabularModel></BIQuery>
		
	<BIQuery name="Query1_crosstab"><cubeReference refQuery="Query1"/><summary><rowEdge><level refLevel="Order year"><item refItem="Order year"/></level></rowEdge><columnEdge><cellMembers/></columnEdge><dataCells><item refItem="Revenue"/><item refItem="Planned revenue"/><item refItem="Variance"/><item refItem="Varaince %"/></dataCells></summary></BIQuery></querySet>
	<layoutList>
		<layout>
			
		<pageSet>
				
			<page name="Page1">
					<pageBody>
						<crosstab refQuery="Query1_crosstab">
							<style>
								<CSS value="border-collapse:collapse"/>
							</style>
							<crosstabCorner/>
							<crosstabRows/>
							<crosstabColumns/>
						<crosstabLevel refLevel="Order year"><textItem><queryItemRef refItem="Order year"/></textItem><levelFooter><textItem><queryItemRef refItem="Order year" content="label"/></textItem><levelCells/></levelFooter></crosstabLevel><crosstabMember refMember="Revenue"><textItem><queryItemRef refItem="Revenue" content="label"/></textItem></crosstabMember><crosstabMember refMember="Planned revenue"><textItem><queryItemRef refItem="Planned revenue" content="label"/></textItem></crosstabMember><crosstabMember refMember="Variance"><textItem><queryItemRef refItem="Variance" content="label"/></textItem></crosstabMember><crosstabMember refMember="Varaince %"><textItem><text>Variance %</text></textItem></crosstabMember><crosstabCell><textItem><queryItemRef refItem="Revenue"/></textItem><member refMember="Revenue"/></crosstabCell><crosstabCell><textItem><queryItemRef refItem="Planned revenue"/></textItem><member refMember="Planned revenue"/></crosstabCell><crosstabCell><textItem><queryItemRef refItem="Variance"/></textItem><member refMember="Variance"/></crosstabCell><crosstabCell><textItem><queryItemRef refItem="Varaince %"/><style><percentFormat decimalSize="2"/></style></textItem><member refMember="Varaince %"/></crosstabCell></crosstab>
					</pageBody>
					<pageHeader>
						<block class="reportTitle">
							<textItem class="reportTitleText">
								<text/>
							</textItem>
						</block>
						<style>
							<CSS value="padding-bottom:10px"/>
						</style>
					</pageHeader>
					<pageFooter>
						<table>
							<tableRow>
								<tableCell>
									<textItem>
										<expression>AsOfDate()</expression>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:left;width:25%"/>
									</style>
								</tableCell>
								<tableCell>
									<textItem>
										<text>- </text>
									</textItem>
									<textItem>
										<expression>PageNumber()</expression>
									</textItem>
									<textItem>
										<text> -</text>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:center;width:50%"/>
									</style>
								</tableCell>
								<tableCell>
									<textItem>
										<expression>AsOfTime()</expression>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:right;width:25%"/>
									</style>
								</tableCell>
							</tableRow>
							<style>
								<CSS value="border-collapse:collapse;width:100%"/>
							</style>
						</table>
						<style>
							<CSS value="padding-top:10px"/>
						</style>
					</pageFooter>
				</page></pageSet></layout>
	</layoutList>
</report>