No grand total if Time Intelligence Calculation is drag on row

Hi,

I've deployed a simple cube with a time dimension on SQL 2005. I added the time intelligence which generated some calculations like Day over Day difference, Month over Month difference. If I drag it to row on a pivot table, the grand total of the measures cannot be shown.

Is it because the calculated member is not aggregatable? Is there any ways to show the grand total as well as those calculation on the pivot table?

Million Thanks.

KJ

Kelvin,

This is the intended behaviour. The calculations are written in such a way as to purposely place "NA" in cells where the calculated value would not make sense. For example, if you think of a YTD calculation in the context of all years, it doesn't really make sense; likewise for a year over year calculation.

-rob

No Error but Export to Excel does not finish

No Error but Export to Excel does not finish

When the report has 2 pages with total 500 rows exporting to Excel is not a problem.

If it has 100 pages 5000 rows exporting to excel does not end and it does not return any error but the process does not end either. What might the problem be?

How you are exporting?

Are you using SSIS or DTS package?

I don't see any relation between reporting services & export to excel or are you trying to save as EXCEL spreadsheet.

|||

In there report manager, I run the report and then choose excel and export.

|||

Try increasing the value of MemoryLimit in the RSReportServer.config file available under Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer folder. The default value is 60, try increasing to a higher value but keep it under 120.

Shyam

sql

No Cursor

How would I take the total of the failures column below so I can do %'s of
the total. I can do it with an ADO recordset from VB, but I'm trying to do
it in TSQL 1st.
Thanks
Paul
DECLARE @.BeginDate DateTime
DECLARE @.EndDate DateTime
Set @.BeginDate = '2005-05-12'
Set @.EndDate = '2005-05-16'
Select Top 5
Dept,
SubString(Category + '-' + SubCategory,0, 50) as 'Failure',
Sum(NumFailures) as 'Failures'
From DBO.FTC_TESTRESULTS
Where Dept = '6600'
AND TestDate Between @.BeginDate AND @.EndDate
GROUP BY SubString(Category + '-' + SubCategory,0, 50),Dept
ORDER BY Dept, Sum(NumFailures) DESC
Dept Failure
Failures PCT
-- ---- --
6600 Misc-No Speed Sensor Signal 28
28/70
6600 Seam Leaks-Rear Retainer to Rear Case 21
21/70
6600 Noise-Neutral
10 10/70
6600 Misc-Stud/Bolt Missing 6
ETC
6600 Noise-All Modes
5
Total
70SELECT TOP 5
dept,
SUBSTRING(category + '-' + subcategory,0, 50) AS 'Failure',
SUM(numfailures) AS 'Failures',
SUM(numfailures) /
(SELECT CAST(SUM(numfailures) AS REAL)
FROM dbo.FTC_TESTRESULTS)
FROM dbo.FTC_TESTRESULTS
WHERE dept = '6600'
AND testdate BETWEEN @.begindate AND @.enddate
GROUP BY SUBSTRING(category + '-' + subcategory,0, 50),dept
ORDER BY dept, SUM(numfailures) DESC
David Portas
SQL Server MVP
--|||Dave,
Will I need to further narrow the divisor... I want to divide by 70...
the total of the top 5, it is currently dividing by the entire population .
Bottom of post is the original code that returns top 5 with no divisor.
dept Failure
Failures
-- ---- -- --
--
6600 Misc-No Speed Sensor Signal 28
8.5995086E-3
6600 Seam Leaks-Rear Retainer to Rear Case 21
6.4496314E-3
6600 Noise-Neutral 10
3.071253E-3
6600 Misc-Stud/Bolt Missing 6
1.8427519E-3
6600 Noise-All Modes 5
1.5356265E-3

> SELECT TOP 5
> dept,
> SUBSTRING(category + '-' + subcategory,0, 50) AS 'Failure',
> SUM(numfailures) AS 'Failures',
> SUM(numfailures) /
> (SELECT CAST(SUM(numfailures) AS REAL)
> FROM dbo.FTC_TESTRESULTS)
> FROM dbo.FTC_TESTRESULTS
> WHERE dept = '6600'
> AND testdate BETWEEN @.begindate AND @.enddate
> GROUP BY SUBSTRING(category + '-' + subcategory,0, 50),dept
> ORDER BY dept, SUM(numfailures) DESC
> --
> David Portas
> SQL Server MVP
> --
>
Select Top 5
Dept,
SubString(Category + '-' + SubCategory,0, 50) as 'Failure',
Sum(NumFailures) as 'Failures'
From DBO.FTC_TESTRESULTS
Where Dept = '6600'
AND TestDate Between @.BeginDate AND @.EndDate
GROUP BY SubString(Category + '-' + SubCategory,0, 50),
Dept
ORDER BY Dept, Sum(NumFailures) DESC|||On Wed, 25 May 2005 20:25:51 -0400, Paul Ilacqua wrote:

>Dave,
> Will I need to further narrow the divisor... I want to divide by 70...
>the total of the top 5, it is currently dividing by the entire population .
>Bottom of post is the original code that returns top 5 with no divisor.
Hi Paul,
Try this one. I couldn't test it, since you didn't provide CREATE TABLE
and INSERT statements (see www.aspfaq.com/5006).
SELECT TOP 5 Dept,
SUBSTRING(Category + '-' + SubCategory, 0, 50) AS Failure,
SUM(NumFailures) AS Failures,
SUM(NumFailures) /
(SELECT SUM(Failures)
FROM (SELECT TOP 5 SUM(NumFailures) AS Failures
FROM dbo.FTC_TestResults
WHERE Dept = '6600'
AND TestDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY SUBSTRING(Category + '-' + SubCategory, 0, 50)
ORDER BY SUM(NumFailures) DESC) AS d) AS Pct
FROM dbo.FTC_TestResults
WHERE Dept = '6600'
AND TestDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY SUBSTRING(Category + '-' + SubCategory,0, 50)
ORDER BY SUM(NumFailures) DESC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Works perfectly... and I spent the last couple of days figuring out how
it works. Now I think I got it. Thanks again.
Paul
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9mse911404i9c32u9cak1o4e40gl16t6b7@.
4ax.com...
> On Wed, 25 May 2005 20:25:51 -0400, Paul Ilacqua wrote:
>
> Hi Paul,
> Try this one. I couldn't test it, since you didn't provide CREATE TABLE
> and INSERT statements (see www.aspfaq.com/5006).
> SELECT TOP 5 Dept,
> SUBSTRING(Category + '-' + SubCategory, 0, 50) AS Failure,
> SUM(NumFailures) AS Failures,
> SUM(NumFailures) /
> (SELECT SUM(Failures)
> FROM (SELECT TOP 5 SUM(NumFailures) AS Failures
> FROM dbo.FTC_TestResults
> WHERE Dept = '6600'
> AND TestDate BETWEEN @.BeginDate AND @.EndDate
> GROUP BY SUBSTRING(Category + '-' + SubCategory, 0, 50)
> ORDER BY SUM(NumFailures) DESC) AS d) AS Pct
> FROM dbo.FTC_TestResults
> WHERE Dept = '6600'
> AND TestDate BETWEEN @.BeginDate AND @.EndDate
> GROUP BY SUBSTRING(Category + '-' + SubCategory,0, 50)
> ORDER BY SUM(NumFailures) DESC
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Newbie-Grand Total

This should be easy right? just need a total of the Tier_cnt. Should be 80 but I want the server to tell me.

Thanks

Select tier, count(tier) as tier_cnt
from leads
group by tier

tier tier_cnt
---------------- ----
NULL 0
5
Other 35
Tier 1 25
Tier 2 11
Tier 3 4

Tier_tot
---
????Add the COMPUTE function to your SELECT

Select tier, count(tier) as tier_cnt
from leads
group by tier
compute sum(count(tier))|||Muito Gracias

Exactly what I needed.