impromptu issue

[zhanghua]

hi,guys

i have a sql like this:

select billingcyclid as month,
sum(case when to_char(recstatchgdate,'YYYYMMDD')>
to_char(add_months(to_date (billingcyclid||'25','YYYYMMDD'),1),'YYYYMMDD')
then fee
else 0 end) as mounts
from tab_billdetail
where recstat='10'
AND TO_CHAR(recstatchgdate,'YYYYMMDD')>='20020926'
AND TO_CHAR(recstatchgdate,'YYYYMMDD')<='20021025'
group by billingcyclid

and data from it is like:

month mounts
2002-01 $12232.45
2002-02 $45234
2002-03 $67890
. .
. .
. .
total: $22347.21

in impromptu, i use &quot;sample list&quot; to do it.but the last row
of &quot;total&quot;, i can't generate it.
if use &quot;crosstab&quot;, &quot;input SQL directly&quot; is not allowed.but
the last row of &quot;total&quot; is easy to generate.

In my opinion, i don't want to create view in database or use hotfiles in impromptu. although they can do.


Count anyone can give me a hint?
thank you.

 

[EpicXtreme]

Hi Zhangua,
Just making sure what you are trying to do. You are trying to sum all of the totals in the &quot;mounts&quot; column? If so then just click one of the numbers in that column and then click the sum button on the tool bar, this will add all the numbers in that column and generate a grand total at the last row. Hope this is what you were asking for.

Carm.

 

[griffindm]

Hi Zhangua,

You have not said, but I assume you are on SQL Server, from the use of Case When syntax. If so, you do not have to directly input this SQL into Impromptu. It will automatically parse the If-Then-Else syntax into Case When statements. Then, as Carm indicates above, just sum the results down by highlighting the column and using the Sum button.

If you are in another RDBMS, or just want to speed the query a little bit, you can save the SQL you are using as a SQL view. Then add the view to the Impromptu catalog and use it like a table.

Hope this is helpful,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[zhanghua]

Thank you for Carm. and Griffin

but as you said,&quot;If so then just click one of the numbers in that column and then click the sum button on the tool bar, this will add all the numbers in that column and generate a grand total at the last row&quot;.
i find it's only available when i select something from
table or view. when &quot;Direct SQL&quot; in &quot;sample list&quot;,then &quot;sum&quot; button is gray.
why is it?

waitting for you two.

 

[griffindm]

zhanghua,

As we both have already said, stop doing the direct SQL and let the product do the SQL for you. Direct SQL is very restrictive in what you can do afterwards.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[zhanghua]

Thanks for Griffin.

now i slowly begin to accept your advise.