Difference column in a crosstab report

[jennyn]

Hello , can someone help me with this question:
I am working on a report that looks like this

              2006            2007       Yearly difference
source1   10  33%      30  50%       20   17% 
source2   10  33%      10  25%       0     -8%
source3   10  33%      10  25%       0     -8%
Total       30  100%    50  100%     20     0%

Both Years are selected in the prompt page and may not be consecutive.
If someone know how to do the difference  - please give me a detailed instructions
since I am very new to ReportNet.
Thanks in advance.

[almeids]

Are the years selected from a single multiselect prompt, or 2 separate prompts?
There may be a better way but I would use a union as follows - I'm ignoring your percentage columns for simplicity:
- have 2 year prompts/parameters (e.g. ?year1? and ?year2?)
- use an IN filter in your existing tabular model to retrieve the 2 years:
   [your_year_column] in (?year1?,?year2?)
- select your tabular model and cut
- drag in a tabular set
- double click into the tabular set
- in the left box, paste the tabular model you cut
- in the right box, paste it again, then doubleclick into it
- replace your year data item with a calculated data item (fixed):
    'Yearly difference'
- replace your amount column with a calculated data item:
   case [your_year_column]
   when=?year1?
   then -[amount]
   else [amount]
   end

That should do it for the amounts, the percentages might take care of themselves depending on how you are currently calculating them.  The basic idea is to use your data twice, once for the year details and once for the aggregate - if performance is an issue a tabular reference may help, so that you only hit the database once and then reuse the results locally.  Post back if any questions.

[jennyn]

Thank you for posting the instructions, but I am still a little lost.
I started to create the tabular set ( this is something I never done before , so I am not so sure what to expect out of it ).
When I created the data item as you advised like this:
case [CREATE_YEAR]
when ?FirstYear?
   then - [REGS_CREATED]
   else [REGS_CREATED]
end

I am getting an error:
QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.

UDA-SQL-0358 Line 16: Syntax error near "=".
Also, I am not sure if I need to create item like this on the left and right sides?

Another question: if this will be a calculation that I will drag into report
it will display this column for each year, but I need it only displayed at the end. How to take care of it?
Thanks.

[almeids]

The data item you are "creating" is the same data item you already have - the tabular set (union) lines up the results of the 2 tabular model vertically, so you won't be dragging anything new into the report.
As the message indicates, it's probably a simple syntax error, my example was off the top of my head and may not be syntactically correct.  I'm not sure how you're getting an equal sign in the error when there isn't one in the case statement, the error may be coming from elsewhere.
I think I have a GOSAR example of this sort of thing, if not I'll make one and post it next.

[almeids]

