Problem Report Binding With Stored Procedure.
Mar 12, 2008
Hi all,
I have some problem about bind field from stored procedure to report.
Now I have dataset that come form stored procedure. In Dataset Dialog, "Fields" tab, I add Fieldname like fieldname in stored procedure. And "Parameters" tab, I add parameters those are neccessary for my stored procedure.
I test on data tab view. When I click "!" to run, then there is input dialog parameter.
I filled it, then the result is ok.
But when I build project, I noticed warning message....
[rsMissingFieldInDataSet] The data set €˜MyDataSet€™ contains a definition for the Field €˜MyField€™. This field is missing from the returned result set from the data source. c:.....visual studio 2005projects
eport project1
eport project1Report7.rdl 0 0
[rsErrorReadingDataSetField] The data set €˜MyDataSet€™ contains a definition for the Field €˜MyField€™. The data extension returned an error during reading the field. There is no data for the field at position 6. c:.....visual studio 2005projects
eport project1
eport project1Report7.rdl 0 0
After I build my project, I run the report, each textbox that binded field, display empty value.
What happen with my report? And how can I solve this problem?
Thank you very much.
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
I have a strange problem with an OleDB call to a stored procedure thatreturns a rowset.Only the first time I execute the query, after I restart SqlServer, myprogram crashes becausethe rowset is empty. All next calls (after restarting my program, ofcourse) run successfully.The context is:1) if I do not bind the output rowset, my program doesn't crash2) If I run the call without the binding and then I run the call withthe binding, it doesn't crash3) It is not the first query I do in my session4) the call crashes only if in the SP there are some inserts: it is awell known problem with OleDB, but in manyother cases I have fixed it setting NOCOUNT to ON
View 2 Replies
View Related
Oct 11, 2004
Hi all;
How can I show the Name & Age of the selected student's ID in the appropriate TextBox(s) (txtName & txtAge)?
BTW:
My "ShowStudent" stored procedure:
ALTER PROCEDURE ShowStudent
@ID int
AS
SELECT ID, Name, Age FROM Student WHERE ID=@ID
RETURN
And this is the code for "Show" button:
' Which SP? Connection?
Dim cmdSelect As New SqlCommand("ShowStudent", con)
cmdSelect.CommandType = CommandType.StoredProcedure
'-----[ Spacifay SP parameters ]-----
'ID
cmdSelect.Parameters.Add("@ID", SqlDbType.Int, 4)
cmdSelect.Parameters("@ID").Value = CType(txtID.Text, Integer)
'Open Connection
con.Open()
'-----[ Execute the select command ]-----
cmdSelect.ExecuteReader()
'----------------------------------------
'Close connection
con.Close()
'========================================================
End Sub
So, do I have to use an OUTPUT parameter in the stored procedure? If Yes, How to get its (the parameter) value and bind it to the appropriate TextBox?
Hope my question is clear!!
Thanks in advanced!
View 6 Replies
View Related
May 7, 2008
Hi everyone
I am using Stored procedure :
ALTER PROCEDURE [dbo].[ReportChart]@Num int,@patID char(16) ASbegin if @Num=2 Begin select * from table1 where patientid=@patID End
else If @Num=1 Begin select * from table2 where patientid=@patID End end
While using the above stored procedure, when i bind it with Dataset. The fields corresponding to table1 are displayed in the Fields Tab of the Dataset whereas the Fields corresponding to table2 are not displayed.
Please help me out .
Thanks In Advance
Regards
Navdeep
View 20 Replies
View Related
Mar 6, 2007
Hi there everyone. I have a stored procedure called “PagingTable� that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:
strSQL = "EXECUTE PagingTable " & _
"@ItemsPerPage = 10, " & _
"@CurrentPage = " & CStr(intCurrentPage) & ", " & _
"@TableName = 'Products', " & _
"@UniqueColumn = 'ItemNumber', " & _
"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _
"@WhereClause = '" & strSQLWhere & "'"
The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.
'Open the recordset
rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table
intCurrentPage = rsItems.Fields("CurrentPage").Value
intTotalPages = rsItems.Fields("TotalPages").Value
intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset
Set rsItems = rsItems.NextRecordset
I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:
A) Bind the repeater control to the second resultset
B) Build a “pager� of some sort using the values from the first resultset
View 3 Replies
View Related
May 22, 2008
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
ASPX.CS file
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = null;
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
da.Fill(ds);
IFDdl.DataSource = ds;
IFDdl.DataTextField= ds.Tables[0].Columns[0].ToString();
IFDdl.DataValueField = ds.Tables[0].Columns[0].ToString();
IFDdl.DataBind();
}
protected void IncAddBtn_Click(object sender, EventArgs e)
{
SqlConnection con = null;
try
{
con = DataBaseConnection.GetConnection();
SqlCommand cmd = new SqlCommand("AddIncome", con);
cmd.CommandType = CommandType.StoredProcedure;
//SqlCommand cmd = new SqlCommand("",con);
//cmd.CommandText = "insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)";
cmd.Parameters.Add("PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
cmd.Parameters.Add("@PA_IFName", SqlDbType.VarChar, 10).Value = IFDdl.SelectedValue;
cmd.Parameters.Add("@PA_IDesc", SqlDbType.VarChar, 50).Value = IFDescTB.Text;
cmd.Parameters.Add("@PA_IDate", SqlDbType.DateTime).Value = Convert.ToDateTime(IFDateTB.Text);
cmd.Parameters.Add("@PA_IAmt", SqlDbType.Money).Value = Convert.ToDecimal(IFAmtTB.Text);
cmd.ExecuteNonQuery();
IFLabelMsg.Text = "Income Added Successfully!";
} // end of try
catch (Exception ex)
{
IFLabelMsg.Text = "Error : " + ex.Message;
}
finally
{
con.Close();
}
}
Stored Procedure
ALTER PROCEDURE dbo.AddIncome (@PA_UID int,@PA_IFName varchar(10),@PA_IDesc varchar(50),@PA_IDate datetime,@PA_IAmt money)
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/*SET NOCOUNT ON*/
insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)
ASPX File
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="AddIncome.aspx.cs" Inherits="AddIncome" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>
Add Income </h2>
<br />
<table>
<tr>
<td>
Select Income Field</td>
<td>
<asp:DropDownList ID="IFDdl" runat="server" Width="247px" >
</asp:DropDownList>
<a href="addincomefield.aspx">Add Income Field</a>
</td>
</tr>
<tr>
<td>
Enter Income Amount
</td>
<td>
<asp:TextBox ID="IFAmtTB" runat="server" Width="96px"></asp:TextBox>
Date <asp:TextBox ID="IFDateTB" runat="server" Width="93px"></asp:TextBox>(MM/DD/YY)</td>
</tr>
<tr>
<td>
Enter Income Description
</td>
<td>
<asp:TextBox ID="IFDescTB" runat="server" Width="239px"></asp:TextBox></td>
</tr>
</table>
<br />
<asp:Button ID="IncAddBtn" runat="server" Text="Add Income" OnClick="IncAddBtn_Click" /><br />
<br />
<asp:Label ID="IFLabelMsg" runat="server"></asp:Label>
</asp:Content>
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
View 3 Replies
View Related
Dec 14, 2007
Hi All,
I have written a Query and Stored Procedure in SQL 2000 which fetched around 15 Lac data. It takes around 17 seconds to fetch the data in the Query Analyzer. But when i am using the Query/Stored Procedure in the report and I have deployed it in the server. When i am accessing the report in the application it taking around 2-3 minutes to bind the data.
What might be the issue? My tables in the database has been normalized.
Please help me to sort out this problem.
Regards
View 15 Replies
View Related
Oct 24, 2007
Hello,
I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.
The report language is "English (United Kingdom)"
The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.
The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.
However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).
I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.
Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?
View 3 Replies
View Related
Nov 24, 2007
I want to be able to have an authorized person set or change the default values of a table column in a SQL Database. I have a stored procedure that sets the default which works fine: ALTER PROCEDURE [dbo].[addMyConst]ASBEGINALTER TABLE [dbo].[tbl1]ADD DEFAULT 70 FOR [Auto_ourlim]END(I still need to put parameters in so that I can run the stored procedure from a form, but for now....)
To change it I know that I have to drop the constraint first like this:
ALTER PROCEDURE [dbo].[dropmyValue]ASALTER TABLE [dbo].[tbl1] DROP CONSTRAINT [Auto_ourlim]
The problem is that when I execute the procedure I get the error that "Auto_ourlim" is not a constraint so it does not drop the Default Value. When I go over to SQL Server Management Studio Express I can see why:
If I open up the table and open up the Constraints the constraint is "DF__tbl1__Auto_ourli__5FB337D6". I could change the DROP CONSTRAINT to this, and that works, but it changes every time I add the new DEFAULT VALUE. I don't know how to get around it. Is there a way to put wildcards around "Auto_our" in DROP CONSTRAINT [Auto_ourlim}? Any suggestions would be welcome...even if there's a totally different way to do it.
What I'm trying to ultimately accomplish is this: Column1 (AutoLimits) would be user insert to the database and then in the database it would MINUS Column2 (Auto_ourlim - set with the default value) = Column3 (Difference - a computed field in the database)
Steve
View 2 Replies
View Related
Sep 23, 2004
Hi,
Is there a way I can display the data returned from more than one stored procedure in a report using reporting services. Or can I use more than one dataset in the report????
View 3 Replies
View Related
Apr 19, 2004
i have the stored procedure in the SQl and i m using the crystal report how to pass the paramaters to the stored procedure ..from the crystal report .:confused:
pls its urgent:mad:
View 1 Replies
View Related
Jul 16, 2007
Hi, there,
I have a report that requires to join a lot of tables.
May I ask if I can use a stored procedure to make all the joins and populate a temp table, then make the report sit on the temp table. so every time the user runs the report, it will kick off the stored procedure which will truncate the temp table and repopulate it, then show the report.
Thanks
View 4 Replies
View Related
Aug 22, 2007
Hi
I'm not sure if this is possible, i want to create a report from a stored procedure, all the stored procedure does is select data from my database, however, the code is 1800+ lines of code, it executes in about 1 minute, which is fine. My problem is that i can't paste 1800+ lines of code into the dataset in Reporting Services, so is it possible to get the values from my stored procedure into reporting services so that i can use them to design and execute my report?
Or musst i find an alternative way to do this?
Any help or suggestions will be greatly appreaciated and welcome.
Thanks in advance
Kind Regards
Carel Greaves
View 3 Replies
View Related
Nov 14, 2007
Using sql server reporting services, I want to create a report against a stored proc which has input parameters to the stored proc. Thanks!
View 5 Replies
View Related
Jul 11, 2007
I have the following stored procedure. I have a few questions regarding its use in Reporting Services.
1) Invalid Object ##Tmp_Table1, this prevents the report designer from displaying the available fields.
2) The @totalcount isn't available (I need this value for a calculated field in the report)
3) How would I go about creating dropdowns/calendar controls for users to fill in the parameter values?
ALTER PROCEDURE [dbo].[spTermCodeReport]
@tablename char(12) = 'results_NAF',
@source varchar(20) = 'AmExpProvList',
@sourcedate datetime = '05/30/2007',
@TotalCount int = 0 OUTPUT
AS
DECLARE @SQLString varchar(255)
BEGIN
SET NOCOUNT ON;
SELECT @SQLString = 'SELECT count(*) AS TRCount INTO ##Tmp_Table1 FROM ' + @tablename + ' WHERE source=''' + @source + ''' and sourcedate=''' + convert(varchar(10),@sourcedate,110) + ''''
EXEC(@SQLString)
SELECT @TotalCount = TRCount FROM ##Tmp_Table1
DROP TABLE ##Tmp_Table1
SELECT @SQLString = 'SELECT termcd, count(termcd) AS TermCDCount FROM ' + @tablename + ' WHERE source=''' + @source + ''' and sourcedate=''' + convert(varchar(10),@sourcedate,110) + ''' GROUP BY termcd'
EXEC(@SQLString)
END
View 2 Replies
View Related
May 8, 2007
Hi, I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report. That is NOT what everyone was asking. I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something likeUnique_Login_IPsreturns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in. Note: the following does not work in this context : EXEC Unique_Login_IPs ParameterValue nor does EXEC Some_proc_without_paramsVisual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPs ParameterValue" does not exist.... Thanks in advance,Dustin L
View 1 Replies
View Related
Aug 13, 2007
Hi Guys,
I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly
unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen
(or the stored procedure) so i need some help for SQL Gurus. Following are the details:
1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month)
2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day
3. There are around 500 employees data im dealing with
4. The user will select any given department and employee's data for the respective department has to be displayed for any given month
5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen
6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee
7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record
Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the
way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in
the attendance table, we are dealing with approximately half a million attendance records.
Thanks and waiting for your suggestions...
View 7 Replies
View Related
Jun 25, 2001
Can anyone tell me if this can be done?
I'm using CR8 against SQL Server 7 and am trying to use a stored procedure as my data source.
Basically my boss would like to move all the code that is now client-side(formula fields, parameters, suppressions, etc.) to the server-side.
I connect by Database>ODBC and then choose my sp here.
I get the error: "There are no fields in the file"
All my sp is doing is accepting two parameters: a report type and a user name, and then generating a report based on this data.
I can post the exact sp but it is a pretty long IF THEN ELSE block.
I checked the Seagate site and it said to convert the database driver to the native driver;
I guess this would mean to connect by: Database>More Data Sources>Microsoft SQL Server
But we need to connect by ODBC since we don't want the popup for the Login to Database, Server, etc., to be entered by the client.
Can someone tell me if there is a way around this to connect by ODBC using stored procedures.
Thanks in Advance.
View 1 Replies
View Related
Mar 15, 2004
I'm working on a stored procedure to populate a Crystal report. My company insists that we put the report parameters in the stored procedure instead of in Crystal...so that the SQL server (rather than the desktop)does the work of restricting the data. Is there anything I can do on the SQL side(possibly User Defined Data Type) to get Crystal to prompt me for a date WITHOUT the time? I started with this:
CREATE proc uspReportData @BeginDate datetime, @EndDate datetime
When Crstal prompts me for the parameters, I can type the date or use the calendar to pick a date, but I AM FORCED to enter a time. I know I could choose to ignore the time in the stored procedure, but the users don't want to see the time section of the parameter. Apparently SQL doesn't have a plain "date" parameter without a time.
I've also considered this:
CREATE proc uspReportData @BeginDate char(10), @EndDate char(10)
However if I do it this way, I can't seem to find a way to make sure a valid date is entered when Crystal prompts the user for the dates.
If using char(10) turns out to be the best method, is there a way I can pre-populate the Crystal prompts like this:
@BeginDate = 1st day of the current month
@EndDate = the current system date
Crystal seems to allow hard coded default values, but I can't find a way to do calculated default values.
I'm open to suggestions.
Thanks,
Mike
View 10 Replies
View Related
Aug 7, 2007
I've tried to find this documented on the Internet and found Crystal Reports User's Guid online documentation (582 pages of it!) It looks like it will be helpful for my other questions, but what I'm trying to do right now is call a stored procedure from my crystal report. So in Field Explorer under Database Fields my stored procedure is there with the three columns it selects. How do I put these three columns on my report? I try dragging them to Section 3 details and they're there but when I preview the report all I get are the headers, not the records. Is there something else I need to do? It requires an input parameter which I am not getting prompted for - maybe that is my problem, without an input parameter it can't successfully run the stored procedure, so how do I make it prompt me?
Thanks for helping me with my first Crystal Report!
View 2 Replies
View Related
Jun 29, 2007
Hi guys
i am developing a Performance Management System for my organisation and reports are quite complex. so i thought to create a CLR stored procedure and then use this in Crystal Report.
The problem I am facing is that the CLR stored procedures I create using Visual Studio 2005 are not visible from Crystal Reports XI come with Visual Studio 2005. I can create stored procedures using standard transact sql and these are visible, but the CLR stored procedures are not.
I Simply create a CLR store procedure in Visual Basic and debug it. This CLR procedure then appeares under stored procedure node in SQL Server 2005.
I've noticed when I browse the stored procedures in my SQL Management Studio that the image of the stored procedure has a padlock shown in the icon, as if they are locked?
Do I have to explicitly enable a security attribute on CLR stored procedures to make them visible?
any help would be highly appretiated.
best regards
Ali
View 2 Replies
View Related
Mar 14, 2007
Hi,
I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)
Could not find stored procedure 'stpBranch'.
But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102
Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.
I am not sure why I am getting this error and I greatly appreciate any help.
Thanks
View 2 Replies
View Related
Aug 19, 2007
Almost new to ssrs, accessing Oracle's stored procedures to feed the reports (no other choice), working on a master/detail report and badly stuck. This report needs to access 3 stored procedures one for master and two for small tables under it. The master's order ID is fed to the two stored procedures that may or may not pull any thing for certain order ID's
I am approaching it by creating 2 datasets and a page-long list, added desired text boxes and a table in the list, grouped the list on order ID and passing it to tables using default values etc. but keep getting the following message.
"The Group expression for the table €˜table1€™ refers to the field €˜ProductName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope".
The list seems to be associated to one dataset only and any item in it cannot refer to anyother dataset, even if I try to access the table's dataset in its properties (The 'current data set scope' implies that).
(Not sure what will happen when I will try to add the second table and dataset with the same parameter name).
How should I approach this issue. I will appreciate help, if explained in steps but won't mind a good tip.
Thanks.
View 8 Replies
View Related
Oct 21, 2015
Created a report with parameters that can have multiple values.
Ran Stored procedure with same above criteria and data is retrieved
Ran the report and no data is displayed
I am unable to figure out where it might have gone wrong.
View 3 Replies
View Related
Feb 13, 2008
Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
View 4 Replies
View Related
Jun 21, 2007
I have a stored procedure that works in my dataset editor, but when i try to run the report, only the "amount" field shows up. Everything else is blank. why is this happening. Here is the stored procedure.
USE [RC_STAT]
GO
/****** Object: StoredProcedure [dbo].[PROC_TE_MKT_DETAIL_EXPENSE] Script Date: 06/21/2007 09:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_TE_MKT_DETAIL_EXPENSE]
(@Territory varchar(10) = Null)
AS
BEGIN
IF @Territory IS Null
BEGIN
SELECT
[Item_Description]+' '+'('+[Item_No]+')' Entry_Description
,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime, 'SALESAMP') * -1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL * -1 ) Amount
,-1*[Item_Ledger_Invoiced_Qty] Quantity
,Customer_Name
,'' External_Doc_no
,[Item_Ledger_Sales_Responsible] SR_Code
,[Item_Ledger_Mars_Period_Code] ThePeriod
, Budget_Reporting_Group.Budget_Reporting_Group_Description
, Budget_Type.Budget_Type_Code, Budget_Type.Budget_Type_Description
, Budget_Reporting.Budget_Forecast_Period, Salesperson_Purchaser.SalesPerson_Purchaser_Code
, Salesperson_Purchaser.SalesPerson_Purchaser_Description
, CASE WHEN Budget_Reporting_Group.Budget_Reporting_Group_Id = 1 THEN Budget_Reporting_Amount ELSE - 1 * Budget_Reporting_Amount END AS Amount
, Salesperson_Purchaser.Territory_Code
,Territory.Name AS Territory_Name
,Region.Region AS Region_Name
, Budget_Reporting_Group.Budget_Reporting_Group_Id
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting AS Budget_Reporting
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group AS Budget_Reporting_Group
ON Budget_Reporting_Group.Budget_Reporting_Group_Id = Budget_Reporting.Budget_Reporting_Group_Id
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Type AS Budget_Type
ON Budget_Reporting.Budget_Type_Code = Budget_Type.Budget_Type_Code
INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser
ON Budget_Reporting.SalesPerson_Purchaser_Code = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]
ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory
ON Territory.Code = Salesperson_Purchaser.Territory_Code
LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region
ON Territory.Region_Key = Region.Region_Key
WHERE Budget_Reporting.Budget_Year = 2007
AND Budget_Type.Budget_Type_Code in ('T&E', 'MKT')
END
IF @Territory IS NOT Null
BEGIN
SELECT Budget_Reporting_Group.Budget_Reporting_Group_Description
, Budget_Type.Budget_Type_Code, Budget_Type.Budget_Type_Description
, Budget_Reporting.Budget_Forecast_Period, Salesperson_Purchaser.SalesPerson_Purchaser_Code
, Salesperson_Purchaser.SalesPerson_Purchaser_Description
, CASE WHEN Budget_Reporting_Group.Budget_Reporting_Group_Id = 1 THEN Budget_Reporting_Amount ELSE - 1 * Budget_Reporting_Amount END AS Amount
, Salesperson_Purchaser.Territory_Code
,Territory.Name AS Territory_Name
,Region.Region AS Region_Name
, Budget_Reporting_Group.Budget_Reporting_Group_Id
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting AS Budget_Reporting
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Group AS Budget_Reporting_Group
ON Budget_Reporting_Group.Budget_Reporting_Group_Id = Budget_Reporting.Budget_Reporting_Group_Id
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Type AS Budget_Type
ON Budget_Reporting.Budget_Type_Code = Budget_Type.Budget_Type_Code
INNER JOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser
ON Budget_Reporting.SalesPerson_Purchaser_Code = Salesperson_Purchaser.SalesPerson_Purchaser_Code
INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory
ON Territory.Code = Salesperson_Purchaser.Territory_Code
LEFT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region
ON Territory.Region_Key = Region.Region_Key
WHERE Territory.Code = @Territory
AND Budget_Reporting.Budget_Year = 2007
AND Budget_Type.Budget_Type_Code in ('T&E', 'MKT')
END
END
View 1 Replies
View Related
Jan 12, 2008
I have the following stored procedure:
Code Block
CREATE PROCEDURE udsp_td_queryowner
@state varchar(10) = NULL,
@businesstype varchar(20) = NULL,
@size int = NULL,
@sortorder varchar(20) = 'state'
AS
SELECT gl_t.state AS [State],
gl_t.business_type AS [Business Type],
gl_t.lowsize AS [Low Size],
gl_t.highsize AS [High Size],
e.InternetAddress AS [Owner]
FROM gl_territory gl_t JOIN employee e ON gl_t.employeenumber = e.EmployeeNumber
WHERE state = COALESCE(@state, state) AND
business_type = COALESCE(@businesstype, business_type) AND
COALESCE(@size, lowsize, highsize) between lowsize AND highsize
ORDER BY CASE WHEN @sortorder = 'state' THEN gl_t.state
WHEN @sortorder = 'business type' THEN gl_t.business_type
WHEN @sortorder = 'owner' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END ASC,
CASE WHEN @sortorder = 'low size' THEN gl_t.lowsize
WHEN @sortorder = 'high size' THEN gl_t.highsize
END ASC,
CASE WHEN @sortorder = 'statedesc' THEN gl_t.state
WHEN @sortorder = 'business typedesc' THEN gl_t.business_type
WHEN @sortorder = 'ownerdesc' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName)
END DESC,
CASE WHEN @sortorder = 'low sizedesc' THEN gl_t.lowsize
WHEN @sortorder = 'high sizedesc' THEN gl_t.highsize
END DESC
What it allows me to do is enter in any number of the variables when running the stored procedure. For example, EXECUTE udsp_td_queryowner @state = 'IA' would give me everything in the table in the state of IA regardless of the other field values. Likewise, if I ran EXECUTE udsp_td_queryowner @state = 'KY', @size = 15 it would return the records that are in KY and the size of 15 is in the range of the low and high value.
If I run the first example in Query Analyzer I get 53 records from the data I have. It returns every record that has IA as the state. I run the same thing in Reporting Services and all I get is 3 records. Just the 3 records for the state of IA where the business type is either null or blank (I can't tell.) If I allow all of the variables to accept Nulls then it returns the correct data. However, I would like to find a better alternative because when the report is run it returns all of the records in the table initially and if they user wants to enter in any parameters they have to toggle off the null box for the corresponding paramter.
Any ideas?
View 12 Replies
View Related
Aug 23, 2007
I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.
Please help--Iam stumped.
There are no infinite loops in the report. I checked.
View 9 Replies
View Related
Feb 3, 2006
Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks
View 7 Replies
View Related
Oct 23, 2015
I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.
View 3 Replies
View Related
Dec 19, 2007
Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:
CREATE PROCEDURE spprodsales
@productid int
AS
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
RETURN
And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
So, can anyone please help me out possibly using the same stored procedure as above.
Thanks,
Kripa
View 5 Replies
View Related
Feb 3, 2007
Hi,
I have created an report by selecting stored procedure as dataset data source.
Now when I run the report, it is asking for the parameter values for my stored procedure, but I want to pass the values from another .aspx page.
Could any one please let me how do I pass values to the stored procedure parameters in the report. I tried doing the Local method of reports, but the printer icon is not appearing in that, if I use Reportviewer control of my own. When I use the Remote method of reports, I am facing this problem.
Please could any one help me in this.
Regards,
Sudhakara.T.P.
View 3 Replies
View Related
Mar 23, 2008
Details :
Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message.
"The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM.
If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered
select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab.
Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
Thanks.
View 3 Replies
View Related
