Comparing two crosstabs with separate queries (conditional formatting?)

[SimonWM]

Hello everyone,

I am building a Report that consist of 2 crosstables. Both crosstables have the same structure (column/row), but separate queries. The difference between the two is, that they have been "sliced" for different years (plus some other dimensions..).

Now I want to apply some conditional formatting (? that is, if there is no better way to handle it) to both of them in order to show if cell values have changed (at all, no matter the amount).

Is there an easier way achieve that, than applying a conditional formatting to each cell?


Thanks a lot for your help!

Regards,
Simon

[bdbits]

Are you trying to do periodic comparisons? For example, YTD this month versus YTD this month last year. If you are, and it sounds like you are using dimensional data, there a bunch of builtin functions to do those comparisons. If you could use those in the conditional expressions, there are a lot of ways you might show the change.

[SimonWM]

Hi,

yes the data is dimensional and periodic, but unfortunately not YTD or anything like it. We are retrieving data 2-3 times a year, and we want to track changes between the latest message this year (cross tab 1), vs. the latest message last year (cross tab 2).

What would those builtin functions be, that you have mentioned (I am quite new to Cognos)?

Thanks and regards,
Simon

[bdbits]

I have this really old concise dimensional function reference I found ages ago, back in the version 8 days. If you are not familiar with dimensional functions, I would advise perusing this document. I have not gone looking for a more up-to-date version if it exists. As far as I know everything in here is still valid anyway.

https://www.ibm.com/developerworks/community/forums/atom/download/attachment_14246163_Dimensional_function.pdf?nodeId=8a146e34-d92c-4c38-b25f-f083bd2bf78c&usg=AFQjCNEsPkq6q6TOjvv9LVoSY3LNmOd7Gw

Appendix A has a "functional" grouping. You probably want to at least look at the ones under Date. When I wrote my post I was thinking of periodstodate and parallelperiod, openingPeriod and closingPeriod, lead and lag. But the family/member/set functions are also very useful, e.g. head, tail, firstchild, etc for the first/last member in a level.

Once you start using dimensional functions, you will find SQL somewhat limiting. :-)

[SimonWM]

Hi bdbits,
sorry for the late replay! At first glance, I couldn't find an updated version of your document, but it definitly does help. I think, I will have to go with "tuple" somehow, PTD-functions probably wont work in my context.
I have no doubt, that the dimensional functions are very powerful tools, but many are not exactly self-explanatory. It is going to take some time :-)

[SimonWM]

Hi again,
unfortunately, I cant get my conditional formatting to work. I am using the "conditional style" function at a crosstab intersection. I tried to use an expression like this in the "Expression Definition"-box:

value( tuple( [Dimension1].[Element]; [Dimension].[Measure] ) )
<>
value( tuple( [Dimension2].[Element]; [Dimension].[Measure] ) )

In return, I get the following error(s):
RSV-VAL-0002
CRX-API-0011

I can't make any sense of the Google-results to these errors. Am I using the "Conditional Styles"-property wrong, altogether?

[bdbits]

It's a bit of a shot in the dark, but do all of the data items in your expressions appear in the report? If not, you need to "bind" them to a data item in the report that does appear. Set your focus to the data item on the report (Crosstab Node should be selected). On the property sheet, you'll find a property called, I kid you not, Properties. Click the ellipsis and check the data items that are in the query but not on the report. Click OK and try to run the report. Instructions are from memory but I hope you can follow what I wrote to the proper place.

[SimonWM]

I think I got what you meant, unfortunately it doesn't work neither. Now, I am getting the next Error-Msg:

XQE-QTE-0003
The transformation "V5QueryToCogMDXQueryTransformationLibrary - CopyV5DetailFilterSlicerSummaryFilterToQuery" was applied but did not modify the query plan.

I am going to try a different approach now, by using one big query for all my crosstables and pre-calculating all differences. Maybe I will get that to work. :(