o***@gmail.com
2007-02-26 15:13:01 UTC
After reading tons of articles about how to prevent initial execution
of a report (which I do know is by design), I came up with my own
hack. WARNING: This is definatey a Hack!, but once you have gotten
past the initial shock it does work quite well. (The code was only
tested on SQLRS 2005, so it may need tinkering for other versions)
Basically, from what I can gather the only way to prevent a report
from running automatically is to have a parameter whose default value
is null but required, for instance a string parameter whose Default
Value has been set to Null, with the Allow Blank Values option
checked, and the Allow Null Values option unchecked.
This works fine, if you have a paramater like this already that you
can just set the default value to Null, but if you don't want a Null
default value then you are out of luck.
You could create an additional parameter just for the sake of
preventing the report from running, but this mucks up the interface,
and having to tell your users, to just ignore that one parameter, it's
only there to keep the report from running straight-away is pretty
rubbish.
But following on that logic, you could create one of these extra
"ghost parameters" and then hide it from view when the page is loaded,
giving you the functionality of the above scenario, but without the
ugly textbox hanging around. This is quite different from creating a
"hidden parameter" by selecting the hidden radio button on the
parameter edit screen in reporting services which for a number of
reasons cannot be used to keep to the report from running.
So to implement it depends on where you are running the report from,
if you are using the Report Manager, the nice little out-of-the-box MS
tool for viewing the reports, then you need only need to modify the C:
\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services
\ReportManager\Pages\Report.aspx file
or wherever your SQL server reporting services install is located to
look like the following:
<%@ Register TagPrefix="MSRS"
Namespace="Microsoft.ReportingServices.UI"
Assembly="ReportingServicesWebUserInterface" %>
<%@ Page language="c#" Codebehind="Report.aspx.cs"
AutoEventWireup="true"
Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage"
EnableEventValidation="false" %>
<script language="C#" runat="server">
void Page_Load(object sender, EventArgs e) {
string ScriptText =
"<script language='javascript'>" +
"var x =document.getElementsByTagName('span'); " +
"for (i=0;i<x.length;i++) " +
"{ " +
"if (x[i].innerText == 'ghostparam') { " +
"var node = x[i].parentElement; " +
"if (node) " +
"node.style.visibility = 'hidden'; " +
"node = node.nextSibling; " +
"if (node) " +
"node.style.visibility = 'hidden'; " +
"} " +
"}";
ScriptText += "<" + (char)47 + "script>";
Page.RegisterStartupScript("hackit",ScriptText);
}
</script>
Note the AutoEventWireup="true" in the Page Directive has been
changed, it is set to "false" by default.
If however you are calling the Web Service directly and not from the
Report Manager and embedding it in your app the change is even easier,
just edit the C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportServer\Pages\ReportViewer.aspx file and add the
following javascript directly just after the closing </form> tag.
<script language="javascript">
var x =document.getElementsByTagName("span");
for (i=0;i<x.length;i++)
{
if (x[i].innerText == "ghostparam") {
var node = x[i].parentElement;
if (node)
node.style.visibility = "hidden";
node = node.nextSibling;
if (node)
node.style.visibility = "hidden";
}
}
</script>
There may be a better way to write this javascript, and of course it
WILL BREAK if microsoft changes the way they output the html in a
future version of Reporting Services, so you will have to modify it
accordingly,
but any modifications should be straightforward and simple just find
the ghostparam textbox in the html and hide it.
The javascript examples above do rely on you creating a textbox
parameter called "ghostparam" with a Default Value of Null, and Allow
Nulls Values set to False and Allow Blanks Set to True. The absence
of such a parameter will not cause an error, it just means unless
there is another parameter preventing it, the report will just run
automatically as usual.
This should help a few of the more "liberal developers" out there :)
Al
of a report (which I do know is by design), I came up with my own
hack. WARNING: This is definatey a Hack!, but once you have gotten
past the initial shock it does work quite well. (The code was only
tested on SQLRS 2005, so it may need tinkering for other versions)
Basically, from what I can gather the only way to prevent a report
from running automatically is to have a parameter whose default value
is null but required, for instance a string parameter whose Default
Value has been set to Null, with the Allow Blank Values option
checked, and the Allow Null Values option unchecked.
This works fine, if you have a paramater like this already that you
can just set the default value to Null, but if you don't want a Null
default value then you are out of luck.
You could create an additional parameter just for the sake of
preventing the report from running, but this mucks up the interface,
and having to tell your users, to just ignore that one parameter, it's
only there to keep the report from running straight-away is pretty
rubbish.
But following on that logic, you could create one of these extra
"ghost parameters" and then hide it from view when the page is loaded,
giving you the functionality of the above scenario, but without the
ugly textbox hanging around. This is quite different from creating a
"hidden parameter" by selecting the hidden radio button on the
parameter edit screen in reporting services which for a number of
reasons cannot be used to keep to the report from running.
So to implement it depends on where you are running the report from,
if you are using the Report Manager, the nice little out-of-the-box MS
tool for viewing the reports, then you need only need to modify the C:
\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services
\ReportManager\Pages\Report.aspx file
or wherever your SQL server reporting services install is located to
look like the following:
<%@ Register TagPrefix="MSRS"
Namespace="Microsoft.ReportingServices.UI"
Assembly="ReportingServicesWebUserInterface" %>
<%@ Page language="c#" Codebehind="Report.aspx.cs"
AutoEventWireup="true"
Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage"
EnableEventValidation="false" %>
<script language="C#" runat="server">
void Page_Load(object sender, EventArgs e) {
string ScriptText =
"<script language='javascript'>" +
"var x =document.getElementsByTagName('span'); " +
"for (i=0;i<x.length;i++) " +
"{ " +
"if (x[i].innerText == 'ghostparam') { " +
"var node = x[i].parentElement; " +
"if (node) " +
"node.style.visibility = 'hidden'; " +
"node = node.nextSibling; " +
"if (node) " +
"node.style.visibility = 'hidden'; " +
"} " +
"}";
ScriptText += "<" + (char)47 + "script>";
Page.RegisterStartupScript("hackit",ScriptText);
}
</script>
Note the AutoEventWireup="true" in the Page Directive has been
changed, it is set to "false" by default.
If however you are calling the Web Service directly and not from the
Report Manager and embedding it in your app the change is even easier,
just edit the C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportServer\Pages\ReportViewer.aspx file and add the
following javascript directly just after the closing </form> tag.
<script language="javascript">
var x =document.getElementsByTagName("span");
for (i=0;i<x.length;i++)
{
if (x[i].innerText == "ghostparam") {
var node = x[i].parentElement;
if (node)
node.style.visibility = "hidden";
node = node.nextSibling;
if (node)
node.style.visibility = "hidden";
}
}
</script>
There may be a better way to write this javascript, and of course it
WILL BREAK if microsoft changes the way they output the html in a
future version of Reporting Services, so you will have to modify it
accordingly,
but any modifications should be straightforward and simple just find
the ghostparam textbox in the html and hide it.
The javascript examples above do rely on you creating a textbox
parameter called "ghostparam" with a Default Value of Null, and Allow
Nulls Values set to False and Allow Blanks Set to True. The absence
of such a parameter will not cause an error, it just means unless
there is another parameter preventing it, the report will just run
automatically as usual.
This should help a few of the more "liberal developers" out there :)
Al