Easy way to copy over calculated fields to another dataset

I have a dataset in which I created about 10 calculated fields. I
often use this dataset for other reports. How can I copy over those
fields to the dataset I create in my other reports quickly?Hi.
Edit the code (RDL) of the report, look for the dataset fields, copy
and paste them onto the code of the new report.
Regards,
Paulo Cunhasql

Early User Questions for developing large number of reports

Hi All,
Firstly, let me say who we are.....a group of us are developing a BI
product where we have decided to use Report Services as the reporting
tool. We are long term BI experts and IT consultants......We are
experienced in all manner of reporting and query tools we are just new
to RS. We settled on RS as our 'lower end' reporting tool for our
product and we will give our clients the 'option' of selecting other
reporting tools but the 'out of the box' reports will be in RS.
We have been dilligent and done our reading (WROX RS book) and testing
and figured out most of what we want to do. We have our first report up
and running and we are learning what the limits of RS seem to be to us.
So hopefully I will not ask too many 'dumb' questions... :-)
We also hope by asking questions here that other users of RS might also
benefit from the public discussion.
Q1. We are also working on our 'templates' for reports and looking at
how we develop a large number (200-300) of reports according to a
'template'. In the WROX Book they talk about codesmith and a way of
handling templates. But we didn't 'get' it...Also, when we try using
the VS.NET report wizard it seems that it insists on a query rather
than a stored procedure for it's data for the wizard to work.
So we are thinking we need to write each report by copying the rdl xml,
editting it by hand and then putting it into vs.net to further edit.
We have no real problem with that but we were thinking someone must
have come up with a better idea than editing the XML by now!!!
Has anyone published papers/ideas/tools (fee or free) to manage the
development and deployment of large numbers of reports?
Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
talking about the pricing of SQL Server 2005. But we have not seen any
comment on the possible cost of RS2005 if it is NOT purchased with SQL
Server 2005. We would like to have the option of deploying our product
onto SQL Server with RS running either on that machine or elsewhere. Is
there any public comment from MSFT as to the possible pricing of RS2005
when not purchased with SQL Server?
Q3. Is there any published information on 'best practice' or 'best
examples of usage' of report services? For example MicroStrategy,
Business Objects etc post demos of their products to showcase the 'best
possible use' of the product. Dundas have also put out some really
great demos. But the RS demos from MSFTs web site look a little
'plain' especially the graphics. Are there any really great examples
of RS 'out there' or are the demos on the MSFT web site the best there
is? We have decided to go with DNN for the portal and we are currently
testing RS/DNN integration.
Q4. Layout of prompts on a report.
We have reports with quite a few prompts and they are 'grouped'. For
example we have a set of 4 prompts for the user to select the levels in
the company reporting structure. ANd then we have prompts for the
client to select year and month. Then we might have campaign structure
selection which is 3 levels....so we would like to lay out
Line 1: 4 prompts
Line 2: 2 Prompts
Line 3: 3 prompts
We are told this is not supported...but it also seems like it only
ever places two prompts on one line? If we cannot group prompts can we
at least get it to put more than 2 prompts per line?
Thanks in Advance
Peter Nolan
www.peternolan.comRS is like most of the other MS products: a "back-end" tool with high
custimization available. You can extend it from many way.
Q2:
The licensing is the same as 2000. RS is part of SQL 2005 and if you want
report capabilities only, you have to buy a license of SQL 2005. But the RS
repository required SQL Server.
So for your answer, yes RS can be purchased "separatly" but at the full
price of SQL 2005.
Q3:
On the MS web site, there is some samples reports for different usage:
CRM, Exchange, Axapta, SQL Logs, RS logs.
Best pratices & usages depend on your needs.
Designing printing reports is different from web based usage only reports.
Today there is nothing except simple samples. But these small samples don't
limit me to use RS.
Generally I start from an overall report, then I link to detailed reports.
Q4:
Create you own prompt page if you want to create a specific layout.
Remember that RS is designed for developpers to include reporting capability
into their applications.
But there is a lack of sample and helps to create front end tools around RS.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1120988013.636226.99630@.o13g2000cwo.googlegroups.com...
> Hi All,
> Firstly, let me say who we are.....a group of us are developing a BI
> product where we have decided to use Report Services as the reporting
> tool. We are long term BI experts and IT consultants......We are
> experienced in all manner of reporting and query tools we are just new
> to RS. We settled on RS as our 'lower end' reporting tool for our
> product and we will give our clients the 'option' of selecting other
> reporting tools but the 'out of the box' reports will be in RS.
> We have been dilligent and done our reading (WROX RS book) and testing
> and figured out most of what we want to do. We have our first report up
> and running and we are learning what the limits of RS seem to be to us.
> So hopefully I will not ask too many 'dumb' questions... :-)
> We also hope by asking questions here that other users of RS might also
> benefit from the public discussion.
>
> Q1. We are also working on our 'templates' for reports and looking at
> how we develop a large number (200-300) of reports according to a
> 'template'. In the WROX Book they talk about codesmith and a way of
> handling templates. But we didn't 'get' it...Also, when we try using
> the VS.NET report wizard it seems that it insists on a query rather
> than a stored procedure for it's data for the wizard to work.
> So we are thinking we need to write each report by copying the rdl xml,
> editting it by hand and then putting it into vs.net to further edit.
> We have no real problem with that but we were thinking someone must
> have come up with a better idea than editing the XML by now!!!
> Has anyone published papers/ideas/tools (fee or free) to manage the
> development and deployment of large numbers of reports?
>
> Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
> talking about the pricing of SQL Server 2005. But we have not seen any
> comment on the possible cost of RS2005 if it is NOT purchased with SQL
> Server 2005. We would like to have the option of deploying our product
> onto SQL Server with RS running either on that machine or elsewhere. Is
> there any public comment from MSFT as to the possible pricing of RS2005
> when not purchased with SQL Server?
>
> Q3. Is there any published information on 'best practice' or 'best
> examples of usage' of report services? For example MicroStrategy,
> Business Objects etc post demos of their products to showcase the 'best
> possible use' of the product. Dundas have also put out some really
> great demos. But the RS demos from MSFTs web site look a little
> 'plain' especially the graphics. Are there any really great examples
> of RS 'out there' or are the demos on the MSFT web site the best there
> is? We have decided to go with DNN for the portal and we are currently
> testing RS/DNN integration.
>
> Q4. Layout of prompts on a report.
> We have reports with quite a few prompts and they are 'grouped'. For
> example we have a set of 4 prompts for the user to select the levels in
> the company reporting structure. ANd then we have prompts for the
> client to select year and month. Then we might have campaign structure
> selection which is 3 levels....so we would like to lay out
> Line 1: 4 prompts
> Line 2: 2 Prompts
> Line 3: 3 prompts
> We are told this is not supported...but it also seems like it only
> ever places two prompts on one line? If we cannot group prompts can we
> at least get it to put more than 2 prompts per line?
>
> Thanks in Advance
>
> Peter Nolan
> www.peternolan.com
>|||Hi Jerome,
Q2. In licensing MSFT talks about buying RS when it needs to be
deployed on a server other than the SQL Server...and the WROX book
talks about this as being a 'medium' sized deployment.....if that
means that we need to buy a full SQL Server license (I guess workgroup)
to run RS on a separate server that's ok...we just wanted to
know....but that seems a bit pricey....
Q4. I found examples on odetocode web site but most of the examples I
am finding are 'fragments' intended for developers to include into
already designed/built apps.....that's ok..if there are no other
examples out there we can start from where we are at...just wanted to
check there was not something more publicly available before we spent
our time and effort...it's kind of suprising MSFT has not published
some more things to give developers a better base to work from to go up
against the likes of Crystal/Business Objects/Cognos...maybe more is
coming'
Best Regards
Peter|||Q1, Q3, and Q4--answered in depth in Peter Blackburn and William Vaughn's
Hitchhiker's Guide!
Amazon: http://shrinkster.com/6km
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1120988013.636226.99630@.o13g2000cwo.googlegroups.com...
> Hi All,
> Firstly, let me say who we are.....a group of us are developing a BI
> product where we have decided to use Report Services as the reporting
> tool. We are long term BI experts and IT consultants......We are
> experienced in all manner of reporting and query tools we are just new
> to RS. We settled on RS as our 'lower end' reporting tool for our
> product and we will give our clients the 'option' of selecting other
> reporting tools but the 'out of the box' reports will be in RS.
> We have been dilligent and done our reading (WROX RS book) and testing
> and figured out most of what we want to do. We have our first report up
> and running and we are learning what the limits of RS seem to be to us.
> So hopefully I will not ask too many 'dumb' questions... :-)
> We also hope by asking questions here that other users of RS might also
> benefit from the public discussion.
>
> Q1. We are also working on our 'templates' for reports and looking at
> how we develop a large number (200-300) of reports according to a
> 'template'. In the WROX Book they talk about codesmith and a way of
> handling templates. But we didn't 'get' it...Also, when we try using
> the VS.NET report wizard it seems that it insists on a query rather
> than a stored procedure for it's data for the wizard to work.
> So we are thinking we need to write each report by copying the rdl xml,
> editting it by hand and then putting it into vs.net to further edit.
> We have no real problem with that but we were thinking someone must
> have come up with a better idea than editing the XML by now!!!
> Has anyone published papers/ideas/tools (fee or free) to manage the
> development and deployment of large numbers of reports?
>
> Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
> talking about the pricing of SQL Server 2005. But we have not seen any
> comment on the possible cost of RS2005 if it is NOT purchased with SQL
> Server 2005. We would like to have the option of deploying our product
> onto SQL Server with RS running either on that machine or elsewhere. Is
> there any public comment from MSFT as to the possible pricing of RS2005
> when not purchased with SQL Server?
>
> Q3. Is there any published information on 'best practice' or 'best
> examples of usage' of report services? For example MicroStrategy,
> Business Objects etc post demos of their products to showcase the 'best
> possible use' of the product. Dundas have also put out some really
> great demos. But the RS demos from MSFTs web site look a little
> 'plain' especially the graphics. Are there any really great examples
> of RS 'out there' or are the demos on the MSFT web site the best there
> is? We have decided to go with DNN for the portal and we are currently
> testing RS/DNN integration.
>
> Q4. Layout of prompts on a report.
> We have reports with quite a few prompts and they are 'grouped'. For
> example we have a set of 4 prompts for the user to select the levels in
> the company reporting structure. ANd then we have prompts for the
> client to select year and month. Then we might have campaign structure
> selection which is 3 levels....so we would like to lay out
> Line 1: 4 prompts
> Line 2: 2 Prompts
> Line 3: 3 prompts
> We are told this is not supported...but it also seems like it only
> ever places two prompts on one line? If we cannot group prompts can we
> at least get it to put more than 2 prompts per line?
>
> Thanks in Advance
>
> Peter Nolan
> www.peternolan.com
>|||thanks for the pointer goodman 93...I'll see if I can get a copy of
that too.....
Peter

DynamicValuesUnavailable

I am using the WS interface and returning reports for rendering will our UI.
I am successfully getting back the parameters. I would like to create a
drop down list box where appropriate for the parameters. So i iterate
through the ValidValues[]. This works fine for the exception of when those
values are driven from a secondary dataset in the report. In this case i
get the DynamicValuesUnavailable enum for the State and the ValidValues[] is
undefined value. I believe I have ruled out security in that both the main
report and the drop down are driven from the same shared data source. Any
Ideas?Never mind - need to set ForRendering to true, although the one liner on
msdn below is a little misleading.
ForRendering
A Boolean expression that indicates how the parameter values are to be used.
If set to a value of true, parameter properties that are returned are based
on the parameter data that was used during the execution of the report.
"jsl" <someone@.microsoft.com> wrote in message
news:%23I%23LaIQmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I am using the WS interface and returning reports for rendering will our
>UI. I am successfully getting back the parameters. I would like to create
>a drop down list box where appropriate for the parameters. So i iterate
>through the ValidValues[]. This works fine for the exception of when those
>values are driven from a secondary dataset in the report. In this case i
>get the DynamicValuesUnavailable enum for the State and the ValidValues[]
>is undefined value. I believe I have ruled out security in that both the
>main report and the drop down are driven from the same shared data source.
>Any Ideas?
>

Dynamically Referencing a Field Name

Is there any way to reference a Data Set field name dynamically in an expression?
For example, I have 5 reports that have the same layout but reference different columns.
For example, TextBox1 would be one of the following names depending on the report:
=Fields!A_Total.Value
=Fields!B_Total.Value
=Fields!C_Total.Value
=Fields!D_Total.Value
=Fields!E_Total.Value
I would like to build the field name dynamically instead (e.g., "Fields!" & Parameters.XXX.Label & "_Total.Value").
I know we could use IFF statements but that gets messy.
I have used dynamic sql in stored procedures to solve this issue for other reports, but I was wondering if there was an easy way to do this within the report (I have limited ability to modify this particular stored proc).
Thanks,
Mike=Fields(Parameters.WhichField.Value & "_Total").Value
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mike Lyncheski" <MikeLyncheski@.discussions.microsoft.com> wrote in message
news:CCFFB904-03B0-41CF-87C9-ED43CC027355@.microsoft.com...
> Is there any way to reference a Data Set field name dynamically in an
expression?
> For example, I have 5 reports that have the same layout but reference
different columns.
> For example, TextBox1 would be one of the following names depending on the
report:
> =Fields!A_Total.Value
> =Fields!B_Total.Value
> =Fields!C_Total.Value
> =Fields!D_Total.Value
> =Fields!E_Total.Value
> I would like to build the field name dynamically instead (e.g., "Fields!"
& Parameters.XXX.Label & "_Total.Value").
>
> I know we could use IFF statements but that gets messy.
> I have used dynamic sql in stored procedures to solve this issue for other
reports, but I was wondering if there was an easy way to do this within the
report (I have limited ability to modify this particular stored proc).
> Thanks,
> Mike
>

Dynamically Passing a Dataset to a Report in Reporting Services 2005

We are investigating the possibility of moving our reports from a Crystal Reports environment over to Reporting Services 2005. Currently we sometimes create a ADODB.Recordset in VB6 code which is passed to a Crystal Report to be displayed or printed. The Crystal Report structure has previously been created using a .ttx file and the recordset then supplies the data at runtime.

Could someone please tell how this scenario can be achieved using VB.Net 2005 and Reporting Services.

Thank you, in advance.

The equivalent of doing this in SSRS-land is to use the VS 2005 Report Viewer contorl in local mode (the report is processed at the client, not on the SSRS server), provide it with the RDL for the report (called RDLC), and "marry" a dataset to the datasource of the control. You'll find examples of same in books online and out on gotreportviewer.com

Dynamically display/hide the parameter input

I have a handful of reports that are currently used by sales reps, and I'm trying to make them available to their regional VP's, and coporate users (executives and administrative staff that support Sales nationwide).

Currently, the reports take the UserID and resolve it to show the information that is only appropriate for that specific rep.

What I would like to do is have the parameter section at the top of the report be displayed for higher level users, so they could select an individual sales rep from a drop-down. (Ideally, the RVP's would only be able to select from reps in their region, but the corporate users would be able to select any rep.) The problem is, I don't want any of the sales reps to be able to select a rep other than themselves, for obvious reasons.

Is there a way to have the parameter section hidden/displayed dynamically, based on the UserID, so that users other than reps would have the ability to enter the desired rep name, but reps would not?

If you are using the .NET ReportView, it is just:

ReportViewer1.ShowParameterPrompts = False

|||I am using Report Manager.|||If you want any level of control, you are going to have to host the ReportViewer control in an ASP.NET page and customize the web page/report based on the user. There is not much you can do in ReportManager as far as fine control.|||

1. One way is to control this by writting a html page instead of report manager home page within which you will have a link to each of the report. Depending on who the user is you will change the path of the link (with or without parameters), meaning you will default all the parameters and pass them within the hidden link, but this wont work if you have more that 1 parameter and would want to hide only the user id parameter

2. Another option is to control this within the report. As your userid parameter list is being populated by a stored procedure, you will make the stored procedure accept a input parameter which is the userId of the person that is trying to run the report. Depending on who the user is the SP will return only the required users list.

Meaning

1. if XXX is passed and if XXX is RVP then it will return only the sales persons of XXX's region.

2. If YYY is passed and if YYY is sales person then it will return back only YYY

3. If CORP is passed and if CORP is a corporate user then the procedure will return all sales person's names

Hope this is clear and helps!!!

|||I think another way of achieving this is writting specific RDL code.|||

Thanks.

In the short term, this was the best solution. I wrote a sp that populates the drop down based on the NetLogin value.

So, the sales reps can see the drop down, but it only has their name in it.

|||You can also set the parameters visible in the URL using the &rsarameters=True/False.

i.e.

Code Snippet

http://SERVER/ReportServer/ReportPath&rs:Command=Render&rs:Parameters=False

HTH,
Jimmy

Dynamically creating SQL Server Report

I've been working with Crystal Reports for a couple of months and I'm now interested in creating a SQL Server Report but need assistance. With CR I'm able to point a report to a dataset within the application and then modify the dataset by passing parameters from the web page that hosts the crystalreportviewer. The CR then reflects the updated dataset. I'm looking to create the same functionality with SQL Server Reports but I'm unfamiliar. Below is the code I use for one of my reports called 'Holds'. Any assistance is appreciated!

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<asp:Panel ID="rptPanel" runat="server" CssClass="rptViewer" Visible="false">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"
HasCrystalLogo="False" EnableDrillDown="False" />
</asp:Panel>
</form>

Holds.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
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 CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
public partialclass Holds : System.Web.UI.Page
{
private ReportDocument myReport;
// Handles initial page loadprotected void Page_Load(object sender, EventArgs e)
 {
if (!IsPostBack)
 {
 Session["Season"] =null;
 }
if (Session["Season"] !=null)
 {
 ConfigureCrystalReports();
 }
 }
// Handles configuring crystal reportsprivate void ConfigureCrystalReports()
 {
 myReport =new ReportDocument();
string reportPath = Server.MapPath("reports/Holds.rpt");
 myReport.Load(reportPath);
 DataSet dataSet = DataSetConfiguration.Holds;
 myReport.SetDataSource(dataSet);
 CrystalReportViewer1.ReportSource = myReport;
 }
// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e)
 {
if (ddlSeason.SelectedIndex == 0)
 {
 Session["Season"] =null;
 }
else
 {
 Session["Season"] = ddlSeason.SelectedValue;
 ConfigureCrystalReports();
 }
 }
}

DataSetConfiguration.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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;
/// <summary>
/// Summary description for DataSetConfiguration
/// </summary>public class DataSetConfiguration
{
public DataSetConfiguration()
 {
//
 // TODO: Add constructor logic here
 // }// Handles creating holds datasetpublic static DataSet Holds
 {
get {// get parametersstring SEASON = System.Web.HttpContext.Current.Session["Season"].ToString();
string TK_EVENT_STATUS ="TK_EVENT_" + SEASON +"_STATUS";
string TK_EVENT ="TK_EVENT_" + SEASON;
 DataSet dataSet =new DataSet();
string query ="SELECT TOP 100 PERCENT A.[STATUS], '" + SEASON +"' AS [SEASON], C.NAME AS [DESC]," +
" A.[Z_ID] AS [EVENT], A.[NSTATUS], B.[NAME], B.[DATE], B.[TIME]" +
" FROM " + TK_EVENT_STATUS +" A LEFT OUTER JOIN" +
" " + TK_EVENT +" B ON A.Z_ID = B.Z_ID LEFT OUTER JOIN" +
" TK_SEAT_STATUS C ON A.STATUS = C.STATUS COLLATE SQL_Latin1_General_CP1_CS_AS" +
" WHERE (A.Z_ID NOT LIKE '%P') AND (A.Z_ID NOT LIKE 'W%')" +
" AND (A.STATUS <> 'O') ORDER BY B.[DATE], B.[TIME], B.[NAME]";
 dataSet.ReadXmlSchema(@."D:\Web Sites\Holds\App_Code\HoldsDataSet.xsd"); SqlConnection conn =new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString);
 SqlDataAdapter cmd =new SqlDataAdapter(query, conn);
 cmd.Fill(dataSet,"Holds");
return dataSet;
 }
 }
}

Thanks!

I created a solution for my question. Now I have a SQL Server Report(.rdlc) displaying the data.

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" Width="400px">
</rsweb:ReportViewer>
</div>
</form>

Holds.aspx.cs

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Collections;using System.Text;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 Microsoft.Reporting.WebForms;public partialclass Test : System.Web.UI.Page{// Handles initial page loadprotected void Page_Load(object sender, EventArgs e) {if (!IsPostBack) { Session["Season"] =null; } }protected void ConfigureReports() { ReportViewer1.LocalReport.ReportPath = Server.MapPath("reports/Holds.rdlc"); DataSet dataSet = DataSetConfiguration.Holds; ReportDataSource ds =new ReportDataSource("HoldsDataSet_Holds", dataSet.Tables[0]); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(ds); ReportViewer1.LocalReport.Refresh(); }// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e) {if (ddlSeason.SelectedIndex == 0) { Session["Season"] =null; }else { Session["Season"] = ddlSeason.SelectedValue; ConfigureReports(); } } }

DataSetConfiguration.cs is the same.

FYI I found the information I needed at http://www.codeproject.com/aspnet/ReportViewer.asp

Dynamically Created Stored Procs??

Okay. Scenario:

We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.

From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.

This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.

So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?

Or if not, is there some round-about yet effective way of doing this?

Thank you in advance for any help.
-ZanderBI guess I don't understand why you feel you need to be able to alter existing stored procedures on demand. Automating something like this would take a lot more programming skill and development time than just copying an existing procedure and modifying it to create a new one.|||The point of the matter is that right now, there is no efficient on demand procedure (that I know of). Any dynamic SQL must rely on having an execution plan made by the server every time (as I understand).

When you have web enabled reports that already take several minutes, it becomes key that optimizations are made. At the same time, having something that is dynamic, but generally used enough to say it's called all the time is something that would be worthy of being called by a stored proc. Perhaps even something equivical would be nice that is not as static, but there is nothing I know of that does that for dynamic SQL.

If you had a web server that server for 4 items 80% of the time, but those 4 items changed frequently (like once a week) you could say there would be a good argument for something like this, could you not?

The structure of the where statement can cause the execution plan to be changed.

So my question again is why you wouldn't want a dynamically created stored proc for this.

Are dynamic queries going to have a cached execution plan if the same ones are called enough?|||When the procedure is supposed to be used only once, it is perhaps not a good idea to create the procedure and the drop it after having executed it once.

Another approach that i have tried, is to create what is called an anonymous block. This is the body of the procedure without the create procedure part. This is a T-sql block that is sent to the database and executed as if it where a straight forward select. It can contain exeption handling and give all the functionality you expect from a procedure, but will not impact the system dictionary. The overhead will probably be less than the procedure approach since the system dictionary is not updated.

n both cases you will get one parse, optimize, compile and execute. The database will perform it as optimal as a procedure. I have tried it with code performaing a cursor loop and doing a couple of queries for each row in the cursor. This is code that is much better to perform inside the database than from an external application.|||Nevermind. It is possible through a standard connection to the server (with right permissions) to change/add stored procs. I didn't know if this was a possibility or not. It only surprises me that noone said it's possible with dynamic SQL statements.|||Originally posted by ZanderB
When you have web enabled reports that already take several minutes

Every new request becomes a log of the report in a table.

The get a list and select what they want... the table stores the proc name which gets executed

You'll come to a point when you've created almost all variations.

And if you're telling us that you have to wait several minutes for a web based report...that's waaaaaaaaaaaaaaaaaaaay too long

You need to build a process to denormalize the data so they pop...

I would imagine an in/experienced user would think the damn thing is broken...|||Originally posted by ostrande
anonymous block.

Don't look now...but your Oracle is showing...

:D|||No one said it was possible with dynamic SQL because no one wants to recommend dynamic SQL. Dynamic SQL is usually slower and less efficient, although admittedly in some cases where there are complex conditions dynamic SQL can be faster. In these cases, though, the dynamic SQL is best created and executed within the procedure given the parameters supplied. The procedure itself is not modified.

And bear in mind that having a pre-compiled stored procedure does save execution time - like 1 or 2 seconds (at the most!) for each time the procedure is executed. It does not take that long for SQL server to compile a procedure. If your querys are taking 3-4 minutes, the problem is not in the compilation time.|||Yo, Blind dude...

ever wait MINUTES for a web based report?

Dynamically changing reports at runtime

Hi - I'm trying to decide among reporting options without spending too much
time going down the wrong path, so I hope someone can answer my questions
about Reporting Services capabilities...
With Reporting Services, can I dynamically (at runtime via code):
1) add or remove subreports from a report?
2) make a subreport visible or hidden?
3) add or delete columns?
4) make columns visible or hidden?
5) add or delete tables?
6) make a table visible or hidden?
Is doing any of these things fairly straightforward?
Thanks so much in advance,
SherylYes, you can use an expression for the Hidden property of respective report
elements to dynamically show/hide them at runtime.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sheryl Landon" <shland@.comcast.net> wrote in message
news:OoDKy5$pEHA.3244@.tk2msftngp13.phx.gbl...
> Hi - I'm trying to decide among reporting options without spending too
much
> time going down the wrong path, so I hope someone can answer my questions
> about Reporting Services capabilities...
> With Reporting Services, can I dynamically (at runtime via code):
> 1) add or remove subreports from a report?
> 2) make a subreport visible or hidden?
> 3) add or delete columns?
> 4) make columns visible or hidden?
> 5) add or delete tables?
> 6) make a table visible or hidden?
> Is doing any of these things fairly straightforward?
> Thanks so much in advance,
> Sheryl
>

