SQL Server Performance

Querying a local Dataset

Discussion in 'SQL Server Reporting Services' started by dougwood, Feb 3, 2004.

  1. dougwood New Member

    OK Jasper,
    This Oracle DB issue is giving me fits, so here comes my next question.
    I can query the database and load a dataset with , let's say, this query

    SELECT event, details, time FROM events
    which is no problem, it returns all events.(the dates are formatted like 9/15/2003 12:00 AM)

    But now I want to create a report which has a column for each month, and which counts the number of events within each month and displays it. SO... can I now query this local dataset (call it 'EventsTable') with some sort of BETWEEN statement, so that in my report table, I have a field with an expression that asks "count the number of events within 'EventsTable' that occur between 1/1/2003 and 1/31/2003" and display in the January column.
    Does this make sense ?
    Thanks so much for all your help!

  2. jasper_smith New Member

    The best approach would be to do this in Oracle, you just need to find what its equivalent to the DATENAME and DATEPART functions are and then do a GROUP BY. However, you can certainly do this in RS although it seems inefficent to bring all the detail back unless you are do a master/detail type report. You can create two calculated fields (right click the fields list and choose Add. Call the first one Month and make it's expression =MonthName(DatePart("m", Fields!time.Value)), call the second one MonthNum and make it's expression =DatePart("m", Fields!OrderDate.Value). Then add a table to layout, set one detail column to Month field. Right click the detail row for the whole table and choose Edit Group. Add a grouping expression =Fields!Month.Value and set the Name of the group to MonthGroup. Now in the second detail column you can add the expression =CountRows("MonthGroup"). Finally select the whole tables properties and set the Sorting expression to =Fields!MonthNum.Value so the results are in the right order.This is obviously a lot more work than <pre>select datename(mm,OrderDate),count(*)<br />from Orders<br />group by datepart(mm,OrderDate),datename(mm,OrderDate)<br />order by datepart(mm,OrderDate)</pre> however I don't know what the equivalent Oracle functions are [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />HTH<br /><br />Jasper Smith
  3. jasper_smith New Member

    Don't know if this is right but this might be the equivalent to datepart for month in oracle
    to_number(to_char(date, ‘MM#%92))


    Jasper Smith
  4. dougwood New Member

    Although I haven't tried the Oracle option yet, I tried the lengthier alternative and it worked great, exactly what I'm looking for. It will work fine until I take on the Oracle issue.
    You have saved me hours of banging my head against the wall !
    Thanks again

Share This Page