What luck!  Saved this for a rainy day (and it's actually raining here).
Hopefully you have Cognos' GO Sales and Retailers sample package available.  The xml below is for a report similar to what you want to do, though it's calculating an actual vs. budget delta, nested within year, rather than a year-over-year delta. 
The structure is somewhat different than I described previously - in this case budget and actual (for you, year1 and year2) are separate queries/models, with separate tabular references in the crosstab's tabular set.  You'll probably want to stick to a single model, single reference (or 2 copies of the model as in my original suggestion).  If you do use a tabular reference you'll need an intermediate level above it, like the "Delta Model" tabular model in the example, to introduce the sign-flipping; if you copy and tweak your tabular model instead this is not necessary.
Finally - if you haven't done this before - the way to get the xml into a report is to copy it, then select Open Report from Clipboard after starting Report Studio in the appropriate package.

<report xml:lang="en-us" xmlns="http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->
   <modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
   <querySet xml:lang="en-us">
      <BIQuery name="Crosstab Query"><cube><factList><item refItem="Quantity" aggregate="none" isHeader="true"/></factList><dimension name="Data Type"><level name="Data Type"><item key="true" refItem="Year" aggregate="none" isHeader="true"/></level><level name="Data Type1"><item key="true" refItem="Data Type" aggregate="none" isHeader="true"/></level></dimension><dimension name="Product name"><level name="Product name"><item key="true" refItem="Product name" aggregate="none" isHeader="true"/></level></dimension></cube><tabularSet name="Actual and Budget/Delta Tabular Set"><left><tabularReference refQuery="Actual Query"/></left><right><tabularSet name="Tabular Set1"><left><tabularReference refQuery="Budget Query"/></left><right><tabularModel name="Delta Model"><tabularSet name="Delta Tabular Set"><left><tabularReference refQuery="Actual Query"/></left><right><tabularReference refQuery="Budget Query"/></right></tabularSet><dataItem name="Data Type" aggregate="none"><expression>'Delta'</expression></dataItem><dataItem name="Product name" aggregate="none"><expression>[Delta Tabular Set].[Product name]</expression></dataItem><dataItem name="Year" aggregate="none"><expression>[Delta Tabular Set].[Year]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>if ([Delta Tabular Set].[Data Type]='Actual') then (1) else (-1)*[Delta Tabular Set].[Quantity]</expression></dataItem></tabularModel></right></tabularSet></right></tabularSet></BIQuery><BIQuery name="Actual Query">
         <cube><factList><item refItem="Product name" aggregate="none"/></factList></cube>
      <tabularModel name="Actual Tabular Model"><dataItem name="Data Type" aggregate="none"><expression>'Actual'</expression></dataItem><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Product name]</expression></dataItem><dataItem name="Year" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Year]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Product forecasts].[Actual quantity]</expression></dataItem></tabularModel></BIQuery>
      
   <BIQuery name="Budget Query">
         <cube><factList><item refItem="Product name" aggregate="none"/></factList></cube>
      <tabularModel name="Budget Tabular Model"><dataItem name="Data Type" aggregate="none"><expression>'Budget'</expression></dataItem><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Product name]</expression></dataItem><dataItem name="Year" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Year]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Product forecasts].[Expected volume]</expression></dataItem></tabularModel></BIQuery><BIQuery name="Crosstab Query_crosstab"><cubeReference refQuery="Crosstab Query"/><summary><rowEdge><level refLevel="Product name"><item refItem="Product name"/></level></rowEdge><columnEdge><level refLevel="Data Type"><item refItem="Year"/></level><level refLevel="Data Type1"><item refItem="Data Type"/></level></columnEdge><dataCells><item refItem="Quantity"/></dataCells></summary></BIQuery></querySet>
   <layoutList>
      <layout>
         
      <pageSet>
            
         <page name="Page1">
               <pageBody>
                  
               <crosstab refQuery="Crosstab Query_crosstab">
                     <style>
                        <CSS value="border-collapse:collapse"/>
                     </style>
                     <crosstabCorner/>
                     <crosstabRows/>
                     <crosstabColumns/>
                     <crosstabLevel refLevel="Product name"><textItem><queryItemRef refItem="Product name"/></textItem></crosstabLevel><crosstabLevel refLevel="Data Type"><textItem><queryItemRef refItem="Year"/><style><numberFormat useGrouping="false"/></style></textItem><style><CSS value="text-align:center"/></style></crosstabLevel><crosstabLevel refLevel="Data Type1"><textItem><queryItemRef refItem="Data Type"/></textItem></crosstabLevel><crosstabCell><textItem><queryItemRef refItem="Quantity"/></textItem><member refMember="Quantity"/></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>

[almeids]

Here's a simpler example in line with my original suggestion...

<report xml:lang="en-us" xmlns="http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->
   <modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
   <querySet xml:lang="en-us">
      <BIQuery name="Crosstab Query"><cube><factList><item refItem="Quantity" aggregate="none" isHeader="true"/></factList><dimension name="Year"><level name="Year"><item key="true" refItem="Year" aggregate="none" isHeader="true"/></level></dimension><dimension name="Product name"><level name="Product name"><item key="true" refItem="Product name" aggregate="none" isHeader="true"/></level></dimension></cube><tabularSet name="Actual and Budget/Delta Tabular Set"><right><tabularModel name="Delta Model"><dataItem name="Year" aggregate="none"><expression>'Delta'</expression></dataItem><dataItem name="Product name" aggregate="none"><expression>[DataModel].[Product name]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>case [DataModel].[Year]
