×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Group by with desc column only

Group by with desc column only

Group by with desc column only

(OP)
Dear all,

I have a table that contains:

cd_razao_tc(PK)
cd_so_razao_tc
desc_razao_tc

In the fact table, I use the cd_razao_tc column to make join and in the attribute, I display cd_so_razao_tc and desc_razao_tc columns.

The Microstrategy is executing the query like below:

select    a11.CD_RAZAO_TC  CD_TC,
    a14.NM_RAZAO_TC  NM_TC,
    a14.CD_SO_RAZAO_TC  CD_SO_RAZAO_TC,
    a12.CD_MES  CD_MES,
    a13.NM_MES  NM_MES,
    sum(a11.QT_ICG_CGB) QUANTIDADEIN
from    dw.TBDWF_ANALISE_CGB a11,
    dw.TBDWD_PERIODO_DIA a12,
    dw.TBDWD_PERIODO_MES a13,
    dw.TBDWR_RAZAO_TC a14
where    a11.CD_DIA_INCOMING = a12.CD_DIA and
    a12.CD_MES = a13.CD_MES and
    a11.CD_RAZAO_TC = a14.CD_RAZAO_TC
 and    a12.CD_MES in (200108, 200110)
group by    a11.CD_RAZAO_TC,
        a14.NM_RAZAO_TC,
        a14.CD_SO_RAZAO_TC,
        a12.CD_MES,
        a13.NM_MES

and the results appears:
        Metrics                       Quantity     
        Month                Oct 2001      Aug 2001
Razão TC                
60    COPIA RECEBIDA ILEGIVEL        0         202
72    SEM AUTORIZACAO                0            202
77    CARTAO INEXISTENTE        4         101
88    CARTAO VENCIDO                3              0
88    CARTAO VENCIDO                102           0

Note that cd_razao_tc is unique,  but when i show the cd_so_razao_tc, display two rows with the same id and the same description. It occurs because in the GROUP BY CLAUSE, the Microstrategy puts cd_razao_tc too.

Someone knows how can i set to Microstrategy puts only cd_so_razao_tc and desc_razao_tc in the group by clause ?

Without cd_razao_tc in the group by clause (and select clause) the results appears like this:

        Metrics                       Quantity     
        Month                Oct 2001      Aug 2001
Razão TC                
60    COPIA RECEBIDA ILEGIVEL        0         202
72    SEM AUTORIZACAO                0            202
77    CARTAO INEXISTENTE        4         101
88    CARTAO VENCIDO               105            0
                

Thanks,

RE: Group by with desc column only

try using cd_razao_tc to be id of attribute A1,
cd_so_razao_tc and desc_razao_tc to be id and desc of attribute A2

make A2 parent of A1.

then add A2 on your report instead of A1.  that should work.

good luck

RE: Group by with desc column only

Since cd_razao_tc is your PK then cd_so_razao_tc is a parent of this attribute. Have you defined that relationship?
See if that will fix your problem.

RE: Group by with desc column only

(OP)
thanks for help.
The first solution works very well.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close