Having Clause in Report Studio

[phanicrn]

fdg

[COGNOiSe administrator]

Why oh why, would you use SQL? Use what the Cognos framework has to offer for crying out loud. I've just been to a client where they had 300 reports all of them hand crafted in SQL. What a nightmare and misuse of Cognos technology.

[phanicrn]

U mean , we can't use  Having Clause in report studio.

[MFGF]

Hi,

No, that's not what Administrator means.  You're free to code your own query including a having clause if you want to - it's just not a good use of the product to write your reports in this way.  Instead, model your framework to give you accurate, predictable, efficient results in your studios, and then you don't need to worry about the details of the SQL being generated.  In this way, you can focus on "what does this report need to show" rather than "what SQL do I need to generate".

Regards,

MF.

[phanicrn]

Hi

I am just getting 3 calculated column in report,for that do i have do all calculation in FM and get to report studio. If i am mistaken, can i know how do we model ..

[COGNOiSe administrator]

You would benefit from self study using PDFs and the GOSAR package, or attendting a Cognos class. Framework Manager I & II I'd start with in your case. The Proceed with Report Studio and Advnaced. Without core skills you are making your job more difficult then it has to be.

[phanicrn]

solved..

[goose]

phanicrm Cognos can generate having clause off a normal model without you having to write your own sql. Create a summary filter and set the scope and a having clause will get generated. below is the xml for a report spec showing this using the GO Data Warehouse sample package.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-us"><!--RS:8.2-->
<modelPath>/content/package[@name='GO Data Warehouse']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Product type" aggregate="none"><expression>[Sales].[Product].[Product].[Product type]</expression></dataItem><dataItem name="Region" aggregate="none"><expression>[Sales].[Staff (by sales branch)].[Staff (by sales branch)].[City].[Region]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales].[Sales].[Quantity]</expression></dataItem></selection>
<summaryFilters><summaryFilter use="required"><filterExpression>[Quantity] &gt; 5000</filterExpression><summaryFilterLevels><summaryFilterLevel refDataItem="Product type"/><summaryFilterLevel refDataItem="Region"/></summaryFilterLevels></summaryFilter></summaryFilters></query>
</queries>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents>
<list class="ls" refQuery="Query1">



<style>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Region"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Region"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="Region"/></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Product type"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Product type"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="Product type"/></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><listGroups><listGroup refDataItem="Region"/><listGroup refDataItem="Product type"/></listGroups></list>
</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>

[rockytopmark]

Weird... I copied this report XML and pasted into a new report and the report runs fine, validates fine, but I get an Application Error when I click to View the Generated SQL

[phanicrn]

solved...

[goose]

rockytopmark I get the same error to not sure why. But a good way check the sql that gets sent to the db is to validate via the   menu option and select Information as the level that works.

phanicrn I will check out your problem asap

[goose]

phanicrn here is a report showing what you want hopefully, your requirement is not that clear but I think this is what you want.

Step 1 Create a table with the below sql on a database cognos has access to (this sql is for sql server express so you may have to modify the data types to suite your db

Code Select Expand


CREATE TABLE [cognoise](
[member_id] [nchar](10) NOT NULL,
[product_type] [nchar](2) NOT NULL,
[product_category] [nchar](40) NOT NULL,
[account_balance] [smallint] NULL
)

Step 2 Run the below sql to populate sample data

Code Select Expand

insert into cognoise values ('1006113','LN','New Indirect Auto Loan',21254);
insert into cognoise values ('1025246','LN','New Indirect Auto Loan',24699);
insert into cognoise values ('1049006','LN','New Indirect Auto Loan',8643);
insert into cognoise values ('105426','LN','New Indirect Auto Loan',17330);
insert into cognoise values ('1054683','LN','Indirect Boat Loan',13569);
insert into cognoise values ('105817','LN','New Indirect Auto Loan',10433);
insert into cognoise values ('1066380','LN','Indirect Boat Loan',19685);
insert into cognoise values ('1067008','LN','New Indirect Auto Loan',23704);
insert into cognoise values ('1067065','LN','Indirect Boat Loan',15947);
insert into cognoise values ('1072024','LN','New Indirect Auto Loan',8270);
insert into cognoise values ('1067008','SA','Savings',1000);
insert into cognoise values ('1067065','LN','New Auto Loan',12000);
insert into cognoise values ('1072024','SA','Savings',13500);
insert into cognoise values ('1072024','CD','Cheque',17000);
insert into cognoise values ('1067065','CD','Cheque',7500);


Step 3 Create a new report based on this xml, you will have to modify the datasource to be the data source in cognos that points to the db you created the above table in

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-us"><!--RS:8.2-->
<modelPath>/content/package[@name='GO Data Warehouse']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>

<sqlQuery name="SQL1" dataSource="go_data_warehouse">
<sqlText>select * from cognoise</sqlText>
<mdProjectedItems><mdProjectedItem name="member_id"/><mdProjectedItem name="product_type"/><mdProjectedItem name="product_category"/><mdProjectedItem name="account_balance"/></mdProjectedItems></sqlQuery>
</source>
<selection><dataItem name="member_id" aggregate="none"><expression>[SQL1].[member_id]</expression></dataItem><dataItem name="product_type" aggregate="none"><expression>[SQL1].[product_type]</expression></dataItem><dataItem name="Loan Count" aggregate="total"><expression>CASE
WHEN [product_type] = 'LN' and [product_category] in ('Indirect Boat Loan', 'Indirect Auto Loan') THEN (1) ELSE (0)
END
</expression></dataItem><dataItem name="Deposit Count" aggregate="total"><expression>CASE
WHEN [product_type] in ('SA','CD') THEN (1) ELSE (0)
END
</expression></dataItem><dataItem name="Other Count" aggregate="total"><expression>CASE
WHEN [product_type] = 'LN' and [product_category] not in ('Indirect Boat Loan', 'Indirect Auto Loan') THEN (1) ELSE (0)
END
</expression></dataItem><dataItem name="Deposit Amount" aggregate="total"><expression>CASE
WHEN [product_type] in ('SA','CD') THEN ([account_balance]) ELSE (0)
END
</expression></dataItem><dataItem name="Loan Amount" aggregate="total"><expression>CASE
WHEN [product_type] = 'LN' and [product_category] in ('Indirect Boat Loan', 'Indirect Auto Loan') THEN ([account_balance]) ELSE (0)
END
</expression></dataItem><dataItem name="Other Amount" aggregate="total"><expression>CASE
WHEN [product_type] = 'LN' and [product_category] not in ('Indirect Boat Loan', 'Indirect Auto Loan') THEN ([account_balance]) ELSE (0)
END
</expression></dataItem><dataItem name="product_category" aggregate="none"><expression>[SQL1].[product_category]</expression></dataItem><dataItem name="account_balance" aggregate="total" rollupAggregate="total"><expression>[SQL1].[account_balance]</expression></dataItem></selection>
<queryHints><localCache value="false"/></queryHints><summaryFilters><summaryFilter use="required"><filterExpression>[Deposit Amount] &gt; 0</filterExpression><summaryFilterLevels><summaryFilterLevel refDataItem="member_id"/></summaryFilterLevels></summaryFilter></summaryFilters></query>
</queries>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents>
<list class="ls" refQuery="Query1">



<style>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="member_id"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="member_id"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="member_id"/></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Loan Count"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Loan Count"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Loan Amount"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Loan Amount"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Other Count"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Other Count"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Other Amount"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Other Amount"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Deposit Count"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Deposit Count"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Deposit Amount"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Deposit Amount"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><listGroups><listGroup refDataItem="member_id"/></listGroups></list>
</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>


Hope this helps you out

[phanicrn]

I succeded some what..
I am able to bring in $ amount for these members.. When i try find the count of members, It only filters on first condition, rest of the columns shows as zeros.
I applied after aggregation, it puts having clause..I logged a call with cognos, let see what happens.

Thanks guys
Phanicrn

[goose]

phanicrn if you post the sql for the table and some sample data and report spec maybe I can help you out.

[phanicrn]

solved...

[phanicrn]

solved..

[phanicrn]

Report Sovled..

I did all the calculations in calculated columns and counts also in calculated columns,count aggregate  is defined as total. booom

report worked..

I really Appreciate support of cognoise forum Member

thanks a lot

phanicrn

[phanicrn]

 :)