calculation based on 2 rows to include specific columns for each row?

[Cognosnewbie14]

Hi all,
Hoping someone can help me. Have just started using Report Studio (was previously using BIA a lot more but I have been told I am able to perform more complex calculations in Report Studio!)
Basically the package I am using pulls data from multiple models, one model for the current year (FY13/14) and one for future years (FY14/15 onwards).

My report consolidates data from the 2 models so effectively I have the current year data from the first model and future years data from the second model. But a problem I have is that the first model now also contains the ability to populate data for the next year. Thus my consolidated report has two rows for FY14/15. So currently I have separated these two lines (one line for current year model and 2nd line for future years model) on the report else I end up double counting FY14/15! Is there any way I can consolidate these two lines in the report so that for FY13/14 it picks up data from current year model and for FY14/15 onwards it picks up data from future years model?

Or if its easier, is there a way for me to insert different calculations for an inserted calculation row for each column? i.e. it would show the variance between costs and budget: 'Current Year Model cost less Budget' for FY13/14 and for future years 'Future Year Model cost less Budget'.

So my end aim is to be able to show that variance line but making sure I don't double count FY14/15 in calcs! Apologies for the long winded email but I would really appreciate some guidance on which route I should take to achieve this?

thanks in advance :)

[BigChris]

Long emails are always better than one-line requests for urgent help.

In your consolidated model are you able to tell which model the data comes from? If you can I would imagine a detail filter along the lines of ([Financial Year] = 'FY13/14' and [Model]=1) or ([Financial Year] <> 'FY13/14' and [Model]=2)

[Cognosnewbie14]

Hi Chris!

appreciate the rapid response!  :)

I can tell which model the data is from by the object, i.e. currently I have a row for each object - titled 'In Year' and 'Planned'. So I can create a calc which totals up thse two lines to give me one line for costs but I have the double count of 14/15.

In using your detail filter I would amend it to suit the report as follows:   [Fiscal Year Name] = '13/14' and [Model]=1) or ([Fiscal Year Name <> 'FY13/14' and [Model]=2

But I am a bit unsure what I would replace 'Model' with? (sorry if I sound stupid!)

thanks :)

[BigChris]

Hi - not stupid at all. Sometimes the hardest thing is accurately describing the problem to someone that can't see what you're looking at.

So, have you got a field (maybe called something like Category) in your data that contains either 'In Year' or 'Planned'? If that's the case you'd end up with a filter that looks more like:

([Fiscal Year Name] = '13/14' and [Category] = 'In Year') or ([Fiscal Year Name] <> '13/14' and [Category] = 'Planned')

If that bears no relation to your data structure just let me know...it might be me being stupid.

[Cognosnewbie14]

Ah I have 'Application Name' which splits out from which model the data is coming from!

But I have just remembered that we actually have 2 models which make up 'Planned', sorry i should have mentioned this earlier but it didnt occur to me!

([Fiscal Year Name] = '13/14' and [Application Name] = 'In Year') or ([Fiscal Year Name] <> '13/14' and [Application Name] = 'Planned A' or 'Planned B')

would the above work? or am i being too optimistic haha!

thanks again appreciate your help! :)

[BigChris]

Nope, looks like you're just about there. Just watch out mixing your ANDs and ORs (might not be a problem these days - used to cause all sorts of trouble back in the day) - I'd alter your filter to be :

([Fiscal Year Name] = '13/14' and [Application Name] = 'In Year') or ([Fiscal Year Name] <> '13/14' and [Application Name] in  ('Planned A', 'Planned B'))

If that doesn't work just post back and let me know

[Cognosnewbie14]

Chris YOU ARE A LEGEND!

just tried it and it seems to work perfect!! I can't believe it! I wish i found this site earlier as I have wasted a good part of a week trying to figure it out somehow!

just going to test it now over more data and make sure its pulling through the values i want for each year and also ill try inserting a variance calc now and hopefully that should work fine!

cheers :)

My next challenge is to now use report studio to create various tables (which currently I will currently create by pulling the above report into excel and running macros to summarise information in various ways!)

Don't suppose you know off the top of your head whether there is any guidance document out there to give me a steer on how i could set up a report which would contain numerous different tables based organisational filter selected? If not I'll have a play with the different options and see what I can come up with  ;D

thanks again :)

[BigChris]

Flattery will get you everywhere - glad I was able to help you get it sorted.

I'm not quite sure what you mean by

Don't suppose you know off the top of your head whether there is any guidance document out there to give me a steer on how i could set up a report which would contain numerous different tables based organisational filter selected?

You can certainly have various prompts in a report, and you can drive the values for those prompt from tables in your database. So for example, say you wanted a filter for cost centre (it sounds like you're working with financial data). If you've got a table with cost centres in, you could create a value prompt and tell it to get its data from that table. You could then use that prompt to filter the data in your query. (If I'm telling you how to suck eggs just ignore me).

So your prompt would have a source of [tblCostCentres].[Cost Centre] and would be attached to a parameter which will hold the cost centre that you choose...I'll call it pCostCentre. In your query you'd then have a filter that would look something like [Financial Table].[Cost Centre] = ?pCostCentre?

Give it a try and see how far you get. If I've got the wrong end of the stick just let me know and I'll try to help as best I can...although I probably ought to get on with some work now!

C

[Cognosnewbie14]

no i appreciate all the help i can get!

Hmm i'm not sure thats what i need...

Basically the end product i want is a report i can run, using prompts to select department required, which outputs a series of different tables on each page.

presently i've only attempted to make single crosstab reports!

Also would it be possible for me to insert an if calculation where for selected years if a cell exceeds 10 or is below -10 then return '1'.

for example excel formula i would use is: =IF(OR(C76>10,C76<-10,D76>10,D76<-10)"1","")

could i go with something like: If(or([Fiscal Year Name] = '13/14')<-10,([Fiscal Year Name] = '13/14')>10),"1","")

No rush you can get back to me when you have a few mins! work comes first :)

cheers

[BigChris]

I'll do the easy bit and come back to the more difficult bit another time if that's ok. You can certainly do if...then..else calculations in Cognos. If anything they're easier than excel

=IF(OR(C76>10,C76<-10,D76>10,D76<-10)"1","")

becomes

If([Fiscal Year Name] = '13/14' and [Field1] between -10 and 10) then (0) else (1)

I just switched the logic round to make it easier to read

[Lynn]

Wonderful assistance here Chris!

I'd just chime in that the use of '13/14' in the filters might cause an issue when the next fiscal year comes along. Replacing that hard coded reference with a prompt will make the report more durable over time if there are plans to run it regularly. Sounds like Cognosnewbie is headed in that direction anyway, but thought I'd mention it in case.

[BigChris]

A good point well made Lynn. I'd suggest building a calendar table in the database with a record for every day, and a field with the current financial year flag for that date. You could then build in column for start and end dates for the financial period, start and end dates for the financial year, number of working days in the period, number of working days elapsed etc. etc.

But I think that's probably beyond the scope of what CognosNewbie14 is looking for at the moment...but bear it in mind for future reference.

[Cognosnewbie14]

ah ok yeah it makes sense doing it your way!

hmm just tried it though and comes up with error message when i try to run the report:

Parsing error before or near position: 66 of: "If([Fiscal Year Name] = '13/14' and [Variance to CT] between -10,"

i used:

If([Fiscal Year Name] = '13/14' and [Variance to CT] between -10,000,000 and 10,000,000) then (0) else (1)

any ideas?

Lynn: thanks for your reply, you're right going forward it will be run regularly so if having a prompt would negate the need to update the report every year then would be fab! is it relatively easy to do (for a newbie like me!)

thanks everyone for assistance :)

[MFGF]

hmm just tried it though and comes up with error message when i try to run the report:

Parsing error before or near position: 66 of: "If([Fiscal Year Name] = '13/14' and [Variance to CT] between -10,"

i used:

If([Fiscal Year Name] = '13/14' and [Variance to CT] between -10,000,000 and 10,000,000) then (0) else (1)

any ideas?

Character 66 looks to be somewhere around the position of the first comma in your "-10,000,000" value. I'm 99.99% sure it won't like the comma separators in the value

Try

If([Fiscal Year Name] = '13/14' and [Variance to CT] between -10000000 and 10000000) then (0) else (1)

Cheers!

MF.

[Cognosnewbie14]

cheers MF, it now accepts that calculation and the report runs but unfortunately when i look at the export it seems where rows hit the threshold it is summing up the total for all years as opposed to showing '1'?

[Cognosnewbie14]

sorry should have said this is the line i'm using now:

If([Fiscal Year Name] = '13/14' and [Variance to Budget] between -10000000 and 10000000) then (0) else (1)

[BigChris]

I'd take a small step back to make sure that the calculation is working the way you're expecting. Can you make a copy of your query, then ct out all the other fields except [Financial Year Name], [Variance to Budget] and [Variance to Budget Flag]. That way you can see if the calculation is working correctly and flagging the right records with either 0 or 1. You might need to bring in [Cost Centre] or [Account] or similar so that you get the right level of detail, but you get the idea.