First column displays all the values

[mindNET]

Hello everyone,

First off I'm new to report studio and Cognos itself. I know this has to be easy, and I know why am I getting the result that I'm getting, but I'm unable to figure it out, and after some searching (probably done poorly) I'm coming here for your help.

I have a crosstab report.
Rows: Hospitals
Columns: Year
Measures: miscarriage, miscarriage after procedure

miscarriage after procedure: I used a simple case: CASE WHEN miscarriage_type = 'PRON' Then miscarriages END

When the report has only miscarriage measure it looks like this:

                  Year
                 2016
             Miscarriages
Hospital1    1
Hospital2    26
Hospital3    2

When I add the second measure the results look lik this

                                         Year
                                         2016
            Miscarriages        miscarriage after procedure
Hospital2    6                                      0
Hospital3    2                                      1

So from what I understand the first measure displays all the results where there was miscarriage_type (which was used in the case). Now what I need is a total of all miscarriages regardless if there was a procedure or there wasn't.
I am able to get the result that i desire using a UNION, but I think that there should be a more simple or "elegant" way of accomplishing this task. in the end what I need is:

                                         Year
                                         2016
                Miscarriages    miscarriage after procedure
Hospital2    26                                     0
Hospital3    2                                       1

Any advice is more than welcome :)

Regards,
mindNET

[BigChris]

Could you alter your report so that you have a category field instead?

Code Select Expand

Case
  when miscarriage_type = 'PRON' then 'Procedure'
  else 'Normal'
END

[hespora]

While I don't quite understand why your result currently looks the way it does, the one thing I do know is that your case statement assigns no value if [miscarriage_type] is not equal to 'PRON'. Thus, [miscarriage after procedure] is NULL in those cases, which I've seen cognos treat unexpectedly when performing arithmetic operations. You may want to extend your case statement to

Code Select Expand

CASE WHEN [miscarriage_type] = 'PRON' Then [miscarriages] else 0 END

[mindNET]

As far as I understand when I add [miscarriage after procedure] to report [miscarriages] measure shows only those values where there was some kind of a procedure. Now what I'm looking for is a way to show all the miscarriages regardless of whether there was a procedure or not. As I understand this is how corsstabs work and this should be expected since it takes the intersection of miscarriages, procedure types, years and hospitals for all the measures in the report. The question is, how and if it is possible to overwrite the intersection for the first measure [miscarriages] so it would take the intersection of Years,Hospitals.

[mindNET]

So it seems this is impossible?

[Lynn]

Is this a relational package? Can you look at the SQL being generated when you have only the miscarriage measure vs. both miscarriages and the derived measure that references miscarriage type? I'm wondering if the latter introduces a join to another table that is causing rows to drop out and therefore leaving you with only miscarriages where a procedure was involved.

Ideally, your data warehouse design and framework model would be set up to avoid this (if in fact it is the situation here).

If we can determine whether I'm right or wrong in my guess then we can consider further options.

[mindNET]

Yes, this is a relational package. Once I add the new measure into the query, the sql alters there are some additional joins, but if there is only the [miscarriages] measure in the report it still shows good result. After I add the new measure [miscariages after procedure] to report then the [miscariages] result alters.

So in conclusion:
*SQL alters when the second measure is added to the query, but report shows good numbers if there is only [miscariages] measure in it.[miscarriages] = 40
*Once I add both measure to report it shows:
**[Miscariages] previously the total was 40 (which is ok), after I add the second measure [miscariages after procedure] it shows [miscarriages] = 9 (which is the number of all miscarriages which    had any sort of procedure)
**[Miscarriages after procedure] = 1 which is correct

[Lynn]

If you have the ability to speak with the person responsible for your model then I'd recommend you explain your requirement and confirm that the model, as currently designed, doesn't support this in a manner that is easy for report authors to implement.

Ideally the data warehouse and framework manager design would not allow information to drop out of queries in this way. You didn't actually describe how the SQL is altered so I'm making an assumption here that join drop out is truly the culprit. You should be looking to verify that an additional table is included via an inner join when the miscarriage type is introduced and that rows drop out of the result set as a consequence.

As a workaround in the report, you can create separate queries for the two different metrics and join (or union) together for your end result. In addition to being not terribly end-user friendly to implement, this is not necessarily the most efficient approach since local processing will be required. I would suggest that this is not a slight on the Cognos product, but rather on how it was implemented in your organization.

[mindNET]

If you have the ability to speak with the person responsible for your model then I'd recommend you explain your requirement and confirm that the model, as currently designed, doesn't support this in a manner that is easy for report authors to implement.

Ideally the data warehouse and framework manager design would not allow information to drop out of queries in this way. You didn't actually describe how the SQL is altered so I'm making an assumption here that join drop out is truly the culprit. You should be looking to verify that an additional table is included via an inner join when the miscarriage type is introduced and that rows drop out of the result set as a consequence.

As a workaround in the report, you can create separate queries for the two different metrics and join (or union) together for your end result. In addition to being not terribly end-user friendly to implement, this is not necessarily the most efficient approach since local processing will be required. I would suggest that this is not a slight on the Cognos product, but rather on how it was implemented in your organization.

When I introduce the second measure to the query the SQL inner joins the miscarriage types table.

Yeah, well I've figured this way out, I just union two queries, and I get the desired result although this is not the most efficient way, and I don't really know if it will work on the more complex reports. Well, thank you for the information.