Implementing VLOOKUP and COUNTIF using Reporting Services

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

java2architect@gmail.com - 29 Nov 2006 14:27 GMT

Hi!

I was wondering if Reporting Services could be used to implement
VLOOKUP and COUNTIF functions in particular available with MS Excel on
a Table. I would like to venture a guess and say no. Unless there was
someway I could use RunningValue or something to simulate these
implementations.

If I can great. If I can't, what are my options. Doing it in the SQL
Query is driving me nuts. So I'm thinking I'll used a stored proc, dump
the data into a temp table and then do the calculations to fill in the
columns I need VLOOKUP and COUNTIF for. Either way, if someone can
please point me in the right direction or provide alternative/better
solution.

Thanks in advance.

Reply to this Message

Topher - 29 Nov 2006 17:59 GMT

I'm not sure about the VLOOKUP equivalent, but for COUNTIF I generally
use:

SUM(IIF(fields!field1.value = "X", 1, 0))

> Hi!
>
[quoted text clipped - 12 lines]
>
> Thanks in advance.

Reply to this Message

java2architect@gmail.com - 29 Nov 2006 20:50 GMT

Thanks for COUNTIF. Seems obvious now. It will work provided counting
is done over the entire range. What if I wanted to count all "X"s until
current row. I'm thinking there is way to do this, but I'm drawing a
blank. Sorta like a "conditional RunningTotal" ?

Also regarding VLOOKUP, If I may run something else by you. Have been
doing up some reading. Thinking I can perhaps implement my own VLOOKUP
using embedded VB code. I think I have access to ReportItems
collection. I can therefore access values in multiple columns by
passing in their names as parameters. Thing is I would need to pass in
entire columns into my function. Is this possible? If so, then I could
iterate over the column values row by row and give it a shot.

Regards.

> I'm not sure about the VLOOKUP equivalent, but for COUNTIF I generally
> use:
>
> SUM(IIF(fields!field1.value = "X", 1, 0))

Reply to this Message

aaron.kempf@gmail.com - 29 Nov 2006 21:16 GMT

use a subquery; jackass

Excel isn't a database lose the training wheels

-Aaron

> Hi!
>
[quoted text clipped - 12 lines]
>
> Thanks in advance.

Reply to this Message

java2architect@gmail.com - 29 Nov 2006 22:46 GMT

Yo Dumbkoff. If I wanted to do everything in SQL I would not bother to
use Report Services.

One's ignorance is best shown by keeping one's mouth shut. Not spouting
insults.

Loser. Go suck a lemon.

> use a subquery; jackass
>
[quoted text clipped - 18 lines]
> >
> > Thanks in advance.

Reply to this Message

aaron.kempf@gmail.com - 29 Nov 2006 22:50 GMT

what.. you're a java architect

two of the most worthless words EVER

-Aaron

java2archit...@gmail.com wrote:

> Yo Dumbkoff. If I wanted to do everything in SQL I would not bother to
> use Report Services.
[quoted text clipped - 26 lines]
> > >
> > > Thanks in advance.

Reply to this Message