Grouping problem

I am working on a report for a small POS. The report should allow the user to choose the time interval to group sales records, e.g. 1 hour, 2 hours or 4 hours. I believe I can setup this by the DiscretizationMethod and DiscretizationBucketCount of the Hour attribute in my DimTime dimension. However, the problem that I am facing is this POS will support multiple branches. Each branch will have their particular opening and closing hour. So, how can I group all the transaction into groups, said "Before Shop Open" and "After Shop Closed"? This sounds strange but will happen quite often as overtime work is always expected in my living place.

If this is infeasible, is there any workaround? I think the business user certainly want to know how many transaction has been created in those extra time.

In the other report, it is required to generate a transaction count by amount. The user should be able to specify the amount interval and upper limit. e.g. if amount interval and upper limit are set to 50 and 150, then the transaction will be grouped into 4.
0<=amount<50
50<=amount<100
100<=amount<150
amount>=150

I have no idea to this. First, I don't know how can I get the amount for each sale order as my fact table is storing sales order item information only. Second, how can I make this customizable grouping just like the report stated above? Thanks!

Hi Alex:

You pose two difficult problems. I'll address the second problem because you provided the most detail and clearly stated the issues. To restate, the issues are:

(1) How can you get the amount for each sale order?

(2) How can you allow customizable grouping?

Addressing issue (1) about the amount for the sales order. If the sales amount for the sales order is not in your fact table then you will not be able to access the sales amount in your cube. You have to go back to the ETL process and bring in the sales amount as part of yur fact table.

Issue (2), customizable grouping, is best approached on the client side of your application. Alternatively you, as an administrator, could create a separate attribute hierarchy for each branch with it's own amount interval and upper limit. I think your choice of a solution (client side, or separate hierarchy per branch) depends upon how many branches you have, and how much management you want to put in as an administrator. Creating transaction count by amount on the client is simple if you have the transaction amount as a measure. Get the transaction count by using a calculated member with the MDX count() function. Within each query you can adjust the amount interval and upper limit for each user. Here's an example:

WITH MEMBER MEASURES.[Less than 50] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] < 50)'

MEMBER MEASURES.[Between 50 and 100] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] > 50 AND Measures.[Sales Amount] < 100)'

SELECT {MEASURES.[Less than 50] , MEASURES.[Between 50 and 100]} ON COLUMNS FROM [my cube]

Hope this helps.

PGoldy

|||Hi PGoldy,

First, thank you for your input to these difficult problems that I am facing right now. Actually, I have come up with sort of solution after the post but it still doesn't work very well.

For issue 1, I found out that even I don't have a total for the sales order stored in the fact table. I can get it by creating a "Named Query". In this query, I will group the fact table records by the transaction ID. In this way, I obtain the sales amount per transaction, not per item. It looks good.

For issue 2, I use the "Named Query" that just created a bit further. In that query, besides the total amount per transaction. I create another field which is a floored amount. I am using this function.

floor(convert(decimal, sum(ItemAmount)) / 50) * 50

By doing this, I am able to make those sales total into the starting value of their groups. e.g. 38 returns 0, 59 returns 50 and 160 returns 150.
It seems really good at first. However, I have another problem to make this perfect or really usable. In SSAS, if there's no data exists for a specific group. It won't get display. e.g. if I got 38, 59 and 160 in my sales order total. I will only get the groups 0~49, 50~99 and 150~149. The problem is the missing 100~149. For business user, I think it's not acceptable to have a gap in the report like this. So, how can I fill in this gap?

Moreover, is there any best practice for my situation? I think this is a very common scenario but I can't find any useful reference.

Regards,
Alex|||

Hi Alex:

Best practice is creation of a hierarchy which has the "bucket" ranges you want. Then link each fact table record to the appropriate bucket with a foreign key. It's a common practice and used in most implementations. Below is a link to a series of articles by Bill Pearson which articulate (very well) the functionality you're looking for and a lot more. Good luck.

PGoldy

|||Dear PGoldy,

Could you please check whether the links has been posted? Thanks!

Regards,
Alex|||

Hi Alex. Sorry about the delay. Below is the link. PaulG

http://www.databasejournal.com/article.php/1459531

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 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 by week starting on Sunday

I have a matrix report where I group employee hours by week. I have the
grouping working using the following expressions:
=DatePart(DateInterval.WeekOfYear,Fields!businessdate.Value)
=DatePart(DateInterval.Year, Fields!shift_date.Value)
It looks like the week of year starts on monday. Is there a way to change
this to start on Sunday? Or is there a better way to group by week starting
on Sunday? Any help is appreciated.I tried expression:
=DatePart("ww", Fields!shift_date.Value, FirstDayofWeek.Sunday)
and
=DatePart("ww", Fields!shift_date.Value, vbSunday)
neither of these work either.
"TBraun" wrote:
> I have a matrix report where I group employee hours by week. I have the
> grouping working using the following expressions:
> =DatePart(DateInterval.WeekOfYear,Fields!businessdate.Value)
> =DatePart(DateInterval.Year, Fields!shift_date.Value)
> It looks like the week of year starts on monday. Is there a way to change
> this to start on Sunday? Or is there a better way to group by week starting
> on Sunday? Any help is appreciated.

Grouping accuracy

Hi,
This is a problem which is causing a few difficulties. We have a time
recording system which just tracks what people are working on. The system
basically keeps tracking of how much time is spent by a person on a certain
type of work.
The problem is when we group the data by different categories, the total
usually never matches exactly. The time spent on work is stored as an intege
r
in the table representing "minutes" e.g. 450 mintues is the equivalent of 1
day (we work a 7.5 hour day".
Run this is Query Analyser and you can see what I mean. You will see that
the the 2 query results returned will differ in their total "days" by 0.01.
The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
What can I do to make this fully accurate? Should I use a different datatype
in the table?
--Create a Test Table, it will be dropped later
create table timeRecords (person char(5),Workdate datetime,timeInMins
integer,worktype varchar(10))
--Insert Some Test Data
insert timeRecords
values('jackp','2005-10-01',225,'Client')
insert timeRecords
values('jackp','2005-10-01',225,'R&D')
insert timeRecords
values('jackp','2005-10-01',60,'Sales')
insert timeRecords
values('peter','2005-10-01',225,'Client')
insert timeRecords
values('peter','2005-10-01',180,'R&D')
insert timeRecords
values('peter','2005-10-01',160,'Sales')
insert timeRecords
values('jackp','2005-10-01',225,'Client')
insert timeRecords
values('jackp','2005-10-01',120,'R&D')
insert timeRecords
values('jackp','2005-10-01',60,'Sales')
insert timeRecords
values('peter','2005-10-01',80,'Client')
insert timeRecords
values('peter','2005-10-01',180,'R&D')
insert timeRecords
values('peter','2005-10-01',15,'Sales')
--Group by Person
select person,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by person
--Group by WorkType
select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by worktype
--Drop the table
drop table timeRecordsIt's because of round-off. When I changed the decimal casts to decimal (9,
4), the first totalled 3.9000, while the second came to 3.9001.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"NH" <NH@.discussions.microsoft.com> wrote in message
news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> Hi,
> This is a problem which is causing a few difficulties. We have a time
> recording system which just tracks what people are working on. The system
> basically keeps tracking of how much time is spent by a person on a
> certain
> type of work.
> The problem is when we group the data by different categories, the total
> usually never matches exactly. The time spent on work is stored as an
> integer
> in the table representing "minutes" e.g. 450 mintues is the equivalent of
> 1
> day (we work a 7.5 hour day".
> Run this is Query Analyser and you can see what I mean. You will see that
> the the 2 query results returned will differ in their total "days" by
> 0.01.
> The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
> What can I do to make this fully accurate? Should I use a different
> datatype
> in the table?
> --Create a Test Table, it will be dropped later
> create table timeRecords (person char(5),Workdate datetime,timeInMins
> integer,worktype varchar(10))
> --Insert Some Test Data
> insert timeRecords
> values('jackp','2005-10-01',225,'Client')
> insert timeRecords
> values('jackp','2005-10-01',225,'R&D')
> insert timeRecords
> values('jackp','2005-10-01',60,'Sales')
> insert timeRecords
> values('peter','2005-10-01',225,'Client')
> insert timeRecords
> values('peter','2005-10-01',180,'R&D')
> insert timeRecords
> values('peter','2005-10-01',160,'Sales')
> insert timeRecords
> values('jackp','2005-10-01',225,'Client')
> insert timeRecords
> values('jackp','2005-10-01',120,'R&D')
> insert timeRecords
> values('jackp','2005-10-01',60,'Sales')
> insert timeRecords
> values('peter','2005-10-01',80,'Client')
> insert timeRecords
> values('peter','2005-10-01',180,'R&D')
> insert timeRecords
> values('peter','2005-10-01',15,'Sales')
> --Group by Person
> select person,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
> decimal(9,2))
> From timeRecords
> group by person
> --Group by WorkType
> select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
> decimal(9,2))
> From timeRecords
> group by worktype
> --Drop the table
> drop table timeRecords|||"NH" <NH@.discussions.microsoft.com> wrote in message
news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> Hi,
> This is a problem which is causing a few difficulties. We have a time
> recording system which just tracks what people are working on. The system
> basically keeps tracking of how much time is spent by a person on a
> certain
> type of work.
> The problem is when we group the data by different categories, the total
> usually never matches exactly. The time spent on work is stored as an
> integer
> in the table representing "minutes" e.g. 450 mintues is the equivalent of
> 1
> day (we work a 7.5 hour day".
> Run this is Query Analyser and you can see what I mean. You will see that
> the the 2 query results returned will differ in their total "days" by
> 0.01.
> The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
> What can I do to make this fully accurate? Should I use a different
> datatype
> in the table?
>
It's not your datatype that is at issue. Decimals (and Numeric) store data
in an exact format. Unlike floats and reals, which are a close
approximation. The problem is that by default SQL Server uses a ROUND UP
strategy when performing computations. If you switch everything to decimal
(9,6) you should see better numbers pop up.
You will see:
jackp 2.033333
peter 1.866667
--
3.900000
Client 1.677778
R&D 1.566667
Sales .6555556
--
3.900001
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||thanks for the reply.
So there is no way really of making this fully accurate?
I understant it is a rounding issue, I thought maybe there was something I
was overlooking or some way of doing it better so there are no rounding
effects.
"Tom Moreau" wrote:

> It's because of round-off. When I changed the decimal casts to decimal (9
,
> 4), the first totalled 3.9000, while the second came to 3.9001.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
>
>|||The numbers need to be reported to 2 decimal places, I guess we will live
with it. I knew the cause of it was rounding up but I wasnt sure if there wa
s
a best practice way of doing this. Sounds like there isn't much we can do
about it.
"Rick Sawtell" wrote:

> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> It's not your datatype that is at issue. Decimals (and Numeric) store dat
a
> in an exact format. Unlike floats and reals, which are a close
> approximation. The problem is that by default SQL Server uses a ROUND UP
> strategy when performing computations. If you switch everything to decim
al
> (9,6) you should see better numbers pop up.
> You will see:
> jackp 2.033333
> peter 1.866667
> --
> 3.900000
> Client 1.677778
> R&D 1.566667
> Sales .6555556
> --
> 3.900001
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||The problem is that the numbers work out to repeating decimals. It's just
as likely to be 0.001 too low as .001 too low. If you use WITH ROLLUP, the
total is 3.90:
select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by worktype
with rollup
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"NH" <NH@.discussions.microsoft.com> wrote in message
news:B0DAEC4B-EBF1-4F92-812D-D15852E959E4@.microsoft.com...
> thanks for the reply.
> So there is no way really of making this fully accurate?
> I understant it is a rounding issue, I thought maybe there was something I
> was overlooking or some way of doing it better so there are no rounding
> effects.
> "Tom Moreau" wrote:
>

Group Total..

I'm working on a Financial Report which contains a column "XYZ" , its value
is calculated from a formula by passing the row's record id and commission
rate. (the formula is inside a custom dll). The values are correctly
computed. Now, the footer should display the total of all the rows in the
group.
for instance:
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
Pepsi $361,000 gfyeefyefffee
Coca Cola $475,250 djfdfjdfddddd
RCola $28,757 re8reruejreerr
fdfsfnfsfssf
_________________________________________
Total: $ 865,007
Each of the "XYZ Total" in the above example, uses an expression as = FindTotal(recID!value, comm_rate!value)
In this case, how do I get the total in the footer? How to recursively add
the FindTotal expression when it contains the row's unique record id?
Thanks
P.S. The above data is a sample data. The actual report contains 3 different
levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
Transaction TitleI recently came across a new software, that I think you might want to look into.
www.simx.com/simx/home_report%20manager.htm
Works with SQL Server, and I was able to do reporting much like what you are describing.
"newmem" <"" wrote:
> I'm working on a Financial Report which contains a column "XYZ" , its value
> is calculated from a formula by passing the row's record id and commission
> rate. (the formula is inside a custom dll). The values are correctly
> computed. Now, the footer should display the total of all the rows in the
> group.
> for instance:
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> Pepsi $361,000 gfyeefyefffee
> Coca Cola $475,250 djfdfjdfddddd
> RCola $28,757 re8reruejreerr
> fdfsfnfsfssf
> _________________________________________
> Total: $ 865,007
> Each of the "XYZ Total" in the above example, uses an expression as => FindTotal(recID!value, comm_rate!value)
> In this case, how do I get the total in the footer? How to recursively add
> the FindTotal expression when it contains the row's unique record id?
> Thanks
> P.S. The above data is a sample data. The actual report contains 3 different
> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> Transaction Title
>
>|||While I appreciate your eagerness to help, I think that most people would
prefer that you refrain from advertising other products in a forum dedicated
to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
not to post there. :)
That being said, you should be able to define a custom field that does the
calculation and then referce the custom field in a sum in the group footer.
Presumably, you only need to add values from the inner group as the outer
group is just summary. If you want it to do parent / child hierarcy
aggregates, you need to use the recursive keyword.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>I recently came across a new software, that I think you might want to look
>into.
> www.simx.com/simx/home_report%20manager.htm
> Works with SQL Server, and I was able to do reporting much like what you
> are describing.
> "newmem" <"" wrote:
>> I'm working on a Financial Report which contains a column "XYZ" , its
>> value
>> is calculated from a formula by passing the row's record id and
>> commission
>> rate. (the formula is inside a custom dll). The values are correctly
>> computed. Now, the footer should display the total of all the rows in the
>> group.
>> for instance:
>> "Unit" "BrandName" "XYZ Total" "Comments"
>> Sodas
>> Pepsi $361,000 gfyeefyefffee
>> Coca Cola $475,250 djfdfjdfddddd
>> RCola $28,757 re8reruejreerr
>> fdfsfnfsfssf
>> _________________________________________
>> Total: $ 865,007
>> Each of the "XYZ Total" in the above example, uses an expression as =>> FindTotal(recID!value, comm_rate!value)
>> In this case, how do I get the total in the footer? How to recursively
>> add
>> the FindTotal expression when it contains the row's unique record id?
>> Thanks
>> P.S. The above data is a sample data. The actual report contains 3
>> different
>> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> Transaction Title
>>|||Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as => >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>

Group Total Not working right

Hi,

I have a problem that i cannot figure out how to fix.

I have a sub report that i need to have the group totals in the Page header and i cannot for the life of me remember how to do this.

I have grouped by field 2 which gives me a time for Planned and unplanned events, I need to add up the time of the Planned items and put next to my Downtime Planned Text Box, and then sum the Unplanned and do the same against my unplanned downtime box

Regards SteveExample:
subreport 1 formula:
whileprintingrecords;
shared numbervar x:= sum({table.field})
subreport 2 formula:
whileprintingrecords;
shared numbervar y:= sum({table.field})
Formula in the main report:
whileprintingrecords;
shared numbervar x;
shared numbervar y;
x+y|||just to add some thing. You can use the sum function as

sum({table.field}, "Group on the field");

So you can get the planned and unplanned in the same subreport.|||Very confused with First reply, but will have a go at that, Second reply worked fine fo the same report

Regards

Steve

Group Filter Not Working

I'm developing a simple client application, using local reports populated from a local dataset, which is in turn saved and read from xml.
I am trying to set up a VERY simple group filter expression and I'm running into problems. What I want to do is display all rows where a date column matches a parameter passed to the report. I'm setting the filter up using something like this:
Expression Operator Value
=Fields!SampleDate = =Parameters!DateParam
I keep getting the following error:

Processing Errors
Value cannot be null.
Parameter name: value
OK

I know I'm probably doing something wrong. The only filter that I could get to not give the error is 1=1. LOL. Please help!You may want to try this approach:

Filter expression:
=(Fields!SampleDate.Value = Parameters!DateParam.Value)

Operator:
=

Filter Value:
=True
-- Robert

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 BY Question

Hi, I am trying to group the records by there games.name but it isnt working, I know in mysql you just use GROUP BY but in sql server its different I guess.

A detail explanation would be instead of

CheatID GameID Title Game Console
1 1 Test NFS PC
2 1 Test NFS PC
3 2 Test NFS2 PS2

I need to do this

CheatID GameID Title Game Console
1 1 Test NFS PC
3 2 Test NFS2 PS2

SELECT TOP 5
Cheats.CheatID,
Cheats.GameID,
Cheats.Title,
Cheats.Added,
Games.Name Game,
Consoles.Name Console
FROM Cheats
INNER JOIN Games ON (Games.GameID = Cheats.GameID)
INNER JOIN Consoles ON (Consoles.ConsoleID = Games.ConsoleID)
GROUP BY Games.Name
ORDER BY Cheats.Added DESC

Does anyone know a solution to this?

Thanks, Mike

You need to tell in SQL what to do with the CheatID. I'm surprised that the code you have even compiles, since you are using group expressions on some columns and not others.

Something like this (I removed the Cheats.GameID since you don't need it in the select statement):

SELECT TOP 5
MAX(Cheats.CheatID),
Cheats.Title,
Cheats.Added,
Games.Name Game,
Consoles.Name Console
FROM Cheats
INNER JOIN Games ON (Games.GameID = Cheats.GameID)
INNER JOIN Consoles ON (Consoles.ConsoleID = Games.ConsoleID)
GROUP BY Games.Name
ORDER BY Cheats.Added DESC

You might even want to change the MAX to a COUNT of Cheats.CheatID, then in the gridview you can display the number of cheats for the game on a given console rather than an arbitrary cheat ID.|||

No it doesn't compile. that was my problem. and your solution will not compile either.

I need to select mainly the gameid, title, games.name and consoles.name and group by the games.name and order by the cheats.added

thanks, mike

|||no one knows a solution to this?

GROUP BY not working?

I'm having a really odd problem.. this is basic SQL, so it should be easy for most of you. For some strange reason, the quantity is not being summed up. Any idea why this would be happening? Everything else is grouped. The SQL is below, and some of the results are below that (you can see that it SHOULD be grouping them properly).

SELECT TOP 100 PERCENT dbo.cp_elements.campaign_id, dbo.cp_campaigns.name AS campaign_name, dbo.cp_elements.item_no, dbo.cp_elements.name,
dbo.cp_orderables.est_qty, dbo.cp_orderables.qty_increment, SUM(dbo.cp_order_detail.qty) as qty, dbo.cp_attribs.price
FROM dbo.cp_orderables INNER JOIN
dbo.cp_elements ON dbo.cp_orderables.element_id = dbo.cp_elements.element_id INNER JOIN
dbo.cp_attribs ON dbo.cp_orderables.orderable_id = dbo.cp_attribs.orderable_id INNER JOIN
dbo.cp_order_detail ON dbo.cp_attribs.attrib_id = dbo.cp_order_detail.attrib_id INNER JOIN
dbo.cp_selected_shiptos ON dbo.cp_order_detail.shipto_id = dbo.cp_selected_shiptos.shipto_id INNER JOIN
dbo.cp_campaigns ON dbo.cp_elements.campaign_id = dbo.cp_campaigns.campaign_id
GROUP BY dbo.cp_elements.campaign_id, dbo.cp_campaigns.name, dbo.cp_elements.item_no, dbo.cp_elements.name, dbo.cp_selected_shiptos.state,
dbo.cp_orderables.qty_increment, dbo.cp_attribs.price, dbo.cp_orderables.est_qty
ORDER BY dbo.cp_elements.item_no

RESULTS:
16,Project Z,10S,Ten Spot,10,1,0,9.9900
16,Project Z,10S,Ten Spot,10,1,15,9.9900
16,Project Z,10S,Ten Spot,10,1,0,9.9900

That 2nd from last column is the qty_increment.. it's spitting out 3 rows when it should just show one, with 15 as the qty_increment.

Thanks for any help/clues!Hi,

I noticed that u have missing the dbo.cp_selected_shiptos.state column in SELECT COLUMNS. You have to give all the group by columns in SELECTed columns. Also Group by clause should have the ORDER BY COLUMN. This might be the reason that u face.

U check it out and let me know. for Further,|||AHHHHH!! Duh! Stupid mistake... these are the usual kinds of mistakes I run into :) Nothing complicated or serious, just something really minor.

Thanks for pointing that out :) I removed state from the group by and it works fine now.

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 by and Order By Problem

I have the following statement that is working just fine:

SQL = "select* from products GROUP BY name ORDER BY price"

I have many products with the same name but with different prices and I want to display unique records along with other information but I want to display the cheapest product first. It is displaying the first record in the table with that name but not the cheapest distinct record in the list of all distinct product names.

Not sure of the above makes sense so this is the kind of data in the table.

Name= Test1, Price= 25

Name= Test2, Price= 20

Name= Test1, Price= 15

Name= Test3, Price=30

When listing the products it will show the following order:

Test2, 20

Test1, 25

Test3, 30

However I want it to display:

Test1, 15

Test2, 25

Test3, 30

Thanks for any help.

Daniel

What version of SS are you working with?

In case of having same product with same price multiple times, then we need a tie breaker in order to make the solution deterministic.

select *

from dbo.product as a

where not exists (

select *

from dbo.product as b

where b.[name] = a.[name] and b.price < a.price

)

select *

from dbo.product as a

where price = (

select min(b.price)

from product as b

where b.[name] = a.[name]

)

select *

from dbo.product as a

where price = (

select top 1 b.price

from product as b

where b.[name] = a.[name]

order by b.price

)

-- 2005

with cte

as

(

select *, row_number() over(partition by [name] order by price) rn

from dbo.product

)

select *

from cte

where rn = 1

go

AMB

|||Just a kind reminder, the 2000 and 2005 solution may not return the same results.
Such as
Name= Test1, Price= 25
Name= Test2, Price= 20
Name= Test1, Price= 15
Name= Test3, Price=30
Name= Test1, Price=15

2000 Version return:

Test1,15
Test2,20
Test3,30
Test1,15

2005 version return:
Test1,15
Test2,20
Test3,30

To make two solutions equal, it needs add DISTINCT to outer select.

Such as:

select DISTINCT *

from dbo.product as a

where not exists (

select *

from dbo.product as b

where b.[name] = a.[name] and b.price < a.price

)

|||

You are right. I should have used dense_rank instead row_number or "distinct" as you mentioned. I would prefer to include a second column, like transaction_id, as the tie breaker.

AMB

GridView delete function

This is killing me. I've searched the forums for hours and can't find the answer. My SQLDataSource is working fine except when I want to delete. I've allowed the delete function to be shown on the gridview. This is my SQLDataSource:

 <asp:SqlDataSource ID="IndexDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IndexConnectionString%>" SelectCommand="SELECT * FROM [Index] WHERE (Type LIKE '%' + @.SearchText2 + '%') OR (Product LIKE '%' + @.SearchText2 + '%') OR (Version LIKE '%' + @.SearchText2 + '%') OR (Binder LIKE '%' + @.SearchText2 + '%') OR (Language LIKE '%' + @.SearchText2 + '%') OR (CDName LIKE '%' + @.SearchText2 + '%') OR (Details LIKE '%' + @.SearchText2 + '%') OR (ISOLink LIKE '%' + @.SearchText2 + '%')" DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.original_ID" UpdateCommand="UPDATE [Index] SET Type = @.Type, Product = @.Product , Version = @.Version, Binder = @.Binder, Language = @.Language, CDName = @.CDName, Details = @.Details, ISOLink = @.ISOLink WHERE ID = @.ID"> <SelectParameters> <asp:ControlParameter Name="SearchText2" Type="String" ControlID="SearchText2" PropertyName="Text" ConvertEmptyStringToNull="False" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Type" /> <asp:Parameter Name="Product" /> <asp:Parameter Name="Version" /> <asp:Parameter Name="Binder" /> <asp:Parameter Name="Language" /> <asp:Parameter Name="CDName" /> <asp:Parameter Name="Details" /> <asp:Parameter Name="ISOLink" /> </UpdateParameters> </asp:SqlDataSource>

It doesn't give me an error if I click delete but it doesn't delete the record. I've tried changing the DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> but it gives me the error "Must declare the scaler variable of '@.ID'"... I saw in this post http://forums.asp.net/p/1077738/1587043.aspx#1587043 that the answer was that "The variable you have declared in the definition of the proc isdifferent from the variable you are using in the WHERE clause." when they are both the same. Thanks for any help.

-Brandan

Hello

What if you add a semicolumn after @.original_ID ? like this"DELETE FROM [Index] WHERE [ID] = @.original_ID;"

|||

Is the ID column your table's primary key? If yes, you need to make sure that the ID is set in your GridView's DadaKeyNames and you should change your DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> and DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.ID"

If you cannot make this work, please post your GridView part here and if you can list all your columns' name instead of a * in your SELECT statement, that would be great. Thanks.

|||

If it doesn't fixes it it might just be the DataKeyNames field of the gridview that need to be set to ID .

|||

**RESOLVED**

It was definitely the DataKeyNames. I had recreated the gridview so many times I forgot to put it back in. thanks ya'll.