@Logan Whitaker  Thanks for this info.  I have used the lag in the past and was hoping to be able to use here.  I cannot.  Like you mention, I am not able to.  This final query has 3 unions in the backend.  None of which are sql.  Unfort, I only have until friday to complete this report.  I'm racking my brain with the easiest solution.

@Logan Whitaker  Thanks for this info.  I have used the lag in the past and was hoping to be able to use here.  I cannot.  Like you mention, I am not able to.  This final query has 3 unions in the backend.  None of which are sql.  Unfort, I only have until friday to complete this report.  I'm racking my brain with the easiest solution.

Assuming you have the columns as measures then...

@Logan Whitaker  Thanks for this info.  I have used the lag in the past and was hoping to be able to use here.  I cannot.  Like you mention, I am not able to.  This final query has 3 unions in the backend.  None of which are sql.  Unfort, I only have until friday to complete this report.  I'm racking my brain with the easiest solution.

Assuming you have the columns as measures then...

@Logan Whitaker  Thanks for this info.  I have used the lag in the past and was hoping to be able to use here.  I cannot.  Like you mention, I am not able to.  This final query has 3 unions in the backend.  None of which are sql.  Unfort, I only have until friday to complete this report.  I'm racking my brain with the easiest solution.

@Logan Whitaker  Thanks for this info.  I have used the lag in the past and was hoping to be able to use here.  I cannot.  Like you mention, I am not able to.  This final query has 3 unions in the backend.  None of which are sql.  Unfort, I only have until friday to complete this report.  I'm racking my brain with the easiest solution.

------------------------------
Jenifer Broughton
------------------------------


Original Message:
Sent: Tue July 25, 2023 09:05 PM
From: Logan Whitaker
Subject: Cognos 11.1.7

Tried the technique on your numbers and it seems to work...

I think the numbers in my list match your orginal post. If you've typed in the formula exactly as above, then it could be down to the order that the running totals and moves are working out.

Do you have to use a xtab? Lists are easier to control the orders.

You could try applying a presort in the query.

Debugging the solution should be quite easy.

Just change the calc to running-total( [a] ) and see if this simple running total works. If this works then do the running count [ C + D)

and build it up.

Seeing what running count A gives will give you an indication of if its a row order problem.

------------------------------
Marc Reed
------------------------------


Original Message:
Sent: Wed July 26, 2023 07:28 AM
From: Jenifer Broughton
Subject: Cognos 11.1.7

@Logan Whitaker  Thanks for this info.  I have used the lag in the past and was hoping to be able to use here.  I cannot.  Like you mention, I am not able to.  This final query has 3 unions in the backend.  None of which are sql.  Unfort, I only have until friday to complete this report.  I'm racking my brain with the easiest solution.

------------------------------
Jenifer Broughton


Original Message:
Sent: Tue July 25, 2023 09:05 PM
From: Logan Whitaker
Subject: Cognos 11.1.7

<report xmlns="http://developer.cognos.com/schemas/report/16.2/"           useStyleVersion="11.6" expressionLocale="en-gb">
                
                <drillBehavior/>
                <layouts>
                    <layout>
                        <reportPages>
                            <page name="Page1">
                                <style>
                                    <defaultStyles>
                                        <defaultStyle refStyle="pg"/>
                                    </defaultStyles>
                                </style>
                                <pageBody>
                                    <style>
                                        <defaultStyles>
                                            <defaultStyle refStyle="pb"/>
                                        </defaultStyles>
                                    </style>
                                    <contents><list horizontalPagination="true" name="List1" refQuery="Base">
            
            
            
            <noDataHandler>
                <contents>
                    <block>
                        <contents>
                            <textItem>
                                <dataSource>
                                    <staticValue>No Data Available</staticValue>
                                </dataSource>
                            </textItem>
                        </contents>
                        <style>
                            <CSS value="padding:16px;"/>
                        </style>
                    </block>
                </contents>
            </noDataHandler>
            <style>
                <CSS value="border-collapse:collapse"/>
                <defaultStyles>
                    <defaultStyle refStyle="ls"/>
                </defaultStyles>
            </style>
        <listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Year"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Year"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quarter"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quarter"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="A"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="A"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Running Total"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Running Total"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="C"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="C"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="D"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="D"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents>
                                </pageBody>
                            <XMLAttributes><XMLAttribute output="no" name="RS_legacyDrillDown" value="0"/></XMLAttributes></page>
                        </reportPages>
                    </layout>
                </layouts>
            <XMLAttributes><XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=";"/><XMLAttribute output="no" name="decimalSeparator" value="."/><XMLAttribute output="no" name="RS_modelModificationTime" value="2020-06-11T19:19:39.585Z"/></XMLAttributes><queries><query name="Base"><source><model/></source><selection><dataItem aggregate="none" rollupAggregate="none" name="Year" sort="ascending"><expression>[C].[C_Great_outdoors_data_module].[page_6].[Year_]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value="1"/></XMLAttributes></dataItem><dataItem aggregate="none" rollupAggregate="none" name="Quarter" sort="ascending"><expression>[C].[C_Great_outdoors_data_module].[page_6].[Quarter]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/><XMLAttribute output="no" name="RS_dataUsage" value="1"/></XMLAttributes></dataItem><dataItem name="A" label="A" aggregate="none"><expression>case [Quarter]
   when &apos;Q1&apos; then 239
   when &apos;Q2&apos; then 301
   when &apos;Q3&apos; then 316
   when &apos;Q4&apos; then 349
end</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value=""/></XMLAttributes></dataItem><dataItem name="C" label="C" aggregate="none"><expression>case [Quarter]
   when &apos;Q1&apos; then 0
   when &apos;Q2&apos; then 8
   when &apos;Q3&apos; then 21
   when &apos;Q4&apos; then 1
end</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value=""/></XMLAttributes></dataItem><dataItem name="D" label="D" aggregate="none"><expression>case [Quarter]
   when &apos;Q1&apos; then 0
   when &apos;Q2&apos; then 0
   when &apos;Q3&apos; then 12
   when &apos;Q4&apos; then 0
end</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value=""/></XMLAttributes></dataItem><dataItem name="Running Total"><expression>moving-total( running-total(  [A] ) - running-total( [C] + [D] ), 2) - ( running-total(  [A] ) - running-total( [C] + [D] ) )</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="9"/><XMLAttribute output="no" name="RS_dataUsage" value="2"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterDefinition><filterInValues refDataItem="Year" dataType="integer"><filterValues><filterValue>2015</filterValue></filterValues></filterInValues></filterDefinition></detailFilter></detailFilters></query></queries><modelPath type="module">/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;By feature&apos;]/folder[@name=&apos;Core&apos;]/folder[@name=&apos;Data&apos;]/module[@name=&apos;Great outdoors data module&apos;]</modelPath><reportName>New report</reportName></report>

@Logan Whitaker  Thanks for this info.  I have used the lag in the past and was hoping to be able to use here.  I cannot.  Like you mention, I am not able to.  This final query has 3 unions in the backend.  None of which are sql.  Unfort, I only have until friday to complete this report.  I'm racking my brain with the easiest solution.

Thank you!!  I have been in meeting after meeting today.  Thanks for the extra info.  I will digging into this more shortly.  You have been wonderful!

<report xmlns="http://developer.cognos.com/schemas/report/16.2/"           useStyleVersion="11.6" expressionLocale="en-gb">
                
                <drillBehavior/>
                <layouts>
                    <layout>
                        <reportPages>
                            <page name="Page1">
                                <style>
                                    <defaultStyles>
                                        <defaultStyle refStyle="pg"/>
                                    </defaultStyles>
                                </style>
                                <pageBody>
                                    <style>
                                        <defaultStyles>
                                            <defaultStyle refStyle="pb"/>
                                        </defaultStyles>
                                    </style>
                                    <contents><list horizontalPagination="true" name="List1" refQuery="Base">
            
            
            
            <noDataHandler>
                <contents>
                    <block>
                        <contents>
                            <textItem>
                                <dataSource>
                                    <staticValue>No Data Available</staticValue>
                                </dataSource>
                            </textItem>
                        </contents>
                        <style>
                            <CSS value="padding:16px;"/>
                        </style>
                    </block>
                </contents>
            </noDataHandler>
            <style>
                <CSS value="border-collapse:collapse"/>
                <defaultStyles>
                    <defaultStyle refStyle="ls"/>
                </defaultStyles>
            </style>
        <listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Year"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Year"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quarter"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quarter"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="A"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="A"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Running Total"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Running Total"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="C"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="C"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="D"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="D"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents>
                                </pageBody>
                            <XMLAttributes><XMLAttribute output="no" name="RS_legacyDrillDown" value="0"/></XMLAttributes></page>
                        </reportPages>
                    </layout>
                </layouts>
            <XMLAttributes><XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=";"/><XMLAttribute output="no" name="decimalSeparator" value="."/><XMLAttribute output="no" name="RS_modelModificationTime" value="2020-06-11T19:19:39.585Z"/></XMLAttributes><queries><query name="Base"><source><model/></source><selection><dataItem aggregate="none" rollupAggregate="none" name="Year" sort="ascending"><expression>[C].[C_Great_outdoors_data_module].[page_6].[Year_]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value="1"/></XMLAttributes></dataItem><dataItem aggregate="none" rollupAggregate="none" name="Quarter" sort="ascending"><expression>[C].[C_Great_outdoors_data_module].[page_6].[Quarter]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/><XMLAttribute output="no" name="RS_dataUsage" value="1"/></XMLAttributes></dataItem><dataItem name="A" label="A" aggregate="none"><expression>case [Quarter]
   when &apos;Q1&apos; then 239
   when &apos;Q2&apos; then 301
   when &apos;Q3&apos; then 316
   when &apos;Q4&apos; then 349
end</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value=""/></XMLAttributes></dataItem><dataItem name="C" label="C" aggregate="none"><expression>case [Quarter]
   when &apos;Q1&apos; then 0
   when &apos;Q2&apos; then 8
   when &apos;Q3&apos; then 21
   when &apos;Q4&apos; then 1
end</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value=""/></XMLAttributes></dataItem><dataItem name="D" label="D" aggregate="none"><expression>case [Quarter]
   when &apos;Q1&apos; then 0
   when &apos;Q2&apos; then 0
   when &apos;Q3&apos; then 12
   when &apos;Q4&apos; then 0
end</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value=""/></XMLAttributes></dataItem><dataItem name="Running Total"><expression>moving-total( running-total(  [A] ) - running-total( [C] + [D] ), 2) - ( running-total(  [A] ) - running-total( [C] + [D] ) )</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="9"/><XMLAttribute output="no" name="RS_dataUsage" value="2"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterDefinition><filterInValues refDataItem="Year" dataType="integer"><filterValues><filterValue>2015</filterValue></filterValues></filterInValues></filterDefinition></detailFilter></detailFilters></query></queries><modelPath type="module">/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;By feature&apos;]/folder[@name=&apos;Core&apos;]/folder[@name=&apos;Data&apos;]/module[@name=&apos;Great outdoors data module&apos;]</modelPath><reportName>New report</reportName></report>

@Logan Whitaker  Thanks for this info.  I have used the lag in the past and was hoping to be able to use here.  I cannot.  Like you mention, I am not able to.  This final query has 3 unions in the backend.  None of which are sql.  Unfort, I only have until friday to complete this report.  I'm racking my brain with the easiest solution.

There are a few ways to do this.

The simplest way is if your crosstab columns are fixed, that you will always have the same columns regardless of the data. If so, then just create measures in the query for each column. With them being measures you can simply add additional calculations. In this case you could just end up with a list.

Looking at the names of your columns I think this looks viable.

If the columns are not fixed then you could just add 'grouping' calcs to your query.

For example, lets say my columns are A,B,C,D and I want A+B and A+B-C.

Add a new data item firstGroup like

   name: first group; expression: case when col in ('A','B') then col end

Add a new data item firstGroupTotal like

   name: firstGroupTotal; expression: case when col in ('A','B') then 'A+B' end

Put these as the column of your xtab. This will give you cols A,B then BLANK and 'A+B' and BLANK.

You can then use a conditional format to hide the blank columns.

Repeat this for the final set of columns, But the total column could just be a simple 'Total' data item.

To get the negative, assuming your data is always +ve, just change the underlying sign of the data so that it becomes negative. Then show negatives with no formatting.  

Here is a page showing the intermediate steps: