Crosstab with Multiple Values per square

[jethro89]

Hi, I'm trying to create a crosstab report which is supposed to have 3 different values per sqare - and two items are selected in the prompt and one is a calculated variance. I can only get one value to show up in the crosstab, the other two are left off. When I run the sql in the query, I get back data from all data items. Is there a limitation on how many items you can add to a crosstab? (I'm adding the values in a table within each crosstab block). Any help or advise would be great!
thanks,
anita

[almeids]

Anita,
   I've had the same experience you describe and was unable to find a way to get a report to show multiple values per cell even though report studio lets you structure things that way.
   You should be able to get the same visual results by adding a dimension level to your query for each fact data item you are displaying, nesting it in the rows (for vertical listing of the measures) or columns (for horizontal), and formatting as necessary to suppress the headings if appropriate or simulate the appearance of a single cell (the latter may take some conditional formatting).
   Post back if you need more specific info.
Steve

[jethro89]

Steve,
Thanks, I don't think that will work, or maybe I just don't understand. Let me describe what I'm trying to do a little better. Across the top of the report are lvl3 and lvl4 departments and down the side are values the user picks - different dimensions that are specifically picked for this report. I tried adding the amounts as columns, but it did not populate for all the departments. I've attached a screen print, is this what you were able to do? Maybe I'm just going about it the wrong way....
thanks,
anita

[almeids]

Anita,
   I think perhaps I wasn't clear enough.  You need to manipulate your result set so that you have Scenario A, Scenario B, and Variance each in separate records with some data item which identifies what's in the record, then nest that (5th) data item along with data items 1-4 in your rows.
   I'm pasting the xml below for a GO S&R example reporting actual, budget and variance.  This uses tabular references and sets to build the result set but other approaches which shape the data similarly would work as well.
   I'll follow with another post that formats the example so it looks like 3-value cells (did some experimenting after my previous reply!)
Steve

<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 the formatted version.  I should also have mentioned that my example lays the 3 values out horizontally since my Actual/Budget/variance data item is nested in the columns, yours will need to be in the rows.
The only 2 differences in this version are:
- The headings for the nested data item are set to box type none
- There is a conditional variable, true for one of the data types (Variance I think), and formatting to turn on the right side of the cell border to simulate the appearance of a single cell.

Post again if any questions.
Steve


<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><style><CSS value="display:none"/></style></crosstabLevel><crosstabCell><textItem><queryItemRef refItem="Quantity"/></textItem><style><CSS value="border-left-style:none;border-right-style:none"/></style><conditionalStyle refVariable="is_right_cell"><style/><style refVariableValue="1"><CSS value="border-right:1pt solid silver"/></style></conditionalStyle><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>
<variableList><variable name="is_right_cell" type="boolean"><expression>[Data Type]='Delta'</expression><variableValueList><variableValue value="1"/></variableValueList></variable></variableList></report>

[jethro89]

Steve,
Thanks so much! I will look at these - and try them this afternoon or tomorrow morning! thanks again!
anita

[jethro89]

Steve,
Thanks so much, it worked wonderfully! With a lot of work, but I just wanted to say thanks!
anita