Grouping with Reporting services

Can anyone help me with grouping in Reporting services. I am more used to crystal reports drill-down method

For example i have a simple table that has timestamp and three other columns. I want to drill down by (after Grouping) for Day-Then- Hour and then show the details for three columns. And also group by one of the columns, if i get above working.

All i could do with Reporting services was stepped down model, but i have same dates repeated more than once. i would like them to be grouped under day and then show time stamps for times of day .

-Thanks all

You can do this by creating a Table and adding a grouping, which is grouped by the date portion of the timestamp field, and then place the detail fields in the detail section, as you are now. The expressions should be something like the following:

Grouping expression:
=Fields!TimeStamp.Value.Date

Grouping header textbox for the date:

=Fields!TimeStamp.Value.ToString("D")

Detail textbox for the time:
=Fields!TimeStamp.Value.ToString("T")

Ian

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 question...

Hi,

I am migrating some reports from MS Access2003 to SQL 2005 Reporting Services.

I have a dataset which contains columns for Sex, Age, Name etc... I firstly display the contents of this dataset in a table and this is fine. I also need to display a table of the breakdown of the age and sex. E.G:-

< 16yrs 16yrs-24yrs 25yrs-64yrs 65yrs-74yrs 75yrs-84yrs >=85yrs
Male x x x x x x

Female x x x x x x

Does anyone know if this is possible. I was going to use a DCount function (As found in access) but I can not find it in SRS. What is thet best way to produce this result?

Thanks in advance for your time

Peter Tewkesbury

BlueFlower Limited

Conditional aggregation can be achieved as follows:

=Sum(iif(Fields!Age.Value >= 25 AND Fields!Age.Value < 65, 1, 0))

-- Robert

sql

Grouping parameter value

I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.

VT01

VT02

VT03

VN01

VN02

VN03

ST01

ST02

ST03

instead of listing everything, i want the viewers to see this

VT

VN

ST

or an alias for each of these like this:

Vet Tasks

Vet National

Survey Tasks

Survey National

any ideas, here's my current code, which is pullin up anything with the added substring part

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions]

(@.Region_Key int=null,@.QuestionCode char(5))

AS

BEGIN

SELECT dbo.Qry_Questions.Territory,

dbo.Qry_Questions.SalesResponsible,

dbo.Qry_Questions.Customer,

dbo.Qry_Questions.Date,

dbo.Qry_Questions.StoreName,

dbo.Qry_Questions.PostCode,

dbo.Qry_Questions.Address2,

dbo.Qry_Questions.[Question Code],

dbo.Qry_Questions.Question,

dbo.Qry_Questions.[Response Type],

dbo.Qry_Questions.response,

dbo.Qry_Questions.sales_person_code,

dbo.Qry_Sales_Group.Region_Key,

dbo.Qry_Sales_Group.Region

FROM dbo.Qry_Questions

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

WHERE REGION_KEY=@.Region_Key

AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@.QuestionCode

END

SET NOCOUNT OFF

You might try using the follwing as the grouping expression:

Code Snippet

=Left(Fields!<your_field>.Value, 2)

From there you could either set up a CASE statement or code for your aliases.

Hope this helps!

Scott

|||

I have the report working, i just want to be able to group the choices into 6 different choices. I know there is a way to do this in the report parameters properties box. I created my own non queried values that look like this:

Label Value

Survey national =IIF(Left(Fields!Question_Code.Value, 2)="SN",Fields!Question_Code.Value,nothing)

Survey vet =IIF(Left(Fields!Question_Code.Value, 2)="SV",Fields!Question_Code.Value,nothing)

Survey independent =IIF(Left(Fields!Question_Code.Value, 2)="SI",Fields!Question_Code.Value,nothing)

and so on...

But i keep getting an error :

A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field.

what am i doing wrong?

|||

I believe that if you are populating values for a parameter, you can't use the same dataset used in the report. I vaguely remember running into the same problem when I fist began using parameters. We use separate datasets for the parameters in our reports.

|||

Create a second dataset with the following query:

Select Distinct Left(Question_Code, 2)

FROM dbo.Qry_Questions

Group by Question_Code

Order by Question_Code

Change your parameter to query and point it at this new dataset. Then in your main dataset query add the following to your where statement:

Where Question_Code IN(@.question_code_parm)

|||Thanks that worked beautifully!! And i was able to hard code and rename the Question codes that were group for report parameters.

Grouping parameter value

I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.

VT01

VT02

VT03

VN01

VN02

VN03

ST01

ST02

ST03

instead of listing everything, i want the viewers to see this

VT

VN

ST

or an alias for each of these like this:

Vet Tasks

Vet National

Survey Tasks

Survey National

any ideas, here's my current code, which is pullin up anything with the added substring part

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions]

(@.Region_Key int=null,@.QuestionCode char(5))

AS

BEGIN

SELECT dbo.Qry_Questions.Territory,

dbo.Qry_Questions.SalesResponsible,

dbo.Qry_Questions.Customer,

dbo.Qry_Questions.Date,

dbo.Qry_Questions.StoreName,

dbo.Qry_Questions.PostCode,

dbo.Qry_Questions.Address2,

dbo.Qry_Questions.[Question Code],

dbo.Qry_Questions.Question,

dbo.Qry_Questions.[Response Type],

dbo.Qry_Questions.response,

dbo.Qry_Questions.sales_person_code,

dbo.Qry_Sales_Group.Region_Key,

dbo.Qry_Sales_Group.Region

FROM dbo.Qry_Questions

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

WHERE REGION_KEY=@.Region_Key

AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@.QuestionCode

END

SET NOCOUNT OFF

You might try using the follwing as the grouping expression:

Code Snippet

=Left(Fields!<your_field>.Value, 2)

From there you could either set up a CASE statement or code for your aliases.

Hope this helps!

Scott

|||

I have the report working, i just want to be able to group the choices into 6 different choices. I know there is a way to do this in the report parameters properties box. I created my own non queried values that look like this:

Label Value

Survey national =IIF(Left(Fields!Question_Code.Value, 2)="SN",Fields!Question_Code.Value,nothing)

Survey vet =IIF(Left(Fields!Question_Code.Value, 2)="SV",Fields!Question_Code.Value,nothing)

Survey independent =IIF(Left(Fields!Question_Code.Value, 2)="SI",Fields!Question_Code.Value,nothing)

and so on...

But i keep getting an error :

A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field. Fields cannot be used in report parameter expressions.

[rsFieldInReportParameterExpression] A Value expression used for the report parameter ��QuestionCode�� refers to a field.

what am i doing wrong?

|||

I believe that if you are populating values for a parameter, you can't use the same dataset used in the report. I vaguely remember running into the same problem when I fist began using parameters. We use separate datasets for the parameters in our reports.

|||

Create a second dataset with the following query:

Select Distinct Left(Question_Code, 2)

FROM dbo.Qry_Questions

Group by Question_Code

Order by Question_Code

Change your parameter to query and point it at this new dataset. Then in your main dataset query add the following to your where statement:

Where Question_Code IN(@.question_code_parm)

|||Thanks that worked beautifully!! And i was able to hard code and rename the Question codes that were group for report parameters.

Grouping in Reporting services

I am pulling 200,000 + records from an AS400 database through ODBC into a report. I have a couple of questions and I am totally new to SQL Reporting Services, so I don't expect a full answer to these questions, but maybe a couple of pointers or web sites that I can view for further information.

1) One of the fields that I am pulling in are dates going back to 1988. I need to first group these dates by year. Do the expressions used in the cells of a report table allow for formulas like =date.year?

2) I have a couple of fields that I need to group on: first being year, then state. There is a numeric field (participants) that I need to do a sum on. Do the reports have a (+) next to the grouping levels so for instance I have the 2000 year "closed", I need this to show total participants during that year (for every state). If 2000 was opened then it would list each state (that could further be drilled into) that would show total participants by state. I hope this makes sense. I used to do this in Visual Basic Windows forms 2003 version using the DataView control.

Thanks for any information.

Brad

i don't know about the first one but the second bit is possible take a look at this article on how to do it

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true

Grouping in List Control

Hi,

I am new with RS2000. I am working on a Reporting application. I have some Application names on X-axis of a bar chart say they are 90 in number. I want to group this chart in a list control in such a way that in each portion the chart must contain 15 application names if suppose there are 90 application names.

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/15)

This should result in the desired grouping of 15 items per (repeating) list instance. Then just put the chart inside the list.

-- Robert

|||But i need to add a list fir that.are there any other work arounds?

Grouping in List Control

Hi,

I am new with RS2000. I am working on a Reporting application. I have some Application names on X-axis of a bar chart say they are 90 in number. I want to group this chart in a list control in such a way that in each portion the chart must contain 15 application names if suppose there are 90 application names.

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/15)

This should result in the desired grouping of 15 items per (repeating) list instance. Then just put the chart inside the list.

-- Robert

|||But i need to add a list fir that.are there any other work arounds?sql

Grouping Data into Periods for Reporting

Hi there.

I am working on a set of reports where I am summing/averaging data elements based on what period they are in. For example, the report output should look something like this:

Period Sum May '07 41 April '07 14 Q2 '07 55 March '07 36 February '07 28 January '07 22 Q1 '07 86 June '07 N/A YTD '07 141 December '06 33 November '06 27 October '06 42 Q4 '06 102 September '06 58 August '06 84 July '06 52 Q3 '06 194 June '06 40 May '06 41 April '06 14 Q2 '06 95 March '06 67 February '06 38 January '06 N/A Q1 '06 105 YTD '06 496

For each of the items I am summing, all I have is a datetime of when the event happened. This is a relational database (not a cube), so I am struggling with how to create the 'buckets' based on period. I think the best way is to dynamically create the buckets based on a given date. Is there a way in RS that it can do this bucketing for you?

Thanks, Mike

There are several ways to "create buckets". I recently posted something here http://spacefold.com/lisa/post/Partition-Magic.aspx having discovered some of the SQL 2005 syntax that I never knew existed, which may help you in your explorations of non-cube data -- and there is also a PIVOT clause which is really neat.

When you look at it, it looks as though you can't dynamically figure out how many buckets you have and go for it, but you actually can, if you write a bit of very easy dynamic SQL. I was actually planning on posting about that today, having helped a co-worker do it!

But in RS, you can do this using a matrix layout control, which basically does the thing for you, albeit with (from my POV) some frustrating and counter-intuitive ways of thinking.

Look into the matrix data region first, if you need to display the buckets across, and if you don't like it look into the PIVOT clause to do this in SQL Server (NB: if your data source isn't SQL Server you don't have this T-SQL syntax but there are ways of getting around that if you need to <g>.)

If you need to display the buckets down, I think you have even easier ways to do it. What you seem to be showing (as I read your example table) is a table that has grouping and you've suppressed the detail rows that might ordinarily appear with each line (the dates for each event). OK so far?

The very simplest way to get what you want is to write your query like this:

SELECT MONTH(eventdate) AS M, Datepart(quarter,eventdate) AS Q, YEAR(eventdate) AS Y, eventtype, eventdate from YourEventTable

... now you can summarize by having appropriate groups on the first three calculated values that you see in this query.

The wrinkle that most people have when doing this particular thing is that they actually want the fiscal month, the fiscal quarter, and the fiscal year rather than the base values that you see here. So you generally want to write a couple of UDFs that pass in your first month of fiscal year to handle this properly. These can be a PITA but once you've written them appropriately for your situation, you are usually okay forever. Give a shout if you find you need help with this part. (I may be offline for about a week, but if it is urgent I'm sure many other people can help you with this).

So that's how you do it if your "buckets" are rows, as they seem to be from your example layout. If they are really columns, again, look into matrix and pivot.

>L<

|||

Hi Lisa.

Thanks so much for the great response. I was working on doing something similar, but there are a few more wrinkles (aren't there always?). As you mentioned, the buckets are rows, so that is good, but in this case, my columns can vary, so I need to use a matrix control.

1. The data set I need is for the current quarter (based on getdate()) and the previous 5 full quarters. I think this can be easily handled in the where clause, so that should be ok.

2. The matrix control is strange in that when you add row groups, it actually adds them as columns on the report itself (I posted a question on this recently). My customer doesn't want it that way, so I have to get the dataset to match the control - meaning, I will need to have my dataset return the summed/averaged bucket rows and NOT have the control handle the buckets. So, I think I am stuck.

Can you see any way around that?

Thanks, Mike

|||

Hi again, Mike,

I can't actually think closely about what you're stuck on here, because (I think I said) I'm leaving on a trip and 'way late on preparing <s>. But, in fact, a PIVOT clause might be just the ticket here -- for this reason among others I did end up posting a blog entry about that. http://spacefold.com/lisa/post/Matrix-Rebuilt-More-non-standard-fun-with-T-SQL.aspx

It's discussing some aspects of the clause that you may or may not be interested it but it will give you some idea of the scope of what it can help you accomplish. In your case -- since you actually know how many buckets there are (6 quarters) -- it may be especially apt.

I'll be back in a week, if you haven't got what you need by then I'll do my best to help <s>. Look forward to reading whatever else has been posted on this thread by then!

>L<

|||

Thanks Lisa.

I'll check into it. Enjoy your time 'away'.

- Mike

Grouping and Aggregate Functions in Reporting Services

Hi All,
Can I ask you something about Grouping and Aggregate Functions in
Reporting Services?
My question is, I have a grouping in a table like the following;
Group 1 - Manufacturer - TotalInvoicePerManufacturer
Group 2 - Region - TotalInvoicePerRegion
Group 3 - Distributor - TotalInvoicePerDistributor
Group 4 - RetailerType - Nothing
Detail - Retailer - Nothing
And my stored procedure gives the resultset like this;
Manufacturer - Region - Distributor - InvoiceAmount - Retailer -
RetailerType
As you can see above, since I have retailer name and type in my
resultset, even though InvoiceAmout is only for Distributor, I have a
couple of rows (as much as a distributor has) with the same
InvoiceAmount.
What I want is;
Group 1 - Manufacturer - TotalInvoicePerManufacturer
(=sum(Fields!TotalInvoice.Value) gives me here InvoiceAmount
multiplied by distributor's retailer count, but I need only sum of
distributors' Invoice amounts)
Group 2 - Region - TotalInvoicePerRegion
(=sum(Fields!TotalInvoice.Value) gives me here InvoiceAmount
multiplied by distributor's retailer count, but I need only sum of
distributors' Invoice amounts)
Group 3 - Distributor - TotalInvoicePerDistributor (If
amount is A, to see A - for this I use =Fields!TotalInvoice.Value and
it's OK)
Group 4 - RetailerType - Nothing
Detail - Retailer - Nothing
Can you help me?
Thanks a lot..Hi, or "Merhaba"
Even though I am not very clear about what you are trying to accomplish, I
would suggest you to use
matrix report.
-Put Group 1 > Group 2 > Group 3 > Group 4 in columns. Each group should
refer to its parent.
-Put TotalInvoice in to the data cell.
-At the end play with collapse/expand row features, for aggregates.
Good luck.
Regards,
Cem Demircioglu
"Sema Yuce" <sema.yuce@.eczacibasi.com.tr> wrote in message
news:9844673f.0412072205.70170fd5@.posting.google.com...
> Hi All,
> Can I ask you something about Grouping and Aggregate Functions in
> Reporting Services?
> My question is, I have a grouping in a table like the following;
> Group 1 - Manufacturer - TotalInvoicePerManufacturer
> Group 2 - Region - TotalInvoicePerRegion
> Group 3 - Distributor - TotalInvoicePerDistributor
> Group 4 - RetailerType - Nothing
> Detail - Retailer - Nothing
> And my stored procedure gives the resultset like this;
> Manufacturer - Region - Distributor - InvoiceAmount - Retailer -
> RetailerType
> As you can see above, since I have retailer name and type in my
> resultset, even though InvoiceAmout is only for Distributor, I have a
> couple of rows (as much as a distributor has) with the same
> InvoiceAmount.
> What I want is;
> Group 1 - Manufacturer - TotalInvoicePerManufacturer
> (=sum(Fields!TotalInvoice.Value) gives me here InvoiceAmount
> multiplied by distributor's retailer count, but I need only sum of
> distributors' Invoice amounts)
> Group 2 - Region - TotalInvoicePerRegion
> (=sum(Fields!TotalInvoice.Value) gives me here InvoiceAmount
> multiplied by distributor's retailer count, but I need only sum of
> distributors' Invoice amounts)
> Group 3 - Distributor - TotalInvoicePerDistributor (If
> amount is A, to see A - for this I use =Fields!TotalInvoice.Value and
> it's OK)
> Group 4 - RetailerType - Nothing
> Detail - Retailer - Nothing
> Can you help me?
> Thanks a lot..

Group Totals

Can some one help me with totaling a group value on SQL Server
Reporting Services report? Here is a simplified version of what I am
struggling with:
Let's say I have an SQL statement that returns the flowing set
RoomID Max Occupants Name
1 3 Bill Clinton
1 3 Hilary Clinton
1 3 Chelsea Clinton
2 4 George W Bush
2 4 Barbara Bush
Total 7 5
I have a report with a group that groups rooms, shows the max occupants
on the header of each group and lists the current occupants underneath
each group header. On the bottom I want to show the total number of
occupants the rooms could have and the number of spots currently
occupied, 7 and 5 in my example. What do I need to do to show 7 on the
bottom of the report? It looks simple and I have done this many times
on different report writers but for some reason I have trouble figuring
out what to do on RS report.
Any help is appreciated.
Tim.Group by room ID and then add the First(Fields!occupants.value) from each
group.
"Tim." wrote:
> Can some one help me with totaling a group value on SQL Server
> Reporting Services report? Here is a simplified version of what I am
> struggling with:
> Let's say I have an SQL statement that returns the flowing set
> RoomID Max Occupants Name
> 1 3 Bill Clinton
> 1 3 Hilary Clinton
> 1 3 Chelsea Clinton
> 2 4 George W Bush
> 2 4 Barbara Bush
> Total 7 5
> I have a report with a group that groups rooms, shows the max occupants
> on the header of each group and lists the current occupants underneath
> each group header. On the bottom I want to show the total number of
> occupants the rooms could have and the number of spots currently
> occupied, 7 and 5 in my example. What do I need to do to show 7 on the
> bottom of the report? It looks simple and I have done this many times
> on different report writers but for some reason I have trouble figuring
> out what to do on RS report.
> Any help is appreciated.
> Tim.
>sql

Group Footers?

I am new to reporting services. On a detail table of my report, I would like
to print
a large comment field after each row. The comment should span the entire
width of the table. (Sort of like the outlook preview row capability) Is
this possible?
Thanks,
Deniseright click to the left of you detail row and 'insert row below'.
Select all cells in this row, right click and 'merge'. You can make this any
size you need and will show up between your report detail. You can format as
needed.
--
U. Tokklas
"Denise" wrote:
> I am new to reporting services. On a detail table of my report, I would like
> to print
> a large comment field after each row. The comment should span the entire
> width of the table. (Sort of like the outlook preview row capability) Is
> this possible?
> Thanks,
> Denise

group expression

Hello, I am very new to Reporting Services and want to do something I have
done in other programs using a tool or wizard. I want to group my data by "
30 days over due" , then " 60 days over due" etc..Based on the following
DAYSDUE > 30 and < 60
DAYSDUE > 60 and < 90
DAYSDUE > 90 and < 120
DAYSDUE > 120
I think it will need to be an expression in the group dialog box, but for
the life of me I cant's seem to come up with it. Any help would be
appreciated. ThanksTry this:
=iif(Fields!Daysdue.Value > 120, 1, iif(Fields!Daysdue.Value > 90, 2,
iif(Fields!Daysdue.Value > 60, 3, iif(Fields!Daysdue.Value > 30, 4, 5))))
The expression basically maps the integer values to five distinct groups: 1,
2, 3, 4, 5
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SLB" <SLB@.discussions.microsoft.com> wrote in message
news:D262B295-7A20-45F0-B6A4-1ADC448FD970@.microsoft.com...
> Hello, I am very new to Reporting Services and want to do something I have
> done in other programs using a tool or wizard. I want to group my data by
> "
> 30 days over due" , then " 60 days over due" etc..Based on the following
> DAYSDUE > 30 and < 60
> DAYSDUE > 60 and < 90
> DAYSDUE > 90 and < 120
> DAYSDUE > 120
> I think it will need to be an expression in the group dialog box, but for
> the life of me I cant's seem to come up with it. Any help would be
> appreciated. Thanks
>

Group everything with the same first two letters

I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.

VT01

VT02

VT03

VN01

VN02

VN03

ST01

ST02

ST03

instead of listing everything, i want the viewers to see this

VT

VN

ST

or an alias for each of these like this:

Vet Tasks

Vet National

Survey Tasks

Survey National

any ideas, here's my current code, which is pullin up anything with the added substring part

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions]

(@.Region_Key int=null,@.QuestionCode char(5))

AS

BEGIN

SELECT dbo.Qry_Questions.Territory,

dbo.Qry_Questions.SalesResponsible,

dbo.Qry_Questions.Customer,

dbo.Qry_Questions.Date,

dbo.Qry_Questions.StoreName,

dbo.Qry_Questions.PostCode,

dbo.Qry_Questions.Address2,

dbo.Qry_Questions.[Question Code],

dbo.Qry_Questions.Question,

dbo.Qry_Questions.[Response Type],

dbo.Qry_Questions.response,

dbo.Qry_Questions.sales_person_code,

dbo.Qry_Sales_Group.Region_Key,

dbo.Qry_Sales_Group.Region

FROM dbo.Qry_Questions

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

WHERE REGION_KEY=@.Region_Key

AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@.QuestionCode

END

SET NOCOUNT OFF

You should do the following:

1. Create a separate question code types table with a type column (this will be "VT", "VN", "ST" and so on) and description column

2. Create another table that maps the question code to the types table

3. Now, for display purposes you can show the data from types table

4. Similarly, for your query instead of using the information encoded in the value (using substring etc) just join with the code to types mapping table and filter on the type column

This approach will scale better, perform better and easier to manage. Currently, you are breaking normalization rules by inferring attributes from a value.

Group Count

Good Morning.

This should be fairly easy but for the life of me, I can't figure it out...

I am using SQL reporting Services 2000 and Visual Studio 2003.

I have a very simple report that is grouped by a users name. What I can't figure out is how to get the count of records in a group. I have already got the total number of records for all groups but I also need to have the Group count as well.

Any help would be greatly apprceiated

Try the inscope function?

http://devauthority.com/blogs/chrisslatt/archive/2005/07/06/Reporting_Service_Using_InScope_to_do_custom_subtotals.aspx

|||

For table like this:

Table Header:
Group Header:
Detail:
Group Footer:
Table Footer:

I do the following: add COUNT() into Group Footer line. It will calculate you number of rows for each group. If you put COUNT to Table Footer it will calculate you total number of rows. It's for simple situation. In case of complicated calculation and table structure you can use InScope, as Andrew told you.

Group by Month/Week/Day in DateTime Field?

Crystal Reports has the ability to group by datetime based on
month/week/daily basis in DateTime field. Can Reporting Server do this?I figured it out myself by using DataName and DatePart in SQL query:
SELECT DATENAME(mm, DateTime) + ', ' + CAST(DATENAME(yyyy, DateTime) AS
varchar(4)) AS MonthYearName, DATEPART(yyyy, DateTime) AS Year,
DATEPART(mm, DateTime) AS Month, *
FROM CorporateSales
Then, in Reporting Server, add the GROUP and then group the data by Month,
and by Year.
Add "MonthYearName" in header.
Add "Subtotal" in footer.
Bingo!!!
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:w_qdnf4Sq4V0-gneRVn-rw@.rogers.com...
> Crystal Reports has the ability to group by datetime based on
> month/week/daily basis in DateTime field. Can Reporting Server do this?
>
>

Group by expiration date

Is there a way to get this down to one select statement and group it as such
in Reporting services?
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate() and enddate <=getdate()+30
order by enddate asc
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate()+30 and enddate <=getdate()+60
order by enddate asc
select custnmbr,contnbr,enddate,Cast(total as Money) from svc00600
where enddate >=getdate()+60 and enddate <=getdate()+120
order by enddate ascYou could try either of the options below. Then group on the new
psuedo-field DaysOut
select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
from svc00600
where enddate >=getdate() and enddate <=getdate()+30
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
from svc00600
where enddate >=getdate()+30 and enddate <=getdate()+60
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
from svc00600
where enddate >=getdate()+60 and enddate <=getdate()+120
order by enddate asc
SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
'DAYSOUT' = CASE
WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
THEN '30'
WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=DATEADD(DD,60,getdate()) THEN '60'
WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=DATEADD(DD,120,getdate()) THEN '120'
END
FROM svc00600
WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
ORDER BY enddate ASC|||Awesome, thanks.
"Ches" wrote:
> You could try either of the options below. Then group on the new
> psuedo-field DaysOut
>
> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
> from svc00600
> where enddate >=getdate() and enddate <=getdate()+30
> UNION
> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
> from svc00600
> where enddate >=getdate()+30 and enddate <=getdate()+60
> UNION
> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
> from svc00600
> where enddate >=getdate()+60 and enddate <=getdate()+120
> order by enddate asc
>
> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
> 'DAYSOUT' = CASE
> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
> THEN '30'
> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=> DATEADD(DD,60,getdate()) THEN '60'
> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=> DATEADD(DD,120,getdate()) THEN '120'
> END
> FROM svc00600
> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
> ORDER BY enddate ASC
>|||Be Careful--this query will possibly produce wrong results. If you do
<=getdate()+30 in the first group and >=getdate()+30 in the second, you will
potentially have a customer show up in both results. Your query should look
like:
select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
from svc00600
where enddate >=getdate() and enddate <=getdate()+30
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
from svc00600
where enddate >getdate()+30 and enddate <=getdate()+60
UNION
select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
from svc00600
where enddate >getdate()+60 and enddate <=getdate()+120
order by enddate asc
Even still, this query will list account with less than 30, between 31 than
60 and between 61 and 120--not necessarily the accounts that are 30 days
overdue, 60 days over or 120 days over as I would assume you are interested
in.
Brian
"Jeff Metcalf" <JeffMetcalf@.discussions.microsoft.com> wrote in message
news:BBC3F268-B818-40FB-9D58-BDD8A2199F6E@.microsoft.com...
> Awesome, thanks.
> "Ches" wrote:
>> You could try either of the options below. Then group on the new
>> psuedo-field DaysOut
>>
>> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
>> from svc00600
>> where enddate >=getdate() and enddate <=getdate()+30
>> UNION
>> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
>> from svc00600
>> where enddate >=getdate()+30 and enddate <=getdate()+60
>> UNION
>> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
>> from svc00600
>> where enddate >=getdate()+60 and enddate <=getdate()+120
>> order by enddate asc
>>
>> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
>> 'DAYSOUT' = CASE
>> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
>> THEN '30'
>> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=>> DATEADD(DD,60,getdate()) THEN '60'
>> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=>> DATEADD(DD,120,getdate()) THEN '120'
>> END
>> FROM svc00600
>> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
>> ORDER BY enddate ASC
>>|||Hey,
Querying GreatPlains data, huh?
You can avoid the union query with the following:
select custnmbr,contnbr,enddate datediff(dd, enddate, getdate()),
aging30 = case when datediff(dd, enddate, getdate())<=30 then Cast(total as
money) else 0 end ,
aging60 = case when datediff(dd, enddate, getdate())Between 31 and 60 then
cast(total as money) else 0 end
from SVC00600 order by enddate desc
and if you really wanted you could even sum and group reight in this query
Have you thought about using a matrix report and doing the grouping on the
report?
HS
"goodman" <goodman93@.hotmail.com> wrote in message
news:OcbuGbj3FHA.2144@.TK2MSFTNGP09.phx.gbl...
: Be Careful--this query will possibly produce wrong results. If you do
: <=getdate()+30 in the first group and >=getdate()+30 in the second, you
will
: potentially have a customer show up in both results. Your query should
look
: like:
:
: select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
: from svc00600
: where enddate >=getdate() and enddate <=getdate()+30
: UNION
: select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
: from svc00600
: where enddate >getdate()+30 and enddate <=getdate()+60
: UNION
: select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
: from svc00600
: where enddate >getdate()+60 and enddate <=getdate()+120
: order by enddate asc
:
: Even still, this query will list account with less than 30, between 31
than
: 60 and between 61 and 120--not necessarily the accounts that are 30 days
: overdue, 60 days over or 120 days over as I would assume you are
interested
: in.
:
: Brian
:
:
: "Jeff Metcalf" <JeffMetcalf@.discussions.microsoft.com> wrote in message
: news:BBC3F268-B818-40FB-9D58-BDD8A2199F6E@.microsoft.com...
: > Awesome, thanks.
: >
: > "Ches" wrote:
: >
: >> You could try either of the options below. Then group on the new
: >> psuedo-field DaysOut
: >>
: >>
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '30' as DaysOut
: >> from svc00600
: >> where enddate >=getdate() and enddate <=getdate()+30
: >> UNION
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '60' as DaysOut
: >> from svc00600
: >> where enddate >=getdate()+30 and enddate <=getdate()+60
: >> UNION
: >> select custnmbr,contnbr,enddate,Cast(total as Money), '120' as DaysOut
: >> from svc00600
: >> where enddate >=getdate()+60 and enddate <=getdate()+120
: >> order by enddate asc
: >>
: >>
: >>
: >> SELECT custnmbr, contnbr, enddate, CAST(total AS MONEY),
: >> 'DAYSOUT' = CASE
: >> WHEN enddate >= getdate() AND enddate <= DATEADD(DD,30,getdate())
: >> THEN '30'
: >> WHEN enddate >= DATEADD(DD,30,getdate()) AND enddate <=: >> DATEADD(DD,60,getdate()) THEN '60'
: >> WHEN enddate >= DATEADD(DD,60,getdate()) AND enddate <=: >> DATEADD(DD,120,getdate()) THEN '120'
: >> END
: >> FROM svc00600
: >> WHERE enddate >=getdate() AND enddate <= DATEADD(DD,120,getdate())
: >> ORDER BY enddate ASC
: >>
: >>
:
:

Group by Date

Hi all,

Firstly: I'm a newbie when it comes to reporting services (previously used to Crystal reports).

This is probably going to appear as a stupid question but here goes.

How do i group on a date time field. I have set up a report with a table and as my group i have selected my datetime field

[code]

=Fields!WeekBegin.Value

[/code]

When i group on this field each record still seems to have its own grouping even though all entries in my test database have the same date. I have noticed that the seconds don't match up on some entries and believe that this may have something to do with it. There are however still duplicates that should be grouped on. I really only want to group on the date part of the Datetime field.

Anyone got any ideas where i am going wrong.

Thanks in advance

Grant

If you only want to group on the date part, you can use the datevalue() function:

=datevalue(Fields!WeekBegin.Value)

or use the Format function to get the date part.

Group By clause killing performance

I have recently started working with a new group of people and I find myself doing a lot of reporting. While doing this reporting I have been writing a TON of sql. Some of my queries were not performing up to par and another developer in the shop recommended that I stay away from the "GROUP BY" clause.

Backing away from the "GROUP BY" clause and using "INNER SELECTS" instead as been more effective and some queries have gone from over 1 minute to less that 1 second.

Obviously if it works then it works and there is no arguing that point. My question to the forum is more about gather some opinions so that I can build an opinion of my own.

If I cannot do a reasonable query of a couple of million records using a group by clause what is the problem and what is the best fix?

Is the best fix to remove the "GROUP BY" and write a query that is a little more complex or should I be looking at tuning the database with more indexes and statistics?

I want to make sure that this one point is crystal clear. I am not against following the advice of my coworker and avoiding the "GROUP BY" clause. I am only intersted in listening to a few others talk about why the agree or disagree with my coworked so that I can gain a broader understanding.

It is a combination of few factors.(1) It is imptant to have proper indexes on columns being queried in WHERE and GROUP BY (2) Also see if you can move the GROUP BY to the reporting tool? (3) Is it the GROUP BY thats killing it or something else? Are you using any functions on columns in the WHERE? like some CONVERT(Datecolumn,100) >= '2007/01/01' etc?

|||

It is definetly the group by that is killing it. When the query was rewritten to to remove the group by the execution time dropped through the floor.

from 1 minute to less than 1 second.

Here is a sudo example of what I mean

old query first

SELECT
column1,
column2,
column3,
SUM(something)
FROM
table1
inner join table 2 on 1.columna = 2.columnb
GROUP BY
column1,
column2,
column3

new query

SELECT
column1,
column2,
column3,
(SELECT SUM(something) From sometable) AS 'blah'
FROM
table1
inner join table 2 on 1.columna = 2.columnb

I know that there huge gap between what is really going on and the code above but you get the main idea. moving the sum to a select so that the group by is no longer required. This and this allow drastically reduced the amount of time that it took to get the data. I knew that group by was expensive I just didn't realize how expensive it was.

|||

The group by clause is not in itself a bad performer. There is something else at work but without more detail, I can't tell you what.

The two queries you gave aren't the same thing. The second query doesn't do a sum based on the contents of the current row (No where clause relating the two). Which then of course it runs much faster, it's only executing the sum once, and using it on every row of the outer query.

|||

No mystery. "If I cannot do a reasonable query of a couple of million records..."

Sorting a couple of million records is, well, expensive! That's what a group by does, it sorts. And you don't even have a where clause to limit the answer set.

If you put a clustered index on column1, column2, column3 it will be able to avoid the sort, but you need to look at that carefully since it may have an impact on other queries (and you may already have a clustered index)

|||

True, the second query is unsorted (Not that common to request a set of data and not care about it's sort order), which will obviously be a completely different query plan. I would venture to guess that you don't have a good index on the table either that can/will help you.

Instead of putting a clustered index on the table, if you put an index on column1,column2,column3 and the field you are summing, your query time will drop significantly as well.

Faster yet, would be to use an indexed view.

|||

I am hearing basically what I thought I would hear. GROUP BY equals SORTING, a couple million records is a lot of data, no need to aviod GROUP BY like the plauge, check the indexes and statistics too.

Thanks. There is never a right or wrong answer to this kind of thing, it always depends on the shop and the database.

Group Access Problem

Hi here
After I installed reporting service on Windows 2000 server and upgrade
sp1, I got group access problem. For example, I grant one active directory
group (server name\group name ) as browser of a folder. But the user in this
group can't access the folder, get the permission error.
Anything I can resolve this problem. Because I don't want to add one by
one single user to access the report.
Thanks in advance
TracyI think some security info is cached. Does it work later, or after
restarting? This should not have changed in SP1.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tracy" <trlu@.vsb.bc.ca> wrote in message
news:ee2mECWXEHA.808@.tk2msftngp13.phx.gbl...
> Hi here
> After I installed reporting service on Windows 2000 server and upgrade
> sp1, I got group access problem. For example, I grant one active
directory
> group (server name\group name ) as browser of a folder. But the user in
this
> group can't access the folder, get the permission error.
> Anything I can resolve this problem. Because I don't want to add one by
> one single user to access the report.
> Thanks in advance
> Tracy
>|||Hi Jason,
Finially we found out because that group is a distribute group instead
of a security group, that's the reason the user under the group doesn't have
the access to view the folder.
Thanks
Tracy
"Jason Carlson [MSFT]" <jasoncar@.microsoft.com> wrote in message
news:%23jQ0%23hZXEHA.4000@.TK2MSFTNGP09.phx.gbl...
> I think some security info is cached. Does it work later, or after
> restarting? This should not have changed in SP1.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "tracy" <trlu@.vsb.bc.ca> wrote in message
> news:ee2mECWXEHA.808@.tk2msftngp13.phx.gbl...
> > Hi here
> > After I installed reporting service on Windows 2000 server and
upgrade
> > sp1, I got group access problem. For example, I grant one active
> directory
> > group (server name\group name ) as browser of a folder. But the user in
> this
> > group can't access the folder, get the permission error.
> > Anything I can resolve this problem. Because I don't want to add one
by
> > one single user to access the report.
> >
> > Thanks in advance
> >
> > Tracy
> >
> >
>