Calculated Average w/o a dimension specified

OK, Here's another one...

I want to create a calculated member that compares the current member to the average of the other members at the same level the member is in.

The catch is that I want this to be a calculated member in the cube that works regardless of what dimensions/hierarchies the data is broken down with.

The MDX below shows what I want to do except that it requires the SERIAL_NUM hierarchy to be hardcoded:

Code Snippet

WITH MEMBER [Avg] AS AVG([SERIAL_NUM].Siblings, Measures.[Count Of Failures])

SELECT NON EMPTY { [Measures].[Count of Failures] , [Avg]} ON COLUMNS, NON EMPTY { ([Vehicle Info].[SERIAL_NUM].[SERIAL_NUM].ALLMEMBERS ) } ...

The end result should be something like this:

SERIAL_NUM Count Of Failures Avg 423343 10 30 432434 20 30 123123 60 30

But I again, I don't know what dimensions hierarchies will be used later on, I just want all the values the user is currently looking at be included in the calculated measure. If they replaced SERIAL_NUM with MODEL as the dimension, I want it to perform the same calculated on the data provided from that query.

I tried something like MEASURES.SIBLINGS, MEASURES.MEMBERS, etc, but they returned only the current value, as it appears to be the lowest hierarchy so it has no siblings.

(The end result, BTW, is not the average, but the number of std dev's a certain value is from the average of the other values, essentially a dynamic outlyer flag. So it'll be (Count of Failures - Avg (siblings)) / StdDev (siblings), I'm just trying to keep things simple)

Geof

Well, I thought I had it for a moment...

The function I tried was Axis(0)...

Code Snippet

IIF(MEASURES.[Count of Failures] = Null, Null,

(MEASURES.[Count of Failures] - AVG(AXIS(0), MEASURES.[Count of Failures])) / StDev(Axis(0), MEASURES.[Count of Failures])

)

Seems like it should work, but it's not giving the correct data. The data varies according to where I've drilled down to, and isn't consistent. Perhaps I could filter the Axis(0) set somehow? It looks like it's including the subtotal members from each dimension (the more dimensions I add, the more rows are reported in the Axis(0) set)

|||

You might be able to use a combination of generate and/or filter to filter out the dimension in MDX, but given that you don't really know how many dimensions could be stacked on the axis, I can't envisage how it would really work.

The other problem with Axis(0) is that it refers to the column axis, if a user flips the query around to put the dimension on the rows you would need to use Axis(1), but there is not easy way to tell what the user is doing.

How many dimensions would you want to use with this calculation? Would it be feasable to create a calculated measure for each applicable dimension? This might also lead to some interesting analysis opportunities.

|||

The biggest problem I've got with Axis(0) is that it include the subtotals and grand totals as simply another row, and since I don't have a fixed dimension to work with, I've been unable to filter them out of the set.

The Rows/Columns issue you mention is a problem, but not the end of the world. The users doing the queries will be reasonably experienced, just not enough to craft their own MDX. I can warn them about that scenario (if the other problem can be fixed)

How many dimensions? So far 4 is the largest number for the basic queries currently planned, though when people start wanting more estoteric comparisons, it will increase. So that could be feasible.

|||This sort of filtering might be something that is best suited to a .Net stored proc. I think you might need to iterate over the set a couple of times to figure out what you are looking at and which members are at the lowest level on the axis. The best set of stored proc samples I know if is at a project which I contribute to at www.codeplex.com/asstoredprocedures

Calcaluting total nu of people based on their name field

I want to create a report which should be looked like this.

Members Joined Members Borrower Depositor Members Joined Yesterday 8 4 4 Members Joined Last 7 days 39 20 19 Members Joined Last 30 Days 243 120 122 Members Borrower Depositor 2007 Members Joined to Date 1,052 510 542 Q1-07 415 200 215 Q2-07 445 210 235 Q3-07 (To date) 192 100 92 All Members Joined 1,052 510 542

In the data base, there are some tables called members, loans and deposits containing members names, joined date , etc.

Example:

MembersTable

MemberName MemberID etc

LoansTable

MemberName CommencedDate

DepositorTable

MemberName CommencedDate

I assume that I should be able to create a report using matix format. But I want to clarify few things before I start. How do I calculate nu members? Data base tables contains only names of the members but not numbers. Other thing is how can I group members by perticular time periods as shown above? Do I need to use parameters? Also In the table, members can be borrowers or a depositors? How can clarify this one?

Please Can anyone give an idea? How to start and which direction I should go etc?

Thanks

I personally prefer to do all this work at the data layer within the SQL query rather than trying to get rs to SUM things conditionally.

Looking at your definition, I would creaste multiple datasets and probably stick to returning 3 columns in my dataset and using a table for the layout rather than a matrix as they are more flexible. This makes sense as your number of columns does not need to expand dynamically which is what a matrix is really meant for.

You should also consider whether you will add a date parameter to do "as at" queries or whether you will just use GETDATE() to get the current date and time.

|||

Thanks Adam I will try with a table. I think he wants to see the report once a month without querying. Run the report and get the results end of each month.

I wrote a query something like this.

SELECT COUNT(DISTINCT(Members.Name), COUNT(DISTINCT(Loan.Name) AS Borrowers. , COUNT(DISTINCT(Depositor.Name) AS Depositors

FROM Members M

INNER JOIN Loan L ON M.Name = L.Name

INNER JOIN Depositor D ON M.Name = D.Name

WHERE ( L.CommencedDate BETWEEN (@.Startdate) AND (@.GETDATE( ) )

AND

D.CommenceDate BETWEEN (@.Startdate) AND (@.GETDATE( ) ) )

Am I going right direction?

Thanks

|||

I'd break it up a bit and firstly try to write the queries for the individual bits of infrmation and look to see if you can bring them together.

by loking at your data, borrower + depositor = member, is it safe to assume that? If so then there's no need to join to member is there? or can member potentially be both?

Assuming the former,

Dataset1:

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'Members Joined Yesterday'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-1)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-1)

),

last7daysCTE AS

(

SELECT type = 'Members Joined Last 7 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-7)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-7)

),

last30daysCTE AS

(

SELECT type = 'Members Joined Last 30 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-30)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-30)

),

combinedCTE AS

(

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7daysCTE

UNION ALL

SELECT * FROM last30daysCTE

)

SELECT *

, members = borrowers + depositors

FROM combinedCTE

You can employ a similar startegy to construct the querters except you probably won't to have a separate CTE per quarter.

|||

Thanks lot for the codes Adam..Yes Total Number of members are sum of borrowers and depositors. But member can not be both. But from membertable we can not identify whether they are borrowers or depositors. I need to get that info from borrowers and depositors tables.

I tried with my codes. I had to change @.GETDATE as @.Enddate. Except that My code worked. But drawback is user has to input dates manually inorder to see that. I think the one you wrote does not requre user to enter dates. I will try with your code.

One more thing..what do u mean CTE?

Probably for 2007 year members statistics, I may need to make a seperate data set.

Thanks again

|||

Your code worked perfectly. Thats what exactly I want. I will write a similar code for 2007 qtrs too.

Thanks lot

|||

I wrote the code for quarters too.

I clicked new data set and then in the querry string window I wrote the query.

But when I click ok,it gives the following error message.

"Specify a CLS compliant name for the data set."

What does that mean?

Instead of GETDATE() , can I specify dates?

As a example;

commencementDate BETWEEN '01/01/2007' AND '03/31/2007'

When I do this , I get the error message.

|||well everything works well...Thanks for your help Adam|||

Glad I could help.

CTE stands for Common Table Expression and is a new addition to T-SQL 2005. I think they make code a lot more readable.

|||

ok..I have another problem which is almost similar.

CD - Table

Amount(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Can I use the same query..Here I have only one table. I assume I should be able to use it. I already wrote it. But there is a syntax error.

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, Values = (select sum( Amount )

from CD Table

where StartDate >= GETDATE()-1)

, No Of CDs Sold = (select count(AccountID)

from CD Table

where StartDate >= GETDATE()-1)

, AverageCD Size = (select avg(Amount)

from CD Table
where StartDate >=GETDATE()-1)

),

I get the following error message.

There is an error in the query. Incorrect syntax near the keyword 'Values'.
Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ')'.

I dont know why? Can you please show me where I went wrong?|||

I'd like to help but your query does not resemble the above example, if you want column or table names to have spaces then you have enclose them in [] e.g. [AverageCD Size] instead of just AverageCD Size. Secondly, you should look up the syntax for a CTE and in this case you probably don't need one.

|||Yes I changed the startdate now. I tried with [ ] .But did not work. I looked at the syntax for CTE. As you said I dont need one. But still get the same error message.|||

Like I said, you're not giving me enough information to work with here.

shamen wrote:

Value(int) StartDate(Date) AccountID(int)

If these are the columns in your table then what is CDOrderFK doing in your query. Please be more explicit.|||I'm sorry CDOrderFK should be replaced with AccountID..Now it should be clear.|||

OK, I would still use the CTE, except you need to use 3 of them and the SQL can a be little simpler.

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-1

)

, last7days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-7

)

, last30days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-30

)

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7days

UNION ALL

SELECT * FROM last30days

Calcaluting total nu of people based on their name field

I want to create a report which should be looked like this.

Members Joined Members Borrower Depositor Members Joined Yesterday 8 4 4 Members Joined Last 7 days 39 20 19 Members Joined Last 30 Days 243 120 122 Members Borrower Depositor 2007 Members Joined to Date 1,052 510 542 Q1-07 415 200 215 Q2-07 445 210 235 Q3-07 (To date) 192 100 92 All Members Joined 1,052 510 542

In the data base, there are some tables called members, loans and deposits containing members names, joined date , etc.

Example:

MembersTable

MemberName MemberID etc

LoansTable

MemberName CommencedDate

DepositorTable

MemberName CommencedDate

I assume that I should be able to create a report using matix format. But I want to clarify few things before I start. How do I calculate nu members? Data base tables contains only names of the members but not numbers. Other thing is how can I group members by perticular time periods as shown above? Do I need to use parameters? Also In the table, members can be borrowers or a depositors? How can clarify this one?

Please Can anyone give an idea? How to start and which direction I should go etc?

Thanks

I personally prefer to do all this work at the data layer within the SQL query rather than trying to get rs to SUM things conditionally.

Looking at your definition, I would creaste multiple datasets and probably stick to returning 3 columns in my dataset and using a table for the layout rather than a matrix as they are more flexible. This makes sense as your number of columns does not need to expand dynamically which is what a matrix is really meant for.

You should also consider whether you will add a date parameter to do "as at" queries or whether you will just use GETDATE() to get the current date and time.

|||

Thanks Adam I will try with a table. I think he wants to see the report once a month without querying. Run the report and get the results end of each month.

I wrote a query something like this.

SELECT COUNT(DISTINCT(Members.Name), COUNT(DISTINCT(Loan.Name) AS Borrowers. , COUNT(DISTINCT(Depositor.Name) AS Depositors

FROM Members M

INNER JOIN Loan L ON M.Name = L.Name

INNER JOIN Depositor D ON M.Name = D.Name

WHERE ( L.CommencedDate BETWEEN (@.Startdate) AND (@.GETDATE( ) )

AND

D.CommenceDate BETWEEN (@.Startdate) AND (@.GETDATE( ) ) )

Am I going right direction?

Thanks

|||

I'd break it up a bit and firstly try to write the queries for the individual bits of infrmation and look to see if you can bring them together.

by loking at your data, borrower + depositor = member, is it safe to assume that? If so then there's no need to join to member is there? or can member potentially be both?

Assuming the former,

Dataset1:

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'Members Joined Yesterday'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-1)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-1)

),

last7daysCTE AS

(

SELECT type = 'Members Joined Last 7 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-7)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-7)

),

last30daysCTE AS

(

SELECT type = 'Members Joined Last 30 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-30)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-30)

),

combinedCTE AS

(

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7daysCTE

UNION ALL

SELECT * FROM last30daysCTE

)

SELECT *

, members = borrowers + depositors

FROM combinedCTE

You can employ a similar startegy to construct the querters except you probably won't to have a separate CTE per quarter.

|||

Thanks lot for the codes Adam..Yes Total Number of members are sum of borrowers and depositors. But member can not be both. But from membertable we can not identify whether they are borrowers or depositors. I need to get that info from borrowers and depositors tables.

I tried with my codes. I had to change @.GETDATE as @.Enddate. Except that My code worked. But drawback is user has to input dates manually inorder to see that. I think the one you wrote does not requre user to enter dates. I will try with your code.

One more thing..what do u mean CTE?

Probably for 2007 year members statistics, I may need to make a seperate data set.

Thanks again

|||

Your code worked perfectly. Thats what exactly I want. I will write a similar code for 2007 qtrs too.

Thanks lot

|||

I wrote the code for quarters too.

I clicked new data set and then in the querry string window I wrote the query.

But when I click ok,it gives the following error message.

"Specify a CLS compliant name for the data set."

What does that mean?

Instead of GETDATE() , can I specify dates?

As a example;

commencementDate BETWEEN '01/01/2007' AND '03/31/2007'

When I do this , I get the error message.

|||well everything works well...Thanks for your help Adam|||

Glad I could help.

CTE stands for Common Table Expression and is a new addition to T-SQL 2005. I think they make code a lot more readable.

|||

ok..I have another problem which is almost similar.

CD - Table

Amount(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Can I use the same query..Here I have only one table. I assume I should be able to use it. I already wrote it. But there is a syntax error.

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, Values = (select sum( Amount )

from CD Table

where StartDate >= GETDATE()-1)

, No Of CDs Sold = (select count(AccountID)

from CD Table

where StartDate >= GETDATE()-1)

, AverageCD Size = (select avg(Amount)

from CD Table
where StartDate >=GETDATE()-1)

),

I get the following error message.

There is an error in the query. Incorrect syntax near the keyword 'Values'.
Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ')'.

I dont know why? Can you please show me where I went wrong?|||

I'd like to help but your query does not resemble the above example, if you want column or table names to have spaces then you have enclose them in [] e.g. [AverageCD Size] instead of just AverageCD Size. Secondly, you should look up the syntax for a CTE and in this case you probably don't need one.

|||Yes I changed the startdate now. I tried with [ ] .But did not work. I looked at the syntax for CTE. As you said I dont need one. But still get the same error message.|||

Like I said, you're not giving me enough information to work with here.

shamen wrote:

Value(int) StartDate(Date) AccountID(int)

If these are the columns in your table then what is CDOrderFK doing in your query. Please be more explicit.|||I'm sorry CDOrderFK should be replaced with AccountID..Now it should be clear.|||

OK, I would still use the CTE, except you need to use 3 of them and the SQL can a be little simpler.

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-1

)

, last7days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-7

)

, last30days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-30

)

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7days

UNION ALL

SELECT * FROM last30days

Calcaluting total nu of people based on their name field

I want to create a report which should be looked like this.

Members Joined Members Borrower Depositor Members Joined Yesterday 8 4 4 Members Joined Last 7 days 39 20 19 Members Joined Last 30 Days 243 120 122 Members Borrower Depositor 2007 Members Joined to Date 1,052 510 542 Q1-07 415 200 215 Q2-07 445 210 235 Q3-07 (To date) 192 100 92 All Members Joined 1,052 510 542

In the data base, there are some tables called members, loans and deposits containing members names, joined date , etc.

Example:

MembersTable

MemberName MemberID etc

LoansTable

MemberName CommencedDate

DepositorTable

MemberName CommencedDate

I assume that I should be able to create a report using matix format. But I want to clarify few things before I start. How do I calculate nu members? Data base tables contains only names of the members but not numbers. Other thing is how can I group members by perticular time periods as shown above? Do I need to use parameters? Also In the table, members can be borrowers or a depositors? How can clarify this one?

Please Can anyone give an idea? How to start and which direction I should go etc?

Thanks

I personally prefer to do all this work at the data layer within the SQL query rather than trying to get rs to SUM things conditionally.

Looking at your definition, I would creaste multiple datasets and probably stick to returning 3 columns in my dataset and using a table for the layout rather than a matrix as they are more flexible. This makes sense as your number of columns does not need to expand dynamically which is what a matrix is really meant for.

You should also consider whether you will add a date parameter to do "as at" queries or whether you will just use GETDATE() to get the current date and time.

|||

Thanks Adam I will try with a table. I think he wants to see the report once a month without querying. Run the report and get the results end of each month.

I wrote a query something like this.

SELECT COUNT(DISTINCT(Members.Name), COUNT(DISTINCT(Loan.Name) AS Borrowers. , COUNT(DISTINCT(Depositor.Name) AS Depositors

FROM Members M

INNER JOIN Loan L ON M.Name = L.Name

INNER JOIN Depositor D ON M.Name = D.Name

WHERE ( L.CommencedDate BETWEEN (@.Startdate) AND (@.GETDATE( ) )

AND

D.CommenceDate BETWEEN (@.Startdate) AND (@.GETDATE( ) ) )

Am I going right direction?

Thanks

|||

I'd break it up a bit and firstly try to write the queries for the individual bits of infrmation and look to see if you can bring them together.

by loking at your data, borrower + depositor = member, is it safe to assume that? If so then there's no need to join to member is there? or can member potentially be both?

Assuming the former,

Dataset1:

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'Members Joined Yesterday'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-1)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-1)

),

last7daysCTE AS

(

SELECT type = 'Members Joined Last 7 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-7)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-7)

),

last30daysCTE AS

(

SELECT type = 'Members Joined Last 30 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-30)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-30)

),

combinedCTE AS

(

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7daysCTE

UNION ALL

SELECT * FROM last30daysCTE

)

SELECT *

, members = borrowers + depositors

FROM combinedCTE

You can employ a similar startegy to construct the querters except you probably won't to have a separate CTE per quarter.

|||

Thanks lot for the codes Adam..Yes Total Number of members are sum of borrowers and depositors. But member can not be both. But from membertable we can not identify whether they are borrowers or depositors. I need to get that info from borrowers and depositors tables.

I tried with my codes. I had to change @.GETDATE as @.Enddate. Except that My code worked. But drawback is user has to input dates manually inorder to see that. I think the one you wrote does not requre user to enter dates. I will try with your code.

One more thing..what do u mean CTE?

Probably for 2007 year members statistics, I may need to make a seperate data set.

Thanks again

|||

Your code worked perfectly. Thats what exactly I want. I will write a similar code for 2007 qtrs too.

Thanks lot

|||

I wrote the code for quarters too.

I clicked new data set and then in the querry string window I wrote the query.

But when I click ok,it gives the following error message.

"Specify a CLS compliant name for the data set."

What does that mean?

Instead of GETDATE() , can I specify dates?

As a example;

commencementDate BETWEEN '01/01/2007' AND '03/31/2007'

When I do this , I get the error message.

|||well everything works well...Thanks for your help Adam|||

Glad I could help.

CTE stands for Common Table Expression and is a new addition to T-SQL 2005. I think they make code a lot more readable.

|||

ok..I have another problem which is almost similar.

CD - Table

Amount(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Can I use the same query..Here I have only one table. I assume I should be able to use it. I already wrote it. But there is a syntax error.

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, Values = (select sum( Amount )

from CD Table

where StartDate >= GETDATE()-1)

, No Of CDs Sold = (select count(AccountID)

from CD Table

where StartDate >= GETDATE()-1)

, AverageCD Size = (select avg(Amount)

from CD Table
where StartDate >=GETDATE()-1)

),

I get the following error message.

There is an error in the query. Incorrect syntax near the keyword 'Values'.
Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ')'.

I dont know why? Can you please show me where I went wrong?|||

I'd like to help but your query does not resemble the above example, if you want column or table names to have spaces then you have enclose them in [] e.g. [AverageCD Size] instead of just AverageCD Size. Secondly, you should look up the syntax for a CTE and in this case you probably don't need one.

|||Yes I changed the startdate now. I tried with [ ] .But did not work. I looked at the syntax for CTE. As you said I dont need one. But still get the same error message.|||

Like I said, you're not giving me enough information to work with here.

shamen wrote:

Value(int) StartDate(Date) AccountID(int)

If these are the columns in your table then what is CDOrderFK doing in your query. Please be more explicit.|||I'm sorry CDOrderFK should be replaced with AccountID..Now it should be clear.|||

OK, I would still use the CTE, except you need to use 3 of them and the SQL can a be little simpler.

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-1

)

, last7days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-7

)

, last30days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-30

)

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7days

UNION ALL

SELECT * FROM last30days

Calcaluting total nu of people based on their name field

I want to create a report which should be looked like this.

Members Joined Members Borrower Depositor Members Joined Yesterday 8 4 4 Members Joined Last 7 days 39 20 19 Members Joined Last 30 Days 243 120 122 Members Borrower Depositor 2007 Members Joined to Date 1,052 510 542 Q1-07 415 200 215 Q2-07 445 210 235 Q3-07 (To date) 192 100 92 All Members Joined 1,052 510 542

In the data base, there are some tables called members, loans and deposits containing members names, joined date , etc.

Example:

MembersTable

MemberName MemberID etc

LoansTable

MemberName CommencedDate

DepositorTable

MemberName CommencedDate

I assume that I should be able to create a report using matix format. But I want to clarify few things before I start. How do I calculate nu members? Data base tables contains only names of the members but not numbers. Other thing is how can I group members by perticular time periods as shown above? Do I need to use parameters? Also In the table, members can be borrowers or a depositors? How can clarify this one?

Please Can anyone give an idea? How to start and which direction I should go etc?

Thanks

I personally prefer to do all this work at the data layer within the SQL query rather than trying to get rs to SUM things conditionally.

Looking at your definition, I would creaste multiple datasets and probably stick to returning 3 columns in my dataset and using a table for the layout rather than a matrix as they are more flexible. This makes sense as your number of columns does not need to expand dynamically which is what a matrix is really meant for.

You should also consider whether you will add a date parameter to do "as at" queries or whether you will just use GETDATE() to get the current date and time.

|||

Thanks Adam I will try with a table. I think he wants to see the report once a month without querying. Run the report and get the results end of each month.

I wrote a query something like this.

SELECT COUNT(DISTINCT(Members.Name), COUNT(DISTINCT(Loan.Name) AS Borrowers. , COUNT(DISTINCT(Depositor.Name) AS Depositors

FROM Members M

INNER JOIN Loan L ON M.Name = L.Name

INNER JOIN Depositor D ON M.Name = D.Name

WHERE ( L.CommencedDate BETWEEN (@.Startdate) AND (@.GETDATE( ) )

AND

D.CommenceDate BETWEEN (@.Startdate) AND (@.GETDATE( ) ) )

Am I going right direction?

Thanks

|||

I'd break it up a bit and firstly try to write the queries for the individual bits of infrmation and look to see if you can bring them together.

by loking at your data, borrower + depositor = member, is it safe to assume that? If so then there's no need to join to member is there? or can member potentially be both?

Assuming the former,

Dataset1:

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'Members Joined Yesterday'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-1)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-1)

),

last7daysCTE AS

(

SELECT type = 'Members Joined Last 7 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-7)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-7)

),

last30daysCTE AS

(

SELECT type = 'Members Joined Last 30 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-30)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-30)

),

combinedCTE AS

(

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7daysCTE

UNION ALL

SELECT * FROM last30daysCTE

)

SELECT *

, members = borrowers + depositors

FROM combinedCTE

You can employ a similar startegy to construct the querters except you probably won't to have a separate CTE per quarter.

|||

Thanks lot for the codes Adam..Yes Total Number of members are sum of borrowers and depositors. But member can not be both. But from membertable we can not identify whether they are borrowers or depositors. I need to get that info from borrowers and depositors tables.

I tried with my codes. I had to change @.GETDATE as @.Enddate. Except that My code worked. But drawback is user has to input dates manually inorder to see that. I think the one you wrote does not requre user to enter dates. I will try with your code.

One more thing..what do u mean CTE?

Probably for 2007 year members statistics, I may need to make a seperate data set.

Thanks again

|||

Your code worked perfectly. Thats what exactly I want. I will write a similar code for 2007 qtrs too.

Thanks lot

|||

I wrote the code for quarters too.

I clicked new data set and then in the querry string window I wrote the query.

But when I click ok,it gives the following error message.

"Specify a CLS compliant name for the data set."

What does that mean?

Instead of GETDATE() , can I specify dates?

As a example;

