Sum for only items showed

[Xuan Truong]

I have a report displayed as below :
Crosstab with row group ID :
            ID    Qty        Items
            1     3            P1
                                 P2
                                 P3
            2     2            C1
                                 C2
---------------------------------
Total Qty=  13
Using Sum(Fields.Qty), result = 13 ( = sum all rows)
How to get sum(Fields.Qty) = 5 ? (only items Qty displayed in report)
Thanks.

[Jayraj]

Did you get the solution?

[Xuan Truong]

Sorry, who can help me?
I used function Exec("crosstab1", sum(Fields.Qty) but result = 13.
=> Please advise me best solution.

[Xuan Truong]

Actually Telerik reporting can not solve above problem ?
i spent many days to find solution but now can not .
Can any one help me ? Thanks very  much.

[Steve]

Hello Tran,

Aggregate functions would work on the whole set of data and this is expected. Data-scope functions work on specified scope and in your case the scope you have set is "crosstab1" which is the data item itself i.e. again the aggregate function would be executed over the whole data.
If you need to execute an aggregate under a different scope i.e. a row/column group, you need to specify it as first argument e.g. = Exec("rowgroupID", sum(Fields.Qty))

Kind regards,
Steve
the Telerik team

[Xuan Truong]

I've tried it, but no result. I received an message : An error occurred while processing TextBox 'TextBox10' ; Invalid scope : 'ID_PTC' (althought there are 1 row group 'ID_PTC')
Please see attach file : designer and result picture.
Please advise me .
Thanks.

[Massimiliano Bassili]

I can't say from the screens whether your item is in containing scope of your group. Note what the Expression scope article says: "A named scope can be the name of a data item, or a group. You must specify the current scope or a containing scope; you cannot specify a scope that is lower or at the same level in containment order than the current scope."

Cheers!

[Xuan Truong]

Please check pic below and advise me.
I want only show a total for field Qty (grouped) in textBox2. I don't understand exactly your tutorial and how to apply in this case :
ID_PTC    Qty=5        Items
            1     3                P1
                                      P2
                                      P3
---------------------------------------
            2     2                 C1
                                      C2
---------------------------------------
Thanks.

[Steve]

Hello Xuan,

As I said in my previous post, aggregate functions work on the whole set of data. If Sum(Fields.Qty) returns 13 as result (i.e. the sum of all rows) then this result is expected. Please elaborate on what base you expect the Sum(Fields.Qty) to return 5 instead? Where are the 3 and 2 values coming from? Are those only the first values for a group i.e. Sum(First(Fields.Qty)) in other words are you trying to create a nested aggregate?

Regards,
Steve
the Telerik team

[Xuan Truong]

Sorry, I have a table as below :
ID_PTC      Qty              Items
            1       3                P1
            1       3                P2
            1       3                P3
---------------------------------------
            2       2                 C1
            2       2                 C2
---------------------------------------

In Designer, I set 2 Crosstab Row group as below :
Name                                  Item                           Grouping                    
ID_PTC                               textBox6                    Fields.ID_PTC
     (DetailGroup)                                                   Fields.Item

Then Table will display as below :
ID_PTC      Qty              Items
            1       3                P1
                                       P2
                                       P3
---------------------------------------
            2       2                 C1
                                        C2
---------------------------------------


If I set TableHeader "Qty" as : Qty = {Sum(Fields.Qty)} => result : Qty = 13
But I want only Qty =5 ( sum of 2 and 3 ) such as idea : Qty= {Sum(First(Fields.Qty). } But report show err : "Agregate/RowNumber/columNumber  function can not be nested."
Please, help how to solve this problem?
Thanks.

[Squall]

Hi,
You can't use nested aggregates with telerik reports. However I am very sure that you can do that with a custom aggregate that sums only the first group value.
SN

[Jayraj]

How to do that?

[Dimitar]

For details on how to create a custom aggregate function, please refer to the User Aggregate Functions - Telerik Reporting article.

[Kapil]

Hi 
I want the functionality of nested aggregation like "Sum(First(Fields.Qty))". Please help me to achieve this functionality
thanks..  

[Jitendra]

Hello Sqaull,

 I am facing the same problem what Xuan mention , can you please explain little  bit how we can acheive through custom aggregate ?

 Thanks,

[Ramya]

I have a cross tab where I have used aggregation to find the max in a group. At the end I need to display the total of  all max values. Please let me know if this can be achieved?

[Katia]

Hello Ramya,

This scenario requires additional logic to be added to the report processing (using report events). You can save the processing maximum value for each group in TextBox ItemDataBound event in a variable and then use this variable to set the value for the TextBox showing the sum of maximum value.

A sample report demonstrating this approach is attached.


Regards,
Katia
Telerik by Progress

[Ramya]

Hi Katia,

This solution worked.  Thanks for the help.

Regards,

Ramya

[Ravi]

Hi I am looking for Cross tab Total.

It seems to be summing all the values.but i need what ever the values present in the report it has to sum only.

If choose only one month in filter,it has to show only one moth.

Please reply asp.

Here i attached file.

[Katia]

Hi Ravi,

In order to filter the data in the report based on the value of report parameter you need to apply filter rules to the data item or to use data source parameters to filter the data upon retrieval.
For more detailed information check Filtering Data section.

Example of the filtering applied on report level can be found in Employee Sales demo report. It is located also in Telerik Reporting instalaltion folder if you need to check the settings of this report.

Example of the filtering applied on data source level is demonstrated in Invoice report which is also located in installation folder.


Regards,
Katia
Telerik by Progress

[Ravi]

Thanks Man,It is  working.

[Ravi]

Thanks Man, it is working

[vikas]

Hi 

i want to use Aggregates function in telerik report designer like (Sum,Max,Min). i am using telerik report designer .trdx file. Can you please provide example with syntax to how we use this.

Thanks

[Argie]

Hello, I have the same case with Ramya. I need to get the total of all max values  but I am using a standalone  Telerik designer. Is there a way to achieve this result? Thanks

[Silviya]

Hello Argie,

TRDX reports need to be deserialized | unpackaged in a custom report resolver for the Reporting REST service to be able to modify them at run-time (add events).

Below code snippet demonstrates how to plug a custom resolver for the reports and to deserialize/modify TRDX report in it.
After having the type report instance, you can access it inner items(specific TextBox) with ItemCollection.Find Method and attach the event to it.

//Reporting REST Service
public class ReportsController : ReportsControllerBase
{
    static ReportServiceConfiguration configurationInstance;

    static ReportsController()
    {
        //This is the folder that contains the report definitions
        //In this case this is the Reports folder
        var appPath = HttpContext.Current.Server.MapPath("~/");
        var reportsPath = Path.Combine(appPath, "Reports");

        //Add resolver for trdx/trdp report definitions,
        //then add resolver for class report definitions as fallback resolver;
        //finally create the resolver and use it in the ReportServiceConfiguration instance.
        //var resolver = new ReportFileResolver(reportsPath)
        //    .AddFallbackResolver(new ReportTypeResolver());
        var resolver = new MyReportResolver();

        //Setup the ReportServiceConfiguration
        configurationInstance = new ReportServiceConfiguration
        {
            HostAppId = "Html5App",
            Storage = new FileStorage(),
            ReportResolver = resolver,
            // ReportSharingTimeout = 0,
            // ClientSessionTimeout = 15,
        };
    }

    public ReportsController()
    {
        //Initialize the service configuration
        this.ReportServiceConfiguration = configurationInstance;
    }
}

//Custom Resolver example
public class MyReportResolver : Telerik.Reporting.Services.Engine.IReportResolver
{
    public Telerik.Reporting.ReportSource Resolve(string report)
    {
        var testreport = new Telerik.Reporting.Report();

        System.Xml.XmlReaderSettings settings = new System.Xml.XmlReaderSettings();
        settings.IgnoreWhitespace = true;

        var appPath = HttpContext.Current.Server.MapPath("~/");
        var reportPath = Path.Combine(appPath, "Reports/Report1.trdx");

        using (System.Xml.XmlReader xmlReader = System.Xml.XmlReader.Create(reportPath, settings))
        {
            Telerik.Reporting.XmlSerialization.ReportXmlSerializer xmlSerializer = new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();
            testreport = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
        }

        Telerik.Reporting.TextBox textBox5 = testreport.Items.Find("textBox5", true)[0] as Telerik.Reporting.TextBox;
        textBox5.ItemDataBound += textBox5_ItemDataBound;

        Telerik.Reporting.TextBox textBox7 = testreport.Items.Find("textBox7", true)[0] as Telerik.Reporting.TextBox;
        textBox7.ItemDataBinding += textBox7_ItemDataBinding;


        var IRS = new InstanceReportSource();
        IRS.ReportDocument = testreport;

        return IRS;
    }

    private void textBox5_ItemDataBound(object sender, EventArgs e)
    {
            Telerik.Reporting.Processing.TextBox txt = (Telerik.Reporting.Processing.TextBox)sender;
            int value = Convert.ToInt32(txt.Value);
            sum += value;
    }

    private void textBox7_ItemDataBinding(object sender, EventArgs e)
    {
        Telerik.Reporting.Processing.TextBox txt = (Telerik.Reporting.Processing.TextBox)sender;
         txt.Value = sum.ToString();
    }
}

Regards,
Silviya
Progress Telerik

[Argie]

Thanks Silviya, I'll try this out!