Wednesday, March 28, 2012

Grouping on Multi-Value parameter

I have a report that groups on a field, AccountNumber (which is a parameter)
I changed this param to a Multi-Value field, but now, whenever the report
runs, it blows up saying it's an invalid datatype for the grouping. I have
the rest figured out (how to parse the comma separated list) but I'm stumped
on this. Any ideas?
I know that when you change a parameter to multi-select, the value is
represented differently even if it's just one field that's being selected -
well, at least I think I know b/c my logic stopped working. I changed the
stored proc to parse the comma-separated values and all was well. So it's
working fine on every report that doesn't involve grouping. But on this one,
it blows up.
Thanks,
BillU¿ytkownik "W.G. Ryan [eMVP]" <WGRyaneMVP@.discussions.microsoft.com> napisa³
w wiadomo¶ci news:0DDCF3A4-61D8-4519-ADCC-F22C0B883898@.microsoft.com...
>I have a report that groups on a field, AccountNumber (which is a
>parameter)
> I changed this param to a Multi-Value field, but now, whenever the report
> runs, it blows up saying it's an invalid datatype for the grouping. I have
> the rest figured out (how to parse the comma separated list) but I'm
> stumped
> on this. Any ideas?
If your report dataset is based on SQL 2000/2005 you can prepare SQL
statement like this
SELECT * FROM table WHERE id in (@.MultiValueParam)
Regards
Adam|||"Adam Kobylinski" wrote:
> U¿ytkownik "W.G. Ryan [eMVP]" <WGRyaneMVP@.discussions.microsoft.com> napisa³
> w wiadomo¶ci news:0DDCF3A4-61D8-4519-ADCC-F22C0B883898@.microsoft.com...
> >I have a report that groups on a field, AccountNumber (which is a
> >parameter)
> > I changed this param to a Multi-Value field, but now, whenever the report
> > runs, it blows up saying it's an invalid datatype for the grouping. I have
> > the rest figured out (how to parse the comma separated list) but I'm
> > stumped
> > on this. Any ideas?
> If your report dataset is based on SQL 2000/2005 you can prepare SQL
> statement like this
> SELECT * FROM table WHERE id in (@.MultiValueParam)
> Regards
> Adam
>
Thanks Adam. Actually, I got the IN part to work. I couldn't get it to
work using the exact syntax you used, I had to parse the values out but I got
that to work.
The problem though is that the report creaters grouped on a Parameter as
opposed to a field in the dataset. However, when it was changed to a
multi-valued parameter, the grouping blew up.
I got around it by changing it so that it grouped on a report field instead
of the parameter. Any ideas though on how to get it to group on the
parameter if it's multi-valued?

No comments:

Post a Comment