dynamically changing default parameter?

I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.

Can this be done?

here is how i'm setting my "from date" (datetime) param to be the first of the current month
=DateSerial(Year(Now()), Month(Now()),1)

for today, just use
=Today()

this is for a whole date, you can just pick the year part

Dynamically Adding Subreports

I would like to Create a report that will print as one document, but is
basically made up of multiple reports (rdl files), however, I need to be able
to determine which reports (rdl files) should be added and pass parameters to
each rdl file dynamically. Can this be done with SQL Reporting Services?You could do that in VS 2005 using the RS Winforms/Webforms controls in
"local" processing mode and registering a subreport callback which
dynamically provides different RDLs with different sets of parameters.
I don't really have a good suggestion for RS 2000 - you could probably
achieve some of your requirements by dynamically generating RDLs, publishing
and rendering them by writing an application that uses the SOAP-API.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amie Fedric" <AmieFedric@.discussions.microsoft.com> wrote in message
news:6194746B-7D5B-49CE-8498-50AD8EF3B561@.microsoft.com...
>I would like to Create a report that will print as one document, but is
> basically made up of multiple reports (rdl files), however, I need to be
> able
> to determine which reports (rdl files) should be added and pass parameters
> to
> each rdl file dynamically. Can this be done with SQL Reporting Services?

