Drill through from any level – DMR to Relational.

This post answers the question:

How can you drill up/down in a DMR report then drill through from any level to the detail report? 

There are a number of ways to achieve this functionality.  This example shows how to do it with out setting up a filter for every level in every hierarchy and does not use SDK.

The key is to pass both the ‘Level Unique Name’ and ‘Member Caption’ in the drill through definition.  This allows you to identify which level the user has drilled to and provides a value that can be matched to the relational values.

It works by using:

• A combination of case statement and macro prompt to return the correct relational query item expression for the ‘Level Unique Name’ passed
• A filter that restricts the report to only the values that match the ‘Member Caption’ passed.

Now you can chuck out all those reports with millions of filters.  The solution below uses two parameters, a case statement with a line for each level in the dimension and a single filter. You should be able to adapt this to accommodate any dimension by adding a third parameter.

Start with the target report:

1. Add two new data items to the query …

PassedMemberCaption – stores the Member Caption passed by the drill through.
MacroResult – identifies the level and identifies the correct relational query item.

Expression for [PassedMemberCaption]
?p_MemberCaption?

Expression for [MacroResult] **
Case
when position(‘country’,?p_level?)=0
then #prompt(‘p_result’,’token’,’[Sales (query)].[Retailer site].[Region]’)#
when position(‘Region’,?p_level?)=0
then #prompt(‘p_result’,’token’,’[Sales (query)].[Retailer site].[Retailer country]’)#
else null
end

** Note: this logic works for a two level hierarchy. Adjust the logic to cater for more levels.

2. Add a filter to the report query …

Filter – tests values from the relational query item against the passed member caption

Expression for the filter:
[MacroResult]=[PassedMemberCaption]

Now setup the Drill Through Definition:

p_Level – passes the Level Unique Name of the member you drill on
p_MemberCaption – passes the Level Unique Name of the member you drill on

Drill through Definition - Parameter mapping and metadata properties

Note: Both p_Level and p_MemberCaption are mapped to a dimension in the source package rather than specific level.

 

 

 

 

[Example uses the Go Data Warehouse (analysis) and Go Data Warehouse (query) packages from Cognos 8.4 samples.]

Formatting values without referencing the query

Have you ever wondered why a variable that tests values from one query will return an error if used on a list, chart or crosstab that uses a different query?   (eg: 4 crosstabs in a report, each with its own query. You want to use the same variable to format all four crosstabs. )

This is because the expression in the variable references the query as well as the data item(s) used in the expression, although it is not always obvious.

So, to use the same test across different lists, charts or crosstabs you need to either find a way to reference the values without referencing the query or create a seprate variable for each query.    The trick is is to use report functions rather than query based ones such as cellValue(), ColumnNumber() and RowNumber().

Here’s a handy trick for creating a variable that can single variable that can be used on all 4 crosstabs in the example above … set crosstab negative values to red.

Similarly, a conditional style must be based on a data item and therefore is referencing a specific query in the report.  I haven’t found a way to create a global version of one of these  – let me know if you work one out!

Format negative crosstab values

Recently I had to find a way to create a variable that could be used over multiple data containers in a report.  The report consisted of 15 crosstabs each with their own query.  What I really wanted was a single variable that could test the values in any of the crosstabs and format them accordingly.

The answer was to test the cell values displayed in the crosstab rather than the values returned by a data item from a query.

Here’s a handy little trick:

Format negative values red in any crosstab

1. Create a variable (type:boolean, expression: cellValue()<0)
2. Select the crosstab fact member cells
3. Use the variable to test the cell values and change their stlye to red

[Example use the Sales and Marketing (Cube) package from Cognos 8 samples.]

Trouble shooting – linkMember() function with relative time periods

Having trouble using the linkMember() function with relative time periods such as Current Month and Quarter To Date?

You have a dimensionally modelled source with ‘Current Month’ in it and you need to find the corresponding month two years ago.  So … you try using parallellPeriod() and linkMember() with ‘Current Month’ but you keep getting an error about it the operator not being applicable for the member at the selected level.

Don’t dispair!

The problem is that the member from the Current Month level (‘Current Month’) does not match to any member in the linked hierarchy.  You need to link the member for the current business value, that is, the member that ‘Current Month’ resolves to.

Using the firstChild() function will resolve to the relevent member under the Month level (eg: ’2010/Sep’).

 

In this example:
The data item will return ’2008/Sep’ when the current month is ’2010/Sep’.

[Examples use the Sales and Marketing (Cube) package from Cognos 8 samples.]

Yes, you can perform calculations over multiple hierarchies from the same dimension

Despite the error message you might have been encountering … it is possible to reference members from more than one hierarchy of the same dimension in a calculation.  (eg: use Current Month to find corresponding month in a prior year.)

To do this you’ll need to use linkMember().  This handy function allows you to reference a member in one hierarchy and look up the corresponding member in a specified level of another hierarchy without getting the error “OP-ERR-0250, The function requires operands from the same hierarchy”.

You’ll find all the syntax in the Report Studio function tip but here are some examples to get you going.

In this example: 
The member ’2007/Jul’ from the Month level  ‘Current Month’ is used to find the equivalent month in the ‘Time’ hierarchy.

Now you’ve got the concept let’s use it on a dynamic member.

For dynamic member selection choose a member such as ‘Current Month’ rather than the actual month that it resolves to.  To make this work you’ll need to use another function to resolve the dynamic member to it’s current business value.  firstChild() is a good one to use in this example.

In this example:
firstChild() is used to locate the current business value for the ‘Current Month’ member.  This ensures that the linkMember() function uses a member that can be located in the linked hierarchy.

Note:  Simply using ‘Current Month’ will result in an error about the linkMember not being applicable at the level you’ve specified. (PCA-ERR-051, The LinkMember operator is not applicable for member … at level …”)

This is just the starting point.  Try using linkMember() with a relative time function such as parallelPeriod.

Points to remember:

• linkMember() requires a member that will match a member in the linked hierarchy
• Use linkMember() to avoid the error “OP-ERR-0250″
• Use additional function(s) with dynamic members to avoid the error “PCA-ERR-051″

[Examples use the Sales and Marketing (Cube) package from Cognos 8 samples.]

BI and Reporting QA processes

Answering the question: Would you place QA in a special unit of BI/Reporting or outside of BI/Reporting?

I would suggest that QA should be regarded as yet another unit serviced by BI/Reporting.  Even in the small organisation, although it is easier to justify in larger organisation or those with high QA requirements.

Yes, BI/Reporting has its own QA phases, however, QA  as an internal business process encompasses more than just BI/Reporting. 

QA processes have a heavy reliance on the services provided by the BI/Reporting unit and are often consumers with critical service needs.  However, QA also services other units in the organisation with buisness needs and outcomes that differ to those of the BI/Reporting unit.

This is a great example of how BI/Reporting can return increased value by directing it at internal processes not just at business units.

Encourage QA to be classified as an operational unit regardless of the size of the organisation. 

It is ok if this classification is in name only as the role may be performed by the same individual or the unit may reside in the same area.  What is important is that QA reatains a separate identity for clarity of process and procedural definition.

BI is about the Business – it’s not about the tool!

A quick soapbox moment… BI is about the business, not about the tool! 

This morning I came across a post with the enticing title of “What should be the ideal roadmap for Financial budgeting and planning (Business Intelligence )?“ 

As those who know me are aware, BI strategy is something that I am very passionate about.  So, I opened thread with the expectation of enjoying a juicy discussion around roadmap  and strategy formulation only to find that the discussion, and all but one of the comments, focussed on which tool set would be best.  I was gladdened, however, to see that single post that mirrored the thought screaming around my head – it’s not about the tool!

The vendor/product based discussion that I found is indicative of a major flaw in many BI and CPM initiatives – BI is about the Business and capturing grey matter.  There are many tools that can deliver the same capabilities, albeit with varying degrees of effort and cost. However, you can fail just as spectacularly and easily with any vendor or product if you don’t have a well founded Strategy.  In my experience this applies regardless of the size of your organisation or the initiative. 

The basis of the ideal roadmap for any BI / CPM initiative lies in the desired business outcomes. 

A BI Strategy is required to deliver these outcomes.  It serves to align BI objectives with those of the organisation and forms the roadmap for providing high-value sustainable BI.  As for the discussion about tools, go back to the BI strategy for guidelines on tool selection, options for tactical and strategic scenarios, as well as considerations for introducing new features or tool sets into the organisation. 

These guidelines direct business change through a process of mapping business requirements against BI capabilities (ie: reporting vs analytics, static vs dynamic) and then BI capabilities to the various tools and features that could be used to deliver them.  The upshot is that your decision over which tool(s) to select is: driven by the strength of your business need and balanced by both present and ongoing feasibility for the organisation.  Ideally tool selection guidelines will be used before any purchases are made and not after, as we often find in the real world. 

In conclusion, stay focused on the business, identify valid needs and how to address them with a viable solution and only then will you be armed with enough to ensure your selection hits on the best tool or combination of tools for your purpose.

Namespace Migration – Scheduled Objects

Today I came across the question: “How do I change the location of a schedule? (ie: move it from one namespace to another)” 

Well, the simple answer is … you don’t!

This question arose during a namespace migration involving scheduled reports that had been saved in ‘My Folders’ by various users.  After these users had been migrated to the new namespace they could no longer see any of the objects they had previously scheduled.

The developer used IBM Cognos Administration to view properties for the entries listed under Schedules and noticed that the General properties tab included “Location:  Directory > {namespace} > {user name} > My Folders”.   Find out more about the Location property 

This led the developer to believe that the scheduled resided under the old namespace and to fix the problem he should try to relocate from one namespace to another.  Not correct but an easy assumption to make.

The important thing to note here is that the location property I’ve described above is actually the location of the object (ie: report, job, view etc) that is being run by the schedule.  The developer was viewing the general property tab of a report and mistakenly thinking it was for a schedule.

Ok then, getting back to the developer’s question …. What he really wanted to do is update the schedules so that they were secured against the new namespace.

Try thinking about it like this:

You wish to remove all dependencies on an obsolete namespace so that it can be decommissioned.  These dependencies are defined in the properties of various objects that you can see in Cognos Connection (eg: folders, reports, schedules etc). 

So what you need to do is change the security properties of the scheduled object so that it only references entries from the active namespace.  In other words, you need to change the properties of the scheduled object rather than find a way to relocate it.

In the example above a user logging into the new namespace won’t see his old scheduled objects until the object’s properties have been updated. 

The good news is that this update can be done in the Cognos 8 UI.  The bad news is that it’s a manual process requiring you to work on one object at a time.  Alternatively it can be done in bulk by using SDK. 

VERY IMPORTANT … Which ever method you use the same steps should be followed.  Be careful that you’re not just hacking when using the SDK!

So how do you do it?   Find out more

[Tip: security settings can also be stripped using the Export and Import processes found on the Configuration tab of Cognos Administration.]

Namespace Migration – Updating Object Security

Problem:
You are migrating off an obsolete namespace, however, a number of objects (ie: folders, reports, views, jobs etc) in Cognos Connection have dependencies on this namespace as it was used to define their security.

You wish to update the object security to use the new namespace and remove all dependencies on an obsolete namespace so that it can be decommissioned. 

Solution:

First some preparation in the external security provider:
Before doing anything else you must ensure that all the required users, groups and roles in the obsolete namespace have been replicated in the active one along with their associated membership.

Now, to fix the problem:
This involves modifying the access list on the permissions tab of each object.  That is, adding the new users/group/roles from the active namespace, setting permissions on each and then removing all corresponding entries come from the obsolete namespace.

The process to do this is:

1. Log on to both namespaces
2. Locate the object that needs its security updated
3. Review the access list for the object (Permissions tab under Properties)
4. Identify all entries that are obsolete (ie: users/group/roles)
5. Take note of the permissions for each obsolete entry (ie: read/write/execute/set permissions/traverse)
6. Add corresponding entries (users/group/roles) from the active namespace
7. Grant permissions on each new entry to match those on its corresponding obsolete entry
8. Save changes and test the object
9. Return the permissions tab and remove all obsolete entries from the access list
10. Save changes and test the object again

[Tip: to be able to see entries from both namespaces you'll need to log into both namespaces in the one browser session.]

Security on Scheduled Objects

Today I came across the question: “How do I reset the security associated with a schedule?” 

The answer is … update the associated report (or view, job etc). 

In Cognos 8 objects are arranged hierarchically and inherit their security settings from their parent container. 

Huh?? 

Firstly some terminology: Objects are the various entries you see in Cognos Connection such as folders, reports, views, saved output etc.  

An object’s Security Settings are the list of users/groups/roles and the permissions grant/deny that you see on the ‘Permissions’ tab.  (The permissions tab is visible when viewing the properties of an object.)

So … a report inherits its security settings from the folder it resides in; saved output inherits its security settings from the report that produced it; and so on.

Similarly, a schedule can be considered the child of the object it runs.  So the schedule’s security is determined by the object (report, view job etc) it is scheduled to run.

The impact of this is that if you change the security (ie: access list and permissions) of an object this change will automatically be inherited by the schedule.

[Tip: It is not necessary to edit the schedule in order to trigger this ‘reset’, however, the user may need to refresh their browser session by logging off and logging back on again.]

So how do you do it?  click here