If you are unable to create a new account, please email support@bspsoftware.com

 

Still can't setup Variable to use Data Item values! Plz help!!!

Started by dfite, 21 Sep 2007 02:02:44 PM

Previous topic - Next topic

dfite

I posted on this a while ago, and thanks for the replies, but I'm still stuck!  Let me expound on what I'm trying to accomplish.  I am trying to get cognos to create a letter to a member with several paragraphs of text conditionally displayed based on attributes of the member.  While I have been successful in using Conditional Block List objects with variables set up to read user prompt values, I just cant get them to work with Fact or Data Item values.  I can also use text box objects to successfully conditionally display text based on data item values.  For example, this works:

if ([AOE] > 59 and [PENDING_IND] = 'Y') then
('We are in receipt of your Enrollment Affidavit form') else
('')

BUT - when I create a boolean Variable and set its expression to:

[AOE] > 59 and [PENDING_IND] = 'Y'

then set a conditional block list's Condition Block property to that variable and create my text boxes for Yes, and No, it always evaluates to the "no" side.  I've even tried the thing where you click on the "yes" option of the variable, turning the middle bar green, then setting the text boxes.  I've tried tons of combinations, and nothing seems to work. 

Essentially, all I need is to be able to set a conditional block to point to a boolean variable that evaluates the contents of a query1 data item and display text based on that.  Should be simply, and it works for user prompt parameter based variables, but this doesn't work for me.   PLEASE HELP!!!

COGNOiSe administrator

Here is a sample with Cognos 8.2. If I'll find a ReportNet Server, I'll try to reproduce.


<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-us"><!--RS:8.2-->
<modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Product line" aggregate="none"><expression>[gosales_goretailers].[Products].[Product line]</expression></dataItem><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Products].[Product name]</expression></dataItem><dataItem name="Margin" aggregate="average"><expression>[gosales_goretailers].[Products].[Margin]</expression></dataItem></selection>
</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="Product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Product line"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Product name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Product name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Margin"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Margin"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><staticValue/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Margin"/></dataSource><style><CSS value="display:none"/></style></textItem><conditionalBlocks>
<conditionalBlockDefault>
<contents><textItem><dataSource><staticValue>NO</staticValue></dataSource></textItem></contents>
</conditionalBlockDefault>
<conditionalBlockCases refVariable="Boolean1"><conditionalBlock refVariableValue="1"><contents><textItem><dataSource><staticValue>YES</staticValue></dataSource></textItem></contents></conditionalBlock></conditionalBlockCases></conditionalBlocks>
</contents></listColumnBody></listColumn></listColumns></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>
<reportVariables><reportVariable type="boolean" name="Boolean1">
<reportExpression>upper(substring([Query1].[Product line],1,1))&lt;'N' AND [Query1].[Margin]&lt;0.3</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable>
</reportVariables></report>

COGNOiSe administrator

I have my reasons to believe that CRN behaves quite differently then C8.2 in terms of variables and calculated columns. To my unpleasent surprise, you cannot calculate a perfectly valid SQL column using conditional logic. For example:

upper(substring([Product line],1,1))<'N'

will never compile in CRN 1.1.896 as a List Report Calculation. When used in a variable it fails safely, but false, thus always resulting in a 'NO' condition. I think it is a bug, that has been resolved in 8.2, but might never get resolved in 1.1. Once I get my new testing server, I will be able to retry with the lates and greatest CRN build.

MFGF

Just a thought,

Does it work if you create a calculated query item as

upper(substring([Product line],1,1))

Then use this in your variable expression

[calcitem] < 'N'

Just wondering if this gets round the problem...

MF.
Meep!

dfite

Thanks for the replies, but you guys must be so vastly better than me that yer just confusing the hell out of me.  AOE in my query1 isn't calculated.  That's done on the Oracle side.  AOE is just a number when it gets to cognos (its the member's age as an integer).  The problem is that I can set up a test in a text box's expression (as I wrote earlier) and that works, but as a varible expression it always evaluates to 'no' - either that, or I'm doign something wrong somewhere else, which I'm disinclined to believe.  So, are you saying that this is just a defect and is fixed in version 8?  Any chance its fixed in MR1, 2, 3 or 4?

I'll play around with creating another data item and trying to have it evaluate its contents, but if you guys have any other ideas I'd really appreciate them, as I still feel very lost.

Thanks!

almeids

I gave up long ago on attempts to put any sort of compound logic into variable expressions; I put all logic into a data item in the tabular model or the query so that it evaluates to 'Y' or 'N' and then just test for one of those values in the conditional variable.  This makes it easy to verify the results of the logic which are otherwise hidden in either the report or the tabular data viewer, and minimizes the chances of being bit in the posterior by the lovely conditional-expressions-don't-get-validated "feature" of ReportNet.
You could also try your example as a plain conditional format (box type=none for the no-text condition), I find this more straightforward than conditional blocks for on-off states.
Good luck,
Steve

dfite

Oh my gosh, I finally made a breakthru and got something to work!!!!  THANKS to Almeid!!!!!!  The conditional block list things never worked, so I did what you suggested and created a data item that evaluated both of my other data items' contents. I then created a boolean variable pointing to it and clicked on the Yes branch of the variable (turnign the middle bar green), then set the Box Type property and was able to get it to display conditionally!

So, lets recap so that no one thinks that Reportnet 1.1 is a crappy, defect-ridden user-hostile sack of web based crap - IF you want to test a user prompt, you can make a variable and point a conditional block list to that, but if you want to test a query data item your variable cant be compound at all, and conditional block lists mysteriously fail to work with them anyway, so you gotta set the "Box Type" property of all things to get anything to work...  geeeezus, I love Cognos.  Ya can't GET more straighforward than that!  (PAH!!!!!!!!!!!)