Crosstable in combination with SQLServer 2005

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

Development Tools

Reporting Services Development

Crosstable in combination with SQLServer 2005

Author

Topic

Frenske
Starting Member

3 Posts

Posted - 2006-03-27 : 02:34:02

Hello all,

I'm working on a application which generates reports using microsoft reporting services linked to a MS SQL Server 2005 Database. These reports concern information about the performance of a callcenter.

I have some basic reports finished already but now the tricky part comes into view. Fact is that i don't have a lot of experience regarding reporting services. Now i want to make a report looking like a giant crosstable. It uses multiple datasets which calculate values separetly.

It has to look like this:

The green parts are all read out of the database. The blue parts have to be read out of the database according to some parameters i set. The white part is being computed. Can someone give me a start in building something like this?

All help gladly appreciated!!

Frenske
Starting Member

3 Posts

Posted - 2006-03-27 : 02:37:07

Larger Image: http://img96.imageshack.us/my.php?image=crosstable0yv.jpg

jhermiz

3564 Posts

Posted - 2006-03-27 : 09:39:55

Frenske what are your exact issues. Do you have RS installed? Have you ever created a RS report? Are you running SQL Server 2k5 I assume ? How goods your SQL? A cross tab is a very simple query to write, and RS fully supports the RS Crosstab Matrix Object to drag and drop columns into the x axis headings.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Frenske
Starting Member

3 Posts

Posted - 2006-03-28 : 03:13:24

Hello JHermiz,

The server running is indeed MS SQL Server 2005. My SQL is pretty good. I have written some reports in RS but never used a Crosstab Matrix. I cant seem to get the totals right.

at the moment i am using the following query for my report:

SELECT
  G.GesprekID, G.Starttijd, G.Eindtijd,
  R.Eindtoestand, R.ResultaatOptie,
  M.Voornaam, M.Achternaam,
  WT.aantaluur
FROM
  Resultaat R LEFT JOIN
     ((( Gesprek G RIGHT JOIN CMS_MI_ReportParameters MIP
                 ON (G.Starttijd BETWEEN MIP.DetailOverzichtMedewerker_BeginDatum
                                 AND     MIP.DetailOverzichtMedewerker_EindDatum)

     )
     INNER JOIN CBS_Medewerker M
     ON G.MedewerkerID = M.MedewerkerID)
     LEFT JOIN VIEW_CMS_MI_GewerkteUren WT
     ON G.MedewerkerID = WT.MedewerkerID)
  ON G.ResultaatID = R.ResultaatOptieID
WHERE
  G.GesprekID > 0
ORDER BY
  G.MedewerkerID

It gives me all the information i need in my report. The problem is how to configure the Crosstab Matrix Object in the right manner. At the moment i have the following report:
http://img457.imageshack.us/my.php?image=crosstable7hz.jpg

if you match this with the report i designed in excel the other day there are only a few values missing. I would like to get a totals field for the numbers directly on the left, right next to the names (just above the darkblue totals field which is already there).

Maybe a screenshot of the report @ design time clears things up: http://img20.imageshack.us/img20/2921/crosstableinrs8ec.jpg

I also would like to have one single row total horizontaly. At the moment the total on the top-right repeats itsself for each column group. I would like to have this total only once instead of 4/5 times repeated (for each column group).

Thanks in advance for your trouble!!

jhermiz

3564 Posts

Posted - 2006-03-28 : 09:02:28

Summing the column next to the name is quite simple. In a text box do the following:

=Sum(Fields!Aantal.Value)

Regarding the horizontal solution you will need to either create the SUM in SQL or you can try to use the =Sum(Fields!YourFIeld.Value) and see if that works for you.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources