Dynamic Groups?

[hanchilicious]

Hi there,

I have an XI report on SQL Server, querying a rolling years worth of data. I'm really only interested in this calendar year for about 95% of the report, but there are two or three columns where I have to work out year on year growth and things like that, which require doing calculations on a larger subset of data than I'm actually displaying.

The report is kinda like this:

NEW YORK
Seller	Q1	Q2	Q3	Q4	LYQ2	LY%
------	--	--	--	--	----	---
POW	12	24	24	24	12	200	
BAM	8	14	20	26	14	100
SHAZAM	6	6	8	8	-	-

The issue I have is that in the group tree index, I have the nodes POW, BAM, and SHAZAM, which is great, but I also have KABLOOIE. KABLOOIE shouldn't be in this group because it operates from LONDON these days. But the reason it's in the tree is because last year it was based in NY.

What I need to do is only group by guys who have been operating from a region THIS YEAR only.

Other than writing a view or SP, which I am not authorised to do, is there a way to achieve this in the front end?

Thanks!

 

[synapsevampire]

Okey dokey smokey.

KABLOOIE is a value in a field, so filterout those entries if you don't want them:

Or if you need KABLOOIE to do these year on year growth and things, then create a formula togroup on instead of the current field:

if {table.field} ="KABLOOIE" then
""
else
{table.field}

This will just providea blank one, oryoucouldeven toss it in with oneof the other valid groups

if {table.field} ="KABLOOIE" then
"POW"
else
{table.field}

If you're using the value in the report, you can't really expect to tell Crystal except don't use this value when grouping, but use it elsewhere, you can only redefine it.

-k

 

[hanchilicious]

Hey man,

Cheers for the response. What I've done here is oversimplify. See, there's about 26,000 kapows and kablooies and whatnots in the database, so hardcoding exclusions is not the answer.

And besides, I don't want to NOT report on KABLOOIE. In the example, I just want KABLOOIE to only appear in it's present region. In the example above, it appears in NY's group tree but also in LONDON's, due to the change of region.

I guess, working along the lines of your suggestion, the logical application would be something like:

if year({db.date}) = year({currentdate})
then {customer}
else ''

which does, kinda, solve the problem for the group tree. But then the summary for all last year calculations are off, because KABLOOIE's value for last year is not included in the NY summary in the LY columns...

 

[bmarks]

I haven't worked it all out as I would normally do this sort of thing with a sproc, but you say you don't have the authority to do so. So instead of that, use "Add Command to Report" and create a SQL query (not sure if you are using that for data retrieval now or if you are using the gui/wizard):


- use a subquery in the one you are using now to add the "current city" as a new field

something like:
select table.customer, (select city from table1 where year(table.date) = year(currentdate) and table1.customer = table.customer) as currentcity, ....rest of your current query.

- you can then group on this new current city field.

 

[bmarks]

I've got that a little wrong above, in my haste I combined my table aliasing into the table name. Also, below I give an alternative solution.

The one from above should be:

select t1.customer, (select t2.city from table as t2 where year(t2.date) = year(currentdate) and t2.customer = t1.customer) as currentcity,
....rest of your current query, including aliasing table as t1.

I've been working with Interbase lately, so I'm not sure if my solution above is the most efficient for SQL Server, or if a self-join would be better. You could also try:

select t1.customer, t2.city,...rest of your fields...
from table as t1
inner join table as t2 on t1.customer = t2.customer
and year(t2.date) = year(currentdate)
...rest of your query...

This will return the city they are in now.

(I know the syntax isn't quite right, but this gives you an idea of how to do it, you can also move the "and" portion of the inner join to the where clause - which, depending on your point of view, is the better spot for it)