Please help me : The Official Microsoft ASP.NET Forums

Reply

Decker Dong ...

All-Star

118619 Points

Re: Please help me

Jan 01, 2013 12:34 AM|LINK

PleHelpMePls
with pivoting, what should i use for a table? Gridview or datalist? or is there any other data format that can be use for pivoting?

You can use GridView.

Reply

PleHelpMePls

None

0 Points

12 Posts

Re: Please help me

Jan 01, 2013 01:15 AM|LINK

Decker Dong - MSFT

PleHelpMePls
with pivoting, what should i use for a table? Gridview or datalist? or is there any other data format that can be use for pivoting?

You can use GridView.

Here's the code...just wondering what or where i need to change? and also any changes in the code for the gridview design?

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.OracleClient;
using System.Drawing;


public partial class Build_RoyaltyProvisionSummaryProcess : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            CheckSession.CheckSessionCreated();
        }

        string strFrom = Request.QueryString["StartFrom"].ToString();
        string strTo = Request.QueryString["StartTo"].ToString();

        if (strFrom.Length != 0)
        {
            strFrom = FormatDate(strFrom);
        }

        if (strTo.Length != 0)
        {
            strTo = FormatDate(strTo);
        }

        // string strSQL = null;

        // string sqlFileName = "RoyaltyProvisionSummary.sql";
        // strSQL = "";

        // string filepath = ConfigurationManager.AppSettings["sqlfilepath"].ToString() + "\\" + sqlFileName;
        // strSQL = SQLBuilder.File(filepath);

        // biq.ErrorLog.WriteLog("Filepath RoyaltyProvisionSummary bindGridView: " + strSQL);

        //     try
        //     {
        //         SqlDataSource1.SelectCommand = strSQL;
        //         this.GridView1.DataBind();
        //         biq.ErrorLog.WriteLog("Report is successfully generated by " + Session["UserName"] + " : WIPValueDetailsProcess.aspx.cs");
        //     }
        //     catch (Exception exp)
        //     {
        //        biq.ErrorLog.WriteError(exp.ToString());
        //      }

        string strSQL = "SELECT segment3 AS chipcode, " +
                       "customer_name," +
                       "round ((SUM (shipped_quantity * unit_selling_price) / 1000),2) AS REVENUE_USD_000, " +
                       "ip_provider, " +
                       "round (rate, 3) as rate, " +
                       "((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) as ROYALTIES_USD, " +
                       "round ((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1), 2) as WITHHOLDING_TAX_10_USD, " +
                       "round ((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) + (((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1)), 2) AS TOTAL_ROYALTIES_USD, " +
                       "round (((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) + (((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1)) * conversion_rate), 2) as TOTAL_ROYALTIES_RM " +
                       "FROM (SELECT  msi.segment3, " +
                       "rc.customer_name, " +
                       "wdd.shipped_quantity, " +
                       "wdd.lot_number, " +
                       "ctl.unit_selling_price, " +
                       "gdrv.conversion_rate, " +
                       "wdd.inventory_item_id, " +
                       "sir.ip_provider, " +
                       "sir.rate " +
                       "FROM apps.wsh_delivery_details wdd, " +
                       "apps.mtl_system_items_b msi, " +
                       "apps.ra_customers rc, " +
                       "apps.gl_daily_conversion_types gdct, " +
                       "apps.gl_daily_rates_v gdrv, " +
                       "apps.sm_ip_provider sip, " +
                       "apps.sm_ip_royalty sir, " +
                       "apps.ra_customer_trx_all cta, " +
                       "apps.ra_customer_trx_lines_all ctl " +
                       "WHERE wdd.inventory_item_id = msi.inventory_item_id " +
                       "AND wdd.customer_id = rc.customer_id " +
                       "AND sir.ip_provider = sip.ip_provider " +
                       "and sir.inventory_item_id = wdd.inventory_item_id " +
                       "AND msi.organization_id = 26 " +
                       "AND ctl.interface_line_attribute6 = wdd.source_line_id " +
                       "AND cta.customer_trx_id = ctl.customer_trx_id " +
                       "AND cta.trx_date BETWEEN '" + strFrom + "' AND '" + strTo + "'" +
                       "AND cta.trx_date = gdrv.conversion_date " +
                       "AND gdct.user_conversion_type = gdrv.user_conversion_type " +
                       "AND gdrv.user_conversion_type = 'Corporate' " +
                       "AND gdrv.from_currency = 'USD' " +
                       "AND gdrv.to_currency = 'MYR') " +
                       "GROUP BY segment3, unit_selling_price, conversion_rate, ip_provider, customer_name, rate ";


        biq.ErrorLog.WriteLog("SQL is:" + strSQL);

        try
        {
            SqlDataSource1.SelectCommand = strSQL;
            this.GridView1.DataBind();
            biq.ErrorLog.WriteLog("Report is successfully generated by " + Session["UserName"] + " : RoyaltyProvisionSummary.aspx.cs");
        }
        catch (Exception exp)
        {
            biq.ErrorLog.WriteError(exp.ToString());
        }

    }

    private string FormatMonth(string strMonth)
    {
        if (strMonth == "01" || strMonth == "1")
        {
            return "JAN";
        }
        else if (strMonth == "02" || strMonth == "2")
        {
            return "FEB";
        }
        else if (strMonth == "03" || strMonth == "3")
        {
            return "MAR";
        }
        else if (strMonth == "04" || strMonth == "4")
        {
            return "APR";
        }
        else if (strMonth == "05" || strMonth == "5")
        {
            return "MAY";
        }
        else if (strMonth == "06" || strMonth == "6")
        {
            return "JUN";
        }
        else if (strMonth == "07" || strMonth == "7")
        {
            return "JUL";
        }
        else if (strMonth == "08" || strMonth == "8")
        {
            return "AUG";
        }
        else if (strMonth == "09" || strMonth == "9")
        {
            return "SEP";
        }
        else if (strMonth == "10")
        {
            return "OCT";
        }
        else if (strMonth == "11")
        {
            return "NOV";
        }
        else
        {
            return "DEC";
        }
    }

    private string FormatDate(string strDate)
    {
        string[] arrStr = new string[2];
        char[] splitter = { '-' };

        arrStr = strDate.Split(splitter);

        if (arrStr[0].Length == 1)
        {
            arrStr[0] = "0" + arrStr[0];
        }

        try
        {
            Convert.ToInt32(arrStr[1]);
            arrStr[1] = FormatMonth(arrStr[1]);
        }
        catch
        {
            //do nothing
        }

        arrStr[2] = arrStr[2].Remove(0, 2);
        strDate = arrStr[0] + "-" + arrStr[1] + "-" + arrStr[2];

        return strDate.ToUpper();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        this.GridView1.AllowPaging = false;
        this.GridView1.DataBind();

        GridViewExportUtil.Export("RoyaltyProvisionSummary.xls", this.GridView1);
    }
    

    }

