Remove Prompt from group by

[jabs]

Hi all - I am using a SQL query to grab data. The query looks like this:

Code Select Expand


select client,period, SUM(collected_amt) as Collected,  DENSE_RANK() over (ORDER BY  SUM(collected_amt)  desc ) as CollRank
from (
select client, collected_amt as collected, period
from Collected
where period between #prompt('BegPe')# and  #prompt('EndPe')#
) as GetSum
group by client, period

I want to sum the collected amount by a user specified period (YYYYMM). But, when I use the period, even though it is not in report itself, the report returns the amounts with the period grouped.

For example:

client      collected amt       rank
123         500                     1
123         499                      2
456         450                     3
123         400                     4
999         325                     5

This should read:
client      collected amt       rank
123         1399                    1
456           450                    2
999           325                    3

How can I both use the period on the prompt page to allow the user to pick which periods to use, but sum those values at the client level?

Thanks - let me know if you have any questions.
Joe

[Lynn]

Hi all - I am using a SQL query to grab data. The query looks like this:

Code Select Expand


select client,period, SUM(collected_amt) as Collected,  DENSE_RANK() over (ORDER BY  SUM(collected_amt)  desc ) as CollRank
from (
select client, collected_amt as collected, period
from Collected
where period between #prompt('BegPe')# and  #prompt('EndPe')#
) as GetSum
group by client, period

I want to sum the collected amount by a user specified period (YYYYMM). But, when I use the period, even though it is not in report itself, the report returns the amounts with the period grouped.

For example:

client      collected amt       rank
123         500                     1
123         499                      2
456         450                     3
123         400                     4
999         325                     5

This should read:
client      collected amt       rank
123         1399                    1
456           450                    2
999           325                    3

How can I both use the period on the prompt page to allow the user to pick which periods to use, but sum those values at the client level?

Thanks - let me know if you have any questions.
Joe

Using SQL queries is not a best practice to use for modeling or reporting.

Having said that, you can remove the period from the 'group by' clause if you change your sum expression to use an 'over' clause. I don't remember the syntax off hand.

Another option is to remove the period from the 'select' as well as from the 'group by' clause. Presumably if the user will have just selected the period and would already know what it is, so no need for it in the select clause. The report author could display the selected value in the report, if desired, using a prompt macro or parameter display layout calculation.

[jabs]

Thanks Lynn. This ended up being a user error.

By using the prompt within the SQL query, I didn't need to bring the period forward from query result. Like you suggested, I removed it from the select (and group by). The query summed correctly and the prompt displayed as needed.

[bdbits]

Glad you got what you needed, but as Lynn politely note, it is really not a good practice to do hand-coded SQL (or MDX) in Cognos. I would even say it is counter-productive, and I see nothing in your query that could not be handled with a properly modeled package and built-in functions.

I just want to steer you away from the dark side.  ;)