Can Reporting Server do this? -- sql server reporting services

sql server reporting services : Can Reporting Server do this?

Zean Smith
12/2/2005 11:50:14 AM

I have a simple database see below and I would like the outcome group by
month with subtotal. How do I write the query and then use Reporting Server
to genereate a report like the following?

Data in database:
------------------
Datetime Amount
1/1/2005 1400
1/1/2005 1600
2/1/2005 800
2/5/2005 600

Report outcome:
------------------
January
1/1/2005 1400
1/1/2005 1600
Subtotal : 3000

Febuary
2/1/2005 800
2/5/2005 600
Subtotal: 1400

Pedro
12/4/2005 12:38:04 AM

Create a report containing the dataset from your database. Create a field
that only shows the month of your DateTime and then group it by that field
(Month) and include subtotals.

If you create a view that strips out the month in your datetime, then you
should be able to achieve your result by using the report wizard.

I know Crystal Reports has the ability to group by datetime based on
month/week/daily basis, but I am not sure if that functionality is in RS.

[quoted text, click to view]

Zean Smith
12/5/2005 12:51:34 PM

Thanks Pedro!! To help other people.. here is the Query I used:

By using DataName and DatePart in SQL query:

SELECT DATENAME(mm, DateTime) + ', ' + CAST(DATENAME(yyyy, DateTime) AS
varchar(4)) AS MonthYearName, DATEPART(yyyy, DateTime) AS Year,
DATEPART(mm, DateTime) AS Month, *
FROM CorporateSales

Then, in Reporting Server, add the GROUP and then group the data by Month,
and by Year.
Add "MonthYearName" in header.
Add "Subtotal" in footer.

I will be able to show exactly what I wanted in the first place.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn