Event Studio 8.4 Issues

I am running into a weird issue while using Event Studio. There have been a couple of issues that I have noticed while working with Event Studio [Cannot Burst Report Views with Cognos 8.4]. But the latest one is the strangest.

I have reports in a particular folder in prod. Since we are on 8.4 and not 8.4.1 we cannot burst report views. Hence we burst the reports but save the output in report view by setting the option "Save as Report View" to checked.

Now we need to have some reports emailed to the users. The "Attach link" doesn't work in E Mail Task as we have 8 report task in our event. So we created 2 events and included the 1st event as a task in the second event to get them all to run as a single event would.

This throws an error every time we run the event. The error message reads "..do not have permissions to update/delete the object...." and the path provided is that of the report. Obviously we do not have write permissions to the report folder.

But the question is why is it trying to save / delete the existing report?? Testing this out on dev where we have write permissions on the report folder works fine. The report view is created and report outputs are saved in the report view but then the Modified Date of the original report is changed. This is not expected and is unacceptable in our case.

This happens in the case of master-child event scenario only. If we run both the events separately one after the other no issues but the original report's modified date is changed. We are planning on moving to 8.4.1 so that we can burst the report views and have the original report untouched.

Any one else has faced this issue?
 

Implementing Dynamic Sorting with Dimension Model??

I am trying to implement dynamic sorting but using a dimensional model. Not having much luck here. Written to various Cognos forums as well but no help there.

Any help would be greatly appreciated.
 

Including Comments in Data Items

Here's a tip for those who would like to include comments in data items that include complex calculations or nested case / if-then-else statements:

The syntax for including comments - #/*Include comments here*/#
 

Disabling Drill Through on Totals in Crosstab

If you have Crosstab Reports with drill through on the measure and you also have Totals. You would notice that the drill through is applied on the Totals column as well. If you try removing the drill through on the Totals the drill through on the measures also gets removed.



Solution:

Step 1: In the Total Column, set the Define Contents property to Yes. The measure value disappears in this column.



Step 2: Now re-drag the total measure into the column from Insertable Objects after unlocking the column.



Step 3: Navigate to Crosstab intersection for the column and set the Source Type to Cell Value.



The Drill Through Link is no longer applied on the Total Column.



 

Celebrating 50 Posts!!!

Hurrah!!! This is my 50th post. When I started this blog, I didn't think that I would reach this far in this short amount of time. Thought I would run out of articles to blog about after a couple of months. And now I have at least 10 more articles planned that I need to write about.

I would like to thank all of you for your support and hope you have found this blog useful in some way. In case there are any articles in particular that you would like me to cover please feel free to let me know and I will do my best addressing them.

 

Drill through to different reports on clicking of different series in a chart

Requirement: A chart report displays Sales and Revenue. On clicking Sales value, users should be able to drill through to detailed Sales report. Likewise users should be able to view detailed Revenue report on clicking of Revenue series.

Solution:

Step 1: Create Source report with Data Items - Year, Sales, Revenue.

Step 2: Create 2 data Items - FlagA, FlagB for Sales series and Revenue Series.

FlagA - '1'
FlagB - '2'

Step 3: Nest FlagA data item under Sales Series and FlagB data item under Revenue series.

Step 4: Create 1 Target Report, with 2 lists - one for Sales and one for Revenue and include filter on Year to show up the report for the corresponding year passed from the source report.



Step 5: In Query 1 associated with Sales include filter ?FlagA? is not null to force the parameter to show up in the drill through definition. Likewise in Query 2 include filter ?FlagA? is not null.

Step 6: Create a render variable to conditionally display the required list.

case when ?FlagA? is not null then 'A' else 'B' end

Step 7: Apply render variable on the lists to display accordingly.

Step 8: In the source report, set up the drill through link to pass the Year, FlagA and FlagB data item.





Note: The legend displays the value of FlagA and FlagB concatenated with the Sales and Revenue Keyword. In case you wish to avoid this, then you would need to create a manual legend (design outside of the chart).
 

Cannot Burst Report Views with Cognos 8.4

We are on Cognos 8.4 and I received a bad setback today when I was trying to burst report views. The option to Burst Report Views is no longer available in 8.4. Then when I looked up Support Site there is a PMR on this issue and the suggestion is to move to 8.4.1.

For all those on 8.4, a move to 8.4.1 is suggested.
 

Tree Prompt Dynamic Default Selection

I have a requirement to default my tree prompt to a dynamic value say current year. This is not achievable directly so I tried looking up any existing JavaScript codes but with no luck.

Hence I was forced to dig through the Cognos Source Code to see if any of the internal functions could be used and I did come across a function that could be used to set the default values. There are still some issues with this code for example deselecting a child node doesn't deselect the parent node if no other child nodes are selected.

But the users are willing to live with this. I am not going to post this code unless someone desperately requires the same as I would not like anyone to face issues either upgrading the reports or have this code interfere with any other code in the report. This is not a tried and tested method as well. In case I don't run into any issues with this code and if I am able to iron out the other minor issues I will post the same.
 

Metadata Callbacks due to Table Type not being Set

