Can not see the Group Total when use smart marker group parameter

I'm trying to group the list of vehicles by their Make and then calculate the total purchase price of the group. I have attached the template.xslx that im using. In E2 cell i have setup Smart marker like this
&=[Vehicles].PurchasePrice(subtotal9:Vehicles.Make)

The output shows all the purchase price by group but it DOES NOT show the group total at the bottom of each group.
I follwed the example given here http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/smart-markers.html

Here is my code

            using (Stream inputStream = new FileStream("Template.xlsx", FileMode.Open))
            {
                using (Stream outputStream = new FileStream("TemplateOutput.xlsx", FileMode.Create))
                {
                    TemplateHelper helper = new TemplateHelper();
                    helper.Process(inputStream, outputStream);
                }
            } 
    public class TemplateHelper
    {
        public void Process(Stream inputStream, Stream outputStream)
        {
            // load the designer with workbook
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.Workbook = new Workbook(inputStream);            
        <SPAN style="COLOR: green">// Get Data</SPAN>
        <SPAN style="COLOR: #2b91af">DataSet</SPAN> ds = GetData();

        <SPAN style="COLOR: green">// set data source</SPAN>
        designer.SetDataSource(ds);

        <SPAN style="COLOR: green">// process</SPAN>
        designer.Process(<SPAN style="COLOR: blue">true</SPAN>);           

        <SPAN style="COLOR: green">// save the output</SPAN>
        designer.Workbook.Save(outputStream, <SPAN style="COLOR: #2b91af">SaveFormat</SPAN>.Auto);
    }

    <SPAN style="COLOR: blue">public</SPAN> <SPAN style="COLOR: #2b91af">DataSet</SPAN> GetData()
    {
        <SPAN style="COLOR: #2b91af">DataSet</SPAN> ds = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataSet</SPAN>();
        ds.Tables.Add(GetVehicleListing());
        <SPAN style="COLOR: blue">return</SPAN> ds;
    }

    <SPAN style="COLOR: blue">private</SPAN> <SPAN style="COLOR: #2b91af">DataTable</SPAN> GetVehicleListing()
    {
        <SPAN style="COLOR: green">// setup vehicle listing</SPAN>
        <SPAN style="COLOR: #2b91af">DataTable</SPAN> vehicles = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataTable</SPAN>(<SPAN style="COLOR: #a31515">"Vehicles"</SPAN>);
        vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"Make"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">string</SPAN>)));
        vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"Model"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">string</SPAN>)));
        vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"Year"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">string</SPAN>)));
        vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"VIN"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">string</SPAN>)));
        vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"PurchasePrice"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">double</SPAN>)));
        <SPAN style="COLOR: #2b91af">DataRow</SPAN> dr = <SPAN style="COLOR: blue">null</SPAN>;
        <SPAN style="COLOR: blue">for</SPAN> (<SPAN style="COLOR: blue">int</SPAN> i = 0; i < 10; i++)
        {
            dr = vehicles.NewRow();
            dr[<SPAN style="COLOR: #a31515">"Make"</SPAN>] = <SPAN style="COLOR: #a31515">"Toyota"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"Model"</SPAN>] = <SPAN style="COLOR: #a31515">"Camary"</SPAN> + i.ToString();
            dr[<SPAN style="COLOR: #a31515">"Year"</SPAN>] = <SPAN style="COLOR: #a31515">"2009"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"VIN"</SPAN>] = <SPAN style="COLOR: #a31515">"125RTY45JUY26587K"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"PurchasePrice"</SPAN>] = 40000.00 + i;
            vehicles.Rows.Add(dr);
        }

        <SPAN style="COLOR: blue">for</SPAN> (<SPAN style="COLOR: blue">int</SPAN> i = 0; i < 10; i++)
        {
            dr = vehicles.NewRow();
            dr[<SPAN style="COLOR: #a31515">"Make"</SPAN>] = <SPAN style="COLOR: #a31515">"Honda"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"Model"</SPAN>] = <SPAN style="COLOR: #a31515">"Civic"</SPAN> + i.ToString();
            dr[<SPAN style="COLOR: #a31515">"Year"</SPAN>] = <SPAN style="COLOR: #a31515">"2009"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"VIN"</SPAN>] = <SPAN style="COLOR: #a31515">"125RTY45JUY26587K"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"PurchasePrice"</SPAN>] = 50000.00 + i;
            vehicles.Rows.Add(dr);
        }

        <SPAN style="COLOR: blue">for</SPAN> (<SPAN style="COLOR: blue">int</SPAN> i = 0; i < 10; i++)
        {
            dr = vehicles.NewRow();
            dr[<SPAN style="COLOR: #a31515">"Make"</SPAN>] = <SPAN style="COLOR: #a31515">"Ford"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"Model"</SPAN>] = <SPAN style="COLOR: #a31515">"Focus"</SPAN> + i.ToString();
            dr[<SPAN style="COLOR: #a31515">"Year"</SPAN>] = <SPAN style="COLOR: #a31515">"2009"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"VIN"</SPAN>] = <SPAN style="COLOR: #a31515">"125RTY45JUY26587K"</SPAN>;
            dr[<SPAN style="COLOR: #a31515">"PurchasePrice"</SPAN>] = 60000.00 + i;
            vehicles.Rows.Add(dr);
        }
        <SPAN style="COLOR: blue">return</SPAN> vehicles;
    }        
}</PRE>    </PRE></PRE>

Hi,


Please change/update your smart markers in the E2 and E3 cells accordingly. Please fine attached your template file with updated markers. I have tested with it using your sample code and it works fine according to your requirements.

Thank you.

Couldnt undetsatand your answer. Can you be more specific? Update the E2 and E3 accordingly means what?

Hi,


I mean you need to change your existing smart marker in E2 cell, also you need to insert an additional smart marker in E3 cell. I requested you to kindly check my attached (updated) template file (in my previous reply) for your reference, I mean you can open my updated template file into MS Excel and see the updated smart markers:
https://forum.aspose.com/t/123508


Thank you.

Thanks , got it

is it possible to group by using just the "Year" part of the date. For example if i have date column but i want group them by the year. then what would be the samrt marker?

Hi,


"is it possible to group by using just the “Year” part of the date. For example if i have date column but i want group them by the year. then what would be the samrt marker?"

Well, I think you may do it while extracting data from the back end, you should enhance your SQL Query to extract date field’s data accordingly from the table in the database. For example, you may use some Date function while specifying your query. Also, you may use some string function to extract some part of string value/characters from a given long date value (if the date values are stored in a string column on the back end).

E.g

SELECT Col1, Col2, YEAR(date_field) FROM Table1;

Thank you.

if i do that then the Year column has to be visible in the Excel Sheet. Correct? I have excel sheet which has "Acquired Date" column. but all the rows should be Group By the the "Year" of the "Acquired Date".

Is there any way to do this using Smart Marker? or if i use your technique then is there any way to do this without showing the "Year" column.

Also what is the syntax to Group By multiple columns?

Hi,

There is not any attribute attached with Smart Markers to do so. I think you may also hide the column or even delete the column after processing the markers with data in it.

See the documents for your reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/hidingunhiding-rows-and-columns.html
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/insertingdeleting-rows-and-columns.html


Thank you.

Thanks

Also what is the syntax to Group By multiple columns?

Hi,


"Also what is the syntax to Group By multiple columns?"
Check your other thread for your reference:
<a href="

Thank you.