Using Stored Procedure In Report Designer

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


ADVERTISEMENT

Modifying A Report Created In Report Builder In The Report Designer.

Jun 30, 2006

After I use the report builder to create a generic report, how do I actually get that report into the report designer so that I can modify it more effectivly?



The issue that I have now is that the file on the report server is not a .rdl file and if I simply save it as one and then bring it into VS to modify it the code file is a html structure rater than a XML file type.



Any suggestions would be appreciated. Thanks

View 3 Replies View Related

Custom Assembly Behaves Differently In The Report Designer And Report Manager

Mar 18, 2008

Hello everyone,

I created a custom assembly using C# to transform some binary data into text, and in this assembly I used one win32 dll developed by our customer to help me to do the tranformation.
The code I used to call the win32 dll is like below:
[DllImport("tdasuie.dll", EntryPoint = "AlrtLogConditionToText",
ExactSpelling = false, CharSet = CharSet.Auto, SetLastError = true)]
private static extern UInt32 AlrtLogConditionToText(Byte[] pbCondition, StringBuilder pszText, UInt32 dwSize);

I defined a C# method to call the above win32 method and return a string. Then in the report, I called this C# method to get the correct string.

In the report designer, the C# method in the custom assembly can return the correct string in the preview window. But after I deployed the report into the report server, the textbox will only display "#error" in the report manager web page.

Can anybody help me on it? Thanks a lot.

Danny

View 4 Replies View Related

'Dynamic Time(year,month,date)' Report Template (rdl) Using Report Designer VS2005

Jan 19, 2007

I have three types of specific reports that i have to create with the input parameters (range) either

1: By date (rdl 1)

2.By Month (rdl 2)

3.By Year (rdl 3)

Is it possible ( or how do I ) to create just one report template ( one rdl) with the three sets of parameters ( hiding/invisible which ever two sets base on user selection) and the output of the report will display the desired type( either by year, month or date).

I ask this because its possible to create a drill down report from year down to date etc in report designer (vs 2005). Not sure if I can create one instead of three rdls and with the 'logic' built within that template.

Thanks

Regards

Alu

View 4 Replies View Related

How To Limit Number Of Rows Displayed In A Report From A Report Designer

Jun 5, 2007

How to limit number of rows displayed in a report, i' am using report project in Visual studio 2005 to design my reports

How to use page navigation once i deploy the report on to report server..?

View 1 Replies View Related

Multi Value In Report Parameter Using MSVS Report Designer.

Aug 14, 2006

Hi All,

I have a question, How to user the Multi value in Visual Studio Report Designer.

My dataset contains the following

Select empno,empname,basicpay,deptcd from employee where deptcd in (substring(@gdept,1,4))

Here variable gdept is the report parameter. I am passing the value.
it works fine with single value.

( the source of gdept is a another dataset to list the deparment code+name,

Example

DEP1 - COMPUTER

DEP2 - ADMIN

DEP3 - FINANCE

)

when I preview, i have a combo box to select the department.

If I choose one deparment, it works fine.

If I choose more than one department or select all I have error message

"substring function requires 3 aruguments."

what is wrong is the query

Please advise.

 

Cheers,

Saleem

 

 

View 1 Replies View Related

Report Designer Report That Queries A Cube Model

Jun 15, 2007

In Report Designer I am able to create a Model data source and see my entities/fields.

But when I run the report I get a "Failed to execute Query" message.

I know in the past this was an issue because Designer doesn't allow you to specify a perspective.

Does anyone know if this has been resolved?

I'm using SP2



Thank you,



Joe

View 1 Replies View Related

Report Designer &&> PRINT ONLY A SECTION OF THE REPORT

Oct 18, 2007

Hi,
I dont know if theres a way to print a section of some report, for example a summary report, where I have several charts and tables, and I want to print just the first 2 charts
Could this be done by mapping the document or separating it in some other way?

I tried using 'SubReports' control, but this way I need to have for example the first two charts in a different report (lets say 2charts.rdl), and then in the summary.rdl, add the control and set the link to 2charts.rdl,.... but I think this is pretty annoying considering the future modifications of the format in the 2charts.rdl...

Thanks for your help...

Regards

-Edith Colegio

View 1 Replies View Related

Report Designer Training

Feb 21, 2008

Are there any training seminars/classes anyone would recommend attending? I am looking to further develop my Visual Studio Report Designer Skills. Leaning more to the intermediate/advanced side.


View 4 Replies View Related

Report Does Not Display In Designer

Mar 28, 2007

Hello,



I am developing some reports for an ASP.NET solution, but my IDE does not display the design view of the reports. Instead, it only displays the XML definition even when I click View Designer. Yesterday the reports showed up correctly, and I know that the design view is there. Is there a setting that has been changed somewhere?

