Facing issue with the data in crosstab, need help using Cognos 10.2.2

[xyz]

Hi All,

We have issue with data in one of the crosstab report in Cognos 10.2.2 and database is oracle.

The issue is some thing like this, we have Demand Plan and Demand Plan Scheduled two query subjects in FM these are identical tables just that they have join between Time Horizon time dimension based on (Demand Plan Need Date - Time Horizon Day) columns with cardinality 1-n to 1-1, similarly Demand Plan Scheduled query subject have join with Time Horizon time dimension based on (Demand Plan Scheduled Scheduled Date - Time Horizon Day) column with cardinality 1-n to 1-1. We have Dmnd Pln Met Late measure in Demand Plan Query Subject and Dmnd Pln Schd Met Late measure in Demand Plan Scheduled Query Subject. If I create a crosstab individually data is showing correct in cross tab, if I use the two query subject measures in the same crosstab report then I am not getting data for Demand Plan Scheduled query subject.

What I tried is, I tried to do union at report level so that I can get both measure values from both the query subjects (Demand Plan and Demand Plan Scheduled), but as it has time dimension union is not supporting.

I have attached the actual and expected output of crosstab report for reference.

Query Subject Demand Plan and Demand Plan Scheduled are joined through Time horizon time dimension based on different dates.

Can you guys please let me know, where I am doing wrong, what is the approach needs to be taken in this scenario.

Thanks in advance.

Thanks & Regards,
XYZ

[MFGF]

Hi All,

We have issue with data in one of the crosstab report in Cognos 10.2.2 and database is oracle.

The issue is some thing like this, we have Demand Plan and Demand Plan Scheduled two query subjects in FM these are identical tables just that they have join between Time Horizon time dimension based on (Demand Plan Need Date - Time Horizon Day) columns with cardinality 1-n to 1-1, similarly Demand Plan Scheduled query subject have join with Time Horizon time dimension based on (Demand Plan Scheduled Scheduled Date - Time Horizon Day) column with cardinality 1-n to 1-1. We have Dmnd Pln Met Late measure in Demand Plan Query Subject and Dmnd Pln Schd Met Late measure in Demand Plan Scheduled Query Subject. If I create a crosstab individually data is showing correct in cross tab, if I use the two query subject measures in the same crosstab report then I am not getting data for Demand Plan Scheduled query subject.

What I tried is, I tried to do union at report level so that I can get both measure values from both the query subjects (Demand Plan and Demand Plan Scheduled), but as it has time dimension union is not supporting.

I have attached the actual and expected output of crosstab report for reference.

Query Subject Demand Plan and Demand Plan Scheduled are joined through Time horizon time dimension based on different dates.

Can you guys please let me know, where I am doing wrong, what is the approach needs to be taken in this scenario.

Thanks in advance.

Thanks & Regards,
XYZ

Hi,

Can you confirm that Time Horizon Day is at the 1 end of each relationship and the Demand Plan and Demand Plan Scheduled query subjects are at the n end of the relationships?

Can you show us the Cognos SQL from the original report that does not display values in the crosstab (ie the simple crosstab from one query, not where you have tried to do a union)

Cheers!

MF.

[xyz]

Thank you for the reply, MFGF.

Can you please find the attached screenshot of the model. Please note that in the crosstab report I will use only one measure i.e Met Late. As we have Met Late measure present in both Demand Plan and Demand Plan Scheduled fact tables. We wanted to display a single Met Late measure value, which should display data for the both different dates. So that the crosstab should show the values as attached in the previous screen shot.

As requested, I will attach the sql in the next email.

Thanks in advance.

Thanks & Regards,
XYZ

[xyz]

Hi MFGF,

Can you please find the attached sql in this email.


Thanks & Regards,
XYZ

[MFGF]

Hi MFGF,

Can you please find the attached sql in this email.


Thanks & Regards,
XYZ

Hi,

That SQL has a UNION ALL in it? It looks like you're doing a union with multiple queries in your report. Can you try just building a normal report and dragging the items into your crosstab direct from the package, then report on the result you are getting?

With a common dimension and two facts, I'd expect to see a stitch query with a FULL OUTER JOIN in it - that's not what the SQL you posted is doing...

Cheers!

MF.

[xyz]

Hi MFGF,

You are correct, we have Data source query subject in FM which has union, the Cognos SQL i provided in my previous email didn't show the full outer join because I didn't pull the item from the second fact in the same query, only I pulled data items from single data source query subject, that's why we didn't see the full outer join. Here after pulling the data items from the second data source query subject, I can see full outer join. Here I am attaching the sql.

In such scenario, If I try to pull the data items from Demand Plan Scheduled data source query subject into crosstab, I am not getting the data in the crosstab. Can you please let me know, how to achieve my requirement of having embed the different measures in the single crosstab from two different query subjects, but still get the values?

Thanks in advance.

Thanks & Regards,
XYZ

[xyz]

Hi All,

As of now the issue, I am facing is, I am getting the correct data in tabular view, where the rows from Demand Plan and Demand Plan Scheduled tables are not coming in the same rows, which is expected but when I plot the measure in the crosstab, I am not getting the data as expected.

Can you guys please let me know, what approach I need to follow to combine the measures from two different fact query subjec ts to be used in the single crosstab?
I know the problem is with presentation layer. I tried options of adding the data items in the properties of a crosstab rows or columns, added the schedule date as column for Met Late measure, still didn't get the value for Met Late in the crosstab.

Thanks in advance.

Thanks & Regards,
XYZ