Monday, January 18, 2010

Implementing Most Recently Used Lookups - SQLServerCentral

Great idea for implementing adaptive list/drop boxes of lookup data, with the most commonly selected items first.

How many times have you had to scroll through 267 country names to get to the "United States" in a list box? We constantly force our users to do unnecessary work when it would be a breeze for us to implement adaptive lists ordered by most commonly selected foreign key values or by most recently selected foreign key values.

Implementing Most Recently Used Lookups - SQLServerCentral

Friday, January 08, 2010

Brian Hartman's Report Viewer Blog & How to Move the @*#^$! View Report button

Looking forward to implementations of SQL 2008 R2 and the new Visual Studio 2010.

One of the new features we added to the ASP.Net Report Viewer in Visual Studio 2010 is a JavaScript API to allow you to interact with the viewer on client.  In reading many of the posts on the report controls forum, we found that many people struggle when implementing a custom toolbar or replacing portions of the toolbar functionality.  The new JavaScript API is intended to make it easier for you to provide the same functionality available through the built-in toolbar with a minimum amount of effort.

Brian Hartman's Report Viewer Blog

With the Visual Studio 2005 / SQL 2005 Report Viewer, there is no easy way to manipulate the toolbar.

One common issue is the View Report button scrolls off the page for larger width reports.  Other than implementing your own custom parameters & toolbar functionality, here is another way to manipulate.

                   <rsweb:ReportViewer ID="ReportViewer1" runat="server" ProcessingMode="Remote" Height="100%" Width="100%" ShowDocumentMapButton="false"  SizeToReportContent="true" AsyncRendering="false">
        </rsweb:ReportViewer>
        </td>
        <script language="javascript">
        function getRepViewBtn() {
  return document.getElementsByName("ReportViewer1$ctl00$ctl00")[0];
}
  var btn = getRepViewBtn(); 
  src = btn.outerHTML;
  btn.style.display = 'none';
  src = src.replace('View Report','View/Refresh Report');
  src = src.replace('style="','style="background-color:maroon;color:ffffff"');
  document.write (src);

</script>

This places the View Report button above the Report Viewer, and changes the text.

To fix the double scroll bar issue.

<head runat="server">
<title>Report Viewer</title>
<script type="text/javascript" language="javascript">

window.onload=function()
{

var viewer = document.getElementById("ReportViewer1");
var frame = document.getElementById("ReportFrame");
if (frame != null && viewer != null)
{
var reportDiv = eval("ReportFrame").document.getElementById("report").contentDocument.getElementById("oReportDiv");
viewer.style.height = reportDiv.scrollHeight;
viewer.style.width = reportDiv.scrollWidth;

  //var btn = eval("ReportFrame").document.getElementsByName("ReportViewer1$ctl00$ctl00")[0];
// btn.style.display = 'none';
}

}
</script>
<script type="text/javascript" language="javascript">

window.moveTo(0,0);
window.resizeTo(screen.width,screen.height);

</script>

</head>

It sounds like the Visual Studio 2010 Report Viewer just might solve these issues… but for those of us still stuck in the past…

Monday, January 04, 2010

SQL Server Helper & a helper script to redirect SQL to Excel

 

Useful site for lots of SQL Script examples.

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

SQL Server Helper

If you have Powershell installed, exporting to Excel is probably a non-issue for you.

However, here is a quick script that was useful to me and was fairly simple to implement.

1. Create a directory and copy the Excel shortcut to it.  Rename the shortcut to Excel.lnk
2. Create a command script countalltables.cmd

osql –S [sqlservername] -d [databasename] -E -I -s "," -Q "spcCountAllTables" > CountallTables.Csv
Excel countAllTables.Csv

3. Create a stored procedure in the database spcCountAllTables

create proc spcCountAllTables
as
set nocount on
SELECT  'myTable' as TableName, COUNT(*) as Total FROM MyTable UNION ALL

4. Run the proc countalltables.cmd

You should see a list of tables you defined with their counts.