Trying to reproduce the a calendar

I am using Visual Studio 2008, and SQL Server 2008 R2. I have attached a databse back up and final result of the .rd I would like to accomplish.

I am looking for someone to help me step-by step reproduce the row grouping of this report. In my failed attempts the row groupong keeps on getting messed up in the table and I am at a loss on how I can accomplish this.

If anyone could help that would be great!

Thanks,
petedragunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
I don't use SSRS, so I probably won't be able to help with your problem.  I just wanted to let you know that I don't see any files attached to your post.

 James
0
petedragunAuthor Commented:
Even if you zip up the package, .rdl files are not allowed. :(

But, there are two files in the .zip

1. Calendar.txt -> rename to Calendar.rdl
2. CalendarData.txt -> CelendarData.bak - and restore this file into SQL Server 2008 R2.

You will need to create a dataset and the query is:






 Calendar.zip
SELECT        Dates.MonthDate, Dates.DayNumber, Dates.DayName, Dates.DayOfWeek, Dates.WeekOfMonth, Dates.MonthName, 
                         Appointments.AppointmentDateTime AS ScheduledDate, SUBSTRING(CONVERT(char(19), Appointments.AppointmentDateTime, 100), 13, 7) AS ScheduledTime, 
                         Appointments.AppointmentDescr
FROM            dbo.fn_GenerateMonth(@MonthNum, @Year) AS Dates LEFT OUTER JOIN
                         Appointments ON Dates.MonthDate = CONVERT(datetime, CONVERT(char(10), Appointments.AppointmentDateTime, 101))
ORDER BY Dates.MonthDate

Open in new window

SELECT        Dates.MonthDate, Dates.DayNumber, Dates.DayName, Dates.DayOfWeek, Dates.WeekOfMonth, Dates.MonthName, 
                         Appointments.AppointmentDateTime AS ScheduledDate, SUBSTRING(CONVERT(char(19), Appointments.AppointmentDateTime, 100), 13, 7) AS ScheduledTime, 
                         Appointments.AppointmentDescr
FROM            dbo.fn_GenerateMonth(@MonthNum, @Year) AS Dates LEFT OUTER JOIN
                         Appointments ON Dates.MonthDate = CONVERT(datetime, CONVERT(char(10), Appointments.AppointmentDateTime, 101))
ORDER BY Dates.MonthDate

Open in new window

0
petedragunAuthor Commented:
The biggest problem I am facing is creating the tablix/table inside the matrix. For some reason Visual Studio 2008 and Report Builder 3.0 will not allow me to group the DayNumber, SheculedTime and AppointmentDescr from top to bottom. I figure I am missing some sort of step, but not sure.

If anyoen could help, that would be great!!!
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

planoczCommented:
As far as posting .rdl's just change the ext to .xml
and then back to .rdl when you want to view them locally on your PC.

Personnally I use only tablix by itself. Not mixed in a matrix.
0
petedragunAuthor Commented:
Are you able to read, the .txt I provided in the attachment? Anything I need to fix in order to get some help with this particular question?

Thanks
Pete
0
planoczCommented:
nice function.
This is what I made for you to play with.
STORED PROCEDURE...
USE [Calandar]
GO
/****** Object:  StoredProcedure [dbo].[sp_CalendarSchedule]    
            Script Date: 04/08/2011 23:30:31
******/
 CalandarNew.xml CalandarNew.xml
0
planoczCommented:
Sorry SP again,...
USE [Calandar]
GO
/****** Object:  StoredProcedure [dbo].[sp_CalendarSchedule]    
            Script Date: 04/08/2011 23:30:31
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- Author:            Your Name
-- Create date: 4-8-2011
-- Description:      Collect data for the New Calendar

ALTER PROCEDURE [dbo].[sp_CalendarSchedule]
      @StartDate            datetime,
      @EndDate            datetime
AS
BEGIN
      SET NOCOUNT ON

SELECT
 AppointmentDateTime
,AppointmentDescr
,ScheduleDate
,ScheduleTime
,CustomerName
  FROM Appointments
  WHERE AppointmentDateTime >=@StartDate and  AppointmentDateTime <=@EndDate
END
0
petedragunAuthor Commented:
Unfortunately, what you have provided me, does not help me understand how to re-create the example I am sharing with all of you.

What I am looking for is.
1. For soemone to try to reproduce the exact solution with the data I provided using SQL Server Reporting Services 2008 R2. I want to know if it is possible using R2 or if there is some sort of grouping issue.
2. If it can be done, provide some steps on how you accomplished this, and then I can move forward and apply this example in my current project I am working on.

If the information I provided does not work, please let me know, and I will address this.

Thanks!
Pete
0
petedragunAuthor Commented:
I was able to re-created the desired report using SQL Server 2008. My conclusion is that there is a design bug with row grouping in R2.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
petedragunAuthor Commented:
From my research, as I was able to produce the report using SQL server 2008 within 5 minutes.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.