Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
oriondpd
Starting Member
7 Posts |
 Posted - 2009-05-09 : 21:04:43
|
| I need four numbers; I get the first two with two queries that are joined with a UNION. The 3rd would be the sum of the first two numbers and the 4th a calculated value.Something like:select sum(query1)unionslect sum(query2)This does give me two results.Now - How do I add them together?How do I divide one the first numbers by the new total?This is in SSRS - I know it can be done with SQL - but how do you do this in a report design? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-10 : 02:36:45
|
| what all does you query behind currently have? both the above sums? or just detail data? |
 |
|
|
oriondpd
Starting Member
7 Posts |
Posted - 2009-05-10 : 09:20:40
|
| The query sums the hours worked from a time record table. The first query sums hours worked and the second query sums hours taken for vacation, sick leave, etc. I get both those numbers back as a result of my "select sum(hours) from tbltimerec where ......". What I want to do is add them together to get total hours (I guess I could do a 3rd query). But lastly I want to divide the hours worked by the total hours so I can determine a "staffing level achieved" value.Again, I think this is basic stuff within SQL, but I have been unable to utilize that within the SSRS report. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-10 : 12:57:04
|
| you can just bring two sums alone inside report. then you can use expression inside cell to divide the other two fields using ReportItems!cell1.value/ReportItems!cell2.value where cell1 & cell2 displays the two sum values. |
|
|
|
oriondpd
Starting Member
7 Posts |
Posted - 2009-05-10 : 21:23:17
|
| I thought that would work, but it seems that the results go into the report as a table in a Textbox. There is no way that I can find to identify the cells in the table in order to make a reference to the values themselves. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-13 : 11:06:15
|
| sorry i didnt get that. you mean you show both sum values in same cell? |
|
|
|
oriondpd
Starting Member
7 Posts |
Posted - 2009-05-14 : 21:59:26
|
| I ended up creating 3 datasets; one that returned hours worked, one that returned hour not worked, and another that returned total hours. I put each of them in a different cell and I was then able to compute the staffing level by dividing the value returned by the hours worked query by the value returned by the total query.But I couldn't figure out a way to do it with a single dataset or a single query that returned multiple values. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-15 : 14:35:31
|
| why? can you give the calculation logic for hours worked. then i'll try to put it together in a query |
|
|
|
|
|
|
This should be an easy one (and then maybe not)