Firehose mode: create

Showing posts with label create. Show all posts

Friday, March 30, 2012

Grouping several items in one group

Hi Everyone,
I am new to reporting services and I am trying to create groups which
contains more then one code .
Table
Name, Code, Amount
paper 1101 Â£10
Pens 1102 Â£5
Shoes 2512 Â£20
Clothes 3455 Â£5
I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
group 2 with total.
At the moment I can only seem to group each one individually.
Please help.
John
--
John HoYou question is more of a SQL problem, and there is more than one way
to solve your problem.
SELECT 'GRP1' as groupcode, amount from paper where code =3D 1101
UNION
SELECT 'GRP1' as groupcode, amount from pens where code =3D 1102
UNION
SELECT 'GRP2' as groupcode, amount from shoes where code =3D 2512
UNION
SELECT 'GRP2' as groupcode, amount from clothes where code =3D 3455
save the above query to a View object. When you open the view, you'll
see this:
<pre>
groupcode | amount
GRP1 | =A310
GRP1 | =A35
GRP2 | =A320
GRP2 | =A35
</pre>
Now you can group & sum on your view for your report. I'm sure there
are more elegant solutions (perhaps using StoredProcs), but this is
dirty and quick...heh.
On Apr 7, 11:05 am, Learner <Lear...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I am new to reporting services and I am trying to create groups which
> contains more then one code .
> Table
> Name, Code, Amount
> paper 1101 =A310
> Pens 1102 =A35
> Shoes 2512 =A320
> Clothes 3455 =A35
> I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
> group 2 with total.
> At the moment I can only seem to group each one individually.
> Please help.
> John
> --
> John Ho

Grouping records - HOW TO

Hi,
Is there a way that I can get the same sort of output as when you create a
relationship between two tables, but with only one table?
With a dataset containing two (or more) tables that has a data relation
added, you get a resultant output (say a datagrid) that groups by the column
detailed in the relationship. When the datagrid is displayed it shows each
parent record (row) with a '+' next to it. When selected you can then
display the related child records.
I have a single table with multiple records. Let's say one field is name.
There may be multiple records for each person who is displayed in the name
field. Instead of doing a simple sort and showing all records at the same
time, I would like to have the '+', and only show the persons name once. I
could then expand that record to show all for that person.
Is this possible?
I am using VB.Net, windows display (not IE), and sql server.
Hope there is an answer out there............even if it is NO ;-)
Rgds, PhilPhil,
use
SELECT DISTINCT name FROM table
for the first DataTable and
SELECT name, other_cols_needed FROM table
for the second DataTable.
Then add a DataRelation on the name column.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Phil" <Phil@.nospam.com> wrote in message
news:ddkugr$a1h$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Hi,
> Is there a way that I can get the same sort of output as when you create a
> relationship between two tables, but with only one table?
> With a dataset containing two (or more) tables that has a data relation
> added, you get a resultant output (say a datagrid) that groups by the
> column detailed in the relationship. When the datagrid is displayed it
> shows each parent record (row) with a '+' next to it. When selected you
> can then display the related child records.
> I have a single table with multiple records. Let's say one field is name.
> There may be multiple records for each person who is displayed in the name
> field. Instead of doing a simple sort and showing all records at the same
> time, I would like to have the '+', and only show the persons name once. I
> could then expand that record to show all for that person.
> Is this possible?
> I am using VB.Net, windows display (not IE), and sql server.
> Hope there is an answer out there............even if it is NO ;-)
> Rgds, Phil
>|||Dejan,
Thanks for the response but I only have ONE table, as stated. If I had two
tables then it wouldn't be a problem for me. Can it be done with one table?
Cheers, Phil
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23r89FNBoFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Phil,
> use
> SELECT DISTINCT name FROM table
> for the first DataTable and
> SELECT name, other_cols_needed FROM table
> for the second DataTable.
> Then add a DataRelation on the name column.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Phil" <Phil@.nospam.com> wrote in message
> news:ddkugr$a1h$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||> Thanks for the response but I only have ONE table, as stated. If I had two
> tables then it wouldn't be a problem for me. Can it be done with one
> table?
You can use two selects to fill two DataTable objects from a single SQL
table.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

grouping question

hey all,
i have 2 tables. i'm grouping on table1 i'd like to join this to table2
which would create 1 to 1 relationship. when add extra fields from table2 it
forces me to group by these fields as well. Can someone please explain this
concept to me?
thanks,
rodcharCould you give better specs?
http://www.aspfaq.com/5006
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:DAD412E6-9F72-42D2-8220-A8905E863944@.microsoft.com...
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to table2
> which would create 1 to 1 relationship. when add extra fields from table2
> it
> forces me to group by these fields as well. Can someone please explain
> this
> concept to me?
> thanks,
> rodchar|||Can you post what you are doing to have an idea of what you are talking abou
t?
AMB
"rodchar" wrote:

> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to table2
> which would create 1 to 1 relationship. when add extra fields from table2
it
> forces me to group by these fields as well. Can someone please explain thi
s
> concept to me?
> thanks,
> rodchar|||rodchar wrote:
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to
> table2 which would create 1 to 1 relationship. when add extra fields
> from table2 it forces me to group by these fields as well. Can
> someone please explain this concept to me?
> thanks,
> rodchar
All columns must appear in a group by clause unless you are using an
aggregate. From BOL: "When GROUP BY is specified, either each column in
any non-aggregate expression in the select list should be included in
the GROUP BY list, or the GROUP BY expression must match exactly the
select list expression."
If you were allowed to leave a column off the Group By clause, what
value would SQL Server use for the result set (assuming there were
multiple matches)?
You may be able to use a derived table to do what you want, but as Aaron
mentioned, we need some more details.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The GROUP BY clause is explained here:
http://msdn.microsoft.com/library/d...r />
_9sfo.asp
ML|||thanks David and everyone this helped.
"David Gugick" wrote:

> rodchar wrote:
> All columns must appear in a group by clause unless you are using an
> aggregate. From BOL: "When GROUP BY is specified, either each column in
> any non-aggregate expression in the select list should be included in
> the GROUP BY list, or the GROUP BY expression must match exactly the
> select list expression."
> If you were allowed to leave a column off the Group By clause, what
> value would SQL Server use for the result set (assuming there were
> multiple matches)?
> You may be able to use a derived table to do what you want, but as Aaron
> mentioned, we need some more details.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Grouping in Table

Your help on this could make my day a better one ....

I was trying to create groups on a table report item in SRS 2005 report. The criterion I'm using in one group is exact opposite of the criterion I have in the next group. My dataset has records which fulfill both criteria but my table displays only records for the first group.

Is there a limit on the number of groups we can use in SRS reports and/or on building criteria for filters?

Thanks,

Samtadsh

There is no limit on the number of groups.

Can you please explain you problem in detail?

Priyank

|||

Thanks for you're fast response.

I'll try to explain it better.

I have a filter for group A which says Fields!col1.Value = xxxx. Another group, B, has a filter Fields!col1.Value != xxxx.

Can I display the output of these filters in one table?

Currently, I can see the result of group A but not that of group B.

samtadsh

|||

One of us is confused (and it is probably me <g>).

By "filter" do you mean the "Group on" expression, or are these really filters?

The short answer is "yes it is possible to do what you want" but I'm not entirely sure about what your expectations are of groups, and whether that is the correct way to do what you're trying to do.

I described a similar situation in a recent post, actually (http://spacefold.com/lisa/?date=2007-08-09) which calls it "multiple detail bands" for reasons that are irrelevant here <s>.

The part that is relevant here is that if all you want to do is filter the items first by one set and then by the other set, all you really need to do is create a sort order (forget about filters and groups) to do what you want, forcing some records "up" and some "down"

In my example in the post, the order clause, included among others for the table, was this:

=IIF(Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_","A","Z")

... yours might be IIF(Fields!col1.Value = xxxxx,"A","Z")

... does this ring any bells for you?

>L<

|||

I was refering the actual filters not the 'group on' ones.

But your suggestion of sorting helped me a lot.

I have another problem though. I need to display multiple details with parent-child associations. One set of record in a table needs to have the parent record in one row(top most) and the child records in the following rows but evenly split vertically - all in one table. Something like this:

Parent1: Parent1.Value

Child11: Child11.Value Child12: Child12.Value

Child13: Child11.Value Child14: Child14.Value

Child15: Child15.Value

Parent2: Parent2.Value

Child21: Child21.Value Child22: Child22.Value

Child23: Child23.Value Child24: Child24.Value

The problem I have is all the data is in one dataset and if I try to show it like above, I can only bind one child record per row. Even if the data comes from separate datasets, I will have the same issue. All child values come from the same column in the dataset and Parent values from another column.

Is there a way to do this kind of layout in using table report item?|||

OK -- should I assume you have already read the blog post on this subject that I referred to in my last message ?

-- assuming so, I think that your layout is actually easier than the one I describe there if your data is multiple children all "flattened out" into one dataset. But I am not positive, so can you give me an example SELECT from the parent and 2 children, with a couple of sample rows of what the data table looks like?

>L<

|||

The format of the data looks like this. I didn't show all the columns but these are good samples.

PARENT CHILD

PID NAME CID ROLE PERSONNEL_NAME
- -- -
8 Company1 5 Accountant Jerry Maguire

8 Company1 2 Accountant Doe John

8 Company1 4 Manager Smith John

8 Company1 9 Manager John Q

3 Company2 12 Manager Stuart Little

3 Company2 7 Accountant Erin Brockovich

3 Company2 6 Sales Person Woody Allen

6 Company3 8 Manager Doe John

Thanks.

Samtadsh

|||

SO is the Parent joining to a position table, and the Role value represents your multiple children, is that what you mean?

I thought when you said it was a multi-child that it was separate child tables and that your data would look "flatter".

Do you have a known number of roles (for example, you show three above, which represent three children)? Or do you think of it as "one child per role" where it is dynamic?

As you see, I am guessing. I would feel much better if you supplied a sample SQL statement <s>, so that I could understand what is "child 1" and what is "child 2" from your POV In the data table above, which you want to separate into your separate columns. I have some ideas about how you might do this, but I don't know how to relate your data example to the layout you supplied earlier. I'm thinking the SQL would help, if you don't think it will, can you tell me what columns you want each row above to appear in ?

Or am I terminally confused? <g>

>L<

|||I gave you what I get as a final result set from my stored procedure(one flat table). This will appear as a dataset in my report. I have to find a way to get that layout working off of this flat data. I just labled it 'Parent' and 'Child' on top to give you an idea which set of columns shoud be considered parent and child.

Roles are various. I can't limit myself with some number of roles. The same goes for Personnel_Names. CID will be unique(combination of ROLE and PERSONNEL_NAME will be unique).

I hope this makes it a bit more clear of what I'm trying to say.

Sorry for confusing you.

Thanks for taking the time to respond to my vague questions.

Samtadsh

|||

Lisa,

I think you're making it more complicated than it needs to be and getting a little confused.

From what I've read and seen of the data, the dataset consists of employees who work for a particular company. Withing this company they play a role. I believe the parent ID = company ID and child ID = employee ID and role is just an attribute of an employee.

The layout above seems to be groupped by company and for each company the requirement is to display the employees in multiple columns i.e. more thatn one employee per row.

The problem, as I see it, is that the default behaviour will display 1 employee per row.

I don't mean to butt into your thread but I hope this helps to explain the problem.

In terms of a solution, one thing that springs to mind is a matrix nested in the table with the employee on the columns groupped by =Ceiling(RowNumber(Nothing)/2)

Sorry but I'm not in a position to try this out right now.

Hope this helps.

|||

Sorry, Adam, I am just tring to do what the individual indicated he wanted done. I see the data the same way you do, FWIW, but the trouble is that doesn't explain his/her original question.

And you're not "butting in", and it's not "my thread" <s>. I am glad that somebody else reads the data, as it is presented, the same way I do.

I don't think your solution will work out for him, although matrix was the first thing that came to mind for me as well. I think Pivot might be more flexible here...

The problem for any such solution is what relationship the side-by-side elements really have, though. It bothers me to show stuff going across that has no real relationship to each other. That's why I asked about possible role hierarchies.

>L<

|||

So there are multiple rows (I'm not asking you to limit them, just trying to understand your layout) but you only want two columns -- are you trying to use the order/sort value to determine what goes into the two separate columns, or is this completely separate from your original question <g>?

Do you really want exactly two columns -- Or do you want one column per role, like in a pivot table or a matrix? The latter makes more sense, because you won't be showing the data in a way that that implies a relationship between employees that is not real...

>L<

|||

I have two cases in which this layout should be utilized. IT will be in the same report but different tables.

Case 1: There will be two columns across which employee name together with thier role gets displayed evenly starting from the left column. There is no role heirarchy and no limitation on type of roles. The distribution of the employees should be like this:

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role11) - Employee11 | (Role11)- Employee12

(Role13)-Employee13 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role21)-Employee21 | (Role22)- Employee22

(Role23)-Employee23 | (Role24)- Employee24

Case 2: There will be two columns across which employee name together with thier role gets displayed evenly starting from the left column. There are two roles. The distribution of the employees should be like this:

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA) - EmployeeA1 | (RoleB)- EmployeeB1

- EmployeeA2 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA)-EmployeeA1 | (RoleB)- EmployeeB1

-EmployeeA2 | - EmployeeB2

Thanks,

Samtadsh

|||

I think I have actually done this. I'm going to show you how I would do the first one in pure SQL -- I don't have your data but I believe I have modelled the same situation. I think the way to do the second layout is very similar and I will describe it briefly here as well.

This is what my data looks like:

1) an order header table that will serve as the parent/group
2) an order detail table with line item #s in it, which will serve as the child.

The simple SELECT statement below (order -> line item) is the way I am modeling your relationship company -> employee

Code Snippet

SELECT sales_no, line_no FROM orderheader H
JOIN orderdetail D ON H.sales_no = D.sales_no

OK?

To get your first layout, my query creates a result with three columns, like this -- I will explain the critical details below:

Code Snippet

select h.sales_no, Col1.line_no As Col1, Col2.line_no AS Col2

from orderheader h
join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col1

on h.sales_no = col1.sales_no and col1.orderrank % 2 = 1

left join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col2

on h.sales_no = col2.sales_no

and (col2.orderrank = col1.orderrank + 1 )

order by h.sales_no, col1.line_no

Here are the critical things to notice in this query:

The first (inner) join gives you all the odd-number line numbers, from the first line number on up (Employee #s 1,3,5 and so on in your case).

The second join is an *outer* join. It will give you Employee #s 2,4,6 and so on but will be null on the last row where the total number of employees is odd.

I have highlighted the three ordering/sorting components because I'm pretty sure that they have to match up to get this to work properly.

|||

Thanks Lisa.

I think the idea would work for my problem too. Unfortunately, I have SQL2000 back-end.(I should have mentioned this from the on set though ... sorry about that.)

It looks like row_number() and partition by are SQL2005 features. I'm looking into ways to acheive similar results using SQL2000 constructs. If you know have this implementation in SQL2000, I would appreciate it greatly.

Thanks.

S.T

Wednesday, March 28, 2012

Grouping problem

Ok, I need to create an invoice for different plans that customers are subscribed to but I can't seem to group it in crystal properly. The invoice needs to list the invoice # at the top, followed by all of the different plans the customer is subscribed to, then a quick one time sentence that reads: "Fees for services in connection with:", and finally I need to display the services.

So basically, I may get data from my database that looks like the following:
1 | 51271 (inv #) | Pension Plan (plan name) | Yearly Admin Fee (service desc.)
2 | 51271 (inv #) | Cafeteria Plan(plan name)| Government Forms (service desc.)

The report would then look something like this:
----------------
Invoice #
---------------
Plan(s):
Pension Plan
Cafeteria Plan
--------------
Fees for services in connection with:
--------------

-Yearly Admin Fee
-Government Forms
----------------

Does anyone know how I could group my report this way or can you point me to a tutorial that can help me out?

Thanks in advance.

-Goalie35Ok...try grouping by invoice number, list plans in the details section. In group footer, put a subreport with the fees for services linked to the main report based on invoice number.

Let me know if this helps :)

Grouping my months in a chart

I'm trying to create a chart with monthly comparisons, but when i throw my months into the chart it groups them under one another instead of next to one another. Please Help!!

Kind regards

Carel GreavesARE YOU USING A TABLE OR MATRIX AS YOUR DATA REGION?|||I'm using a standard line chart, no tables in this report, only charts. I had to do two pie charts for Jan2007 which came out fine but now i need to do a past 4 month trend.

Here's my SQL Code if it will help, i created temp tables for each month and then i just select the values how i needed them.

DECLARE @.OctTable Table (UID INT Identity(1,1), month_Name Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), Sales FLOAT, Quantity FLOAT)

INSERT INTO @.OctTable (month_Name, [Description], Manufacture_Name, Sales, Quantity)
SELECT DISTINCT DT.Month_Name, DP.[Description], DP.Manufacture_Name, SUM(FD.Cost), SUM(FD.Qty)
FROM FACT_Dispensary FD, DIM_Product DP, DIM_Time DT
WHERE FD.Time_key = DT.Time_Key
AND DP.Product_Key = FD.Product_Key
AND DT.[Year] = 2006
AND DT.[Month] = 10
AND DP.Product_Key IN ('86943',
'86944',
'86945',
'5656150',
'5656151',
'5656152',
'5915879',
'5915880',
'5915881',
'9347761',
'9347762',
'9347763',
'9751449',
'9751450',
'9751451',
'12701005',
'12701006',
'13105698',
'13105699',
'13105700',
'4978004',
'4978005',
'8160424',
'127962',
'127963',
'127964',
'2853',
'2854',
'13627',
'4765413',
'4765414',
'102687',
'102688',
'2171126',
'3518813')
GROUP BY DP.Manufacture_Name, DP.[Description], DT.Month_name

--SELECT * from @.OctTable

DECLARE @.NovTable Table (UID INT Identity(1,1), month_Name Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), Sales FLOAT, Quantity FLOAT)

INSERT INTO @.NovTable (month_Name, [Description], Manufacture_Name, Sales, Quantity)
SELECT DISTINCT DT.Month_Name, DP.[Description], DP.Manufacture_Name, SUM(FD.Cost), SUM(FD.Qty)
FROM FACT_Dispensary FD, DIM_Product DP, DIM_Time DT
WHERE FD.Time_key = DT.Time_Key
AND DP.Product_Key = FD.Product_Key
AND DT.[Year] = 2006
AND DT.[Month] = 11
AND DP.Product_Key IN ('86943',
'86944',
'86945',
'5656150',
'5656151',
'5656152',
'5915879',
'5915880',
'5915881',
'9347761',
'9347762',
'9347763',
'9751449',
'9751450',
'9751451',
'12701005',
'12701006',
'13105698',
'13105699',
'13105700',
'4978004',
'4978005',
'8160424',
'127962',
'127963',
'127964',
'2853',
'2854',
'13627',
'4765413',
'4765414',
'102687',
'102688',
'2171126',
'3518813')
GROUP BY DP.Manufacture_Name, DP.[Description], DT.Month_name

--SELECT * from @.NovTable

DECLARE @.DecTable Table (UID INT Identity(1,1), month_Name Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), Sales FLOAT, Quantity FLOAT)

INSERT INTO @.DecTable (month_Name, [Description], Manufacture_Name, Sales, Quantity)
SELECT DISTINCT DT.Month_Name, DP.[Description], DP.Manufacture_Name, SUM(FD.Cost), SUM(FD.Qty)
FROM FACT_Dispensary FD, DIM_Product DP, DIM_Time DT
WHERE FD.Time_key = DT.Time_Key
AND DP.Product_Key = FD.Product_Key
AND DT.[Year] = 2006
AND DT.[Month] = 12
AND DP.Product_Key IN ('86943',
'86944',
'86945',
'5656150',
'5656151',
'5656152',
'5915879',
'5915880',
'5915881',
'9347761',
'9347762',
'9347763',
'9751449',
'9751450',
'9751451',
'12701005',
'12701006',
'13105698',
'13105699',
'13105700',
'4978004',
'4978005',
'8160424',
'127962',
'127963',
'127964',
'2853',
'2854',
'13627',
'4765413',
'4765414',
'102687',
'102688',
'2171126',
'3518813')
GROUP BY DP.Manufacture_Name, DP.[Description], DT.Month_name

--SELECT * from @.DecTable

DECLARE @.JanTable Table (UID INT Identity(1,1), month_Name Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), Sales FLOAT, Quantity FLOAT)

INSERT INTO @.JanTable (month_Name, [Description], Manufacture_Name, Sales, Quantity)
SELECT DISTINCT DT.Month_Name, DP.[Description], DP.Manufacture_Name, SUM(FD.Cost), SUM(FD.Qty)
FROM FACT_Dispensary FD, DIM_Product DP, DIM_Time DT
WHERE FD.Time_key = DT.Time_Key
AND DP.Product_Key = FD.Product_Key
AND DT.[Year] = 2007
AND DT.[Month] = 1
AND DP.Product_Key IN ('86943',
'86944',
'86945',
'5656150',
'5656151',
'5656152',
'5915879',
'5915880',
'5915881',
'9347761',
'9347762',
'9347763',
'9751449',
'9751450',
'9751451',
'12701005',
'12701006',
'13105698',
'13105699',
'13105700',
'4978004',
'4978005',
'8160424',
'127962',
'127963',
'127964',
'2853',
'2854',
'13627',
'4765413',
'4765414',
'102687',
'102688',
'2171126',
'3518813')
GROUP BY DP.Manufacture_Name, DP.[Description], DT.Month_name

--SELECT * from @.JanTable

DECLARE @.MyTable TABLE (UID INT Identity(1,1), October Varchar(255), November Varchar(255), December Varchar(255), January Varchar(255), [Description] VARCHAR(255), Manufacture_Name VARCHAR(255), OctSales FLOAT, OctQuantity FLOAT, NovSales FLOAT, NovQuantity FLOAT, DecSales FLOAT, DecQuantity FLOAT, JanSales FLOAT, JanQuantity FLOAT, JanTotalSales FLOAT, JanTotalQuantity FLOAT)

INSERT INTO @.MyTable (October, November, December, January, [Description], Manufacture_Name, OctSales, OctQuantity, NovSales, NovQuantity, DecSales, DecQuantity, JanSales, JanQuantity)--, JanTotalSales, JanTotalQuantity)

SELECT DISTINCT OT.Month_Name, NT.Month_Name, DT.Month_Name, JT.Month_Name, OT.[Description], OT.Manufacture_Name, OT.Sales, OT.Quantity, NT.Sales,NT.Quantity, DT.Sales, DT.Quantity, JT.Sales, JT.Quantity--, SUM(JanSales), SUM(JanQuantity)
FROM @.OctTable OT, @.NovTable NT, @.DecTable DT, @.JanTable JT
WHERE JT.UID = OT.UID
AND JT.UID = NT.UID
AND JT.UID = DT.UID
GROUP BY OT.Manufacture_Name, OT.[Description] ,OT.Sales, OT.Quantity, NT.Sales,NT.Quantity, DT.Sales, DT.Quantity, JT.Sales, JT.Quantity, OT.Month_Name, NT.Month_Name, DT.Month_Name, JT.Month_Name

SELECT October, November, December, January, [Description], Manufacture_Name, OctSales, OctQuantity, NovSales, NovQuantity, DecSales, DecQuantity, JanSales, JanQuantity, (SELECT SUM(JanSales) where Manufacture_Name like '%ADCOCK INGRAM%') AS 'Adock Sales', (SELECT SUM(JanQuantity) where Manufacture_Name like '%ADCOCK INGRAM%') AS 'Adcock Quantity'
FROM @.MyTable
GROUP BY [Description], Manufacture_Name, OctSales, OctQuantity, NovSales, NovQuantity, DecSales, DecQuantity, JanSales, JanQuantity, October, November, December, January|||I came right again, thanks. All i did was create a column that had all months_names in, instead of 4 columns with a single month_name

Grouping in Table

Your help on this could make my day a better one ....

Is there a limit on the number of groups we can use in SRS reports and/or on building criteria for filters?

Thanks,

Samtadsh

There is no limit on the number of groups.

Can you please explain you problem in detail?

Priyank

|||

Thanks for you're fast response.

I'll try to explain it better.

I have a filter for group A which says Fields!col1.Value = xxxx. Another group, B, has a filter Fields!col1.Value != xxxx.

Can I display the output of these filters in one table?

Currently, I can see the result of group A but not that of group B.

samtadsh

|||

One of us is confused (and it is probably me <g>).

By "filter" do you mean the "Group on" expression, or are these really filters?

The short answer is "yes it is possible to do what you want" but I'm not entirely sure about what your expectations are of groups, and whether that is the correct way to do what you're trying to do.

I described a similar situation in a recent post, actually (http://spacefold.com/lisa/?date=2007-08-09) which calls it "multiple detail bands" for reasons that are irrelevant here <s>.

In my example in the post, the order clause, included among others for the table, was this:

=IIF(Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_","A","Z")

... yours might be IIF(Fields!col1.Value = xxxxx,"A","Z")

... does this ring any bells for you?

>L<

|||

I was refering the actual filters not the 'group on' ones.

But your suggestion of sorting helped me a lot.

Parent1: Parent1.Value

Child11: Child11.Value Child12: Child12.Value

Child13: Child11.Value Child14: Child14.Value

Child15: Child15.Value

Parent2: Parent2.Value

Child21: Child21.Value Child22: Child22.Value

Child23: Child23.Value Child24: Child24.Value

Is there a way to do this kind of layout in using table report item?|||

OK -- should I assume you have already read the blog post on this subject that I referred to in my last message ?

>L<

|||

The format of the data looks like this. I didn't show all the columns but these are good samples.

PARENT CHILD

PID NAME CID ROLE PERSONNEL_NAME
- -- -
8 Company1 5 Accountant Jerry Maguire

8 Company1 2 Accountant Doe John

8 Company1 4 Manager Smith John

8 Company1 9 Manager John Q

3 Company2 12 Manager Stuart Little

3 Company2 7 Accountant Erin Brockovich

3 Company2 6 Sales Person Woody Allen

6 Company3 8 Manager Doe John

Thanks.

Samtadsh

|||

SO is the Parent joining to a position table, and the Role value represents your multiple children, is that what you mean?

I thought when you said it was a multi-child that it was separate child tables and that your data would look "flatter".

Do you have a known number of roles (for example, you show three above, which represent three children)? Or do you think of it as "one child per role" where it is dynamic?

Or am I terminally confused? <g>

>L<

Roles are various. I can't limit myself with some number of roles. The same goes for Personnel_Names. CID will be unique(combination of ROLE and PERSONNEL_NAME will be unique).

I hope this makes it a bit more clear of what I'm trying to say.

Sorry for confusing you.

Thanks for taking the time to respond to my vague questions.

Samtadsh

|||

Lisa,

I think you're making it more complicated than it needs to be and getting a little confused.

The layout above seems to be groupped by company and for each company the requirement is to display the employees in multiple columns i.e. more thatn one employee per row.

The problem, as I see it, is that the default behaviour will display 1 employee per row.

I don't mean to butt into your thread but I hope this helps to explain the problem.

In terms of a solution, one thing that springs to mind is a matrix nested in the table with the employee on the columns groupped by =Ceiling(RowNumber(Nothing)/2)

Sorry but I'm not in a position to try this out right now.

Hope this helps.

|||

Sorry, Adam, I am just tring to do what the individual indicated he wanted done. I see the data the same way you do, FWIW, but the trouble is that doesn't explain his/her original question.

And you're not "butting in", and it's not "my thread" <s>. I am glad that somebody else reads the data, as it is presented, the same way I do.

I don't think your solution will work out for him, although matrix was the first thing that came to mind for me as well. I think Pivot might be more flexible here...

>L<

|||

>L<

|||

I have two cases in which this layout should be utilized. IT will be in the same report but different tables.

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role11) - Employee11 | (Role11)- Employee12

(Role13)-Employee13 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role21)-Employee21 | (Role22)- Employee22

(Role23)-Employee23 | (Role24)- Employee24

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA) - EmployeeA1 | (RoleB)- EmployeeB1

- EmployeeA2 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA)-EmployeeA1 | (RoleB)- EmployeeB1

-EmployeeA2 | - EmployeeB2

Thanks,

Samtadsh

|||

This is what my data looks like:

1) an order header table that will serve as the parent/group
2) an order detail table with line item #s in it, which will serve as the child.

The simple SELECT statement below (order -> line item) is the way I am modeling your relationship company -> employee

Code Snippet

SELECT sales_no, line_no FROM orderheader H
JOIN orderdetail D ON H.sales_no = D.sales_no

OK?

To get your first layout, my query creates a result with three columns, like this -- I will explain the critical details below:

Code Snippet

select h.sales_no, Col1.line_no As Col1, Col2.line_no AS Col2

from orderheader h
join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col1

on h.sales_no = col1.sales_no and col1.orderrank % 2 = 1

left join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col2

on h.sales_no = col2.sales_no

and (col2.orderrank = col1.orderrank + 1 )

order by h.sales_no, col1.line_no

Here are the critical things to notice in this query:

The first (inner) join gives you all the odd-number line numbers, from the first line number on up (Employee #s 1,3,5 and so on in your case).

The second join is an *outer* join. It will give you Employee #s 2,4,6 and so on but will be null on the last row where the total number of employees is odd.

I have highlighted the three ordering/sorting components because I'm pretty sure that they have to match up to get this to work properly.

|||

Thanks Lisa.

I think the idea would work for my problem too. Unfortunately, I have SQL2000 back-end.(I should have mentioned this from the on set though ... sorry about that.)

Thanks.

S.T

Grouping in Table

Your help on this could make my day a better one ....

Is there a limit on the number of groups we can use in SRS reports and/or on building criteria for filters?

Thanks,

Samtadsh

There is no limit on the number of groups.

Can you please explain you problem in detail?

Priyank

|||

Thanks for you're fast response.

I'll try to explain it better.

I have a filter for group A which says Fields!col1.Value = xxxx. Another group, B, has a filter Fields!col1.Value != xxxx.

Can I display the output of these filters in one table?

Currently, I can see the result of group A but not that of group B.

samtadsh

|||

One of us is confused (and it is probably me <g>).

By "filter" do you mean the "Group on" expression, or are these really filters?

The short answer is "yes it is possible to do what you want" but I'm not entirely sure about what your expectations are of groups, and whether that is the correct way to do what you're trying to do.

I described a similar situation in a recent post, actually (http://spacefold.com/lisa/?date=2007-08-09) which calls it "multiple detail bands" for reasons that are irrelevant here <s>.

In my example in the post, the order clause, included among others for the table, was this:

=IIF(Left(UCase(Fields!RDLLayoutName.Value),4) = "IMG_","A","Z")

... yours might be IIF(Fields!col1.Value = xxxxx,"A","Z")

... does this ring any bells for you?

>L<

|||

I was refering the actual filters not the 'group on' ones.

But your suggestion of sorting helped me a lot.

Parent1: Parent1.Value

Child11: Child11.Value Child12: Child12.Value

Child13: Child11.Value Child14: Child14.Value

Child15: Child15.Value

Parent2: Parent2.Value

Child21: Child21.Value Child22: Child22.Value

Child23: Child23.Value Child24: Child24.Value

Is there a way to do this kind of layout in using table report item?|||

OK -- should I assume you have already read the blog post on this subject that I referred to in my last message ?

>L<

|||

The format of the data looks like this. I didn't show all the columns but these are good samples.

PARENT CHILD

PID NAME CID ROLE PERSONNEL_NAME
- -- -
8 Company1 5 Accountant Jerry Maguire

8 Company1 2 Accountant Doe John

8 Company1 4 Manager Smith John

8 Company1 9 Manager John Q

3 Company2 12 Manager Stuart Little

3 Company2 7 Accountant Erin Brockovich

3 Company2 6 Sales Person Woody Allen

6 Company3 8 Manager Doe John

Thanks.

Samtadsh

|||

SO is the Parent joining to a position table, and the Role value represents your multiple children, is that what you mean?

I thought when you said it was a multi-child that it was separate child tables and that your data would look "flatter".

Do you have a known number of roles (for example, you show three above, which represent three children)? Or do you think of it as "one child per role" where it is dynamic?

Or am I terminally confused? <g>

>L<

Roles are various. I can't limit myself with some number of roles. The same goes for Personnel_Names. CID will be unique(combination of ROLE and PERSONNEL_NAME will be unique).

I hope this makes it a bit more clear of what I'm trying to say.

Sorry for confusing you.

Thanks for taking the time to respond to my vague questions.

Samtadsh

|||

Lisa,

I think you're making it more complicated than it needs to be and getting a little confused.

The layout above seems to be groupped by company and for each company the requirement is to display the employees in multiple columns i.e. more thatn one employee per row.

The problem, as I see it, is that the default behaviour will display 1 employee per row.

I don't mean to butt into your thread but I hope this helps to explain the problem.

In terms of a solution, one thing that springs to mind is a matrix nested in the table with the employee on the columns groupped by =Ceiling(RowNumber(Nothing)/2)

Sorry but I'm not in a position to try this out right now.

Hope this helps.

|||

Sorry, Adam, I am just tring to do what the individual indicated he wanted done. I see the data the same way you do, FWIW, but the trouble is that doesn't explain his/her original question.

And you're not "butting in", and it's not "my thread" <s>. I am glad that somebody else reads the data, as it is presented, the same way I do.

I don't think your solution will work out for him, although matrix was the first thing that came to mind for me as well. I think Pivot might be more flexible here...

>L<

|||

>L<

|||

I have two cases in which this layout should be utilized. IT will be in the same report but different tables.

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role11) - Employee11 | (Role11)- Employee12

(Role13)-Employee13 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(Role21)-Employee21 | (Role22)- Employee22

(Role23)-Employee23 | (Role24)- Employee24

COL1 | COL2

Parent1: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA) - EmployeeA1 | (RoleB)- EmployeeB1

- EmployeeA2 |

_

Parent2: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

(RoleA)-EmployeeA1 | (RoleB)- EmployeeB1

-EmployeeA2 | - EmployeeB2

Thanks,

Samtadsh

|||

This is what my data looks like:

1) an order header table that will serve as the parent/group
2) an order detail table with line item #s in it, which will serve as the child.

The simple SELECT statement below (order -> line item) is the way I am modeling your relationship company -> employee

Code Snippet

SELECT sales_no, line_no FROM orderheader H
JOIN orderdetail D ON H.sales_no = D.sales_no

OK?

To get your first layout, my query creates a result with three columns, like this -- I will explain the critical details below:

Code Snippet

select h.sales_no, Col1.line_no As Col1, Col2.line_no AS Col2

from orderheader h
join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col1

on h.sales_no = col1.sales_no and col1.orderrank % 2 = 1

left join
(select sales_no, line_no, row_number() over
( partition by sales_no order by line_no) as orderrank from orderdetail ) Col2

on h.sales_no = col2.sales_no

and (col2.orderrank = col1.orderrank + 1 )

order by h.sales_no, col1.line_no

Here are the critical things to notice in this query:

The first (inner) join gives you all the odd-number line numbers, from the first line number on up (Employee #s 1,3,5 and so on in your case).

The second join is an *outer* join. It will give you Employee #s 2,4,6 and so on but will be null on the last row where the total number of employees is odd.

I have highlighted the three ordering/sorting components because I'm pretty sure that they have to match up to get this to work properly.

|||

Thanks Lisa.

I think the idea would work for my problem too. Unfortunately, I have SQL2000 back-end.(I should have mentioned this from the on set though ... sorry about that.)

Thanks.

S.T

sql

Monday, March 26, 2012

Grouping by Time

Hello,
I am trying to create a query where I can group by the time of day something
happens.
For example, somebody (we don't care who) does something ( 'ev' below). We
capture the date and time this thing happens.
For analysis, a doctor wants to know what times the day these things are
happening. The grouping would be by the hour, counting the number of times
a specific thing happens.
I am not sure how to represent the hourly range. Maybe by a number ? For
example, 12:00 am to 1 am would be '1'. Not sure. I need some advice here.
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
Basically, the output should be
time range ev number of ev's in the time range.
I tried this query, but did not work.
SELECT ev, MIN(dt), COUNT(*)
FROM YourTable
GROUP BY ev, DATEDIFF(HH,'20000101',dt)
Thanks for your time.What about that ?
Select ev,DATEPART(hh,dt),count(*)
From YourTable
Group by ev,DATEPART(hh,dt)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jack" <jack@.jack.net> schrieb im Newsbeitrag
news:Zasge.6007$Ay3.501@.lakeread06...
> Hello,
> I am trying to create a query where I can group by the time of day
> something happens.
> For example, somebody (we don't care who) does something ( 'ev' below).
> We capture the date and time this thing happens.
> For analysis, a doctor wants to know what times the day these things are
> happening. The grouping would be by the hour, counting the number of
> times a specific thing happens.
> I am not sure how to represent the hourly range. Maybe by a number ? For
> example, 12:00 am to 1 am would be '1'. Not sure. I need some advice
> here.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
> Basically, the output should be
> time range ev number of ev's in the time range.
> I tried this query, but did not work.
> SELECT ev, MIN(dt), COUNT(*)
> FROM YourTable
> GROUP BY ev, DATEDIFF(HH,'20000101',dt)
> Thanks for your time.
>|||Try,
use northwind
go
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
SELECT
ev,
MIN(dt),
COUNT(*)
FROM
YourTable
GROUP BY
ev,
convert(char(13), dt, 126)
drop table YourTable
AMB
"Jack" wrote:

> Hello,
> I am trying to create a query where I can group by the time of day somethi
ng
> happens.
> For example, somebody (we don't care who) does something ( 'ev' below). W
e
> capture the date and time this thing happens.
> For analysis, a doctor wants to know what times the day these things are
> happening. The grouping would be by the hour, counting the number of time
s
> a specific thing happens.
> I am not sure how to represent the hourly range. Maybe by a number ? For
> example, 12:00 am to 1 am would be '1'. Not sure. I need some advice here
.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
> Basically, the output should be
> time range ev number of ev's in the time range.
> I tried this query, but did not work.
> SELECT ev, MIN(dt), COUNT(*)
> FROM YourTable
> GROUP BY ev, DATEDIFF(HH,'20000101',dt)
> Thanks for your time.
>
>|||That works great. Thank you.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uOyubrlVFHA.628@.TK2MSFTNGP09.phx.gbl...
> What about that ?
> Select ev,DATEPART(hh,dt),count(*)
> From YourTable
> Group by ev,DATEPART(hh,dt)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Jack" <jack@.jack.net> schrieb im Newsbeitrag
> news:Zasge.6007$Ay3.501@.lakeread06...
>

Grouping By Problems

I'h having some throughput problems, and so, I decided to create a view with the info I need.

The problem now is this: I have to select all the days where there were associations in my clients website in this format: dd/MM/yyyy (xx) where XX is the number of associations on that day. Here is the first code, wich worked but resulted in timeout:

SELECT DISTINCT (CONVERT(varchar, buy_date, 103) + ' (' + CONVERT(varchar(10), (SELECT COUNT(*) FROM user_plan up2 WHERE CONVERT(datetime, CONVERT(varchar, up2.buy_date, 101)) = CONVERT(datetime, CONVERT(varchar, up1.buy_date, 101)))) + ')') AS 'text',

CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value'

FROM user_plan up1

WHERE CONVERT(varchar, buy_date, 101) <= CONVERT(varchar, getdate(), 101)

ORDER BY value DESC

Then I tried to create a view in wich I intented to save the number of associations to avoid the n² complexity of my query...

This is the create view script:

CREATE VIEW quadro_social AS
SELECT COUNT(1) AS total,
CONVERT(VARCHAR, buy_date, 103) as buy_date,
CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value'
FROM user_plan
GROUP BY buy_date

But what happens is, becaus the "buy_date" column is datetime, they are not beign grouped because they have different times, but the same days... How can I group the registers with the same date (dd/MM/yyyy) ignoring the hour, minutes and seconds?

Thanks a lot!

Guilherme Bertini Boettcher

SELECTCONVERT(varchar(10),DATEADD(d,0,DATEDIFF(d,0,buy_date)),103)+' ('+COUNT(*)+')'AS'text',DATEADD(d,0,DATEDIFF(d,0,buy_date))as'value'FROM user_plan up1WHERE buy_date<DATEADD(d,1,DATEDIFF(d,0,getutcdate()))GROUP BYDATEADD(d,0,DATEDIFF(d,0,buy_date))

You can convert the second column to varchar if you want, but I always prefer passing back dates and/or datetimes as a true datetime, since many of my applications are of global scope, and the display format for it is unknown at query time. (Displaying mm/dd/yyyy to american users, dd/mm/yyyy to french & british, dd.mm.yyyy for the rest of europe, etc).

|||

Thanks for your help Motley!

Actually, before you answered I had already solved the problem by using an extra column with the same datetime, only with the time part with zeros on my view. Since that column was actually the value wich represented every registry on that date, I would need that column anyway...

After looking at your answer, actually what you said is far more trustful and independent oh gegraphical locations...

Tahnkls a lot mate!

Friday, March 23, 2012

Grouping by day of the week?

I am trying to create a report that will help with some trending documentation that I have to put together. What I am trying to do is get a report that would show me for every Monday, Tuesday, etc a count of the number calls taken per day for the entire year. I can get my result set easily enough, it is displaying it that I am having problems with.

The that returns is a typical MSSQL date/time field. I have tried using Weekday, weekdayname etc but it keeps saying that the value returned is out of range. How might I go about setting up my grouping to get a return set approximatly like the following example?

Monday
Jan 2 = 200
Jan 9 = 188

Tuesday
Jan 3 = 203
Jan 10 = 220

Wednesday.....

Something along those lines..

Any help or tips are GREATLY appreciated.

Thank you.What I have always done is created a formula called @.day then created a group on the @.day formula, supressed that formula and created another formula called @.dayofweek and layed it over the top of the day formula to show the name of the day of the week.

@.Day
dayofweek({yourdatabase.POST_DATE})
//create a group on this formula.

@.DayOfWeek
weekdayname({@.Day})
//place this formula on the groupheader of the @.Day group.

the field being used with dayofweek needs to be a date or date time field.

Grouping by Age

I have a table Age and need to create Report by Grouping Salesfigures according to Age.
I put the following expression into Grouping and Sorting Properties/General/Filter /Sorting Expression... as well as in Textbox Properties/ values..

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20")

Errormessage: Value expression for textbox "Age" error: Argument not specified for parameter 'FalsePart' of 'Public function IIF(Expression as Boolean, TruePart as Object, Falsepart As Object) As Object'.

Question 2 .
How to return Month as January, February... In correct order?
"DATENAME(mm, Sales.time) AS Month" (Ascending ) returns starting with April, August...
DATEPART(mm, Sales.time) AS Month (Ascending ) returns starting with 1, 10, 11 ...

Answer 1

Your expression is incomplete. As the error message says, you are missing the FlasePart of the nested Iif function, as well a parentheses

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20", ">20" ) )

Can you clarify Quaestion 2. E.g. where are you putting this code, can you paste your query?

|||

For Question 1 you don't have a false path to follow in your second iif.

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20",""))

|||Thank you so much!

SELECT SUM(Cd.Price) AS Sales, DATENAME(mm, Purchase.time) AS Month, Staff.Name
FROM Staff INNER JOIN
Purchase ON Staff.Staff_id = Purchase.Salesperson_id INNER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY DATENAME(mm, Purchase.time), Staff.Name
ORDER BY Month Asc

The outcome is starting with April...
(not with January)?|||Thank you.. still, "Argument not specified for false part". Something I've misunderstood?

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20"),IIF(Fields!Age.Value <31,"21-30"), IIF(Fields!Age.Value < 41,"31-40"),IIF(Fields!Age.Value <51,"41-50",">50"))

I put this value expression In textbox "Age"/Expression, as well as in Grouping Sorting properties /General/Filter/ Sorting|||

Yes you have misunderstood this slightly. The definition of the Iif function is

Iif(<<condition>>, TruePart, FalsePart)

Your expression puts many Iif's all passed into a single Iif. You actually have to nest the Iif's as the FalsePart of the previous Iif

Your expression:
=IIF
( Fields!Age.Value < 16 <- condition
, "<16" <- TruePart
, IIF(Fields!Age.Value <21,"16-20") <- FalsePart nested IIF
, IIF(Fields!Age.Value <31,"21-30") <- Error 4th argument
, IIF(Fields!Age.Value <41,"31-40") <- Error 5th argument
, IIF(Fields!Age.Value <51,"41-50",">50") <- Error 6th argument
)

Correct Expression:
=IIF
( Fields!Age.Value < 16 <- condition
, "<16" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 21 <- condition
,"16-20" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 31 <- condition
, "21-30" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 41 <- condition
, "31-40" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 51 <- condition
, "41-50" <- TruePart
, ">50" <- FalsePart
)
)
)
)
)

just make sure you get the parentheses right and you remove the comments

|||

As far as your query goes, display the name but order by number. for this to work you must include both in the GROUP BY clause

|||

Would it be possible to create a field from a select e.g.

SELECT Age, AgeGroup =

CASE

WHEN (age >= 1 and age <= 3) THEN 'Age 1-3'

WHEN (age >= 4 and age <= 5) THEN 'Age 4-5'

WHEN (age >= 6 and age <= 7) THEN 'Age 6-7'

ELSE 'Over age'

END

FROM tblAge

|||In fact, I prefer this option, give RS less work to do.

Grouping By A Certain Range

I would like to create a report that would group the items by their
price. For example, I would like a group for all of the items that are
between 0-50 and then 51-100 (and so on).
Does anyone know an easy way for doing that using Visual Studio?
ANY information is appreciated. Thanks in advance!you can create a "calulated" field in your dataset like:
=iif(MyPrice > 50, '51-100', '0-50')
then use this new field in your report to render it. (group, filter, sort or
any other option which could use a formula)
"axels22" <ericspreher@.hotmail.com> wrote in message
news:1137429986.049744.315060@.g44g2000cwa.googlegroups.com...
>I would like to create a report that would group the items by their
> price. For example, I would like a group for all of the items that are
> between 0-50 and then 51-100 (and so on).
> Does anyone know an easy way for doing that using Visual Studio?
> ANY information is appreciated. Thanks in advance!
>

Grouping based on multiple fields

I am using crystal report version 7.
I am linking the stored procedure to crystal report and display it's fields. I want to create the group having 2 fields and sum the amount field. At present, I can create group with only one field and sum the amount field based on this field.
How can I have the group defined by 2 fields?Create a formula joining the two fields:
{field1}+{field2}

and then group on that formula|||Thanks Anonymous2,
That resolved my problem!

Grouping and Filters

Hi everyone

I am using SSRS2005 with an SSAS cube building in BI

I need to create a custom grouping. Here's what i mean:

I give my period parameter some default Values. Like :
Period = 200501,200502,200601,200602
Now when building the report, I filter 2 Tables on the 2 years respectively.
Grouped by Period

So one table list all the Measures for 2005 and the other for 2006.
Now I want to use a Chart to Display the two totals. I can only get the Chart to
display the by monthley periods. IE:

30 o
|--|-||
20 o o
|--|-||
10 o
|--|-||
200501 200502 200601 200602 (instead of 2005 and 2006 as I need)

I need to create a grouping by which i can tell the chart what data to use.
I can't group by period.year because the Period field is an Integer

Any help is greatly appreciated
If I am unclear about anything please point it out to me

Thanks in advance
Gerhard Davids

So I sorted this out and it seems I was being really retarded.

I used the following statments in the grouping of the Chart.

Series group : =iif(Left(CStr(Fields!Period.Value),4) = "2004", 2004, iif(Left(CStr(Fields!Period.Value),4) = "2005",2005,iif(Left(CStr(Fields!Period.Value),4) = "2006",2006,Nothing)))

Category group : =iif(Right(Cstr(Fields!Period.Value),2) = "01" ,01,iif(Right(Cstr(Fields!Period.Value),2) = "02",02,iif(Right(Cstr(Fields!Period.Value),2) = "03",03,iif(Right(Cstr(Fields!Period.Value),2) = "04",04,iif(Right(Cstr(Fields!Period.Value),2) = "05",05,iif(Right(Cstr(Fields!Period.Value),2) = "06",06,iif(Right(Cstr(Fields!Period.Value),2) = "07",07,iif(Right(Cstr(Fields!Period.Value),2) = "08",08,iif(Right(Cstr(Fields!Period.Value),2) = "09",09,iif(Right(Cstr(Fields!Period.Value),2) = "10",10,iif(Right(Cstr(Fields!Period.Value),2) = "11",11,iif(Right(Cstr(Fields!Period.Value),2) = "12",12,iif(Right(Cstr(Fields!Period.Value),2) = "13",13,Nothing)))))))))))))

This allowed it to group the periods together but seperate for each year
and in the series explanation it gave me the total for
each year respectiveley.

In the data section I then simply sumed my measure

Grouping

Sorry for the newbie question... is it possible to create one group using two fields?Yes,
Create a formula that concatenates your 2 fields then group off of that.
GJ

Wednesday, March 21, 2012

group toggle works in desinger but not when published

Folks -
I have a date group in a simple detail report that toggles the detail
records by date. I used the report desinger wizard to create the initial
report, which toggles fine in the desinger. When published, the toggle
functionality does not work, and no detail records can be
accessed/displayed. RS
server and my VS install have all the latest SPs and patches.
Ideas?
Please reply to the group.
Eric HallI had a very similar problem and worked out by accident that it was because I
was using "lines" in the page header and footer. I removed the lines and used
very thin rectangles and all is good. Very weird though...I think that this
must be a bug.
"Eric Hall" wrote:
> Folks -
> I have a date group in a simple detail report that toggles the detail
> records by date. I used the report desinger wizard to create the initial
> report, which toggles fine in the desinger. When published, the toggle
> functionality does not work, and no detail records can be
> accessed/displayed. RS
> server and my VS install have all the latest SPs and patches.
> Ideas?
> Please reply to the group.
> Eric Hall
>
>
>sql

Monday, March 19, 2012

Group no. of records by text in a text/varchar field

Create table Test
(Text1 varchar(500))
insert Test values('I love SQL')
insert Test values('SQL rocks')
insert Test values('SQL rocks in 2005')
insert Test values('MS rocks too')
insert Test values('MS is short for microsoft')
So i want to run a query where I would like to group by some key text words
..
So i want to get a count of entries in the table that has words 'SQL' and
'MS' in it
Output should be
KeyWord Count
MS 2
SQL 3
What is the query ? I would eventually add more keywords to the query..
Thanksyou'd want to unpack your input string into a table then it's just a matter
of finding the occurrences.
e.g.
declare @.s varchar(100)
set @.s='MS,SQL'
declare @.padded varchar(8000);set @.padded=','+@.s+','
select s,count(*)
from (select
substring(@.padded,digit+1,charindex(',',
@.padded,digit+1)-digit-1)
from racdigits
where digit <= len(@.padded)-1
and substring(@.padded,digit,1)= ',') derived(s)
join Test on Test.Text1 like '%'+derived.s+'%'
group by s
racdigits is just an auxilary table with value from 1-8000 (i.e. select top
8000 digit=identity(int,1,1) into racdigits from sysobjects,syscolumns)
-oj
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5HDQH2jGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Create table Test
> (Text1 varchar(500))
> insert Test values('I love SQL')
> insert Test values('SQL rocks')
> insert Test values('SQL rocks in 2005')
> insert Test values('MS rocks too')
> insert Test values('MS is short for microsoft')
> So i want to run a query where I would like to group by some key text
> words ..
> So i want to get a count of entries in the table that has words 'SQL' and
> 'MS' in it
> Output should be
> KeyWord Count
> MS 2
> SQL 3
> What is the query ? I would eventually add more keywords to the query..
> Thanks
>
>|||Where do you want to show data?
If you use front end application, split data there
Madhivanan
Hassan wrote:
> Create table Test
> (Text1 varchar(500))
> insert Test values('I love SQL')
> insert Test values('SQL rocks')
> insert Test values('SQL rocks in 2005')
> insert Test values('MS rocks too')
> insert Test values('MS is short for microsoft')
> So i want to run a query where I would like to group by some key text word
s
> ..
> So i want to get a count of entries in the table that has words 'SQL' and
> 'MS' in it
> Output should be
> KeyWord Count
> MS 2
> SQL 3
> What is the query ? I would eventually add more keywords to the query..
> Thanks|||On Tue, 13 Jun 2006 20:22:47 -0700, Hassan wrote:

>Create table Test
>(Text1 varchar(500))
>insert Test values('I love SQL')
>insert Test values('SQL rocks')
>insert Test values('SQL rocks in 2005')
>insert Test values('MS rocks too')
>insert Test values('MS is short for microsoft')
>So i want to run a query where I would like to group by some key text words
>..
>So i want to get a count of entries in the table that has words 'SQL' and
>'MS' in it
>Output should be
>KeyWord Count
>MS 2
>SQL 3
>What is the query ? I would eventually add more keywords to the query..
Hi Hassan,
Store the keywords in a seperate table, then use a query such as this:
SELECT k.Keyword, COUNT(t.Text1)
FROM Keywords AS k
LEFT JOIN Test AS t
ON t.Text1 LIKE '%' + k.Keyword + '%'
GROUP BY k.Keyword
Hugo Kornelis, SQL Server MVP

Wednesday, March 7, 2012

Group by month

Hello
I got this table (for testing)... I'm struggeling to create a view that
displays the number of "entries" each person has for each month. Maybe you
guys could show a proper way of dealing with this.
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-01-01 2005-01-31 A 5
2005-02-01 2005-02-28 A 4
2005-01-01 2005-01-31 B 2
*/
DROP TABLE #TestTry this:
SELECT DATEADD(MONTH,mth,'20000101') AS startdate,
DATEADD(MONTH,mth,'20000131') AS enddate,
person, COUNT(*) AS cnt
FROM
(SELECT DATEDIFF(MONTH,'20000101',datecreated) AS mth, person
FROM #Test) AS T
GROUP BY mth, person ;
If you want to include rows in the result for months that have no data
in your table then join the above query with a calendar table or
numbers table to generate the extra months.
David Portas
SQL Server MVP
--|||Thanx for posting DDL and INSERT's:
select
cast (convert (char (6), DateCreated, 112) + '01' as datetime) StartDate
, dateadd (dd, -1, dateadd (mm, 1, convert (char (6), DateCreated, 112) +
'01')) EndDate
, Person
, count (*)
from
#Test
group by
cast (convert (char (6), DateCreated, 112) + '01' as datetime)
, dateadd (dd, -1, dateadd (mm, 1, convert (char (6), DateCreated, 112) +
'01'))
, Person
order by
Person
, StartDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:uGIPKzOyFHA.1856@.TK2MSFTNGP12.phx.gbl...
Hello
I got this table (for testing)... I'm struggeling to create a view that
displays the number of "entries" each person has for each month. Maybe you
guys could show a proper way of dealing with this.
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-01-01 2005-01-31 A 5
2005-02-01 2005-02-28 A 4
2005-01-01 2005-01-31 B 2
*/
DROP TABLE #Test|||Try,
-- for each year and month
SELECT
min(cast(convert(varchar(6), Datecreated, 112) + '01' as datetime)) as
Startdate,
dateadd(day, -1, dateadd(month, 1, min(cast(convert(varchar(6),
Datecreated, 112) + '01' as datetime)))) as Enddate,
Person,
count(*) as [Count]
FROM
#Test
group by
convert(varchar(6), Datecreated, 112),
Person
go
AMB
"Lasse Edsvik" wrote:

> Hello
> I got this table (for testing)... I'm struggeling to create a view that
> displays the number of "entries" each person has for each month. Maybe you
> guys could show a proper way of dealing with this.
>
> CREATE TABLE #Test (
> SomePk int identity(1,1) NOT NULL,
> Person char(1) NOT NULL,
> Datecreated datetime NOT NULL
> )
>
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
>
> SELECT * FROM #Test
>
> /*
> Desired result:
> Startdate Enddate Person Count
> 2005-01-01 2005-01-31 A 5
> 2005-02-01 2005-02-28 A 4
> 2005-01-01 2005-01-31 B 2
>
> */
> DROP TABLE #Test
>
>|||This is pretty much the same as Tom's one, but with less conversions
SELECT
MonthAdded AS StartDate
, DATEADD( d , -1 , DATEADD( m , 1 , MonthAdded ) ) AS EndDate
, Person
, Total AS Count
FROM
(
SELECT
CONVERT( DATETIME , CONVERT( CHAR(7) , Datecreated , 121 ) + '-01' ,
121 ) AS MonthAdded
, Person
, COUNT(*) AS Total
FROM
#Test
GROUP BY
CONVERT( DATETIME , CONVERT( CHAR(7) , Datecreated , 121 ) + '-01' ,
121 )
, Person
) vwResults
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:uGIPKzOyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Hello
> I got this table (for testing)... I'm struggeling to create a view that
> displays the number of "entries" each person has for each month. Maybe you
> guys could show a proper way of dealing with this.
>
> CREATE TABLE #Test (
> SomePk int identity(1,1) NOT NULL,
> Person char(1) NOT NULL,
> Datecreated datetime NOT NULL
> )
>
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
>
> SELECT * FROM #Test
>
> /*
> Desired result:
> Startdate Enddate Person Count
> 2005-01-01 2005-01-31 A 5
> 2005-02-01 2005-02-28 A 4
> 2005-01-01 2005-01-31 B 2
>
> */
> DROP TABLE #Test
>
>

Group By Expressions

I'd like to dynamically create a group by expression. I have

select sourcecd
FROM dbo.ITMV_ScanCardHistory
GROUP BY dbo.fn_GroupBy (@.GroupBy)

Where:

@.groupby = sourcecd and

fn_GroupBy =

CREATE FUNCTION dbo.fn_GroupBy
(@.ColumnName Varchar(55))
RETURNS Varchar(55)
AS
BEGIN

RETURN @.ColumnName
END

I keep getting this error message:

Server: Msg 164, Level 15, State 1, Line 48
GROUP BY expressions must refer to column names that appear in the select list.

Please help.

Hello,

This won't work as the Group By clause is looking for a column name, whereas you are supplying a varchar(55) string...

Have you looked at dynamic statements via ExecuteSQL?

Have a look at EXECUTE in BOL.

Cheers

Rob

|||

The error is saying that what you have in your GROUP by you need in your select.

Why do you want to do this, there are a number of options but I would like to understand why you need this first.

|||thank you. We are designing reports in reporting services and we'd like to give the business user the option of selection which column they'd like to group their data by.|||

thanks, however SQL Books says that scalar functions or any valid expression can be used in a group by:

see "

Invoking User-Defined Functions That Return a Scalar Value"

under Accessing and Changing Relation...

|||

You can do it in RS by making the group by field dynamic. In your group define the field to group by as fields(Paramaters("GroupBy").Value).Value

Or something like that

|||Thank you, I will give this a try.|||

Hi,

By using sp_executesql you can create your dynamic sql and run it as below

declare @.sql as nvarchar(1000)
SELECT @.sql = N'
select ' + dbo.fn_GroupBy('eventid') +
N' FROM Events
GROUP BY ' + dbo.fn_GroupBy('eventid')

-- SELECT @.sql

exec sp_executesql @.sql

But you can also use "distinct" instead of "group by"

declare @.sql as nvarchar(1000)
SELECT @.sql = N'
select distinct ' + dbo.fn_GroupBy('eventid') + N' FROM Events '
-- SELECT @.sql

exec sp_executesql @.sql

Eralper

http://www.kodyaz.com

Friday, March 30, 2012

Grouping several items in one group

Grouping records - HOW TO

grouping question

Grouping in Table

Wednesday, March 28, 2012

Grouping problem

Grouping my months in a chart

Grouping in Table

Grouping in Table

Monday, March 26, 2012

Grouping by Time

Grouping By Problems

Friday, March 23, 2012

Grouping by day of the week?

Grouping by Age

Grouping By A Certain Range

Grouping based on multiple fields

Grouping and Filters

Grouping

Wednesday, March 21, 2012

group toggle works in desinger but not when published

Monday, March 19, 2012

Group no. of records by text in a text/varchar field

Wednesday, March 7, 2012

Group by month

Group By Expressions

Invoking User-Defined Functions That Return a Scalar Value"

Firehose mode

Blog Archive

About Me