Building A Single Page Report On the Fly

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Building A Single Page Report On the Fly

Author

Topic

dtoddp
Starting Member

12 Posts

Posted - 2012-08-08 : 11:39:22

I’m using a SQL 2008 DB in conjunction with MS Project Server 2010, and building my queries using the MS Visual Studio 2008 query designer. I’m pulling data from 3 tables (query builder is doing inner/outer joins for me), basically a master record with 19 task records (so the query will pull 20 records per “project name”). I use a parameter to input the project name the query will use to select the records. I’m building a 1 page request form where the data from all 20 records is combined into that single form. Wanted to just accumulate the totals from those records while the query was running – something simple like “totals field = totals field + amount field”. When I try to use the SUM function I get errors, and its telling me I need to group by or aggregate. Any way I can just total without grouping?

In addition – each of the 19 lines represents a single data type/total – just wanted to do like a Case statement that said something like Case Task-name When “value 1” set rpt-field1 as task-amt, when “value 2” set rpt-field 2 as task-amt, etc. I get error messages trying to use CASE in the Select.

The following is a rough sample of what I have put together so far - you can see where I'm using the same field name over and over for different fields (this is where I'd like a CASE statement or IIF to work)

SELECT P.[Proj Name], P.[Proj Desc], P.Owner, T.TskName, T.TskNotes, T.TskStartDate, T.TskFinishDate, T.TskActualCost, B.[Est Tsk Hrs], B.[On-Going Actual Cost], P.[Grand Total Cost], P.[Total First Time Cost], P.[Total Ongoing Cost], T.TaskBaseCost AS TotalExternalCost, T.TaskBaseCost AS EstDataCabCost, T.TaskBaseCost AS EstNetwkCost, T.TaskBaseCost AS EstTelecomCost, T.TaskBaseCost AS EstUPSCost, T.TaskBaseCost AS EstSrvrCost, T.TaskBaseCost AS EstTFSCost, T.TaskBaseCost AS EstAVCost, T.TaskBaseCost AS EstAVPCCost, B.[On-Going Base Cost] AS TotalOngoingExtCost,B.[On-Going Base Cost] AS EstOGDataCabCost, B.[On-Going Base Cost] AS EstOGNetwkCost, B.[On-Going Base Cost] AS EstOGTelecomCost, B.[On-Going Base Cost] AS EstOGUPSCost, B.[On-Going Base Cost] AS EstOGServerCost, B.[On-Going Base Cost] AS EstOGTFSCost, B.[On-Going Base Cost] AS EstOGAVCost, B.[On-Going Base Cost] AS EstOGAVPCCost, T.[Est Tsk Hrs] AS EstResDCHrs, T.[Est Tsk Hrs] AS EstResNetHrs, T.[Est Tsk Hrs] AS EstResTCHrs, T.[Est Tsk Hrs] AS EstResUPSHrs, T.[Est Tsk Hrs] AS EstResSrvHrs, T.[Est Tsk Hrs] AS EstResTFSHrs, T.[Est Tsk Hrs] AS EstResAVHrs, T.[Est Tsk Hrs] AS EstResAVPCHrs
FROM ProjSrvr_Rptg.dbo.MSP_EpmTsk_UV AS B INNER JOIN
vw_IT_Tsk_List AS T ON B.TaskUID = T.TaskUID LEFT OUTER JOIN
vw_IT_Proj_List AS P ON T.ProjectUID = P.ProjectUID
WHERE (P.[Proj Name] IN (@ProjName))
ORDER BY P.[Proj Name]

All this being said, is it better to build an “temp table” (or matrix) to load all the data and print the report from the table?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 15:12:13

it sounds like what you're looking for is a crosstabbing effect in which case you can bring data in single field itself as [Est Tsk Hrs] and then use matrix with row group on projectname and column group on datatype. then put simply SUM([Est Tsk Hrs])as expression for matrix data to get data split up based on data type values into various columns for each of projects considered

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dtoddp
Starting Member

12 Posts

Posted - 2012-08-08 : 15:22:55

So by CrossTab - is that an "internal table" from which I can assign fields and place those fields into the document?? The document is a very strictly formatted template (a corp std), so I need to keep the data virtual so to speak, and then use it to "fill in the blanks". Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 15:29:41

quote:
Originally posted by dtoddp

So by CrossTab - is that an "internal table" from which I can assign fields and place those fields into the document?? The document is a very strictly formatted template (a corp std), so I need to keep the data virtual so to speak, and then use it to "fill in the blanks". Thanks.

the matrix is internal container within reporting services tool. so if you're using ssrs you can use that and just bring data as it is from your table using select

if you're not using ssrs then you need to do cross tabbing in query itself using logic like below


SELECT projectname,
SUM(CASE WHEN datatype='somevalue' THEN [Est tsk Hrs] ELSE 0 END) AS [datatype1],
SUM(CASE WHEN datatype='someothervalue' THEN [Est tsk Hrs] ELSE 0 END) AS [datatype2],
....

FROM table
WHERE ....conditions
GROUP BY projectname

and if datatype values cant be determined beforehand use dynamic sql along with above query as

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dtoddp
Starting Member

12 Posts

Posted - 2012-08-08 : 16:04:29

I am using SSRS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 16:11:29

quote:
Originally posted by dtoddp

I am using SSRS

then use matrix as suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dtoddp
Starting Member

12 Posts

Posted - 2012-08-08 : 16:30:03

As I mentioned earlier, when running my query against the SQL DB, it will pull about "20 rows" of data - from which I only want 1 page on the report. And yet when I do a trial run i get 20 pages? How do I tell the report to only create 1 page?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 16:31:26

quote:
Originally posted by dtoddp

As I mentioned earlier, when running my query against the SQL DB, it will pull about "20 rows" of data - from which I only want 1 page on the report. And yet when I do a trial run i get 20 pages? How do I tell the report to only create 1 page?

i you use matrix as i suggested and apply row and column groups as suggested you will only single row of data for each project with 20 columns containing hour values split up based on datatype values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources