Monday, January 31, 2011

Directly print any div of web page using JavaScript & HTML


Sample Div that is to print:
<div id="divPrint">
  <br />
<asp:Label ID="lblDetails" runat="server" Font-Bold="True" Font- Size="Medium">
</asp:Label>
  <br />                                   
      <asp:GridView ID="gvSample" runat="server">
      </asp:GridView>
</div>


JavaScript Code:
<script type="text/javascript">

        function CallPrint(strid) {
            var prtContent = document.getElementById(strid);
            var width = 600;
            var height = 500;
            var left = (screen.width - width) / 2;
            var top = (screen.height - height) / 2;
            var WinPrint = window.open('', '', 'letf=' + left + ',top=' + top + ',width=' + width + ',height=' + height + ',toolbar=0,scrollbars=0,status=0');
            WinPrint.document.write(prtContent.innerHTML);
            WinPrint.document.close();
            WinPrint.focus();
            WinPrint.print();
            WinPrint.close();
        }
</script>


Print Button:
<input type="button" value="Print" id="btnPrint" runat="Server" onclick="javascript:CallPrint('divPrint')" />

Wednesday, January 5, 2011

Pivoting Query

 Actual Table:
Year               Quarter            Amount
1990                1                      1.1
199021.2
199031.3
199041.4
199112.1
199122.2
199132.3
199142.4
199242.4
Desired Output: 
(Here Q for Quarter)
Year       Q-1       Q-2      Q-3       Q-4
1990        1.1        1.2        1.3        1.4
1991        2.1        2.2        2.3        2.4
1992        0.0        0.0        0.0        2.4
Query:
Use Northwind
GO
CREATE TABLE Pivot
( Year      SMALLINT,
  Quarter   TINYINT,
  Amount      DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
INSERT INTO Pivot VALUES (1992, 4, 2.4)
GO
SELECT * FROM Pivot
GO
SELECT Year,
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
Another Output:
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
             SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
             SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
             SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
             SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
     FROM Pivot AS P
     GROUP BY P.Year) AS P1
GO