Questions on looping and grouping data

Question

Questions on looping and grouping data

mlbrow5

Valued Member

Points: 57
More actions
January 21, 2009 at 8:28 am

#94755

I am having a problem creating reports using report server and am not sure what SQL statements are need to loop through the data then group them correctly. Use the below sample table for reference:
Sample Table
KeyNum Strategy Strategy Cost
1 Do This Cost $0
1 Do That Cost $5
1 Do Something Cost $10
2 Do Something Else Cost $50
3 Do Testing Cost $5
3 Do Implementation Cost $0
I need to have the report display each strategy individually for the key number given and its corresponding info. Example of needed report if the Key Num= 1:
Strategy Cost
Do This Cost $0
Do That Cost $5
Do Something Cost $10
Total : $15
Instead I'm getting only the first line of yet I'm getting the correct total cost. Example:
Strategy Info
Do This Cost $0
Total: $15
Any help would be greatly appreciated. Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

What SQL statement do you have so far? From the requirements, it looks like it should just be a Select with an input parameter used in the Where clause.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mlbrow5 Valued Member Points: 57 More actions · Answer 2

Here is the SQL I have right now and obviously there is a lot going on other than just this but from my example I think you should get the idea of which part I'm having trouble with...

SELECT DISTINCT cg.cText, ug.sText AS uObj, ug.sGUID AS uObjGUID, ug1.sText AS uGoal, ug.sObjNum,

tblStrategy.sStrategyPrNumber, tblStrategy.sStrategy, tblStrategy.sResponsible, tblStrategy.sCompDate, tblStrategy.sFType,

tblPersonnel.sPosDescription, tblPersonnel.sType, tblPersonnel.sRegTemp, tblPersonnel.sEstSalary, tblPersonnel.sFSource,

tblEquipmentNeeded.sItemDescription, tblEquipmentNeeded.sFNeeded, tblEquipmentNeeded.sFSource AS EquipmentNeeded_FSource,

tblOtherCosts.sItemDescription AS OtherCosts_ItemDescription, tblOtherCosts.sFNeeded AS OtherCosts_FNeeded,

tblOtherCosts.sFSource AS OtherCosts_FSource, tblUnit.sName, tblYear.sYear, ug1.sUnitGUID, ug1.sObjectiveNum

FROM tblCGoal AS cg INNER JOIN

tblUnitGoals AS ug ON cg.sCGUID = ug.sCGoalGUID INNER JOIN

tblUnitGoals AS ug1 ON ug1.sGUID = ug.sPGUID INNER JOIN

tblUnit ON ug.sUnitGUID = tblUnit.sGUID INNER JOIN

tblYear ON ug.sYearGUID = tblYear.sGUID LEFT OUTER JOIN

tblOtherCosts ON ug.sGUID = tblOtherCosts.sGoalGUID LEFT OUTER JOIN

tblPersonnel ON ug.sGUID = tblPersonnel.sGoalGUID LEFT OUTER JOIN

tblEquipmentNeeded ON ug.sGUID = tblEquipmentNeeded.sGoalGUID LEFT OUTER JOIN

tblStrategy ON ug.sGUID = tblStrategy.sGoalGUID

WHERE (ug1.sYearGUID = '########-####-####-####-########')

GROUP BY cg.sCText, ug.sText, ug.sGUID, ug1.sText, ug.sObjectiveNum, tblStrategy.sStrategyPNumber, tblStrategy.sStrategy, tblStrategy.sResponsible,

tblStrategy.sCompDate, tblStrategy.sFType, tblPersonnel.sPosDescription, tblPersonnel.sType, tblPersonnel.sRegTemp,

tblPersonnel.sEstSalary, tblPersonnel.sFSource, tblEquipmentNeeded.sItemDescription, tblEquipmentNeeded.sFundsNeeded,

tblEquipmentNeeded.sFundSource, tblOtherCosts.sItemDescription, tblOtherCosts.sFNeeded, tblOtherCosts.sFSource, tblUnit.sName,

tblYear.sYear, ug1.sUnitGUID, ug1.sObjectiveNum

ORDER BY ug1.sUnitGUID, ug.sGUID, ug1.sObjectiveNum

GSquared SSC Guru Points: 260824 More actions · Answer 3

The only thing I can think of on that one is that perhaps the Where clause is causing a problem.

Also, I don't recommend using both Group By and Distinct in the same query. Probably doesn't matter, but you could get rid of the Distinct command.

To test that query and see what it's doing, I'd need table structures and sample data.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 4

GROUP BY ..... WITH ROLLUP

Only thing is, it's gonna be a pain to add things that are a mix of datatypes like "Cost $5%'.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mlbrow5 Valued Member Points: 57 More actions · Answer 5

What would I add just WITH rollup to the end of the GROUP BY statement. Becuase I am getting the error that WITH rollup limits to 10 expressions in the group by clause which I have more than easily....

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 6

You're grouping by more than 10 columns? Sorry, that's not what I saw in the original post and haven't read the other chunk of code you posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 7

You could try the old COMPUTE and COMPUTE BY clauses... don't know if they have the same limits... but, be advised, the totals are NOT returned in the same result set. They are returned as separate result sets.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)