Reporting Services Query

Hi
SQL Reporting Services 2005 is the first reporting tool I have ever
used so I am battling a bit to set up some of the reports I'd like to
create. I'd very much appreciate some help.
Question 1
Take the following simple dataset as an example.
VALUE
100
120
150
500
501
502
520
Is it possible in RS to group the values on ranges? For example, to
generate a report that looks like:
RANGE COUNT
0-100 1
100-150 2
150-500 0
500-520 4
Question 2
Dataset:
NAME MONTH COUNT
A Jan 10
B Aug 88
B Nov 22
Can I get RS to generate the 'missing' months for me and put those
Counts to 0? In other words...for 'A' there is only a count for the
month of January. I'd like the report to show columns from 'Jan' to
'Dec' and to put 0's in the months that are not in the dataset for that
row.
Example result :
NAME JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
A 10 0 0 0 0 0 0 0
0 0 0 0
B 0 0 0 0 0 0 0
88 0 0 22 0
I can generate the 'missing' months in my dataset query but its fairly
expensive.
Kinds regards,
Pieter LessingThe short answer is no to both questions.
Question 1. What you need is called discretization (equal ranges in this
case) which Analysis Services supports but not Reporting Services. So, one
solution to this requirement is to build a light-weight cube on top your
data source and use Reporting Services to report from it. Another approach,
of course, is to implement your own discretization at the data source level.
Question 2. You should be able to do this at the data source, e.g. using a
cursor.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
<pieter.lessing@.gmail.com> wrote in message
news:1134121073.144341.281820@.z14g2000cwz.googlegroups.com...
> Hi
> SQL Reporting Services 2005 is the first reporting tool I have ever
> used so I am battling a bit to set up some of the reports I'd like to
> create. I'd very much appreciate some help.
> Question 1
> Take the following simple dataset as an example.
> VALUE
> 100
> 120
> 150
> 500
> 501
> 502
> 520
> Is it possible in RS to group the values on ranges? For example, to
> generate a report that looks like:
> RANGE COUNT
> 0-100 1
> 100-150 2
> 150-500 0
> 500-520 4
> Question 2
> Dataset:
> NAME MONTH COUNT
> A Jan 10
> B Aug 88
> B Nov 22
> Can I get RS to generate the 'missing' months for me and put those
> Counts to 0? In other words...for 'A' there is only a count for the
> month of January. I'd like the report to show columns from 'Jan' to
> 'Dec' and to put 0's in the months that are not in the dataset for that
> row.
> Example result :
> NAME JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
> A 10 0 0 0 0 0 0 0
> 0 0 0 0
> B 0 0 0 0 0 0 0
> 88 0 0 22 0
> I can generate the 'missing' months in my dataset query but its fairly
> expensive.
> Kinds regards,
> Pieter Lessing
>