1 "C_____CubeDetailsItem" ??? 1

[blom0344]

Anyone having a clue what this object is supposed to be?

I am just fetching 2 objects from within Report Studio (identifier and a fact) and the native SQL shows this additional object.

From the SQL it looks like it fetches an additional constant 1 , that is aliased as

C_____CubeDetailsItem

Ties Blom

 

[mfgf]

Hi,

Can you tell us what the Cognos SQL looks like? Does it contain syntax anything like the following:

RSUM(1 for [Item Name] Order By [Item Name] asc local) as SC1

It's difficult to pinpoint what's going on in your report unless we know a little more...

Regards,

MF.

 

[blom0344]

select 
       XFW_ZPAT_PATIENT.NR_PAT  as  NR_PAT,
       XSUM(XFW_ZPAT_PATIENT.SEQ_ZPAT_PATIENT  for XFW_ZPAT_PATIENT.NR_PAT )  as  SEQ_ZPAT_PATIENT
 from 
       TWIN..SA_TWIN.XFW_ZPAT_PATIENT XFW_ZPAT_PATIENT
 group by 
       XFW_ZPAT_PATIENT.NR_PAT

This is without a grouping on NR_PAT

select 
       XFW_ZPAT_PATIENT.NR_PAT  as  NR_PAT,
       XSUM(XFW_ZPAT_PATIENT.SEQ_ZPAT_PATIENT  for XFW_ZPAT_PATIENT.NR_PAT )  as  SEQ_ZPAT_PATIENT,
       [COLOR=red]1  as  C_____CubeDetailsItem
[/color] from 
       TWIN..SA_TWIN.XFW_ZPAT_PATIENT XFW_ZPAT_PATIENT
 group by 
       XFW_ZPAT_PATIENT.NR_PAT,
[COLOR=red]       1
 order by 
       NR_PAT asc [/color]

The second SQL is when I apply a grouping on nr_pat.

Ties Blom

 

[mfgf]

Oh OK - I see what's going on.

The report is automatically grouping on NR_PAT and summarizing SEQ_ZPAT_PATIENT (as defined in the properties of the query).

Where you add your own manual grouping on NR_PAT, you are asking the report to do the same work twice. The query engine is trying its best to generate legal syntax based on what you are asking it to do. The group-by clause may not be legal if you were to add the same item twice (ie group by NR_PAT, NR_PAT) so instead the query engine is substituting a derived value of 1 to group on instead, giving you a legal group by clause (group by NR_PAT,1)

My advice is don't manually group on an item that's already being grouped by the query of your report - it's duplicating work and making your queries unneccesarily complex.

Best regards,

MF.

 

[blom0344]

Okay,

Another interpretation would be that in case there is only one column to the left of the facts, then do not apply a grouping in report studio?

When I insert pat_nr twice and then add a grouping on the first pat_nr (I know , pretty senseless) then Cognos does not create a pseudo-column.

Sorry for these rather basic questions, but my brain-cells still need re-alignement after 10 years with BO

Ties Blom

 

[mfgf]

Hi,

In answer to your first question above - yes exactly (unless you decide to turn off the default grouping and aggregation performed by the query, in which case you could replace it with your own manual grouping).

Inserting pat_nr twice into the report will only add it once to the query as pat_nr - the second version will be called pat_nr1, and these two will be treated as entirely separate items.

What causes the issue you are seeing is grouping manually on a column which is already being grouped via the default grouping and aggregation in the query of the report (usually the last non-measure item).

Don't worry about asking questions - we're all here to help. I remember it was difficult for me when I had to get my head around ReportNet after years of using Impromptu, so I fully understand how you must be feeling as you switch your skillset from BO to Cognos.

Best regards,

MF.

 

[blom0344]

Okay, I was wondering,are there any subtle changes/functionality between report studio versions that come with reportnet and Cognos8?

We are currently trying to improve on our Cognos8 skills, and I do see some subtle differences between Cognos connection of Reportnet and Cognos8.

Any way, there are going to be a lot of questions posted on this forum..

Thanks..



Ties Blom

 

[mfgf]

There are a few nice changes between C8 Report Studio and ReportNet Report Studio, but nothing that's too difficult to get to grips with. Crosstabs are much more powerful (stacking of sets is now supported), the structure of the query/tabular model from ReportNet has been simplified, drillthrough is available from charts, maps and gauges have been added, layout component references can refer to objects defined in other reports, OLAP sources can be used to report off, etc etc.

Thanks for another star, by the way.

MF.