Some SQL help - SSRS

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,829
Reaction score
3,033
Location
On the toilet
Hi all,

So below is an extract of my result set. I want to sum the attendees but only the value from the first row. IE. SUM should give me back 4.
SUM(First(Field!NoOfAttendees.Value)) doesn't work as it's a feature not to allow such in aggregates. Can't change anything in SQL, so this change HAS to be done in SSRS.
Any thoughts would be appreciated and 5 Internets are available to the person who assists :)

[table="width: 500"]
[tr]
[td]Id[/td]
[td]Consultant[/td]
[td]NoOfAttendees[/td]
[td]Area[/td]
[/tr]
[tr]
[td]1[/td]
[td]John[/td]
[td]3[/td]
[td]GP[/td]
[/tr]
[tr]
[td]1[/td]
[td]John[/td]
[td]3[/td]
[td]GP[/td]
[/tr]
[tr]
[td]1[/td]
[td]John[/td]
[td]3[/td]
[td]KZN[/td]
[/tr]
[tr]
[td]2[/td]
[td]Piet[/td]
[td]1[/td]
[td]CPT[/td]
[/tr]
[/table]
 
Last edited:
You count the amount in the table ? Why sum the first column?
 
Doesn't work for some reason. I do get what your alluding too but in SSRS the numbers just don't match up.
 
Pho3nix said:
Doesn't work for some reason. I do get what your alluding too but in SSRS the numbers just don't match up.
Click to expand...

Without actually seeing more and how you've implemented the report, its like pissing in the wind.
 
Can't you do the aggregate outside SSRS (e.g. view) and just call this from SSRS?
 
Unfortunately if you cant change the SQL then it isn't SQL help you need.

There are 4 rows in that result set. You say you want to sum the attendees from the first row, which is 3 ? but then you say you expect 7

If its a result set, then it must be some sort of array, and therefore you should be able to access the first row , by specifying either 0 or 1 depending on how SSRS indexes its array elements.

Sorry, I know squat about SSRS, but all languages have some sort of array and ways of getting at the elements, don't they ?
 
semaphore said:
Without actually seeing more and how you've implemented the report, its like pissing in the wind.
Click to expand...

You made a good point. Took a walk and had a piss..Got it working. Stared at this damn report for too long.
Case statement was needed which has been pushed to the higher ups. We were asked not to touch the stored procs for some reason :o and just get it to work :(

Below is why the numbers weren't matching up.
Pho3nix said:
:p Tried that. Just ran the sql query for this and saw that there were some adhoc attendees so in some instances you find something like.

12, John, 103,GP
13,Jenny,2,KZN
13,Jenny,2,GP
Click to expand...
 
Pho3nix said:
You made a good point. Took a walk and had a piss..Got it working. Stared at this damn report for too long.
Case statement was needed which has been pushed to the higher ups. We were asked not to touch the stored procs for some reason :o and just get it to work :(

Below is why the numbers weren't matching up.
Click to expand...

Cool.

Yeah if you touch the sprocs, you would most likely bring down the entire system. Another reason why sprocs are utter ghei.
 
Thanks for all the help. sephmore,, hope you don't mind me keeping you on speed-dial for such :p:)
 
zippy said:
Unfortunately if you cant change the SQL then it isn't SQL help you need.

There are 4 rows in that result set. You say you want to sum the attendees from the first row, which is 3 ? but then you say you expect 7

If its a result set, then it must be some sort of array, and therefore you should be able to access the first row , by specifying either 0 or 1 depending on how SSRS indexes its array elements.

Sorry, I know squat about SSRS, but all languages have some sort of array and ways of getting at the elements, don't they ?
Click to expand...

SSRS = SQL server reporting service
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X