Tag: cognos 11

Uncategorized

Nice looking tooltips without JavaScript!

| cognospaul

One of the things I’m known for is super complicated JavaScript solutions for the most mundane tasks. But I have heard some complaints by people who want to keep things a little simpler and easier to maintain. So while I have written about custom tooltips before, let’s focus on one that requires no JavaScript at all. This is a pure CSS solution that I shamelessly stole from here: https://codepen.io/chocochip/pen/zYxMgRG

The trick here is it’s all CSS, so in order for it to work we do need to add an HTML item to hold the STYLE node. 

<style>
div[lid*=wrapper] {
  position: relative;
  display: inline-block;
}
div[lid*=content]{
  opacity: 0;
  visibility: hidden;
  position: absolute;
  left: -10px;
  transform: translate(0, 10px);
  background-color: #bfbfbf;
  padding: 1.5rem;
  box-shadow: 0 2px 5px 0 rgba(0, 0, 0, 0.26);
  width: auto;
}
div[lid*=wrapper]:hover div[lid*=content] {
  z-index: 10;
  opacity: 1;
  visibility: visible;
  transform: translate(0, -20px);
  transition: all 0.5s cubic-bezier(0.75, -0.02, 0.2, 0.97);
}

div[lid*=wrapper]:hover div[lid*=content] * {
  user-select: text !important; 
  -moz-user-select: text !important;    
  -webkit-user-select: text !important;
  -ms-user-select: text !important;
}


</style>

There are a few interesting things to note here. I’m using the css selector lid*=wrapper, this will match any blocks with the name starting with “wrapper”, so multiple blocks named wrapper, wrapper1, wrapperbig, will all be affected by the style. The last bit is to allow the contents of the popover to be highlighted and copied.

“But how can you use this”, I hear you ask. It’s actually quite easy. First a caveat, this doesn’t work with points on a graph. I’m looking for a solution for graphs, but we might have to rely on JS instead of CSS only solution. The way this specific solution works is to have the structure look like this:

wrapperBlock (in yellow)
Item (this is what the user sees and hovers over. In this case I’m using a text item)
contentBlock (in teal)
This is what appears in the popup tooltip. (I’m pretty sure this can be almost anything except a c11 graph)

Let’s take a look at it in action:

A couple things to note, the tooltip doesn’t instantly appear when you move your cursor over the items, only when you pause on one. We can also see that the user-select does work, and we can now select the text in the popover.

Take a look at the attached report below:
csstooltipreport.txt (852 downloads)

Macros

Quickie: How to use the new macro function queryValue

| cognospaul

A very pleasant surprise when building reports. There’s a new function called “queryValue”.

On the surface it looks fairly innocuous, but the result of this is a lot of effort saved when building reports. There are many times where I want a way of pulling a default value from a database in a filter. For example, my report might need to let the user select a year, but last date with data is unknown. Normally we can do something like:

[Sales (query)].[Time].[Year] = #prompt('Year','integer',
'maximum([Sales (query)].[Time].[Year] for report)'
)#

WITH 
"TQ0_q_SelectedMonth1" AS 
    (
    SELECT
        "GO_TIME_DIM"."CURRENT_YEAR" AS "Year0", 
        MAX("GO_TIME_DIM"."CURRENT_YEAR")
            OVER(
            ) AS "Max1"
    FROM
        "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM"
    )
SELECT
    "TQ0_q_SelectedMonth1"."Year0" AS "Year0"
FROM
    "TQ0_q_SelectedMonth1" 
WHERE 
    "TQ0_q_SelectedMonth1"."Year0" = "TQ0_q_SelectedMonth1"."Max1" 
GROUP BY 
    "TQ0_q_SelectedMonth1"."Year0"

It works, but I don’t like it. It’s creating a window function, and actually hits every row in that table. One way around would be to create a parameter map in the framework model. Create a model query that has the value you want, add a data item with a static 1, and create a parameter map based on that. You could then do:
[Sales (query)].[Time].[Year] = #prompt('Year','integer',
sq($lastYearWithData{1})
)#

Then the SQL will look like: 

SELECT
    "GO_TIME_DIM"."CURRENT_YEAR" AS "Year0"
FROM
    "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM" 
WHERE 
    "GO_TIME_DIM"."CURRENT_YEAR" = 2013 
GROUP BY 
    "GO_TIME_DIM"."CURRENT_YEAR"

This is much better but still way too clunky. It requires access to the framework model, and a new parameter map for each and every scenario that we need. What if we want the last date of data for a specific product line? Or product?

Instead, let’s take a look at queryValue()

[Fact Business].[Dates].[Year] = #prompt('Year','integer'
, queryValue('maximum([Sales (query)].[Time].[Year])')
)#

This behaves exactly like the parameter map, and the SQL is identical. Cognos is actually making a query that runs maximum([Year]), and returns that value to the macro – exactly like a parameter map would. We can add filters to the query like this:
[Sales (query)].[Time].[Year] = #prompt('Year','integer',
queryValue('maximum([Sales (query)].[Time].[Year])','[Sales (query)].[Sales].[Quantity]>0 and [Sales (query)].[Products].[Product]=''Trail Star''')
)#

Notice using a double quote to escape the single quote. Great Outdoors stopped selling Trail Star in 2011, and 2011 is now the default value in the prompt.

This is also especially useful in Data Sets and Data Modules, where parameter maps simply don’t exist.