.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
satyapriyanayak
Sambanthamoorthy
Post New Web Links

SSRS aggregate function

Posted By:      Posted Date: November 16, 2010    Points: 0   Category :Sql Server
 

I have an SSAS MDX query that uses a calculated member;  when adding the subtotal in the report I would like to rely on SSAS doing to aggregation rather than the incorrect sum SSRS is trying to do.  But when I use =Aggregate(Fields!x.Value)  I dont get anything back, the cell is blank.

Why?




Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx


View Complete Post


More Related Resource Links

About the Aggregate Function MIN

  
Hi all, I am using the select statement with MAX(id), MIN(id), COUNT(*) of a very big table and it is returning me the value in less than a second. But surprisingly if i am using aggregate function MIN(id) in a seperate select statement it is taking upto 90 Seconds. Here are the 2 statements I am running SELECT MAX(ID),MIN(ID),COUNT(*) FROM A TABLE  -- Time consuming for this:  less than a Second. SELECT MIN(ID)FROM A TABLE  -- Time consuming for this:  Upto 90 Seconds. Please clarify how it work internally when we call the select statement with Aggregate functions?  chinna

SSRS 2005 SP3 - Instr function with Global Variable fails in Subscriptions

  
Hi I have a very simple expression in a text box in my report:  INSTR(Globals!ReportServerUrl,"Report").  This expression should return the integer value of where the string "Report" appears in the Global variable for the ReportServerUrl.  Simple enough.  This works fine for generating the report directly through report manager, and in my case returns a value of 20. However when run as a subscription and emailed to myself the value returned is 0. This suggests that the search string does not appear in the Global variable. In the reportserver config file the URLroot tag contains the correct server address - http://myserver/reportserver. Does anyone know why i should be experiencing this issue with my subscriptions?  Your help would be much appreciated! Thanks Guy  Guy

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

  

How do i get around this???

select

 

sum(PODet.UnitCost*ReceiverDet.Qty2Receive) as TotalCost

 <

Writing a custom Aggregate function in SSIS

  

Hi is it possible to write a custom aggregate function in SSIS. Is there any other way to do it better? Any help is highly appreciated.

 

My values are like this

Item | VALUE

1,abc

2,def

1,xyz

1,wer

2,tyu

3,ppl

 

I am trying to bring the output. 

ITEM,VALUE

1, abc-xyz-wer

2, def-tyu

3, ppl


Ganesh Ranganathan
[Please mark the post as answer if it answers your question]
blog.ganeshzone.net

Cannot find either Column "dbo" or the user-defined function or aggregate "dbo.GetCandiateID", or th

  

Hi,

Good morning to everybody. I am not understanding why I am getting the following error (also mentioned in the subject line).

I have written the following function in SQL Server 2005:

CREATE FUNCTION dbo.GetCandateID()
RETURNS INT
AS
BEGIN
    declare @candidate_id int
    SELECT @candidate_id = max(c.candidate_id) from dbo.candidates c
    if(@candidate_id is null)
        set @candidate_id = 1001
    else
        set @candidate_id = @candidate_id + 1
    return @candidate_id
END

The function compiled properly. I have used the above function like below in the query:

select dbo.GetCandidateID()

I am getting the following error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetCandidateID", or the name is ambiguous


can i create a Clr ud Aggregate Function with over/ order by clause?

  

i want create a UD Aggregate like  Row_Number () for using with 'Over (Order By ...) ' (not like sum() over (part....)

is it possible?
thanks.


SQL PROCEDURE cannot perform aggregate function on expression containing aggregate or subquery

  

Hi I want do SUM(TABLE1.PARAM1)*(SELECT TABLE2.PARAM FROM TABLE 2 WHERE TABLE2.PARAM3=TABLE.PARAM3

I DID BELLOW CODE BUT GETTING ERROR AS cannot perform aggregate function on expression containing aggregate or subquery

YOUR HELP REALLY APRECIATED

 

ALTER PROCEDURE ShowcClientvsTimevsCost(@Country nvarchar(15) )

AS
select  tTIMESHEET.ACTIVITY , sum(REV_BY_CLIENT.Jan+REV_BY_CLIENT.Feb+REV_BY_CLIENT.Mar+REV_BY_CLIENT.Apr+REV_BY_CLIENT.May+REV_BY_CLIENT.Jun+REV_BY_CLIENT.Jul+REV_BY_CLIENT.Aug+REV_BY_CLIENT.Sep+REV_BY_CLIENT.Oct+REV_BY_CLIENT.Nov+REV_BY_CLIENT.Dec) as SUMREVENUE ,
sum(tTIMESHEET.SUN+tTIMESHEET.MON+tTIMESHEET.THU+tTIMESHEET.WED+tTIMESHEET.THU+tTIMESHEET.FRI+tTIMESHEET.SAT)as SUMTIME ,
sum(((tTIMESHEET.SUN)+tTIMESHEET.MON+tTIMESHEET.THU+tTIMESHEET.WED+tTIMESHEET.THU+tTIMESHEET.FRI+tTIMESHEET.SAT)*
(SELECT HR_CHARGE  FROM tEMPLOYEE t  WHERE  t.EMP_ID =tTIMESHEET.EMP_ID ) )as SUMTIMECOST
from tTIMESHEET INNER JOIN REV_BY_CLIENT on
tTIMESHEET.ACTIVITY = REV_BY_CLIENT.Client and REV_BY_CLIENT.Country = @Country
group by ACTIVITY
RETURN 


Using the "Send To" function to copy SSRS reports (.rdl) from one document library to another

  
We are trying to use the "Send To" function to let users get copies of SSRS reports, but still retain the ability to roll out changes by updating the original.  This would help us have "one version of the truth" but still have the ability to copy reports from one SharePoint site to another.  One reason to have the report on multiple sites is to give users the ability to set default values for the parameters.  IT creates one copy and the users configure it to meet their own needs.

Problem:
Copying the file using "Send To" works.  However, it loses the data source link which effectively kills the copied report.  We have tried this in various forms of permissions and sequences, but experience the same issue consistently.  Obviously, one could re-enter the data source link, but that is not optimal.

We saw the same behavior when we copied the report out of Windows Explorer from library to another.

Question:
Is this is a known issue?  Can it be fixed?  If so, how?

Aggregate (count) function that produces a different result to what is expected.

  
Hi Folks
I have an aggregate transformation, that runs a count on a column.  The  column  has 23 rows  with dates and a further 2 rows that have no date information. When I run a count function on the column, it returns a count of 25.  My hypothesis is that  this is linked to the 2 rows are treated as being  BLANK as opposed to being NULL (I understand the COUNT function will not count rows that have null values).

I have two questions
1) How do test this hypothesis that he difference is due to a blank vs null issue and then more importantly
2) How to I resolve the issue so that the 2 "empty" rows are not counted?

Many thanks

Steve


Sum function giving error in SSRS 2008

  

Hi,

 Below expression is giving #Error in SSRS 2008, but working fine in SSRS 2005.

=Sum(Fields!Rate.Value)

Note: When i export the report result to excel sheet sum value is showing correctly.

Let me know if any one know about this error.

 

Thanks

Shiva


Userdefined aggregate function erroring out when all values are null

  

HI, I have a aggregate function that calculates median in all cases excet if every value that is being passed in is null and I cant seem on how to fix it. I beleive it has to do with either the read or write methods below is the code that I am using and the error. Any help on this would be greatly appreciated. Thanks in advane.

using

 

System;

using

 

System.IO;

<

Built-in Functions - Aggregate Functions

  
Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.

Content page control passes as argument to javascript function

  

Here is the content page:

********************************************************************

<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="JavaDynamicCopyValue.aspx.cs" Inherits="JavaDynamicCopyValue" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
 <script type="text/javascript" >
 function copyValue(input, output )
{
   ouput.value = input.value;
   return true;
}
 </script>
 <asp:TextBox ID="txt1" runat="server" onchange="copyValue(this,document.getElementById('<%=txt2.ClientID%>'))"></asp:TextBox>
    <br />
    <br />
    <asp:TextBox ID="txt2" runat="server"></asp:TextBox>
</asp:Content>

********************************************************************

I got a javascript run time error says output is undefined. if I view source of the page, the txt1 markup became:

<input name="ctl00$ContentPlaceHol

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.

  

I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend