August 27, 2014 at 6:18 pm
Will someone kindly look at the attached image and tell me what report items I need to put together an SSRS report that looks like the attached image? Each page is data for one organization, bottom table grouped by Division, the middle tables grouped by year and month, the top charts y axis by year/month of current year.
As can probably be guessed from my multiple posts on this forum, I've been trying many things for many days:
(1) multiple custom tables with row/column groups in one SSRS report,
(2) SSRS report with List and nested tables,
and now I'm looking into Reports with Subreports.
It would help to know what I should do and know I'm on the right track. Please advise on which report objects I need.
--Quote me
August 27, 2014 at 8:38 pm
I got the matrix working pretty quickly doing it as a subreport. Of course that means performance won't be great, but one step at a time, I guess. Once I got the matrix working, I added a parameter to it (prmYear), and then dropped the report into a tablix of Years, and added the matrix to the row I inserted below the main one.
The 12-month rolling summary might be easier than I originally thought - if you use a running totals query like the one(s) in Itzik Ben-Gan's book (like $35 and well worth it!)
SELECT empId, ordermonth, qty,
SUM(qty) OVER(PARTITION BY empid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS RunQty
FROM Sales.EmpOrders;
If you have data for every month, you could use something simple like
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
Actually, there's a lot to this question... what's the most important part? (I guess I'm just thinking that dealing with it one part at a time would be a lot easier!)
Pieter
August 27, 2014 at 11:13 pm
I agree "one thing at a time" and the most important part is the middle region where there are 3 tables for different years - all reporting by month/ sum of move-ins and referrals.
So you agree that master report/subreport is one good way to go. I will try it with a matrix as subreport. I hope I got you.
:unsure:
Yes, Itzhik Ben-Gan is pretty excellent. I didn't know he had book for SSRS.
--Quote me
August 28, 2014 at 3:29 am
I can try to attach this thing... the fun part is that I had to do some voodoo to get all the dates to show up. Here's the query that I used for the subreport (the matrix reports for each year):
SELECT c.CalendarDate, c.MonthNumber, c.YearNumber, c.DayNumber, m.MoveInID, m.MoveInDate, m.Division,
CASE WHEN MoveType = 1 THEN 'Move In' ELSE 'Referral' END AS TransType
FROM Calendar AS c LEFT OUTER JOIN
MoveIn AS m ON c.CalendarDate = m.MoveInDate
ORDER BY c.CalendarDate
The only catch is that it requires a Calendar table.
CREATE TABLE [dbo].[Calendar](
[CalendarDate] [date] NOT NULL,
[MonthNumber] [tinyint] NULL,
[YearNumber] [int] NULL,
[DayNumber] [tinyint] NULL,
PRIMARY KEY CLUSTERED
(
[CalendarDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
It was the only way I could get all the dates for a month even if the date had no referrals or move-ins.
The rest is easy, right?
August 28, 2014 at 6:10 am
pietlinden. not sure if you get me.
The trouble for me is within SSRS. I don't know how to get three identical tables working together in concert, but for three different years.
--Quote me
August 28, 2014 at 6:55 am
It's a tablix on the outside (for the years) with a matrix inside (for the Totals by month for a single year). Because several years are in the outer tablix, there's a corresponding matrix for each value.
Here are the steps I followed to make it happen:
1. Create a report with a dataset of rental Years.
2. Add a tablix to the report surface.
3. remove all but one column.
4. remove the header cell.
5. Drop the RentalYear field into the tablix cell. (there's only one.)
6. Insert a row inside the group and below.
7. Click on the new empty cell (the bottom one).
8. Insert Subreport.
9. Subreport properties: use the Matrix report as the subreport.
10. Click parameters, add. prmCalendarYear = [RentalYear]
RUN.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply