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 databases with a folder

Does anyone know if there is a way to group user databases in Management
Studio the same way system databases are grouped within a folder? We have
several databases on one server and it is becoming cumbersome to locate the
database a user wants.
Thanks,
CB
No, this functionality is not currently supported. You can vote for similar
requests though:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=209340
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125921
I created one explicitly for custom grouping of databases in Object
Explorer, since it is not quite the same as the others, and your thoughts
prodded me to realize that I could benefit from this specific change as
well:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=290825
Aaron Bertrand
SQL Server MVP
"chrisb" <chrisb@.discussions.microsoft.com> wrote in message
news:26DBDF02-F34A-4A24-918A-F5F785FEEC24@.microsoft.com...
> Does anyone know if there is a way to group user databases in Management
> Studio the same way system databases are grouped within a folder? We have
> several databases on one server and it is becoming cumbersome to locate
> the
> database a user wants.
> Thanks,
> CB

grouping data...

greetings,
i want to show a 2nd lvl report, but i dont want it to be group.. e.g. when
my user clicks on the row or on the + sign , it expends and execute another
query. is that possible?Use drill-through. Click on the row or textbox you want the user to click on,
then go to the Properties window and find a property called Action. Click on
the ellipsis button to the right of it, and choose Jump to report. Take it
from there!
Charles Kangai, MCT, MCDBA
"Asha" wrote:
> greetings,
> i want to show a 2nd lvl report, but i dont want it to be group.. e.g. when
> my user clicks on the row or on the + sign , it expends and execute another
> query. is that possible?

Grouping Data for Consolidated Notification

We have a requirement where we need to send a single consolidated list of items belonging to a user which get active on particular date.

So, for example Item 1, Item 2, Item 3 gets active. These entries are inserted as Events. Now, we need to send single notification to the user with the email as:

=========================

Dear User,

Your following items got active today:

Item 1

Item 2

Item 3

Thanks,

Customer Care

=========================

For this we set the DigestDelievery to true and the emails indeed were consolidated. But, what it did was that it send single email notification repeating the entire content for each item as follows:

=========================

Dear User,

Your following items got active today:

Item 1

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 2

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 3

Thanks,

Customer Care

=========================

Please let me know if we are missing any setting or any changes need to be made in the .xslt for this to work.

regards,

Rajiv

I assume you are using a built-in XSLT formatter.

Since I have no idea about how to use/configure XSLT, in my application I created a custom content formatter in C# (this is really easy), and over there (in the .cs class) I "manually" built the resulting HTML string and inserted my items in a loop. The result HTML string is then sent as the email body.

Advantages:

1. I have 1 message "header" and 1 "footer". What's in between, gets "populated" at runtime in a loop, whether it's just 1 item or many.

2. Maybe, it's just as easy when using XSLT, ... I just don't know. But the emails my customers get contain hyperlinks which bring them right to the web page(s) for those particular item(s). Your management will love this feature!

3. Although I did not have to use this in my project, but if you need this, you can easily fetch additional data from some other non-NS data sources and "plug" it into your email message. It's possible because in a custom content formatter (C# or VB.NET, - your choice) you can use whatever .NET techniques you need, such as ADO.NET, System.IO (if you need to read from, say, some XML files), and whatever else you might want to do: a custom content formatter is just a regular .NET assembly, and you can use it as such.

|||Hi Rajiv -

From a SSNS perspective, it sounds like you have everything configured for digest delivery properly. Since it's not being formatted the way you wish, the issue is in the content formatter.

If you are using the built-in XSLT content formatter, try adjusting the
XSLTransform document.

Try placing the header and footer text directly in the XSLT document and the notification data in an <xsl:template> Match on "notification". You can then use teh <xsl:apply-templates> to call the notification section.

HTH...

Joe|||

Thanks for reply, I was able to resolve the issue as you have suggested.

regards,

Rajiv

Grouping Data By Weeks

I have a requirement to produce a report that breaks down some data into
totals by w. The data is in SQL Server, but the user just wants a one-off
report, so we can use Access or Excel as alternatives is more suitable.
We have a table of stock movements, and we want to total the number of
incoming and outgoing items for each w. We obviously know the dates of
these movements, but I'm unclear as to the SQL (or even general approach)
needed to break down this data.
StockMovements Table:
StockMoveID int
SerialNo int
MoveDate int
LocationFrom int
LocationTo int
etc
Report Format:
Wk Beginning | Num Issued | Num Returned | Running Total | Num Overdue
Any suggestions or pointers?
Thanks in advance,
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Do you want to use ISO ws or just a fixed 7-day w?
For a fixed 7-day w:
SELECT ...
FROM YourTable
GROUP BY ROUND(DATEDIFF(D,'20000101',movedate)/7.0,0,1)
(where '20000101' is a "base date" representing your chosen beginning
date of some arbitrary w)
For ISO ws look at the CREATE FUNCTION topic in Books Online for the
relevant formula.
David Portas
SQL Server MVP
--|||SELECT *
FROM <TABLE>
GROUP BY datepart(ww, <date> )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"CJM" wrote:

> I have a requirement to produce a report that breaks down some data into
> totals by w. The data is in SQL Server, but the user just wants a one-o
ff
> report, so we can use Access or Excel as alternatives is more suitable.
> We have a table of stock movements, and we want to total the number of
> incoming and outgoing items for each w. We obviously know the dates of
> these movements, but I'm unclear as to the SQL (or even general approach)
> needed to break down this data.
> StockMovements Table:
> StockMoveID int
> SerialNo int
> MoveDate int
> LocationFrom int
> LocationTo int
> etc
> Report Format:
> Wk Beginning | Num Issued | Num Returned | Running Total | Num Overdue
> Any suggestions or pointers?
> Thanks in advance,
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||Be careful with the w returned by DATEPART. The w numbering
convention it uses is a bit unusual. Even assuming that the DATEFIRST
setting is correct many people won't find the result of this function
useful.
David Portas
SQL Server MVP
--|||David,
Thanks for that - it looks to be a start, but I'm not quite there yet.
As I mention before I want the following columns for the report: W
commencing, Total Sent, Total Received, Running Total, & Total Overdue
I've started creating the SQL for the Total Sent/Received, but I still have
a problem - your code provides for a w number but how do I engineer the
W Commencing date from this?
Select Count(*) as NumSent, ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
as Wk
from StockMovements
where LocationTo = 2
and MoveDate > '20031231'
and MoveDate < '20050601'
GROUP BY ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
Select Count(*) as NumRecd, ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
as Wk
from StockMovements
where LocationFrom = 2
and MoveDate > '20031231'
and MoveDate < '20050601'
GROUP BY ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
Also, how do I combine these into one query? I tried Select (Select
...etc), (Select...etc) but it came up with an error:
"Only one expression can be specified in the select list when the subquery
is not introduced with EXISTS"
I'm not clear on what this means, not how to avoid it.
Thanks for your help so far...
Chris|||You might want to look up the ISO definition of a w within a year,
since it is different from Microsoft's and finally talk to teh
accounting department about the ws in the fiscal calendar. The best
way to handle this is to set up a calendar table with yourt fiscal
ws in it.|||Hello,
I notice you have posted the same question in our newsgroup, which I have
already responded. So please check my answer there.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Thu, 26 May 2005 15:09:54 +0100, CJM wrote:

>Thanks for that - it looks to be a start, but I'm not quite there yet.
>As I mention before I want the following columns for the report: W
>commencing, Total Sent, Total Received, Running Total, & Total Overdue
>I've started creating the SQL for the Total Sent/Received, but I still have
>a problem - your code provides for a w number but how do I engineer the
>W Commencing date from this?
(snip)
>Also, how do I combine these into one query?
Hi CJM,
I noticed that you crossposted this to a SQL Server group and an Access
group. Since my answer works in SQL Server only, I removed the access
group for my reply.
Check if the following works (note: this assumes you consider monday to
be the first day of the wee; change the date constant '20031229' to
something else if you need another first day of the w - and remember
that it has to be changed in all four places it's used!)
SELECT DATEADD(day,
DATEDIFF(day, '20031229', MoveDate) / 7 * 7,
'20031229') AS WCommencingDate,
COUNT(CASE WHEN LocationTo = 2 THEN 1 END) AS TotalSent,
COUNT(CASE WHEN LocationFrom = 2 THEN 1 END) AS TotalReceived
FROM StockMovements
WHERE MoveDate > '20031231'
AND MoveDate < '20050601'
GROUP BY DATEADD(day,
DATEDIFF(day, '20031229', MoveDate) / 7 * 7,
'20031229')
This is untested, since you didn't post CREATE TABLE and INSERT
statements to create a test database. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Grouping by unrelated field- SQL masters, try this!

I would like to retrieve 10(dynamic) records of table x (proucts) for
each user in table y (users). Can this be done?
I would like the end result to be something like this: (would this be
a union?)
__________________________
y.name | x.pid | x.pname
Bob | 1 | fork
Bob | 2 | spoon
... | |
Bob | 10 | potato
Jeff | 11 | pen
etc....
__________________________
But also with the number to return based off of a query, ex-
select @.pcount = count(products)
select @.ucount = count(users)
select @.pcount / @.ucount
10
And lump all this in an Stored procedure
ex-
get number of total records in x, divide by total y = z
select z records for each user in y.
You would be a master in my book if you can give me hints on this one!
Thanks,
JeffHi

It is always better to post DDL ( CREATE TABLE statements etc...) and
example data (as insert statements) with the expected results that you
require from that data. That removes most of the ambiguities and reduces
that number of assumptions that someone answers your question will have to
make.

This seems to be something similar to what you require
http://tinyurl.com/28dhn

John

"JC" <ujjc001@.charter.net> wrote in message
news:b8c0d25d.0407061959.2f9791ca@.posting.google.c om...
> I would like to retrieve 10(dynamic) records of table x (proucts) for
> each user in table y (users). Can this be done?
> I would like the end result to be something like this: (would this be
> a union?)
> __________________________
> y.name | x.pid | x.pname
> Bob | 1 | fork
> Bob | 2 | spoon
> ... | |
> Bob | 10 | potato
> Jeff | 11 | pen
> etc....
> __________________________
> But also with the number to return based off of a query, ex-
> select @.pcount = count(products)
> select @.ucount = count(users)
> select @.pcount / @.ucount
> 10
> And lump all this in an Stored procedure
> ex-
> get number of total records in x, divide by total y = z
> select z records for each user in y.
> You would be a master in my book if you can give me hints on this one!
> Thanks,
> Jeffsql

Grouping by hour, day, month, etc

I have tables which record data entered by six users.
I would like to creat a query which will return the number of entries
created by each user. The UserId is recorded for each record along with a
date stamp.
I would like to be able to group these results by hour, day, etc.Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||SELECT Count(EntryKey), UserID, DatePart(hh,DateTimeStamp) as TheHour,
DatePart(dd,DateTimeStamp) as TheDay, DatePart(mm,DateTimeStamp) as
TheMonth, (yy, DateTimeStamp) as TheYear
FROM TheEntryTable
--WHERE UserID = 1
GROUP BY UserID, DatePart(hh,DateTimeStamp), DatePart(dd,DateTimeStamp),
DatePart(mm,DateTimeStamp), (yy, DateTimeStamp)
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||CREATE TABLE [ImagePointers] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TrackablesId] [int] NULL CONSTRAINT [DF__Temporary__Track__22751F6C]
DEFAULT (0),
[TrackablesRecordVersion] [smallint] NULL CONSTRAINT
[DF__Temporary__Track__236943A5] DEFAULT (0),
[ScanDirectoriesId] [int] NULL CONSTRAINT [DF__Temporary__ScanD__245D67DE]
DEFAULT (0),
[ScanBatchesId] [int] NULL CONSTRAINT [DF__Temporary__ScanB__25518C17]
DEFAULT (0),
[ScanSequence] [int] NULL CONSTRAINT [DF__Temporary__ScanS__2645B050]
DEFAULT (0),
[FileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDateTime] [datetime] NULL ,
[PageNumber] [int] NULL CONSTRAINT [DF__Temporary__PageN__2739D489] DEFAULT
(0),
[CRC] [int] NULL CONSTRAINT [DF__TemporaryUp__CRC__282DF8C2] DEFAULT (0),
[Orientation] [smallint] NULL CONSTRAINT [DF__Temporary__Orien__29221CFB]
DEFAULT (0),
[Skew] [float] NULL CONSTRAINT [DF__TemporaryU__Skew__2A164134] DEFAULT
(0),
[Front] [bit] NOT NULL CONSTRAINT [DF__Temporary__Front__2B0A656D] DEFAULT
(0),
[ImageHeight] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2BFE89A6]
DEFAULT (0),
[ImageWidth] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2CF2ADDF]
DEFAULT (0),
[ImageSize] [int] NULL CONSTRAINT [DF__Temporary__Image__2DE6D218] DEFAULT
(0),
[BarCodeCount] [smallint] NULL CONSTRAINT [DF__Temporary__BarCo__2EDAF651]
DEFAULT (0),
[BarCodes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgDirectoriesId] [int] NULL ,
[OrgFileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[PageCount] [int] NULL ,
[OrgFullPath] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddedToFTS] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__44EA3301]
DEFAULT (0),
[AddedToOCR] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__47C69FAC]
DEFAULT (0),
CONSTRAINT [ImagePointers_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ScanBatches] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDateTime] [datetime] NULL ,
[PageCount] [int] NULL CONSTRAINT [DF__Temporary__PageC__45544755] DEFAULT
(0),
[DocumentCount] [int] NULL CONSTRAINT [DF__Temporary__Docum__46486B8E]
DEFAULT (0),
[BelowDeleteSizeCount] [smallint] NULL CONSTRAINT
[DF__Temporary__Below__473C8FC7] DEFAULT (0),
[RescannedCount] [int] NULL CONSTRAINT [DF__Temporary__Resca__4830B400]
DEFAULT (0),
[AutoIndexedCount] [int] NULL CONSTRAINT [DF__Temporary__AutoI__4924D839]
DEFAULT (0),
[LastScanSequence] [int] NULL CONSTRAINT [DF__Temporary__LastS__4A18FC72]
DEFAULT (0),
[ScanRulesIdUsed] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ScanBatches_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Select ScanBatches.UserName,
ImagePointers.Scandatetime
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
Order by ImagePointers.Scandatetime
tjones 2005-01-24 08:48:19.000
tjones 2005-01-24 08:50:35.000
tjones 2005-01-24 08:50:47.000
tjones 2005-01-24 08:50:56.000
tjones 2005-01-24 08:51:02.000
tjones 2005-01-24 08:51:04.000
tjones 2005-01-24 08:51:28.000
tjones 2005-01-24 08:51:35.000
Of course, what I would like to produce is the number of records produced by
any user for any unit of time like records per hour by each user. There are
currently six users.
Thanks
Rich
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucX0yOpAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Please post DDL, sample data, and sample output...
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
a
>|||"Richard Lawson" <nospam@.nospam.com> wrote in message
news:eXWzSzpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Of course, what I would like to produce is the number of records produced
by
> any user for any unit of time like records per hour by each user. There
are
> currently six users.
For a per-hour report, you could do something similar to David Buchanan's
solution:
Select ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00',
COUNT(*) AS Total
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
GROUP BY ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00'
Order by ImagePointers.Scandatetime
You can change the CONVERT to get different granularities.
... That will show you only hours that actually have data. To see hours
that didn't have data, you should implement a calendar table of some sort.
Here's some basic reading on the topic:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Grouping based on input parameters

I'm not even sure if this is possible. Our requirement is that the user can input whether to view hourly, daily, or monthly data. Originally I had created three separate reports, but I've been asked to try again.

Is it possible to specify when grouping to group by the whole operation date, or group by days, or group by weeks?

Thanks.Its possible if the grouping is done in logical way say days->weeks->months->years.
Trying to say that if you want to group by Monday or tuesday then the answer is no.
Now what you do is in one report you start from inside grouping by days, then by weeks, then by months and then by years.
Make a parameter through which you can suppress the section accordingly.
Hope you can understand my theory.|||Thank you for your explanation. That was a lot easier than I thought!

:)|||I've been re-doing one of my other reports and noticed that my solution still has one problem with it. I have grouped by (from outer level) : Year, Month, Product Type.

When I view the product type by month, all are listed as one would expect. However, when viewing by year they are not grouped correctly, ie :

Jan 2005
prod x - 5
prod y - 3
prod z - 4

Feb 2005
prod x - 1
prod y - 7

So when viewing by year, I would like to see Prod x - 6. Instead, I get the two individual listings of prod x, etc.

Is there a way around this?

Thanks.|||You should expect only one entry per year if you have done gouping by year->month->type.
Right click on Group Producttype and click on change group and make sure that Order is in ascending and not Original order.

B.thakkar|||Thanks, though everything is grouped with order ascending. I think the problem is that they are essentially still grouped by month (we just choose not to acknowledge/display this by suppressing the header).

GROUPING at runtime

All...

I am using vb.net 2003. i am trying to set report groupings of a crystal report at runtime based on user defined options. MSDN says this:

Dim FieldDef As FieldDefinition
FieldDef =
Report.Database.Tables.Item(0).Fields.Item(comboBox1().Text)
Report.DataDefinition.Groups.Item(0).ConditionField = FieldDef

However, the .ConditionField is Read Only, you cannot assign anything to it. I have searched the object browser at each level of this object model, and cannot figure out how to assign a group to a report.

Heres the link to MSDN... the code for SORTING cr at runrime works perfectly, its just the GROUPING code im having trouble with...

http://msdn.microsoft.com/library/d...resentation.asp

Thanks.The following Example is specific to Crystal Reports 8.5 using RDC and VB6, but you may be able to tweak it a bit to work for you...

The following code can be used to Change the Group through VB Code:

1. In Crystal, create a Formula Field and add 1 column (a String column works best).
2. Insert a Group and choose your Formula for the GroupBy.
3. Now, you can comment out the Column you entered, or you can leave it there, your choice.
4. In VB, add this line of code, substituting {ttxFileName.ColumnName} for the column you want to sort by: Report.FormulaFields(1).Text = "{ttxFileName.ColumnName} ". The FormulaFields can only take a long for the index, so you need to know what number your Formula is so you don't replace the wrong one. The numbers start at 1 and are incremented based on the order they were created (they are listed in chronological order).

Group vs Individual Users

The Books Online indicates that if a 'database user' that is actually repres
ents a NT security group creates a new database etc,
then SQL will automatically create a new 'database user' that is specificall
y signed on user.
I know from experience that the individual user, not the group, becomes the
owner of the newly created database.
Questions are
1) - What privileges and roles etc are associated with the new user.
2) - What is the login that this new user is tied to. Is it still the group
or does a login for the individual get created also.
3) - If a group that contains an individual that also has a 'database user'
record and the individual have conflicting 'database
user' setups (roles, privileges etc) which one wins. (Least restrictive, mo
st restrictive, group, individual, union, intersection
or something else)
Thanks
---
Roy Chastain
KMSystems, Inc.1. In terms of the database, the user creating the database
will be the owner of the database so they would be a member
of db_owner and mapped to dbo for the database.
2. I'm not sure what you are asking on this - the user is
always tied to their login even when they are members of
groups/roles. If the user creates a database, they will be
mapped to dbo in that database.
3. Permissions are cumulative with deny taking precedence.
The only exception would be a login that is a member of the
sysadmin server role. Sysadmins can perform any activity on
the server.
-Sue
On Mon, 21 Jun 2004 14:07:34 -0400, Roy Chastain
<roy@.kmsys.com> wrote:

>The Books Online indicates that if a 'database user' that is actually repre
sents a NT security group creates a new database etc,
>then SQL will automatically create a new 'database user' that is specifical
ly signed on user.
>I know from experience that the individual user, not the group, becomes the
owner of the newly created database.
>Questions are
>1) - What privileges and roles etc are associated with the new user.
>2) - What is the login that this new user is tied to. Is it still the grou
p or does a login for the individual get created also.
>3) - If a group that contains an individual that also has a 'database user'
record and the individual have conflicting 'database
>user' setups (roles, privileges etc) which one wins. (Least restrictive, m
ost restrictive, group, individual, union, intersection
>or something else)
>Thanks
>---
>Roy Chastain
>KMSystems, Inc.sql

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 Top # entered in as Parameter

Background: I have a report that groups by Item number and gives adds
up total amount for that item number. What I want to do is have the
user enter in a numeric value as a parameter such as 10, 15, 20, etc
that will then only display the TOP 10, 15, 20, etc (what they entered
in the parameter) total amounts on the report. Can anyone help me out,
Im sure this can be done but it gets tricky with the parameters thrown
in the mix. Any suggestions is much appreciated. Thanks!hi brent
you can do this w/o issue by using a stored procedure as the source dataset
(and having your 'TOP' value included as one of the parameters).
next, you are going to need to supply a dataset for the dropdown:
select '10' as topval
union
select '20' as topval
union
select '3....
if you plan on 'rolling your own' ASP.NET interface, you can preload the
values for the dropdown in HTML.
Rob
"Brent" wrote:
> Background: I have a report that groups by Item number and gives adds
> up total amount for that item number. What I want to do is have the
> user enter in a numeric value as a parameter such as 10, 15, 20, etc
> that will then only display the TOP 10, 15, 20, etc (what they entered
> in the parameter) total amounts on the report. Can anyone help me out,
> Im sure this can be done but it gets tricky with the parameters thrown
> in the mix. Any suggestions is much appreciated. Thanks!
>

Group by time span

I have a database where user events are recorded quite frequently. I'd like to be able to get a count of the 'good' events that happen in each 5 second period. Unfortunately I don't know how to display and group by a time range.

Here is the query I would like to change:
SELECT count(*), clientTime
FROM dbo.V_COMBINED
WHERE (sessionId = '122b') AND (type = N'sys_goodaction') AND (paraName = 'value')
GROUP BY clientTime

It returns records like:
1 | 2006-02-16 23:21:05.250
1 | 2006-02-16 23:21:05.267
1 | 2006-02-16 23:21:06.470

I'd like it to return records like:
5 | 2006-02-16 23:21:06 - 23:21:10
3 | 2006-02-16 23:21:11 - 23:21:15
4 | 2006-02-16 23:21:16 - 23:21:20

Anyone know how I could do this? Is it even possible?

ThanksYes It is possible.
Post your table structure, sample data and expected result.|||There are many ways to do this, but I'd use:SELECT count(*), DateAdd(second, -DatePart(second, clientTime) % 5
, DateAdd(ms, -DatePart(ms, clientTime), clientTime))
FROM dbo.V_COMBINED
WHERE (sessionId = '122b')
AND (type = N'sys_goodaction')
AND (paraName = 'value')
GROUP BY DateAdd(second, -DatePart(second, clientTime) % 5
, DateAdd(ms, -DatePart(ms, clientTime), clientTime))-PatP

GROUP BY highest score per user

Hi there!

I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.

The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.

Here's my base query:

SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC

...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:

SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID

How can I write combine the two in a nested subquery to display only the top score per user?further, here are the table schema:

USERS
- UserID (INT)
- Name (VARCHAR)

BALLOTS
- UserID (INT)
- SubmissionGUID (VARCHAR)
- TotalTally (INT) DEFAULT '0' -- this is incremented by varying values as a user makes correct selections
- WinningTeam (VARCHAR)

also, you can just assume that there are multiple submission, with each submission consisting of 63 records in the BALLOTS table, each with the same SubmissionGUID.

so, a user's total point would be the SUM'med value of all of their records grouped by a SubmissionGUID. Thus, a user "JOHN", could have the following:

USER SUBMISSIONGUID NAME TOTALTALLY
-- -- -- ----
1 kugiuvbiu JOHN 45
2 olhilugiu STEVE 32
3 oih98y897 MARK 31
1 89769gibi JOHN 29
1 0980jpo90 JOHN 13

I'd like just to select each unique USER's highest TOTALTALLY and display that, and forget about the others.

Clear as mud? :)

group by help?

I am trying to get the last occurance of a display name in a login user log database. Basically I have a table that looks like this:

id user fname lname
-----------
1 jdoe Jane Doe
2 jdoe John Doe
3 jdoe Fred Flinstone

I run the MySQL query: SELECT name, max(id) as max_id, user FROM `logins` GROUP BY user

I get back:
id user fname lname
-----------
3 jdoe Jane Doe

I actually want:
id user fname lname
-----------
3 jdoe Fred Flinstone

Any that can help it would be greatly Appreciated!!by "last" occurrence you mean the one with the largest id?
select id
, user
, fname
, lname
from logins as ZZ
where id
= ( select max(id)
from logins
where user = ZZ.user )|||I tried this result and am still having difficulties? Do you know if this works with all versions of MySQL? I get the following error from phpmyadmin:

You have an error in your SQL syntax near 'select max(id) from logins where user=ZZ.user

Any other thoughts?|||good guess -- subqueries are not supported prior to version 4.1

how come it took you two and a half weeks to try my solution?|||If a correlated subquery is not supported, let's hope a join (and a group by) is?
Could you try this one: select a.id, a.user, a.fname, a.lname
from logins as a, logins as b
where a.user = b.user
and a.id <= b.id
group by a.id, a.user, a.fname, a.lname
having count(*) = 1

Group Access

Hi all
Is there a way to create a script of a database user to see all of the
objects that user has access to and what the access is for each object?
Thanks
Kevin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Hi
You may want to look at the sysprotects table or possibly the
INFORMATION_SCHEMA.TABLE_PRIVILEGES view
John
"Kevin Hayward" wrote:

> Hi all
> Is there a way to create a script of a database user to see all of the
> objects that user has access to and what the access is for each object?
> Thanks
> Kevin
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Group Access

Hi all
Is there a way to create a script of a database user to see all of the
objects that user has access to and what the access is for each object?
Thanks
Kevin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Hi
You may want to look at the sysprotects table or possibly the
INFORMATION_SCHEMA.TABLE_PRIVILEGES view
John
"Kevin Hayward" wrote:

> Hi all
> Is there a way to create a script of a database user to see all of the
> objects that user has access to and what the access is for each object?
> Thanks
> Kevin
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Group Access

Hi all
Is there a way to create a script of a database user to see all of the
objects that user has access to and what the access is for each object?
Thanks
Kevin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi
You may want to look at the sysprotects table or possibly the
INFORMATION_SCHEMA.TABLE_PRIVILEGES view
John
"Kevin Hayward" wrote:
> Hi all
> Is there a way to create a script of a database user to see all of the
> objects that user has access to and what the access is for each object?
> Thanks
> Kevin
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>