Question regarding Nesting ReportNet Tabular models

[MacLeod72]

Hello All-

I am new to Cognos ReportNet and am having an issue with creating a tabular model. Essentially, I need to create 2 sub-queries and join the results. In SQL, I would create a query like:

SELECT
    T1.KEY_VAL,
    T1.F1,
    T2.F2
FROM
    (
    SELECT
        KEY_VAL,
        SUM (F1) F1
    FROM
        X
    GROUP BY
        KEY_VAL
    ) T1
INNER JOIN
    (
    SELECT
        KEY_VAL,
        AVG (F2) F2
    FROM
        Y
    GROUP BY
        KEY_VAL
    ) T2
ON
    T1.KEY_VAL = T2.KEY_VAL

I tried to accomplish this in ReportNet by creating a tabular model (TAB) that contained 2 other tabular models (T1 and T2). I created T1 and T2 to do the appropriate summarization, and put a filter on TAB [T1].KEY_VAL = [T2].KEY_VAL.

This creates a cross-product between the T1 and T2 which I assume will be filtered after the cross-product is created. I think that this process will be very slow, and I am trying to avoid using Tabular SQL to perform this.

Is there a way to make ReportNet perform sub-queries without cross-products?

Thanks!

--Rob

 

[drseuss]

It seems like you're trying to do this in a Report Studio report. If you have access to Framework Manager, you should try creating Query Subjects based on the 2 subqueries--the good thing about Framework Manager is that you can type in SQL. You can then specify the join between the 2 Query Subjects. You can also specify how aggregation is done in your FM model. I hope this helps.

 

[Cogno]

Without access to Framework you are better off using Tabular SQL. It is the only way that I have found to be able to control your data properly. I too am new to Cognos and have found that tabular SQL is the only way to get away from Cogno's inherit action of = joins when letting it creating the links between tables.

Best of luck to you.