when ?year2? then [DataModel].[Quantity]
else -[DataModel].[Quantity]
end</expression></dataItem><tabularReference refQuery="DataQuery"/></tabularModel></right><left><tabularReference refQuery="DataQuery"/></left></tabularSet></BIQuery><BIQuery name="DataQuery">
         <cube><factList><item refItem="Product name" aggregate="none"/></factList></cube>
      <tabularModel name="DataModel"><dataItem name="Year" aggregate="none"><expression>to_char([gosales_goretailers].[Product forecasts].[Year])</expression></dataItem><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Product name]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Product forecasts].[Actual quantity]</expression></dataItem><filter><condition>[gosales_goretailers].[Product forecasts].[Year] in (?year1?,?year2?)</condition></filter></tabularModel></BIQuery>
      
   <BIQuery name="YearPromptQuery"><cube><factList><item refItem="Year" aggregate="none" isHeader="true"/></factList></cube><tabularModel><dataItem name="Year" aggregate="none"><expression>[gosales_goretailers].[Product forecasts].[Year]</expression></dataItem></tabularModel></BIQuery><BIQuery name="Crosstab Query_crosstab"><cubeReference refQuery="Crosstab Query"/><summary><rowEdge><level refLevel="Product name"><item refItem="Product name"/></level></rowEdge><columnEdge><level refLevel="Year"><item refItem="Year"/></level></columnEdge><dataCells><item refItem="Quantity"/></dataCells></summary></BIQuery></querySet>
   <layoutList>
      <layout>
         
      <promptPageList><page name="Prompt Page1">
               <pageBody>
                  <style>
                     <CSS value="padding:12px"/>
                  </style>
               
               <table><tableRow><tableCell><textItem><text>year 1</text></textItem></tableCell><tableCell><selectValue refQuery="YearPromptQuery" parameter="year1"><useItem refItem="Year"/></selectValue></tableCell></tableRow><tableRow><tableCell><textItem><text>year 2</text></textItem></tableCell><tableCell><selectValue refQuery="YearPromptQuery" parameter="year2"><useItem refItem="Year"/></selectValue></tableCell></tableRow><tableRow><tableCell/><tableCell/></tableRow><style><CSS value="border-collapse:collapse;width:100%"/></style></table></pageBody>
               <pageFooter>
                  <style>
                     <CSS value="padding:12px;border-top:1pt solid #999999"/>
                  </style>
                  <promptButton type="cancel">
                     <style>
                        <CSS value="margin-right:7px"/>
                     </style>
                  </promptButton>
                  <promptButton type="back">
                     <style>
                        <CSS value="margin-right:7px"/>
                     </style>
                  </promptButton>
                  <promptButton type="next">
                     <style>
                        <CSS value="margin-right:7px"/>
                     </style>
                  </promptButton>
                  <promptButton type="finish"/>
               </pageFooter>
            </page>
         </promptPageList><pageSet>
            
         <page name="Page1">
               <pageBody>
                  
               <crosstab refQuery="Crosstab Query_crosstab">
                     <style>
                        <CSS value="border-collapse:collapse"/>
                     </style>
                     <crosstabCorner/>
                     <crosstabRows/>
                     <crosstabColumns/>
                     <crosstabLevel refLevel="Product name"><textItem><queryItemRef refItem="Product name"/></textItem></crosstabLevel><crosstabLevel refLevel="Year"><textItem><queryItemRef refItem="Year"/><style><numberFormat useGrouping="false"/></style></textItem><style><CSS value="text-align:center"/></style></crosstabLevel><crosstabCell><textItem><queryItemRef refItem="Quantity"/></textItem><member refMember="Quantity"/></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>