sql

[sntrao]

hi,

i generate the following sql while executing a report:

1 select a14.PAY_PERIOD_YEAR PAY_PERIOD_YEAR,
2 sum(a11.DEALER_CONCESSION_AMOUNT) WJXBFS1
3 from CPC.CPC_PRODUCT_FACTS a11,
4 CPC.CPC_REPS a12,
5 CPC.CPC_BRANCHES a13,
6 CPC.CPC_PAY_PERIODS a14
7 where a11.REP_CODE = a12.REP_CODE and
8 a12.BRANCH_CODE = a13.BRANCH_CODE and
9 (a11.PAY_PERIOD_END_DATE - 365) = (a14.PAY_PERIOD_END_DATE -
365) and
10 a11.PAY_PERIOD_END_DATE = a14.PAY_PERIOD_END_DATE
11 and a13.REGION_CODE between '0' and '200'
12 group by a14.PAY_PERIOD_YEAR

the report gave me the following result:

PAY_PERIOD_YEAR WJXBFS1
--------------- -------
2000 31927500.24
2001 25914861.68
2002 25439892.64
2003 16063643.27
2004 18771977.88

Now when i remove line 9 from the original sql and execute it against
the warehouse, i get the same result set (pls note that there r 2
leap years in the result set). i think line 9 (or 10) is redundant.
how do i get rid of it in microstrategy? - any vldb properties or
report data options?

thanks
thukaram

 

[entaroadun]

It's not a VLDB setting. Your PAY_PERIOD_END_DATE attribute isn't set up properly. You probably have two forms in your ID attribute form group. The first form is "PAY_PERIOD_END_DATE". The second form is "PAY_PERIOD_END_DATE - 365". Both forms are grouped in the ID form group.

Is this correct?

If so, you need to ungroup the two forms from the group. Make the first form ("PAY_PERIOD_END_DATE") the ID form. You do this by setting the attribute form type to ID. Name the second form something else, and make sure the type is neither ID nor DESC; it should be OTHER.

That should do it.

 

[sntrao]

Hey, thanks. this did solve the problem

Thukaram