Distinct Sum - How To?

Hello everybody,
My data looks like this:
Program StudentID CreditHours
MBA 1 4
MBA 2 3
MSC 1 4
MSC 3 8
(student id=1 is in both programs)
Then, based on the above data, I need to do a summary report by Program
and grand total. Grand total should NOT count the same student twice.
Program CreditHours
MBA 7
MSC 12
--
Total 15
Again, for the grand total I do NOT want to count the same student
twice.
It looks like what I need is DistinctSum() function, distinguishing by
parameter other than the one being summarized (StunentId in this case).
How do I accomplish this with MS Reporting Services?
I remember I was able to do that with Crystal Reports.
Of course this contrived example is only meant to present the problem.
Please advice,
Tomasz
(I apologize if this post shows twice)Hi Tom,
Welcome to the MSDN newsgroup.
Regarding on the Distinct Sum problem, it is a bit difficult to directly
calculate the distinct results from your given dataset. For the first part
in your report
MBA 7
MSC 12
this can be done through a group on the "program" column. For the grand
total value, do you think if convenient that we put another Dataset on the
report which already queried the distinct data(according to the StudentID
column) from datasource? Thus, we can make our Grand total textbox bind to
that dataset. This is done at datasource/database side.
So far there is not direct support on calculate distinct sum in SSRS,
however, there are some other members use the custom code to calculate such
value, you can have a look at the following article which may give you some
idea on this:
http://72.14.203.104/search?q=cache:sSV0CC-nWpwJ:forums.microsoft.com/MSDN/S
howPost.aspx%3FPostID%3D240606%26SiteID%3D1+SSRS+2005+grouping+distinct+data
+&hl=en&ct=clnk&cd=1
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Well, I tried something similar already.
The problem I had was that SSRS processes the outer groups first, so the
however stored "running sum", even checked against some dictionary of
already added values first, is simply not available when the outer group
total, like grand total, is being rendered.
For the grand total or report total I can add additional textbox, outside
the table, but this actually does not solve my problem since my reports are
grouped on 4 levels and I need distinct sum on levels 3 and 4.
Another question: is it guaranteed that some textbox placed under a table
renders in server memory always after the table renders? I would rather not
relay on such assumptions.
Another method I tried was adding some hidden, calculated field with
function like: =Fields!CreditHours.Value /
CountDistinct(Fields!Program.Value, "Session") and than adding those
adjusted values on appropriate levels but then, again, due to the order SSRS
processes groups (outer first) this did not work.
Is there any way I can extend SSRS functionality and add DistinctSum(value,
id) function? I would rather spent some time doing that than resource to SQL
Server based solutions, which, in may case might not even be an option since
I need distinct sums on mid grouping levels.
Please advice.
Tomasz
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:G$xgn1tUGHA.5016@.TK2MSFTNGXA01.phx.gbl...
> Hi Tom,
> Welcome to the MSDN newsgroup.
> Regarding on the Distinct Sum problem, it is a bit difficult to directly
> calculate the distinct results from your given dataset. For the first part
> in your report
> MBA 7
> MSC 12
> this can be done through a group on the "program" column. For the grand
> total value, do you think if convenient that we put another Dataset on the
> report which already queried the distinct data(according to the StudentID
> column) from datasource? Thus, we can make our Grand total textbox bind to
> that dataset. This is done at datasource/database side.
> So far there is not direct support on calculate distinct sum in SSRS,
> however, there are some other members use the custom code to calculate
> such
> value, you can have a look at the following article which may give you
> some
> idea on this:
> http://72.14.203.104/search?q=cache:sSV0CC-nWpwJ:forums.microsoft.com/MSDN/S
> howPost.aspx%3FPostID%3D240606%26SiteID%3D1+SSRS+2005+grouping+distinct+data
> +&hl=en&ct=clnk&cd=1
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your response Tomasz,
I'm afraid this would be a bit limited here. As far as I know, SSRS is
planning to provider user ability to create own report item(like Image,
List ,Line...), however, so far this is not available in the current
version, no public document on this. If you do want to do some
customization or extension on this, I think you may consider contacting the
MS consulting service since they usually focus on such decent requests.
Anyway, I'm sorry for the inconvenience this brings you.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Is there any way I can access underlying DataSet? I believe SSRS internally
uses DataSet object, does not it?
That would help in solving my problem because I could access it
programmatically and do all the custom summarization myself.
Tomasz
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:spQjOH%23UGHA.1364@.TK2MSFTNGXA01.phx.gbl...
> Thanks for your response Tomasz,
> I'm afraid this would be a bit limited here. As far as I know, SSRS is
> planning to provider user ability to create own report item(like Image,
> List ,Line...), however, so far this is not available in the current
> version, no public document on this. If you do want to do some
> customization or extension on this, I think you may consider contacting
> the
> MS consulting service since they usually focus on such decent requests.
> Anyway, I'm sorry for the inconvenience this brings you.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your followup Tomasz,
I'm afraid so far the reporting service interface dosn't support this since
underlying dataset is not exposed. Actually this limitation also somewhat
related to the particular table used in your case, the certain table which
contains the two columns does not quite conform to the NF rules which make
the common RS items unable to do the work at report level. And yes, I
totally agree with you that this can be done if there has interface which
expose the underlying dataset object, however, so far we still need to wait
for the later update or version.
Thanks for your understanding.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)