Dynamic way to change Font, Size, etc.

Hi,
I have one report format for 20 companies. Some of them require differnt
fonts and font sizes. I don't want to duplicate reports because they need
different font format. Is there a dymanic way to change font using one report
only? Thanks.
ChuckThere are two possible ways to do this:
1) Have your font settings in expressions that are populated by report
parameters or data. (It really works!) You can set default parameter
values.
2) Create a process that dynamically updates the RDL (report source) for
each company. This would mean inserting an extra step at either deployment
time or run time.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:3B1DF44A-14C7-4D90-B9D3-A3E67C913E0F@.microsoft.com...
> Hi,
> I have one report format for 20 companies. Some of them require differnt
> fonts and font sizes. I don't want to duplicate reports because they need
> different font format. Is there a dymanic way to change font using one
> report
> only? Thanks.
>
> Chuck|||Now I get some ideas to work on my project. Thanks so much for your reply.
-Chuck
"Jeff A. Stucker" wrote:
> There are two possible ways to do this:
> 1) Have your font settings in expressions that are populated by report
> parameters or data. (It really works!) You can set default parameter
> values.
> 2) Create a process that dynamically updates the RDL (report source) for
> each company. This would mean inserting an extra step at either deployment
> time or run time.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:3B1DF44A-14C7-4D90-B9D3-A3E67C913E0F@.microsoft.com...
> > Hi,
> >
> > I have one report format for 20 companies. Some of them require differnt
> > fonts and font sizes. I don't want to duplicate reports because they need
> > different font format. Is there a dymanic way to change font using one
> > report
> > only? Thanks.
> >
> >
> > Chuck
>
>|||Another solution without having to add all those Parameters would be to
create a custom class that returns the font and font size etc. Then reference
the class in an expression. I have done this and it works great. Then you
could either distrubute a seperate class for each customer or have the class
read the information in from file and just distrubute a seperate file for
each.
See "Writing Custom Code" in the RS Help
Dave
"Chuck" wrote:
> Hi,
> I have one report format for 20 companies. Some of them require differnt
> fonts and font sizes. I don't want to duplicate reports because they need
> different font format. Is there a dymanic way to change font using one report
> only? Thanks.
>
> Chuck

Dynamic way to change font, font size, etc. in reports

Hi,
I have one report format for many clients. Some of them request different
fonts and font sizes. I don't want to duplicate reports because of differnt
fonts. Is there a dynamic way to change font or font size using only one
report format? Thanks.
ChuckChuck,
Custom code either embedded or as a custom assembly is the answer. This
article is a good starting point. http://odetocode.com/Articles/130.aspx.
Also, refer to the BOL for using custom assemblies.
You can use custom code to set font properties in your report.
"Chuck" wrote:
> Hi,
> I have one report format for many clients. Some of them request different
> fonts and font sizes. I don't want to duplicate reports because of differnt
> fonts. Is there a dynamic way to change font or font size using only one
> report format? Thanks.
>
> Chuck

Dynamic Tooltip for TextBoxes in Reports?

Hi,

I want to add dynamic tooltip for the textboxes on a report where the tooltip text comes from a database lookup or maybe a resource file?

How can this be done. This is a required feature for our client and we do not want to add "Constants" as tooltip text which can be done in min as the drawback would be if the value for the tooltip changes, it has to be changed in all the reports wherever it appears.

Thanks again.You can do this by creating a second data set and binding the tooltip to aggregate expressions, i.e. =First(Fields!Tooltip1.Value, "TipQuery"). Or you could write a cusom assembly and get them from a custom resource file (would require additional permission in the assembly on the server).

dynamic SQL stored procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored Procedure. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied." Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text" instead of "Stored Procedure" I got the correct SP answer, but because all the reports get their parameters from the application (user) I can not leave the report like that.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comYou should be able to do this:
exec x @.y and then map the query parameter to the report parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Alot of times this will happen when you do not have the parameter defined in
the dataset. RS takes your parameters but ends up not passing them to the
stored proc because you haven't explicitly told it to in the properites of
the dataset. Check it by going to the appropriate dataset. hit the elipsis
(...)-> parameters tab. You need to list the parameters that the proc takes
in the correct order that it takes them. The name value list would look as
such
NAME VALUE
@.x =Parameters!X.value
@.y = Parameters!Y.Value
See if that helps!
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Dynamic SQL Stored Procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored
Procedure. First, I had to handle the problem that in the Layout Tab
there were no fields by adding them manually. However, when I try to
show the report in the Preview tab I got the error: "Procedure x
Expects a parameter @.y that was not Supplied" Despite the fact that I
define the parameter in report parameter and in the Data Tab I got the
correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text"
instead of "Stored Procedure" I got the correct SP answer, but
because all the reports get their parameters from the application
(user) I can not leave the report like that.
Any Ideas?
Thank you.
AviI've had this problem a few times, and ended up solving it by simply
shutting down and restarting Visual Studio (after saving the report, of
course). Not sure if it will work for your problem, but its worth a
try if you haven't done that already.
AB wrote:
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab
> there were no fields by adding them manually. However, when I try to
> show the report in the Preview tab I got the error: "Procedure x
> Expects a parameter @.y that was not Supplied" Despite the fact that I
> define the parameter in report parameter and in the Data Tab I got the
> correct SP answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but
> because all the reports get their parameters from the application
> (user) I can not leave the report like that.
> Any Ideas?
> Thank you.
> Avi

Dynamic SQL Stored Precedure problem

Hi,

I have a project that all its reports are based on Dynamic SQL Stored Procedures. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied."Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.

When I wrote in the data setquery"execx @.y=1"and chose "Text" instead of"Stored Procedure"I got thecorrect SP answer, but because all the reports gettheir parameters from the application (user)I can not leave the report like that.

Try this: As a data set write static query that returns all the fields of your dynamic query and uses all the parameters. Then refresh report so it will update parameter list and field names. After that you can change static query to dynamic.

I did it like this:

declare @.sql varchar(max); set @.sql = '';

set @.sql = '.....'

exec (@.sql)

Maciej