commencementDate BETWEEN '01/01/2007' AND '03/31/2007'

When I do this , I get the error message.

|||well everything works well...Thanks for your help Adam|||

Glad I could help.

CTE stands for Common Table Expression and is a new addition to T-SQL 2005. I think they make code a lot more readable.

|||

ok..I have another problem which is almost similar.

CD - Table

Amount(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Can I use the same query..Here I have only one table. I assume I should be able to use it. I already wrote it. But there is a syntax error.

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, Values = (select sum( Amount )

from CD Table

where StartDate >= GETDATE()-1)

, No Of CDs Sold = (select count(AccountID)

from CD Table

where StartDate >= GETDATE()-1)

, AverageCD Size = (select avg(Amount)

from CD Table
where StartDate >=GETDATE()-1)

),

I get the following error message.

There is an error in the query. Incorrect syntax near the keyword 'Values'.
Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ')'.

I dont know why? Can you please show me where I went wrong?|||

I'd like to help but your query does not resemble the above example, if you want column or table names to have spaces then you have enclose them in [] e.g. [AverageCD Size] instead of just AverageCD Size. Secondly, you should look up the syntax for a CTE and in this case you probably don't need one.

|||Yes I changed the startdate now. I tried with [ ] .But did not work. I looked at the syntax for CTE. As you said I dont need one. But still get the same error message.|||

Like I said, you're not giving me enough information to work with here.

shamen wrote:

Value(int) StartDate(Date) AccountID(int)

If these are the columns in your table then what is CDOrderFK doing in your query. Please be more explicit.|||I'm sorry CDOrderFK should be replaced with AccountID..Now it should be clear.|||

OK, I would still use the CTE, except you need to use 3 of them and the SQL can a be little simpler.

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-1

)

, last7days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-7

)

, last30days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-30

)

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7days

UNION ALL

SELECT * FROM last30days

Calcaluting total nu of people based on their name field

I want to create a report which should be looked like this.

Members Joined Members Borrower Depositor Members Joined Yesterday 8 4 4 Members Joined Last 7 days 39 20 19 Members Joined Last 30 Days 243 120 122 Members Borrower Depositor 2007 Members Joined to Date 1,052 510 542 Q1-07 415 200 215 Q2-07 445 210 235 Q3-07 (To date) 192 100 92 All Members Joined 1,052 510 542

In the data base, there are some tables called members, loans and deposits containing members names, joined date , etc.

Example:

MembersTable

MemberName MemberID etc

LoansTable

MemberName CommencedDate

DepositorTable

MemberName CommencedDate

I assume that I should be able to create a report using matix format. But I want to clarify few things before I start. How do I calculate nu members? Data base tables contains only names of the members but not numbers. Other thing is how can I group members by perticular time periods as shown above? Do I need to use parameters? Also In the table, members can be borrowers or a depositors? How can clarify this one?

Please Can anyone give an idea? How to start and which direction I should go etc?

Thanks

I personally prefer to do all this work at the data layer within the SQL query rather than trying to get rs to SUM things conditionally.

Looking at your definition, I would creaste multiple datasets and probably stick to returning 3 columns in my dataset and using a table for the layout rather than a matrix as they are more flexible. This makes sense as your number of columns does not need to expand dynamically which is what a matrix is really meant for.

You should also consider whether you will add a date parameter to do "as at" queries or whether you will just use GETDATE() to get the current date and time.

|||

Thanks Adam I will try with a table. I think he wants to see the report once a month without querying. Run the report and get the results end of each month.

I wrote a query something like this.

SELECT COUNT(DISTINCT(Members.Name), COUNT(DISTINCT(Loan.Name) AS Borrowers. , COUNT(DISTINCT(Depositor.Name) AS Depositors

FROM Members M

INNER JOIN Loan L ON M.Name = L.Name

INNER JOIN Depositor D ON M.Name = D.Name

WHERE ( L.CommencedDate BETWEEN (@.Startdate) AND (@.GETDATE( ) )

AND

D.CommenceDate BETWEEN (@.Startdate) AND (@.GETDATE( ) ) )

Am I going right direction?

Thanks

|||

I'd break it up a bit and firstly try to write the queries for the individual bits of infrmation and look to see if you can bring them together.

by loking at your data, borrower + depositor = member, is it safe to assume that? If so then there's no need to join to member is there? or can member potentially be both?

Assuming the former,

Dataset1:

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'Members Joined Yesterday'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-1)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-1)

),

last7daysCTE AS

(

SELECT type = 'Members Joined Last 7 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-7)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-7)

),

last30daysCTE AS

(

SELECT type = 'Members Joined Last 30 Days'

, borrowers = (select count(*)

from LoansTable

where CommencementDate >= GETDATE()-30)

, depositors = (select count(*)

from DepositorTable

where CommencementDate >= GETDATE()-30)

),

combinedCTE AS

(

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7daysCTE

UNION ALL

SELECT * FROM last30daysCTE

)

SELECT *

, members = borrowers + depositors

FROM combinedCTE

You can employ a similar startegy to construct the querters except you probably won't to have a separate CTE per quarter.

|||

Thanks lot for the codes Adam..Yes Total Number of members are sum of borrowers and depositors. But member can not be both. But from membertable we can not identify whether they are borrowers or depositors. I need to get that info from borrowers and depositors tables.

I tried with my codes. I had to change @.GETDATE as @.Enddate. Except that My code worked. But drawback is user has to input dates manually inorder to see that. I think the one you wrote does not requre user to enter dates. I will try with your code.

One more thing..what do u mean CTE?

Probably for 2007 year members statistics, I may need to make a seperate data set.

Thanks again

|||

Your code worked perfectly. Thats what exactly I want. I will write a similar code for 2007 qtrs too.

Thanks lot

|||

I wrote the code for quarters too.

I clicked new data set and then in the querry string window I wrote the query.

But when I click ok,it gives the following error message.

"Specify a CLS compliant name for the data set."

What does that mean?

Instead of GETDATE() , can I specify dates?

As a example;

commencementDate BETWEEN '01/01/2007' AND '03/31/2007'

When I do this , I get the error message.

|||well everything works well...Thanks for your help Adam|||

Glad I could help.

CTE stands for Common Table Expression and is a new addition to T-SQL 2005. I think they make code a lot more readable.

|||

ok..I have another problem which is almost similar.

CD - Table

Amount(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Can I use the same query..Here I have only one table. I assume I should be able to use it. I already wrote it. But there is a syntax error.

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, Values = (select sum( Amount )

from CD Table

where StartDate >= GETDATE()-1)

, No Of CDs Sold = (select count(AccountID)

from CD Table

where StartDate >= GETDATE()-1)

, AverageCD Size = (select avg(Amount)

from CD Table
where StartDate >=GETDATE()-1)

),

I get the following error message.

There is an error in the query. Incorrect syntax near the keyword 'Values'.
Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ')'.

I dont know why? Can you please show me where I went wrong?|||

I'd like to help but your query does not resemble the above example, if you want column or table names to have spaces then you have enclose them in [] e.g. [AverageCD Size] instead of just AverageCD Size. Secondly, you should look up the syntax for a CTE and in this case you probably don't need one.

|||Yes I changed the startdate now. I tried with [ ] .But did not work. I looked at the syntax for CTE. As you said I dont need one. But still get the same error message.|||

Like I said, you're not giving me enough information to work with here.

shamen wrote:

Value(int) StartDate(Date) AccountID(int)

If these are the columns in your table then what is CDOrderFK doing in your query. Please be more explicit.|||I'm sorry CDOrderFK should be replaced with AccountID..Now it should be clear.|||

OK, I would still use the CTE, except you need to use 3 of them and the SQL can a be little simpler.

Code Snippet

WITH yesterdayCTE AS

(

SELECT type = 'CDs Sold Yesterday'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-1

)

, last7days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-7

)

, last30days AS

(

SELECT type = 'CDs Sold Last 7 Days'

, total_sales = sum(Amount)

, num_accounts = count(DISTINCT AccountID)
, average_sales = avg(Amount)

FROM [CD Table]

WHERE StartDate >= GETDATE()-30

)

SELECT * FROM yesterdayCTE

UNION ALL

SELECT * FROM last7days

UNION ALL

SELECT * FROM last30days