Firehose mode: company

Showing posts with label company. Show all posts

Friday, March 30, 2012

grouping similar data

In layout view: how do I group all the company names together who have the same value for a certain field? For instance, 10 companies all have the same booth size, but in Preview mode, it lists each company seprately with the booth size on each line. I need to have the booth size listed once with all the companies who share that common size. I have tried adding a group and using the expression for the company name as well as the booth size and it does not work. Can someone provide some detailed instructions?
Thank you,
Bil
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf I am understanding correctly you want to have the booth size and then
show all companies under that that match that description. Have you tried
grouping it by the booth size and then put your companies in the detail?
"Bkana" <nospam@.developmentnow.com> wrote in message
news:97f68ac4-a6e2-429d-a4e5-3b98fed63020@.developmentnow.com...
> In layout view: how do I group all the company names together who have the
> same value for a certain field? For instance, 10 companies all have the
> same booth size, but in Preview mode, it lists each company seprately with
> the booth size on each line. I need to have the booth size listed once
> with all the companies who share that common size. I have tried adding a
> group and using the expression for the company name as well as the booth
> size and it does not work. Can someone provide some detailed instructions?
> Thank you,
> Bill
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Monday, March 26, 2012

grouping data to a field

Hey
Im doing a report wich is causing me some trouble:
Im doing a dataset selecting all values regarding the company, and then
im mapping the fields to textboxes in my VS designer. This should
result in a list with all my customers and their data.
Now below each customer i would like to place a table holding data from
another dataset, but i only want the data in the table to correspond to
the company just above the table.
I have placed both the customer data and the table in a list item, but
the problem seems to be the data in the table. I either get evrything
each time for all customers, or i only get the first customer data
below each customer.
Can anyone tell me how i can make this work.
Thanks
JimmyHi
I think there are two ways to solve this
first and the easiest :
get all the data in one dataset and use grouping facility in the reporting
services to group the data
second :
use sub report to build the details and pass the company id from the
parent report to the sub report
Joe
"Jimbo" wrote:
> Hey
> Im doing a report wich is causing me some trouble:
> Im doing a dataset selecting all values regarding the company, and then
> im mapping the fields to textboxes in my VS designer. This should
> result in a list with all my customers and their data.
> Now below each customer i would like to place a table holding data from
> another dataset, but i only want the data in the table to correspond to
> the company just above the table.
> I have placed both the customer data and the table in a list item, but
> the problem seems to be the data in the table. I either get evrything
> each time for all customers, or i only get the first customer data
> below each customer.
> Can anyone tell me how i can make this work.
> Thanks
> Jimmy
>|||Hey Joe
Ok, its not optimal, but it got the job done.
Im actually using a combination of the two.
Your posting lead me on the way though :)
Thanks
Jimmy

Friday, March 23, 2012

Grouping a report

I am trying to group a report like so:
Company: ABC
Trip # Leg Amount
1 1 100
1 2 200
Sub Total 300
Trip # Leg Amount
2 1 100
2 2 200
Sub Total 300
Company: DEF
Trip # Leg Amount
3 1 100
3 2 200
Sub Total 300
Trip # Leg Amount
4 1 100
4 2 200
Sub Total 300
I get get the detail info in the report just fine with a grid. But
I'm running into problems when trying to add the company into the
grid. Currently I have a group created to get the sub total for each
trip and that works fine. But when I create the group to add the
company into the grid, it keeps putting the header columns (Trip #,
Leg, Amount, etc) above the company name. I have tried everything I
can think of to get the company name above it but can't get it to work.if you right click on the group row's headers(the grey part on the far
left when you have the table focused) you can insert a new group.
have a group with the company as the expression. then another with
the trip as the expression.
* company
* trip
** DETAILS|||I got it to work doing that and putting the column header row inside
my group row (instead of in a header row). But now I have another
question - not sure if this is possible, but since I am in a grid, my
Company name ends up in the same column as my trip number. The
company name is obviously going to be bigger than the trip number, but
if I stretch out the column to handle that, then the leg box looks
much larger than it needs to be (I'll try to show it here, but it'll
be tough):
Is there some way I can change the spacing on the company column
without affecting the leg column?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x Company: xx ABCDEFGHIJKLMNOPQ xx x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x Trip # xx Leg xx Amount x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x 1 xx 1 xx
100 x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x 1 xx 2 xx
200 x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x xx Sub Total xx 300
x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Wednesday, March 21, 2012

Group sums wrong in Matrix

Banging my head against the wall on this one.
Running a matrix report in RS 2000. I have 2 groupings, Department and
Company. I want to get a sum of total hours for each job code and
Department and Company. The job code hours show up in the detail, as
=Iif (Len(sum(Fields!HoursTotal.Value)) = 0 , 0,
sum(Fields!HoursTotal.Value ))
When I run the report, the sum for the department is off.
Job A 8.0
Job B 0.0
Job C 8.2
Job D 71.1
Job E 99.5
--
Group Sum 186.7 (should be 186.8)
Any idea of what is wrong?
The Stored procedure returns values to 1 decimal place as a double.
Joewhy do you want to use:
Iif (Len(sum(Fields!HoursTotal.Value)) = 0 , 0,
sum(Fields!HoursTotal.Value ))
can you try
Iif (sum(Fields!HoursTotal.Value) = 0 , 0,
sum(Fields!HoursTotal.Value ))
or
Iif (sum(Fields!HoursTotal.Value) = 0.0 , 0,
sum(Fields!HoursTotal.Value ))
to see what happen?
in format of group sum, set it to n2 and see what happen?
"Jsarna" wrote:
> Banging my head against the wall on this one.
> Running a matrix report in RS 2000. I have 2 groupings, Department and
> Company. I want to get a sum of total hours for each job code and
> Department and Company. The job code hours show up in the detail, as
> =Iif (Len(sum(Fields!HoursTotal.Value)) = 0 , 0,
> sum(Fields!HoursTotal.Value ))
> When I run the report, the sum for the department is off.
> Job A 8.0
> Job B 0.0
> Job C 8.2
> Job D 71.1
> Job E 99.5
> --
> Group Sum 186.7 (should be 186.8)
> Any idea of what is wrong?
> The Stored procedure returns values to 1 decimal place as a double.
> Joe
>

Monday, March 19, 2012

Group formatting question

Hi. I have 2 groups associated with a data table, one on company name, and then one on management,

Company Name Management License # Phone Southwest Bank & Trust Southern Management, LLC 123456 123-123-1234 789123 321-456-7890 838485 888-111-5555

However, I would like the table to display the information in this fashion, so that the grouped items appear as part of the details, rather than on a separate line. How can I accomplish this? Thanks in advance.

Company Name Management License # Phone Southwest Bank & Trust Southern Management, LLC 123456 123-123-1234 789123 321-456-7890 838485 888-111-5555

You can definitely do this. Did you use the wizard and a "stepped" format, by any chance, when you created this report?

Based on your second sample format, I would start by making a single group that has both expressions in it, and I would remove both those group header lines. I would put the company and management expressions directly in the detail line, and use "hide duplicates" to suppress the values after detail row #1.

HTH,

>L<

|||Thanks, Lisa. No, I did the report using the designer not the wizard. I'll try your suggestion and see what I get.
|||Hi, Lisa. Your suggestion worked like a charm. I set the group headers not to show; is there a way to see the group headers after unchecking the box that shows them? I can't seem to find a way to edit those groups after hiding them. Thanks.
|||

>> is there a way to see the group headers after unchecking

Yep. When you rightclick on an element of the table, you should have an option in the context menu that allows you to select the parent, something like "select table1". Or, if you click on the little grey square box at the top-left corner of the table (above the square row indicators on the left) you can select the table. However you do it, when the selection marquee is around the table, rather than a table element, if you rightclick and select Properties you will get into the Table Properties dialog.

You will see a Group tab in that dialog. When you select it (is this starting to sound like Zork?) you should see your group in a listbox; if you click the Edit button in that Tab with your group selected it brings you back to that Group dialog that you want.

There may be a better way. There are probably several -- although it's good to know about that Table dialog, so I want to make sure you do <s>.

My main thought right now is... why do you need the groups at all, why not eliminate them? Order your data properly, and the groups should be "natural" in this case, unless you have some aggregate functions that need them?

Alternatively, this might be a case for "details grouping" rather than "groups" -- although frankly I'm not sure I understand that "feature". You will find an example/walkthrough here http://msdn2.microsoft.com/en-us/library/ms155903.aspx . You can get to "details grouping" by rightclicking on the row indicator for the details band, or from the Table Properties dialog (there is a Details Grouping... button in the Group tab).

>L<

|||Thanks, Lisa, for the very informative and detailed explanation. It's very much appreciated

Group formatting question

Hi. I have 2 groups associated with a data table, one on company name, and then one on management,

Company Name Management License # Phone Southwest Bank & Trust Southern Management, LLC 123456 123-123-1234 789123 321-456-7890 838485 888-111-5555

You can definitely do this. Did you use the wizard and a "stepped" format, by any chance, when you created this report?

HTH,

>L<

>> is there a way to see the group headers after unchecking

There may be a better way. There are probably several -- although it's good to know about that Table dialog, so I want to make sure you do <s>.

>L<

|||Thanks, Lisa, for the very informative and detailed explanation. It's very much appreciated

Group formatting question

Hi. I have 2 groups associated with a data table, one on company name, and then one on management,

Company Name Management License # Phone Southwest Bank & Trust Southern Management, LLC 123456 123-123-1234 789123 321-456-7890 838485 888-111-5555

You can definitely do this. Did you use the wizard and a "stepped" format, by any chance, when you created this report?

HTH,

>L<

>> is there a way to see the group headers after unchecking

There may be a better way. There are probably several -- although it's good to know about that Table dialog, so I want to make sure you do <s>.

>L<

|||Thanks, Lisa, for the very informative and detailed explanation. It's very much appreciated

Sunday, February 26, 2012

Group By Count

Dear all

How do i group the count by Top ?

For example, We want to show Top 5 high salary people in my company , but may top 3 people's salary is same. So I want to sperate 5 groups, the output data look like this :
[Top 1 high salary] - Philip , Alex , David
[Top 2 high salary] - Emily
[Top 3 high salary] - Tom, May, Ada
[Top 4 high salary] - Queenie
[Top 5 high salary] - Ellie, Candy, Hazel, Stella, Tanya, Jacky

Hence, the total count of name should be over 5, so how do i output this record ? because it should just show 5 peoples (Philip, Alex, David, Emily and Tom) if i just use "Top 5" .

Thanks you for your helpin SQL server, you could declare table variable, and insert those names into the table variable, then return from the table variable|||This is effectively a pivot on top of aggregation. That means that it is best done on the client.

With that said, it can be done in SQL. Something like:CREATE TABLE dbo.salary (
name VARCHAR(20) NOT NULL
, salary MONEY NOT NULL
)

INSERT dbo.salary (salary, [name]) VALUES (50, 'Phillip')
INSERT dbo.salary (salary, [name]) VALUES (50, 'Alex')
INSERT dbo.salary (salary, [name]) VALUES (50, 'David')
INSERT dbo.salary (salary, [name]) VALUES (40, 'Emily')
INSERT dbo.salary (salary, [name]) VALUES (30, 'Tom')
INSERT dbo.salary (salary, [name]) VALUES (30, 'May')
INSERT dbo.salary (salary, [name]) VALUES (30, 'Ada')
INSERT dbo.salary (salary, [name]) VALUES (20, 'Queenie')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Ellie')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Candy')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Hazel')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Stella')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Tanya')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Jacky')

INSERT dbo.salary (salary, [name]) VALUES ( 5, 'Sam')
INSERT dbo.salary (salary, [name]) VALUES ( 5, 'Sammy')

INSERT dbo.salary (salary, [name]) VALUES ( 3, 'Clyde')

SELECT TOP 5 r.salary, Min(s1.[name]) + Coalesce( ', ' + Min(s2.[name]), '')
+ Coalesce( ', ' + Min(s3.[name]), '') + Coalesce( ', ' + Min(s4.[name]), '')
+ Coalesce( ', ' + Min(s5.[name]), '')
FROM (SELECT TOP 5 salary FROM dbo.salary GROUP BY salary ORDER BY salary DESC) AS r
JOIN (SELECT salary, [name] FROM dbo.salary) AS s1
ON (s1.salary = r.salary)
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s2
ON (s2.salary = r.salary
AND s2.[name] > s1.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s3
ON (s3.salary = r.salary
AND s3.[name] > s2.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s4
ON (s4.salary = r.salary
AND s4.[name] > s3.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s5
ON (s5.salary = r.salary
AND s5.[name] > s4.[name])
GROUP BY r.salary
ORDER BY r.salary DESC-PatP

GROUP BY Clause Problem.

I have a query that has a number of fields being returned from two tables (a
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Yes.

Friday, March 30, 2012

grouping similar data

Monday, March 26, 2012

grouping data to a field

Friday, March 23, 2012

Grouping a report

Wednesday, March 21, 2012

Group sums wrong in Matrix

Monday, March 19, 2012

Group formatting question

Group formatting question

Group formatting question

Sunday, February 26, 2012

Group By Count

GROUP BY Clause Problem.

Firehose mode

Blog Archive

About Me