here's the gridview design code

<%@ Register TagPrefix="uc1" TagName="VHead" Src="../Controls/vHead.ascx" %>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="RoyaltyProvisionSummaryProcess.aspx.cs" Inherits="Build_RoyaltyProvisionSummaryProcess" %>





<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>BIQ</title>
    <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
    <meta name="CODE_LANGUAGE" content="C#">
    <meta name="vs_defaultClientScript" content="JavaScript">
    <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
    <link rel="stylesheet" href="../Styles/Table.css" type="text/css">
    <link rel="stylesheet" href="../Styles/BIQStyle.css" type="text/css">

    <script language="javascript" type="text/javascript">
        function openCalendar() {
            window.open('../Calendar/StdCalendar.html','calendar','WIDTH=220,HEIGHT=250,scrollbars=yes');    
        }

        function IMG1_onclick() {

        }
    </script>

</head>
<body>
    <uc1:VHead ID="VHead" runat="server" />
    <form id="form1" runat="server">
        <div style="text-align: center">
            &nbsp;&nbsp;&nbsp;
            <table cellspacing="0" cellpadding="0" width="100%" align="center">
                <tbody>
                    <tr>
                        <td>
                            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                                CellPadding="4" CellSpacing="1" ForeColor="#333333" GridLines="None" PageSize="100"
                                Width="100%" DataSourceID="SqlDataSource1">
                                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                <Columns>
                                    <asp:TemplateField HeaderText="Chipcode">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl1" runat="server" Text='<%# Bind("chipcode") %>'></asp:Label>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Customer's Name">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl2" runat="server" Text='<%# Bind("customer_name") %>'></asp:Label>
                                        </ItemTemplate>
                                        <ItemStyle Wrap="False" />
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Revenue (USD'000)">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl3" runat="server" Text='<%# Bind("REVENUE_USD_000") %>'></asp:Label>
                                        </ItemTemplate>
                                        <ItemStyle Wrap="False" />
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Ip Provider">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl4" runat="server" Text='<%# Bind("ip_provider") %>'></asp:Label>
                                        </ItemTemplate>
                                        <ItemStyle Wrap="False" />
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Rate (%)">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl5" runat="server" Text='<%# Bind("rate") %>'></asp:Label>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    
                                   <asp:TemplateField HeaderText="Royalties (USD)">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl6" runat="server" Text='<%# Bind("ROYALTIES_USD") %>'></asp:Label>
                                        </ItemTemplate>
                                    </asp:TemplateField>
 
                                    <asp:TemplateField HeaderText="Withholding Tax 10% (USD)">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl7" runat="server" Text='<%# Bind("WITHHOLDING_TAX_10_USD") %>'></asp:Label>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                   <asp:TemplateField HeaderText="Total Royalty (USD)">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl8" runat="server" Text='<%# Bind("TOTAL_ROYALTIES_USD") %>'></asp:Label>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Total Royalty (RM)">
                                        <ItemTemplate>
                                            <asp:Label ID="lbl9" runat="server" Text='<%# Bind("TOTAL_ROYALTIES_RM") %>'></asp:Label>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                </Columns>
                                <RowStyle BackColor="#EFF3FB" />
                                <EditRowStyle BackColor="#2461BF" />
                                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                <AlternatingRowStyle BackColor="White" />
                            </asp:GridView>
                            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringOracle %>"
                                ProviderName="<%$ ConnectionStrings:ConnectionStringOracle.ProviderName %>"></asp:SqlDataSource>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            <br />
                            &nbsp;<asp:Button ID="Button2" runat="server" OnClick="Button1_Click" Text="Export To Excel" />
                            &nbsp; &nbsp;
                            <input id="btnBack" type="button" value="Back" onclick="javascript:history.back();" /></td>
                    </tr>
                </tbody>
            </table>
        </div>
    </form>
</body>
</html>

Reply

PleHelpMePls

None

0 Points

12 Posts

Re: Please help me

Jan 01, 2013 01:21 AM|LINK

Decker Dong - MSFT

PleHelpMePls
with pivoting, what should i use for a table? Gridview or datalist? or is there any other data format that can be use for pivoting?

You can use GridView.

where should i change or add so the data will show up as i wanted to?

Edited by PleHelpMePls on Jan 01, 2013 01:23 AM

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Please help me

Jan 01, 2013 01:42 AM|LINK

PleHelpMePls
where should i change or add so the data will show up as i wanted to?

Hi，

My codes can generate DataTable，and you can directly bind to the GridView.

Reply

PleHelpMePls

None

0 Points

12 Posts

Re: Please help me

Jan 01, 2013 01:56 AM|LINK

Decker Dong - MSFT

PleHelpMePls
where should i change or add so the data will show up as i wanted to?

Hi，

My codes can generate DataTable，and you can directly bind to the GridView.

The with the declare @sql.....Right?

here's my SQL code

SELECT   segment3 AS chipcode,
	     customer_name,
		 round ((SUM (shipped_quantity * unit_selling_price) / 1000),2) AS REVENUE_USD_000,
		 ip_provider,
		 round (rate, 3) as rate,
		 ((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) as ROYALTIES_USD,
		 round ((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1), 2) as WITHHOLDING_TAX_10_USD,
		 round ((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) + (((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1)), 2) AS TOTAL_ROYALTIES_USD,
		 round (((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) + (((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1)) * conversion_rate), 2) as TOTAL_ROYALTIES_RM
    FROM (
	SELECT  msi.segment3,
		 	  rc.customer_name,
              wdd.shipped_quantity,
			  wdd.lot_number,
			  ctl.unit_selling_price,
			  wdd.inventory_item_id,
			  gdrv.conversion_rate,
			  sir.ip_provider,
			  sir.rate
                     FROM apps.wsh_delivery_details wdd,
                          apps.mtl_system_items_b msi,
                          apps.ra_customers rc,
						  apps.gl_daily_conversion_types gdct,
						  apps.gl_daily_rates_v gdrv,
                          apps.sm_ip_provider sip,
                          apps.sm_ip_royalty sir,
                          apps.ra_customer_trx_all cta,
                          apps.ra_customer_trx_lines_all ctl
          WHERE       wdd.inventory_item_id = msi.inventory_item_id
                      AND wdd.customer_id = rc.customer_id
                      AND sir.ip_provider = sip.ip_provider
					  and sir.inventory_item_id = wdd.inventory_item_id
                      AND msi.organization_id = 26
                      AND ctl.interface_line_attribute6 = wdd.source_line_id
                      AND cta.customer_trx_id = ctl.customer_trx_id
					  AND cta.trx_date BETWEEN '01-NOV-12' AND '30-NOV-12'
					  AND cta.trx_date = gdrv.conversion_date
					  AND gdct.user_conversion_type = gdrv.user_conversion_type
					  AND gdrv.user_conversion_type = 'Corporate'
					  AND gdrv.from_currency = 'USD'
					  AND gdrv.to_currency = 'MYR'
						  )
GROUP BY segment3,
unit_selling_price,
shipped_quantity,
conversion_rate,
	  	 ip_provider,
         customer_name,
         rate

where should add your code into my code?

sorry because im new to this stuff...

Edited by PleHelpMePls on Jan 01, 2013 02:06 AM

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Please help me

Jan 01, 2013 02:05 AM|LINK

PleHelpMePls
The with the declare @sql.....Right?

Hi，

What actually do you wanna do？

Reply

PleHelpMePls

None

0 Points

12 Posts

Re: Please help me

Jan 01, 2013 02:13 AM|LINK

Decker Dong - MSFT

PleHelpMePls
The with the declare @sql.....Right?

Hi，

What actually do you wanna do？

currently im using the code (look previous post for the codes) to generate a report. but i want is to be like the design on the 1st post

Name Subject Total

Math Science

Roy 90 90

Jack 81 87 168

Reply

PleHelpMePls

None

0 Points

12 Posts

Re: Please help me

Jan 01, 2013 02:51 AM|LINK

Decker Dong - MSFT

PleHelpMePls
PleHelpMePls

Hi，

Maybe you should use SQL convertion，here's the sample：

【Your SQL's table】
A B C
1 1 11
1 11 1
22 2 32
3 42 1
52 4 5
【Your SQL statement】
declare @sql varchar(2000)set @sql=''select @sql=@sql+',max(case B when '+cast(B as varchar(10))+' then c end) ['+cast(B as varchar(10))+']' from (Select distinct B from TB) xset @sql='select A'+@sql+' from (select A,B,count(distinct C) c from tbgroup by A,B) vgroup by a'exec(@sql)

how to combined your code into mine?

SELECT   segment3 as chipcode, 
	     customer_name,
		 round ((SUM (shipped_quantity * unit_selling_price) / 1000),2) AS REVENUE_USD_000, 
		 ip_provider,
		 round (rate, 3) as rate,
		 ((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) as ROYALTIES_USD,
		 round ((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1), 2) as WITHHOLDING_TAX_10_USD,
		 round ((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) + (((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1)), 2) AS TOTAL_ROYALTIES_USD,
		 round (((((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) + (((((SUM (shipped_quantity * unit_selling_price) / 1000) * rate) / 100) *1000) * 0.1)) * conversion_rate), 2) as TOTAL_ROYALTIES_RM	 
    FROM (		
	SELECT  msi.segment3, 
		     rc.customer_name,
                     wdd.shipped_quantity, 
			  wdd.lot_number,
			  ctl.unit_selling_price,
			  wdd.inventory_item_id,
			  gdrv.conversion_rate,
			  sir.ip_provider, 
			  sir.rate
                     FROM apps.wsh_delivery_details wdd,
                          apps.mtl_system_items_b msi,
                          apps.ra_customers rc,
			  apps.gl_daily_conversion_types gdct,
			  apps.gl_daily_rates_v gdrv,
                          apps.sm_ip_provider sip,
                          apps.sm_ip_royalty sir,
                          apps.ra_customer_trx_all cta,
                          apps.ra_customer_trx_lines_all ctl
          WHERE       wdd.inventory_item_id = msi.inventory_item_id
                      AND wdd.customer_id = rc.customer_id
                      AND sir.ip_provider = sip.ip_provider
		      AND sir.inventory_item_id = wdd.inventory_item_id
                      AND msi.organization_id = 26
                      AND ctl.interface_line_attribute6 = wdd.source_line_id
                      AND cta.customer_trx_id = ctl.customer_trx_id
		      AND cta.trx_date BETWEEN '01-NOV-12' AND '30-NOV-12'
            	      AND cta.trx_date = gdrv.conversion_date
		      AND gdct.user_conversion_type = gdrv.user_conversion_type                                
		      AND gdrv.user_conversion_type = 'Corporate'
		      AND gdrv.from_currency = 'USD'
	              AND gdrv.to_currency = 'MYR'	 			  
						  )
GROUP BY segment3,
unit_selling_price,
shipped_quantity,
conversion_rate,
	  	 ip_provider,
         customer_name,
         rate

Reply

Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Please help me

Jan 01, 2013 05:20 AM|LINK

Hello,

Do you wanna show data contents or……？

Reply

PleHelpMePls

None

0 Points

12 Posts

Re: Please help me

Jan 01, 2013 05:25 AM|LINK

Decker Dong - MSFT

Hello,

Do you wanna show data contents or……？

yes...

Please help me RSS

21 replies

Decker Dong ...

Re: Please help me

PleHelpMePls

PleHelpMePls

Re: Please help me

Decker Dong - MSFT

PleHelpMePls

PleHelpMePls

Re: Please help me

Decker Dong - MSFT

PleHelpMePls

Decker Dong ...

Re: Please help me

PleHelpMePls

PleHelpMePls

Re: Please help me

Decker Dong - MSFT

PleHelpMePls

Decker Dong ...

Re: Please help me

PleHelpMePls

PleHelpMePls

Re: Please help me

Decker Dong - MSFT

PleHelpMePls

PleHelpMePls

Re: Please help me

Decker Dong - MSFT

PleHelpMePls

Decker Dong ...

Re: Please help me

PleHelpMePls

Re: Please help me

Decker Dong - MSFT

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft