Grouping Data with Weekly break out?

Hi All,
I'm trying to figure out and learn how to do the following:
/*I have the following query which works:*/
SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber]
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
My question here is how can I get a wly breakout of the COUNT(PoNumber)?
I want a column for each w and the number of PONumbers Per Datasource and
NamePrep for that w.
Example Output:
Service Line | PO Created By | Total Of PONumber | 3/5/2005 |
3/12/2005 | ETC...
MNS JOHN 10
MNS ERIC 25
FMS CARL 8
Hope my question makes sense :)
John.If you had a calendar table, this would be easier, but if not, you need to
know the columns you want, or use dynamic SQL...
Select DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber],
Sum(Case When ReqSubmitDate
Between '03/ 01/2005' AND '03/8/2005' Then 1 End) Wk1Count,
Sum(Case When ReqSubmitDate
Between '03/ 09/2005' AND '03/16/2005' Then 1 End) Wk2Count,
Sum(Case When ReqSubmitDate
Between '03/ 17/2005' AND '03/24/2005' Then 1 End) Wk3Count,
Sum(Case When ReqSubmitDate
Between '03/ 25/2005' AND '03/31/2005' Then 1 End) Wk4Count
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
If you want it dynamic, you have to write code to dynamic construct an SQL
statement like the one above, based on the date ranges you pass it, and then
execute that SQL Statement using EXECUTE, or sp_ExecuteSQL() functions
"John Rugo" wrote:

> Hi All,
> I'm trying to figure out and learn how to do the following:
> /*I have the following query which works:*/
> SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
> COUNT(PoNumber) AS [Total Of PONumber]
> FROM OPW
> WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
> GROUP BY DataSource, NamePrep
> My question here is how can I get a wly breakout of the COUNT(PoNumber)
?
> I want a column for each w and the number of PONumbers Per Datasource a
nd
> NamePrep for that w.
> Example Output:
> Service Line | PO Created By | Total Of PONumber | 3/5/2005
|
> 3/12/2005 | ETC...
> MNS JOHN 10
> MNS ERIC 25
> FMS CARL 8
> Hope my question makes sense :)
> John.
>
>sql

Grouping attribute member in Dimension

Hi,

I have a Dimension with this structure:

ServiceID

ServiceType

ServiceTypeDesc

These are the sample of their members:

Service ID Service Type ServiceType Desc

1 Walk-In Walk-In

2 Contract Contract

3 HomeService Home Service

4 HomeService2 Home Service

5 Contract2 Contract

6 WalkIn2 Walk-In

7 Contract3 Contract

I assigned the ServiceTypeDesc in Service ID's (this serves as the key column) name column. When I drag it to filter, I could see 7 items, instead of grouping them into 3 (Walk-In, Contract, Home Service).

Is it possible to group them into 3 instead of giving me all the members?

cherriesh

You would want to create at least 2 attributes in your dimension. One based on Service ID which would be the key attribute, which you would use to join to the fact table(s) and another based on ServiceTypeDesc. In your example the ServiceTypeDesc attribute would want to have ServiceTypeDesc used as the key and name.

You get a distinct member in your attribute for each distinct value in the key column.

Group Access Problem

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