Showing posts with label summary. Show all posts
Showing posts with label summary. Show all posts

Friday, March 9, 2012

Displaying Details of a group

I have two groups. I want to do this:

Group 1

Group2

Group2 Summary info

Group2 Details

Group2 Details

Group2 Details

Group2 Details

I don't know how to get the table details row to only show my group 2 details.

Thanks

Patawa,

You can do this by creating a Table that has two groupings for Group1

and Group2. From the layout described, remove the Table Header and the

Tables and grouping Footers. For the Group2 summary information, add a

second header row to the Group2 grouping and add the appropriate

aggregates. Then, you can place the detail fields in the detail section

of the Table.

Ian

Displaying detail and summary in same bar chart

I have a bar chart that displays quality averages for all plants by
year.
Category = Plant
Series = Year
Detail = average
I would like to add a bar for the total company. Is there an easy way
to do this?
I am using VS 2005.
Thanks.Hello Heather,
You could not do this in the Chart setting.
The only thing you could do is that add a column in the dataset to include
the total data.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I thought that might be the answer. I wanted to make sure. Thanks.

Friday, February 24, 2012

display summary week total rows from sql database

(I moved this thread from datagrid area)

I have a sql database that has individual records consisting of name, date, hours worked among other fields.

Date and name is part of a unique identifier, so there can NOT be two records for the same person for the same date.

My users need a grid view that displays days worked in ONE LINE per user. I have gotten close, but can't quite get the last part. Ive tried group by, distinct, and with rollup and no luck.

TABLE:

dan 12/13/2012 12:00:00 AM9.123dan 12/14/2012 12:00:00 AM3.123123cara 12/12/2012 12:00:00 AM4.222cara 12/16/2012 12:00:00 AM3.3333cara 12/17/2012 12:00:00 AM2

CODE:

Select distinct(name),
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Sunday')as Sunday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Monday')as Monday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Tuesday')as Tuesday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Wednesday')as Wednesday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Thursday')as Thursday,
(select (y.hours) from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours and datename(dw, date)='Friday')as Friday,
(select(y.hours) from dbo.testtime y where y.name=YT.name AND y.hours =YT.hours and datename(dw, date)='Saturday')as Saturday,
(select sum(hours)from dbo.testtime y where y.name=YT.name AND y.hours = YT.hours) as Total
from dbo.testtime YT
group by date, name, hours

RESULTS:

cara NULL NULL NULL 4.222 NULL NULL NULL 4.222
cara NULL 2 NULL NULL NULL NULL NULL 2
cara 3.3333 NULL NULL NULL NULL NULL NULL 3.3333
dan NULL NULL NULL NULL NULL 3.123123 NULL 3.123123
dan NULL NULL NULL NULL 9.123 NULL NULL 9.123

Like I said, I am SO close, I just need it to look like;

NAME SUN MIN TU WED TH FR SA TOTAL

cara 3.333 2 4.222 9.555
dan 9.125 3.125 12.5

TIA

dan



You need to create a table variable with columns of name, Sun-Sat columns to accumulate the data into. You can then select from that table the required summary.

|||

OK, sounds good.

Um, what is a table variable?

Is that like a view?

can you give me psudo-code to work with?

thanks MUCH!

Dan

|||

With table create script of SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TimeData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Date] [datetime] NOT NULL,
[Hours] [decimal](8, 6) NOT NULL,
CONSTRAINT [PK_TimeData] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Table insert script of

INSERT INTO TimeData(Name, Date, Hours)
VALUES ('dan', CONVERT(DATETIME,'13/Dec/2012 12:00:00 AM'), 9.123) -- I am in the UK, hence the change of date format
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('dan', CONVERT(DATETIME,'14/Dec/2012 12:00:00 AM'), 3.123123)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'12/Dec/2012 12:00:00 AM'), 4.222)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'16/Dec/2012 12:00:00 AM'), 3.3333)
INSERT INTO TimeData(Name, Date, Hours)
VALUES ('cara', CONVERT(DATETIME,'17/Dec/2012 12:00:00 AM'), 2)

|||

The TSQL

DECLARE @.MyTableVar table(
[Name] VARCHAR(5) NOT NULL,
Sun [decimal](8, 6) NOT NULL DEFAULT ((0)),
Mon [decimal](8, 6) NOT NULL DEFAULT ((0)),
Tue [decimal](8, 6) NOT NULL DEFAULT ((0)),
Wed [decimal](8, 6) NOT NULL DEFAULT ((0)),
Thu [decimal](8, 6) NOT NULL DEFAULT ((0)),
Fri [decimal](8, 6) NOT NULL DEFAULT ((0)),
Sat [decimal](8, 6) NOT NULL DEFAULT ((0)),
Total [decimal](8, 6) NOT NULL DEFAULT ((0))
);
DECLARE @.NAME VARCHAR(50)
DECLARE @.DATE DATETIME
DECLARE @.HOURS decimal(8, 6)
DECLARE xCURSOR CURSOR FOR
SELECT Name, Date, Hours FROM TimeData
OPEN xCURSOR
FETCH xCURSOR INTO @.NAME, @.DATE, @.HOURS
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT * FROM @.MyTableVar WHERE [Name] = @.NAME)
INSERT INTO @.MyTableVar([Name]) VALUES (@.NAME)
IF datename(dw, @.DATE)='Sunday'
UPDATE @.MyTableVar SET Sun = Sun + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Monday'
UPDATE @.MyTableVar SET Mon = Mon + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Tuesday'
UPDATE @.MyTableVar SET Tue = Tue + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Wednesday'
UPDATE @.MyTableVar SET Wed = Wed + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Thursday'
UPDATE @.MyTableVar SET Thu = Thu + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Friday'
UPDATE @.MyTableVar SET Fri = Fri + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
IF datename(dw, @.DATE)='Saturday'
UPDATE @.MyTableVar SET Sat = Sat + @.HOURS, Total = Total + @.HOURS WHERE [Name] = @.NAME
FETCH xCURSOR INTO @.NAME, @.DATE, @.HOURS
END
SELECT * FROM @.MyTableVar
CLOSE xCURSOR
DEALLOCATE xCURSOR

gives

Name Sun Mon Tue Wed Thu Fri Sat Total
-- --- --- --- --- --- --- --- ---
dan 0.000000 0.000000 0.000000 0.000000 9.123000 3.123123 0.000000 12.246123
cara 3.333300 2.000000 0.000000 4.222000 0.000000 0.000000 0.000000 9.555300

Obviously a Cursor is not particularly efficient and needs to be eliminated. Also the Name column would need to be indexed (if possible) if there are more than 10 rows.

|||

TAT~

THat is awsome!
Thank you SO much for your efforts.

I ventured out on my own and came up with the following (I actually changed it to look at a test/prod table, so name is UserName)

But the code actually WORKEd!

Here it is, if youd care to comment:

--make var tqable

Declare @.tempweek TABLE
(UserName nvarchar(50), Sunday DECIMAL(8,6), Monday DECIMAL(8,6), Tuesday DECIMAL(8,6), Wednesday DECIMAL(8,6), Thursday DECIMAL(8,6), Friday DECIMAL(8,6), Saturday DECIMAL(8,6), Total DECIMAL(8,6))

--fill table

INSERT INTO @.tempweek
SELECT UserName,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Sunday')AS Sunday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Monday')AS Monday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Tuesday')AS Tuesday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Wednesday')AS Wednesday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Thursday')AS Thursday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Friday')AS Friday,
(SELECT (y.HoursWorked) from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.DateWorked=YT.DateWorked AND datename(dw, DateWorked)='Saturday')AS Saturday,
(SELECT SUM(HoursWorked)from db_owner.PS_HR_Hrs y WHERE y.UserName=YT.UserName AND y.HoursWorked = YT.HoursWorked) AS Total
from db_owner.PS_HR_Hrs YT


--select data

select UserName , sum(sunday)as Sunday, sum(monday) as Monday, sum(tuesday)as Tuesday, sum(wednesday)asWednesday, sum(thursday)as Thursday, sum(friday)as Friday, sum(saturday)as Saturday, sum(total) as Total
from @.tempweek
group by UserName

|||

Your solution will probably be faster as you do not use a CURSOR! Both solutions will be gluttons for memory for the few milliseconds they run, so as always, never stint on the RAM for a server hosting SQL Server.

Display space on report server

Hi,
In my report summary I need to display space. The output looks like:
Total Restaurants: 20
Total seats : 315
I'm using
="Total Restaurants: " & Sum(Fields!Restaurant.Value)
="Total seats: " & Sum(Fields!Seats.Value)
It looks fine in the preview but after I deployed it on the report
server, it doesn't look right on the server:
Total Restaurants: 20
Total seats: 315
The spaces after "Total seats" got eaten up.
I tried to use Space(5) after "Total seats" but it still doesn't work.
Is there way to do this other than spliting them into two fields?
Thanks in advance.
DanniHi,
I'm sorry but I still don't know how to use "ALT+255".
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> If it would be urget, I would have used "ALT+255" instead of SPACE
> "Danni Liao" wrote:
> > Hi,
> > In my report summary I need to display space. The output looks like:
> >
> > Total Restaurants: 20
> > Total seats : 315
> >
> > I'm using
> > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > ="Total seats: " & Sum(Fields!Seats.Value)
> >
> > It looks fine in the preview but after I deployed it on the report
> > server, it doesn't look right on the server:
> >
> > Total Restaurants: 20
> > Total seats: 315
> >
> > The spaces after "Total seats" got eaten up.
> >
> > I tried to use Space(5) after "Total seats" but it still doesn't work.
> >
> > Is there way to do this other than spliting them into two fields?
> >
> >
> > Thanks in advance.
> > Danni
> >|||Hold ALT key and press 255 on num pad and then release ALT key. Hope this
help..
"Danni Liao" wrote:
> Hi,
> I'm sorry but I still don't know how to use "ALT+255".
> "Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> > If it would be urget, I would have used "ALT+255" instead of SPACE
> >
> > "Danni Liao" wrote:
> >
> > > Hi,
> > > In my report summary I need to display space. The output looks like:
> > >
> > > Total Restaurants: 20
> > > Total seats : 315
> > >
> > > I'm using
> > > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > > ="Total seats: " & Sum(Fields!Seats.Value)
> > >
> > > It looks fine in the preview but after I deployed it on the report
> > > server, it doesn't look right on the server:
> > >
> > > Total Restaurants: 20
> > > Total seats: 315
> > >
> > > The spaces after "Total seats" got eaten up.
> > >
> > > I tried to use Space(5) after "Total seats" but it still doesn't work.
> > >
> > > Is there way to do this other than spliting them into two fields?
> > >
> > >
> > > Thanks in advance.
> > > Danni
> > >
>|||Hi Ameet:
Thank you for your reply.
I need 5 white spaces so according to your suggestion I used:
="Total seats:" & "ALT+255 4" & Sum(Fields!Seats.Value) in the
expression of that textbox, I don't see the "ALT+255" shown and the
result is:
Total seats:4201
So it still doesn't work.
I think this is a bug in the reporting services.
Danni
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<3AB33890-3B65-4C6F-9A21-622AE7E9B3C4@.microsoft.com>...
> Hold ALT key and press 255 on num pad and then release ALT key. Hope this
> help..
> "Danni Liao" wrote:
> > Hi,
> > I'm sorry but I still don't know how to use "ALT+255".
> >
> > "Ameet" <Ameet@.discussions.microsoft.com> wrote in message news:<BE2C51F8-9FDD-48D8-9AFD-C081D18ACA10@.microsoft.com>...
> > > If it would be urget, I would have used "ALT+255" instead of SPACE
> > >
> > > "Danni Liao" wrote:
> > >
> > > > Hi,
> > > > In my report summary I need to display space. The output looks like:
> > > >
> > > > Total Restaurants: 20
> > > > Total seats : 315
> > > >
> > > > I'm using
> > > > ="Total Restaurants: " & Sum(Fields!Restaurant.Value)
> > > > ="Total seats: " & Sum(Fields!Seats.Value)
> > > >
> > > > It looks fine in the preview but after I deployed it on the report
> > > > server, it doesn't look right on the server:
> > > >
> > > > Total Restaurants: 20
> > > > Total seats: 315
> > > >
> > > > The spaces after "Total seats" got eaten up.
> > > >
> > > > I tried to use Space(5) after "Total seats" but it still doesn't work.
> > > >
> > > > Is there way to do this other than spliting them into two fields?
> > > >
> > > >
> > > > Thanks in advance.
> > > > Danni
> > > >
> >