View 2 Replies View Related

Report Designer Not In VS 2003

Feb 7, 2007

I have Reporting services 2000 (Version 8.00.1038.00) installed (installed with installation of MS CRM) and visual studio 2003 installed however, do not have the business intelligence report designer available. Where can I obtain the report designer addin? We don't have a RD 2000 CD. Is what I need available via download?

Thanks

View 3 Replies View Related

Using A Like Clause In Report Designer

Apr 14, 2007

Well, I noticed several other threads on this topic, but the solutions accepted as answers did not resolve my issue.



I'm trying to pass a like clause via a parameter in a report. I'm running the report against an Oracle 10 db using the microsoft for oracle oledb driver. Here's my sql I'm using in the report designer:



select * from dbprod.database_names_vw
where db_name LIKE ('%' + :1 + '%').



When I run this, I get the dreaded: ORA-01722 error



I referred to this page: http://msdn2.microsoft.com/en-us/library/aa337223.aspx

which covers: Using Query Parameters with Specific Data Sources



I tried all the variations from that link...I tried the "@" sign. I tried the ?. I tried double quotes...I've tried everything...



I was under the impression I had to use unnamed parameters, as above....



Frustrated in Cali.



View 1 Replies View Related

Cannot Shut Down VS After Using Report Designer

Jan 27, 2006

Routinely when I use BIDS to work on a report design, I cannot close BIDS because I get a message box that says:


Microsoft Visual Studio cannot shut down because a modal dialog is active. Close the active dialog and try again.
Of course, there is no visible dialog open. I actually have to terminate the process from task manager.
I do not get this problem when using SSIS or SSAS. Only SSRS.
Any Ideas?
Thanks!
BobP

View 76 Replies View Related

Problem On Report Designer

Mar 12, 2007

Hi all.

I have the problem on showing the records in my report.
I explain how i designed the report. I made the report in Business Intelligence Project. The report I am achieving is the Personal Information of all employees.
After creating a query/dataset, I dragged the fields to the textboxes. I am trying to show 1 employees' record in each page.
My report looks like this...

EMPLOYEES PERSONAL INFORMATION

Employee Name : _______________ <There is a photo Image here>
Age : _________________________
Address: ______________________
Telephone #:___________________
Province:______________________
BirthPlace:____________________
Birthdate:_____________________

Gender:_______________________WEIGHT:____
Civil Status:_________________Height:____
Relgion:______________________Hair Color:____
Nationality:__________________Eye Color:_____


IN CASE OF EMERGENCY PLEASE CONTACT
Name:__________
Address:_______
telephone:_____

EDUCATIONAL BACKGROUND
EDUCATIONAL LEVEL.......SCHOOL.....FROM-TO......DEGREE
Elementary..............Harvard....1990-1992
High School.............LaSalle....1992-1995
College.................UP.........1995-2000

_______________________________________________
Run Time: 2/3/2007
Page No. : 1


I used dot above just for spacing and alignment but not included in my actual report.

I used table for EDUCATIONAL BACKGROUD.

My problem is: when i ran the report, it can't automatically creates report for all employees. It only shows the first employee in the database.

Any suggestions and guidance please.

Thanks.
-Ron-

View 9 Replies View Related

Report Designer - Not A WYSIWYG?

Oct 29, 2007

What you see on the designer is not what you get? How frustrasing can this be when you develop a report? Why did Microsoft made such a deviation from actually designing the windows UI (where you always get what you want and it is a true wysiwyg)

We are doing some data entry apps and would like to simulate a paper based report at the end of the data entry. I designed a portion of it and preview showed what I want. Published the report and found out that the layout is messed up in the IE. I went trough the pain of correcting the layout and made it work for IE. Now when I use the preview the layout is messed up. How can I desgin a layout that works on both worlds. (client side and server side??)

We would like to make the report avaliable in both the windows application(client side) as well as through the report manager (server side)

View 1 Replies View Related

Results From More Than Stored Procedure In A Report

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

Stored Procedure And Crysral Report

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

Can Report Service Run A Stored Procedure?

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

Creating A Report From A Stored Procedure

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

How To Create A Report Against A Stored Procedure

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

Report Designer Client Software

Feb 18, 2004

I've loaded the Report Services from Microsoft unto our server. How do I get the Client Designer tool loaded on other boxes. Does anyone know where the Install is to install the report designer into a Client machine?

Thanks
Al

View 1 Replies View Related

How Do I Get Visual Studio Report Designer?

May 25, 2008

...and Report Builder?

I have SQL Server 2005 Express and Reporting Services and Report Manager up and running...

Thanks

View 5 Replies View Related

Microsoft SQL Server Report Designer

Aug 23, 2007



Hi, I have installed SQL Server 2000 and Reporting Services 2005 and Visual Studio 2003 all works OK but I need to create a report with the Microsoft SQL Server Report Designer 2005 and it doesn't appear in my VS, someone could help me with this?

Marcos.

View 8 Replies View Related

Report Designer ActiveX Control?

Feb 7, 2008



Hi,

Is there an ActiveX control for the Report Designer available so I can just drop it into my program so the user can edit or add reports without buying the Business Intelligence Studio or Visual Studio 2005? I understand I'll have to pay royalties on this but depending on the cost I am open to it.

Thanks

View 1 Replies View Related

Dynamic Grouping With Report Designer

Feb 26, 2007

I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.

I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?

Thx

Helen

View 3 Replies View Related

Report Designer Dynamic Filter

Feb 28, 2007

I have a requirement to add the ability to filter a report by partial last name, at the report level (i.e. not at the database server). In case someone else needs to work out how to do this, here is what I did.

Add a Parameter to the report. In my case it is named LastName, is non-queried, allows Null or Blank, and has a default of null.
Add a filter to the dataset that contains the field to be searched. Set its Expression to:
=IIF(Parameters!LastName.Value = System.DBNull.Value OR Parameters!LastName.Value = "",
Fields!LastName.Value, Nothing) ,
its operator to "="
and its value to =IIF(Parameters!LastName.Value = Nothing OR Parameters!LastName.Value = "", Nothing, Parameters!LastName.Value & "*")

voila!

Helen

View 2 Replies View Related

Issue Running MDX In Report Designer

May 1, 2008



Below I have query I run in Management Studio. The sample results are below. When I put the same query in SSRS Report Designer it strips the "All" result out and replaces it with a Null. Is there a reason for this? Is there a way to force a string result instead of a Null.

Any assistance would be appreciated.


SELECT NON EMPTY { [Measures].[FACT NETWORK TT 2 Count] } ON COLUMNS, NON EMPTY

([FACT NETWORK TT 2].[Year].[Year].ALLMEMBERS

* [FACT NETWORK TT 2].[Quarter].[Quarter].ALLMEMBERS

* [FACT NETWORK TT 2].[Month].[Month].ALLMEMBERS * [DIM NETWORK TT 2].[EVENT DETECTOR].Allmembers)

ON ROWS FROM [Network TT2]


Sample results:
2006 2006-Q1 2006-02 All 1
2006 2006-Q1 2006-02 SMARTSNET 1
2006 2006-Q1 2006-03 All 613
2006 2006-Q1 2006-03 217
2006 2006-Q1 2006-03 Netview 25
2006 2006-Q1 2006-03 SMARTSNET 371
2006 2006-Q2 2006-04 All 62
2006 2006-Q2 2006-04 17
2006 2006-Q2 2006-04 SMARTSNET 45
2006 2006-Q2 2006-05 All 2
2006 2006-Q2 2006-05 SMARTSNET 2


View 3 Replies View Related

How To Get LIST Of Fields In A Report (designer)

Jun 25, 2007

Hi All,



When I'm building a report in report designer and get error message that says --error like --field textbox25--whats the easiest way to see all my report fields and quickly access them instead of clicking every field in a report?



Ideally a fields map with links to these fields.



thanks



Sonny

View 1 Replies View Related

Aggregation Problem In Report Designer

Feb 14, 2007

Hi!!! Please help me.

I have the following table structure.

-A (name)

--B (name)

--C (name, total)

For example.

A { Tom, Sam John }

B {Mazda, Audi, Ford }

C: { (Monitor, 100), (Telephone, 230), (Mouse, 370)}

The corresponding sql select:

select * from A left join B left join C

Retrive obvios result:

And now I have desing report with the following structure:

1) Create list element (A_List) and use detail group to grouping data by A.name in it.

2) Then I use 2 another lists and placed it in A.

Now I want get the textbox = Sum(C.total) in the A_List area. It is obvious that ealier represented sql select make cartesian product (AxBxC) of A,B,C tables. And now I have multiple record for single row for each row in each table. For example I have three equals records for totals.

And I could't use aggregation function in Sum. Anobody know how this problem solves???



View 1 Replies View Related

Passing A Parameter Into A Stored Procedure In A Report ..........

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

Writing A Monthly Report With Stored Procedure

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

Report Based On Stored Procedure - ODBC

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

SQL Stored Procedure To Populate Crystal Report

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







Copyrights 2005-15 www.BigResource.com, All rights reserved