Hello Cognos Community,
I am trying to drill-through on a percentage number in a crosstab but the targeted report doesn't display the right data related to the number.
The crosstab looks like this:
2018/may
Delivery person Correct time 50%
Wrong time 50%
Amount of orderrs 2
So when I drill on amount of orders it will display the right data in the targeted report but if I drill on 50% it should only show one of the orders.
I am passing correct time, wrong time amd amount of orders as a value using define content yes then dragging the data item into the crosstab cell.
The Correct time data item is a calculation looking like this to get the percentage:
[Correct time] / [Amount of orders]
data item correct time query looks like this:
case
when
[Mottagetdatum] >= _add_days([Önskat Leveransdatum],?Tidiga?*-1)
and
[Mottagetdatum] <= _add_days([Önskat Leveransdatum],?Sena?)
then
1
else
0
end
Quote from: oscarca on 30 Aug 2018 03:18:00 AM
Hello Cognos Community,
I am trying to drill-through on a percentage number in a crosstab but the targeted report doesn't display the right data related to the number.
The crosstab looks like this:
Delivery
Hi,
Have you checked to see what values are being passed from the source report to the target report? Are they what you expect? If not, the issue is in the drill-through definition. If they are, the issue is in the target report and the way it is filtering...
Cheers!
MF.
Thank you for answering MFGF.
All the parameters are sent as a member caption. The prompts are being passed as parameters and the data items displayed in the crosstab are passed as data items.
Here is the XML of the main report attached
this is the xml for the targeted report:
it seems that it can't understand the Correct time percentage calculation so when clicking on 50% it should display only one order since the amount of orders are 2 then 50% should be 1. All the parameters are working just that when it comes to the [correct time] /[amount of orders] it only gives me the amount of orders not knowing what the percentage is.
Quote from: oscarca on 30 Aug 2018 03:38:25 AM
Thank you for answering MFGF.
All the parameters are sent as a member caption. The prompts are being passed as parameters and the data items displayed in the crosstab are passed as data items.
Here is the XML of the main report attached
Hi,
You need to determine what is *actually* being passed from the source report to the target report when you drill through. The report spec shows what you think should be passed, but you won't know for sure what is really passed unless you check. My tip is to add layout calculations on the page of the target report - one for each passed parameter, each one using ParamValue('YourParameter') as the syntax.
Cheers!
MF.
All the parameters do come out as I want them using paramdisplay. But I don't know how to filter the target report to pass the right percentage number.
Quote from: oscarca on 30 Aug 2018 04:26:36 AM
All the parameters do come out as I want them using paramdisplay. But I don't know how to filter the target report to pass the right percentage number.
Hi,
Usually a drill-through from a crosstab will pass the row/column headings as parameters rather than the value in the cell. Can you use these row/column heading values in the target report to calculate the same value as in the source report?
MF.
That is why I have placed the data item value inside the crosstab cell then created a drill-through on the data item instead of using the cell value.
if I place "Correct time" data item in the crosstab
"case
when
[Mottagetdatum] >= _add_days([Önskat Leveransdatum],?Tidiga?*-1)
and
[Mottagetdatum] <= _add_days([Önskat Leveransdatum],?Sena?)
then
1
else
0
end"
instead of [Correct time] / [Amount of orders]
it will give me a number for example 1 since there are two orders and "Correct time" is equal to 50 %. So if I drill on the number "1" instead of "50%" it does work. The problem is that we want to see a percentage value not number value.
I have found one alternative solution and that is to create two seperate target report one being called "Drillreport Correct time" where [Wrong time] values are filtered and the second one being called "Drillreport Wrong time" where [Correct time] values are filtered.
But I really don't like this solution cause it requires 2 drill reports instead of just 1...