The lineage function is a great tool for users to understand how items are calculated. Business users can use it to fully understand what they’re seeing. The downside of the lineage is that users can fully understand what they’re seeing.
To the best of my knowledge there is no way of telling Cognos to obfuscate a step in the lineage path. This can cause problems if, for example, you use Cognos functions to decrypt a numeric value.
Case in point: an unnamed large company has a field called “Encrypted Data”. The users need to be able to build adhoc queries with the data, but they are not allowed, under any circumstances, to know the decryption formula. A custom database function is out, so it can only be done in Cognos. The users have a tendency to use lineage to double check numbers.
As you can see, lineage leaves a gaping hole in security. Fortunately there is a solution.
The first step is to create a calculation in FM that decrypts the field.
In the properties of the calculation set Is Hidden to true.
Next create a parameter map. Call it Decrypter.
Give it a key. Since the example I’m using is based on the orders table, I gave it the key Order. In the value give it the path of the calculation.
Now for the last step. In the business layer instead of calling the calculation directly you’ll call the parameter map with ‘Order’ as the key.
Now when the user tries to peek in at the lineage he sees this:
Instead of seeing the super secret formula, he only sees the call to the param map. If he doesn’t have access to FM, he will never be able to see the formula now.
Is it possible to omit a query item in a crosstab from tree prompt which is applied to the query
I have sales, cancellations and net gains, over a 5 year period, by month, but also need a cumulative total of net gains to show closing customers
I have a period tree prompt but if i select a period the running total starts with first date selected,
I need net gains to start with the selection, but cumnet gains to start from day 1 and end with the tree prompt selection
Thanks Marc
Hi Marc,
What you’re asking for should be possible, depending on your query and how your crosstab is set up.
I’m imagining your crosstab has Months on the rows, and the measures in the columns.
Is this a purely dimensional source, or is it DMR? Are you using detail filters and the running-total function, or are you using something along the lines of total(currentMeasure within set periodsToDate(currentMember(),[Level]]))?
Hi
Purely dimensional
Months as Columns
P&L items as Rows eg Sales, Cancellations Net Gains
Default measure in the crosstab is QTY
Then have a running total on QTY
and a Tuple on running total QTY and Net Gains to give me cumulative customers
Cheers Marc
You’re using the running total function?
Yes
Sorry if not clear
Then have a running total on QTY
and a Tuple on running total QTY and Net Gains to give me cumulative customers
To begin, running totals may not work as expected on a multi-dimensional source. I strongly recommend switching to the method I detailed previously here.
With this method you could do something like:
total([cube].[Measures].[NetGain] within set periodsToDate([AllLevel],currentMember([TimeHier])))
For each month it will return the total from the beginning of the time dim to that month. You can then subtract the previous years by adding – total([NetGain] within set except([YearLevel],[5YearSet]))
total([cube].[Measures].[NetGain] within set periodsToDate([AllLevel],currentMember([TimeHier]))) – total([NetGain] within set except([YearLevel],[5YearSet]))
But you also said that you want the running total to only go up to the date selected. Are the users likely to select individual days, or just up to the month? If it’s just to the month you might be able to do something like:
total([cube].[Measures].[NetGain] within set intersect(periodsToDate([AllLevel],currentMember([TimeHier])),periodsToDate([AllLevel],[SelectedMonth]))) – total([NetGain] within set except([YearLevel],[5YearSet]))
The intersect will ensure that only months before the selected date will be used for the total.
I tried this, it works fine in FM but in QS after publishing the package I get a message about a 2 part reference error which is a a depricated feature. We are running Cognos 8.4.
I got this working ok, of course you must tick the calculation for inclusion in the published package.
That makes sense – I was going slightly crazy trying to think of a reason it wouldn’t work.