Recently while testing my FM Model I noticed that one of the query subject was causing metadata callbacks and this was with the message "The metadata for...will be retrieved from the Database due to the table type not set for query subject...". I verified that the model query subject did not have any relationships to other query subjects. The data source query subjects did not have any filters / determinants / calculations.

It was only when I tried re-creating the query subject that I figured out the cause for this as being a difference in the case on the table name in the Query Subject and in the Schema. The Query Subject had the table name as "TABLE" while in the schema it was "table".

Once I modified the data source query subject to point to "table" as in the schema, there were no metadata callbacks.
 

Using Parameter Maps to implement Data Masking

Here's a real world example of using Parameter Maps to implement data masking as well as disadvantages of the below solution.

Requirement: Data is stored in 2 different schemas with the same table names. One schema contains Secured Data and the other Unsecured or masked data.

Users have access to either Secured Data or Unsecured Data. This information is stored in a security table. Users need to be dynamically pointed to either of the schemas depending on their security level clearance.

Solution:

Step 1:The security table contains the following columns - UsrID, HasSecurityClearanceFlag.

Step 2:In FM create a data source query Subject called SecurityLevel based on the above table.

Step 3:Create a Parameter map called Usr_Access based on the above Query Subject with key set to UsrID and value set to HasSecurityClearanceFlag.

Step 4:Create a model Query Subject called SchemaName with columns - UsrID, HasSecurityClearanceFlag, Schema.

Schema -

case #$Usr_Access{$account.defaultName}# when 1 then 'Schema1'
when 2 then 'Schema2'
else 'DefaultSchema' end

Step 5:Create another Parameter Map called Schema based on the above Model Query Subject with key set to UsrID and value set to Schema.

Step 6:In Query Subjects that need to be dynamically re-pointed to the schemas modify the SQL as below:

Select * from #$Schema{$account.defaultName}#.TableName

Disadvantages:

Modifying the Data Source Query Subjects results in Metadata Callbacks and this is a cause of concern.

I am still trying to find a viable solution wherein there are no callbacks and yet we can achieve the above security requirement.

Any suggestions?
 

Generated Native SQL vs Tools Native SQL

Cognos doesnt always run the Native SQL that you see from the Tools / Generated SQL/MDX Option. There are scenarios wherein it runs the Native SQL that you see from the Query Level Generated SQL options.

I have a report with some columns and count(distincts). The report also includes a footer that should display count(distincts ) at the footer level rather than the sum.

The Native SQL from Tools/Generated SQL/MDX generates a SQL:

Sel T1.C1 attribute_1, T1.C2 attribute_2,
T1.C3 Qty_A, T0.C3 Qty_A_Footer
from ()T0, ()T1
where....

and the Native SQL from the Query Level Generated SQL options does not include the footer level calculation. Hence the SQL would look like:

Sel attrib_1, attrib_2, count(distinct fact_1) from....

Now I would expect Cognos to fire the Native SQL from the Tools/Generated SQL/MDX options but that is not the case.

Cognos fires the Native SQL from the Query Level Generated SQL. The reason in this case was due to some fields that required Cognos functions. Once I changed the fields to do a DB only processing and included DB only functions Cognos started firing the Native SQL from the Tools option. Not sure if this is a scenario with Teradata only or if this is how Cognos is intended to behave.

Planning on doing some more research on this. Will update when I do so.

Tip: To be on a safer side always verify both the Native SQLs to ensure that both the SQLs are what you would expect to be fired against the DB.
 

Bursting Charts / Crosstabs

Note: Charts / Crosstabs cannot be bursted using a single query as Burst query, Burst Recipient Query and Crosstab query.

You would need to create a Master Query and use that as Burst query and Burst Recipient query. Create a master detail relationship with the Chart / Crosstab query.
 

Teradata - Pointers

When using Teradata, ensure that you thoroughly check the native SQL as well as have a look at the query that Cognos fires against the back-end.

As I have come across situations wherein the Native SQL looks weird cause there are some functions that Cognos doesn't seem to pass to Teradata even though the function used is valid in Teradata.

For example : sel current_time works in Teradata but when used in Cognos this doesn't get passed to the DB. This results in Native SQLs that may not be what is expected.

In one other case I had a master-detail relationship for burst reasons and the master and detail queries were joined on ID columns and a date column. In the query that Cognos fired against the DB I expected to see the detail queries having a filter on the ID columns and the date column. But that was not the case. The join on date column went completely missing from the detail query. This was being handled by the Cognos server rather than the DB.

So to figure out why this was happening, I set the processing to database only on all queries and validated the report.

This threw "requires local processing.." error because of some Cast. The report doesn't have any casted fields. Then I removed the join on Date field in the master-detail relationship. This then validated fine. Next step, I included a cast on date on both sides of the relationship, re-included the relationship and then validated. The report validated perfectly. I bursted the report and then noticed that the detail query included the required date filter.

Here's an advice in case you are using Teradata as your back-end or even otherwise as well. Build your report queries without using any Cognos functions to begin with. Set all queries to database only and then validate the report. After this if required then use Cognos functions. This way you are assured that whatever you expect to get passed to the DB gets passed. So you don't have any surprises like I did.