Get back data from Reports

I have just started working with RS 2005 and was wondering if / how I can do the following.

I created an asp.net website that has a report viewer to show my reports on the web.
In addition there is a page that lists all of the reports a user has access to, which is pretty much a series of links:

Report 1
Report 2
Report 3
.....

Now in my reports I generally create a grand total of one of the column and was wondering if there was some way to get that information back into an asp page, ie:

Report 1 - $125,713
Report 2 - ($23,111)
Report 3 - $1,762,142

I was thinking if there was some way to send a parameter to the report like Summary=True which would tell it to just return a XML bit of data holding the total for that report (Anything will work, doesn't have to be XML). So the page with the report links would call each of the reports.

I realize that it would probably be possible to do this through a stored procedure, but that would just create another point of maintenace. Instead I could create some function in all of my reports and have a standardized way of doing this so my webserver or database wouldnt have to be updated all the time.

Thanks, and let me know if there is any other information I can provide to make this clearer.

Try using the XML renderer. You will get a "report" which is really just an XML stream with the data. You can then XPath into it to get the values you are interested in.|||

Sounds promising. Being rather new to reports, are there any pages you can think of off hand that provide examples or explanations?

Get a List of Output Columns on Script Transformation

I am using a script component to transform data. In the script component I created a bunch of fields for the output. Is there any way to loop through that list of columns? Is there code I can use in the script component to access the names, data types, data etc?

I saw a lot of informaiton on the OutputColumnCollection as part of some IDTSOuput90 thing (greek to me). As best I can guess this is for creating your own new columns, but can I see what columns are already defined via the script interface?

I was able to get a specific column by doing this...

Dim o As IDTSOutput90

o = ComponentMetaData.OutputCollection.GetObjectByID(88)

MsgBox("Column Count: " & o.OutputColumnCollection.Count)

MsgBox("Colum: " & o.OutputColumnCollection.FindObjectByID(139).Name)

But Is there a way to just loop through all the ids? I have to know the id to stick in findobjectbyid(x). Can i do a loop and have x = all the ids? Additionally the 88 is hard code value... is there a way to get the current or specific output set?

The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postion.

|||

Ok... sorry about the multiple posts here... but I got some more logic to work...

I was able to get able to get columns by doing this...

Dim o As IDTSOutput90

o = ComponentMetaData.OutputCollection.GetObjectByID(88)

For Each col As IDTSOutputColumn90 In o.OutputColumnCollection

MsgBox("Name: " & col.Name)

Next

So the updated question is this...

The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postions value. I can't find how to set the column value equal to something while i loop through it. Additionally... is there a reference or good way to get the data type? When i use the col.datatype is returns a number vs description. I want to have logic when it set the value to do coverts based on the data type.

Get "Invalid cursor state" when modifying a table in SQL 2000

When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state.

SP3 has been applied to SQL Server 2000.

Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.

If you reuse an hstmt after doing a query that opens a cursor, you need to call SQLCloseCursor(hstmt) before you make your next call.

Hope that helps,

John

|||I guess that you message is from any GUI to manage SQL Server, right ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Generating SQL Database from Visio

I created a data model in Visio. When I try to generate a database in SQL
from the datamodel, the table and column descriptions do not tranfer.
I am using visio for Enterprise Architects (10.0.2705)Hi docsql,
Thank you for using the MSDN Managed Newsgroups.
This appears to be related to visio. This inquiry would best be posted to
the Microsoft.public.visio. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other customers who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us. Thank you for your
understanding.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Generating SQL Database from Visio

I created a data model in Visio. When I try to generate a database in SQL
from the datamodel, the table and column descriptions do not tranfer.
I am using Visio for Enterprise Architects (10.0.2705)
Hi docsql,
Thank you for using the MSDN Managed Newsgroups.
This appears to be related to visio. This inquiry would best be posted to
the Microsoft.public.visio. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other customers who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us. Thank you for your
understanding.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Generating SQL Database from Visio

I created a data model in Visio. When I try to generate a database in SQL
from the datamodel, the table and column descriptions do not tranfer.
I am using Visio for Enterprise Architects (10.0.2705)Hi docsql,
Thank you for using the MSDN Managed Newsgroups.
This appears to be related to visio. This inquiry would best be posted to
the Microsoft.public.visio. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other customers who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us. Thank you for your
understanding.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Generating Reports From LoadTest Database

Dear All,

I have configured reproting services in Sql Server 2005.

I have also created the LoadTest Database.

I have seen Sean Lumley's Blog sample report also visited the link provided by him 'http://blogs.msdn.com/slumley/archive/2006/02/16/533356.aspx' however i am not able to figure out what to do next with this link ,how togenerate report in the format as he has suggested from my load test store.

Regards,

Follow the link to gotdotnet, the report are downloadable from there and can be easily customized to your needs.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Generating and Printing Reports

I've created a data flow where I have linked to an OLE DB Source then created a Flat File Destination. My file is now on my c: drive. I'd like to use that data to create a report, then print to a PDF. How would I do that?

Currently I was doing this in Access, but I am moving my processes to SSIS.

You wouldn't use SSIS to do that. SSIS is an ETL tool, not a reporting tool.

-Jamie

|||But if I created my report in Reporting Services for instance, how could I put that in my data flow in SSIS?|||I was also assuming that I could put this in a Script Task, but I didn't know how far I could go with generating a report from there.|||

I don't know what you mean by "put a report in the data flow" or "put it in a script task". Can you expand on that?

There is a way that you can consume a SSIS dataflow from within SSRS but that doesn't seem to be what you're after here. You seem to want to build a report on top of a text file (that just happens to have been created by SSIS). I'm sorry, I don't understand what it is you actually want SSIS to do.

-Jamie

|||

I've gotten my answer:

Write an application in VB using the Report Viewer from the toolbar to create and print the report

Then execute that application from SSIS.

|||

So you're calling the SSRS API, is that correct? If so, you don't have to write another app to do that - you can embed it into SSIS.

-Jamie

|||What do you mean by calling the SSRS API? I'm using Visual Studio 2005.|||

ifaber wrote:

What do you mean by calling the SSRS API? I'm using Visual Studio 2005.

Shall we start again. What exactly is your VB app going to do?

|||My application is going to pull data from many tables, then export to a flat file. Our network person will pick up the files, and put the data on a website. Then from 2 of those tables, I pulled a list of instructors that currently is on our website in PDF reports. In Access, I have pulled the data, created a report and exported to a PDF. Our network person pulls those reports and puts them on the website. It's pretty simple compared to some other stuff I have done.|||

Fair enough, if it works for you that's good. I was just wondering if there was an option to utilise SQL Server Reporting Services (SSRS) seeing as that is a reporting application. I mistakenly assumed that seeing as you were using SSIS that you would be trying to use SSRS for reporting - my mistake. Sorry.

I also thought you were trying to automate the creation of PDF files - something that I suspect could be done from SSRS and ergo from SSIS if there is an API available for doing that. Again it looks like my assumption was wrong.

To me, it seems strange that you would use a differrent application to do all this when it can all be achieved with SSRS but then again I am a SQL Server zealot so I would say that :)

Good luck with it.

-Jamie

|||Wellllll, I know how to use SQL Server Reporting Services too. In fact, I have a lot of reports using SSRS. Pretty much, after I have updated the flat file, I'd like to run something that would create a report and would print to PDF.|||

Right. Well what I was driving at is if SSRS can create PDF reports (and I'm reliably informed that it can) then you can call the SSRS API from SSIS in order to create those reports for you. I haven't actually done it but I'm sure its possible.

-Jamie

|||Bingo!! Now how do I call the API :)|||

ifaber wrote:

Bingo!! Now how do I call the API :)

I was worried you were about to ask that.

I don't actually know, but I've no doubt that you can do it. To know more about the SSRS API you;d be best checking out the SSRS forum.

What I DO know is that SSRS itself is a web service which means that everything that can be done in SSRS can be called via that web service. essentially that web service IS the SSRS API.

You can calls web services from SSIS using the Web Service task. HOWEVER, I am aware that there are limitations with SSIS's web service task (search this forum to find out more) but don't despair because you have another optionavailable to you. You can call web services from within the SSIS script task.using dotnet APIs.

I hope that is useful and I hope you didn't mind me continuing the thread. Its just that you seemed to want to do all from within one package and I figured it would be possible. Anything that can be accessed via dotnet code can be done from SSIS's Script Task..

-Jamie

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong'
--
Ranbdy KrofickI don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:

>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter
"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong'

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong'
--
Ranbdy KrofickI don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:
>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong'

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong?
Ranbdy Krofick
I don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:

>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong?

Generate AutoNumber

Hi, I have a question, I have created a table and with a primary key called "ID". However, I want the "ID" be auto increment as well. when inserting new record into the database.I'm using vb.net. how can I do in the following format: "1", "2", "3", ......... etc. I've the code below but it's not working in the right way, what's wrong with my code? PrivateSub BtnAdd_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnAdd.Click Dim ssqlAsString Dim ItemidAsInteger Dim updcmdAs SqlClient.SqlCommand Itemid = 0 mysqladap =New SqlClient.SqlDataAdapter("select MAX(Item_id) From auction where item_type= '" & (Image1.ImageUrl) & "'", mySqlConn) Itemid = (Itemid) + 1 ssql = "insert into auction (item_id,owner_name,owner_mail,owner_mobile,owner_phone,owner_ext,item_type,item_name,item_image,item_desc,item_cost,start_date) values ('" & (Itemid) & "','" & Trim(ownertxt.Text) & "' ,'" & Trim(emailtxt.Text) & "', '" & Trim(mobiletxt.Text) & "', '" & Trim(phonetxt.Text) & "','" & Trim(exttxt.Text) & "','" & Trim(DropDownList1.SelectedValue) & "','" & Trim(itemtxt.Text) & "','" & Trim(Image1.ImageUrl) & "','" & Trim(desctxt.Text) & "','" & Trim(costtxt.Text) & "','" & Trim(Today.Date) & "')" updcmd =New SqlClient.SqlCommand(ssql, mySqlConn) updcmd.ExecuteNonQuery() lblmsg.Visible =True EndSub Anyone can help me? Thanks.

As I replied elsewhere where you asked the exact same question, use an IDENTITY column. Then you will not need to insert the value for the column, it will populate itself.|||

First of all thanks for your reply,
Can you please explain more?
Can you write the statement for me or the modification on my given code?
Thank you

|||You have to set the Identity column in the datatable table properties. You dont do that via code. In Sql Enterprise manager, when you open the table in design time, you can set the Identity property of that column to "yes" at the bottom of the screen.|||Thank you sooooooo much, and after that, delete the code I've written? and don't inset that column in the insert statement of sql?|||What's meant by identity seed? it's set to 1, shall I change it?|||

I've inserted 6 records and they are given the numbers 1,2,3,4,5, and 6, then I deleted them all and add new record, it has given the number 7, WHY?
why it isn't set to 1 again?

|||the numbers will not reset unless you recreate the table, or truncate table.

Generate A Report Via Login

To all you experts out there, I have one hurdle I need help on.
I've created a SQL report which has 3 parameters asking for a from_date, a
to_date & an employee. The report then returns all projects, tasks,
yadda-yadda, for the employee, within the date range from a time tracking
database. Very usefull come performance review time.
My intention is to make this report available on Sharepoint via a link (or
Report Manager), where whoever logs into sharepoint (or Report Manager) can
access this report for themselves and no one else, meaning they click on the
report and get prompted for the date range parameters but not the employee
parameter. The report should recongize who is accessing the report by virtue
of their login & find the corresponding employee information in the database
& display the report for that employee.
Should this functionality be implemented in the SQL report or should it be
implemented within Sharepoint? Based on that question, how can this
functionality be implemented?
Thank you very much for the help.I assume you want to have the manager get all of his employees. You would
need a table somewhere with this relationship. Then use the global variable
User!UserID. This is the user running the report. Your query parameter does
not have to be mapped to a report parameter. Click on the ... for the
dataset, parameters tab, right side of list select expression which takes
you to the expression builder. Note that User!UserID returns the user with
the domain i.e. domain\userid
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> To all you experts out there, I have one hurdle I need help on.
> I've created a SQL report which has 3 parameters asking for a from_date, a
> to_date & an employee. The report then returns all projects, tasks,
> yadda-yadda, for the employee, within the date range from a time tracking
> database. Very usefull come performance review time.
> My intention is to make this report available on Sharepoint via a link (or
> Report Manager), where whoever logs into sharepoint (or Report Manager)
> can
> access this report for themselves and no one else, meaning they click on
> the
> report and get prompted for the date range parameters but not the employee
> parameter. The report should recongize who is accessing the report by
> virtue
> of their login & find the corresponding employee information in the
> database
> & display the report for that employee.
> Should this functionality be implemented in the SQL report or should it be
> implemented within Sharepoint? Based on that question, how can this
> functionality be implemented?
> Thank you very much for the help.|||A manager can get to his employees (individually) as the report exists (with
a report parameter for the emp_name).
I completely forgot about the userid. Good news is I can match up the userid
to the substring of a field already being returned (emp_name), but I don't
quite follow how to set it up as a query parameter. Do I create a new dataset
or create this query parameter on the dataset for my existing result set
details(where emp_name exists)? Can you give me a hint on what the syntax
would look like?
Thanks
"Bruce L-C [MVP]" wrote:
> I assume you want to have the manager get all of his employees. You would
> need a table somewhere with this relationship. Then use the global variable
> User!UserID. This is the user running the report. Your query parameter does
> not have to be mapped to a report parameter. Click on the ... for the
> dataset, parameters tab, right side of list select expression which takes
> you to the expression builder. Note that User!UserID returns the user with
> the domain i.e. domain\userid
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> > To all you experts out there, I have one hurdle I need help on.
> >
> > I've created a SQL report which has 3 parameters asking for a from_date, a
> > to_date & an employee. The report then returns all projects, tasks,
> > yadda-yadda, for the employee, within the date range from a time tracking
> > database. Very usefull come performance review time.
> >
> > My intention is to make this report available on Sharepoint via a link (or
> > Report Manager), where whoever logs into sharepoint (or Report Manager)
> > can
> > access this report for themselves and no one else, meaning they click on
> > the
> > report and get prompted for the date range parameters but not the employee
> > parameter. The report should recongize who is accessing the report by
> > virtue
> > of their login & find the corresponding employee information in the
> > database
> > & display the report for that employee.
> >
> > Should this functionality be implemented in the SQL report or should it be
> > implemented within Sharepoint? Based on that question, how can this
> > functionality be implemented?
> >
> > Thank you very much for the help.
>
>|||Thanks Bruce...Got It. I created the UserID parameter as @.EmployeeName &
refered to that parameter in the data set as;
WHERE pe_date between @.FromDate AND @.ToDate
AND ('DOMAIN\' + Substring(descr,3,25)) = @.EmployeeName
OR ('DOMAIN\' + Substring(descr,1,1) + Substring(descr,3,25)) = @.EmployeeName
and it works just great. Thanks for shaking the cobwebs loose.
JD
"Bruce L-C [MVP]" wrote:
> I assume you want to have the manager get all of his employees. You would
> need a table somewhere with this relationship. Then use the global variable
> User!UserID. This is the user running the report. Your query parameter does
> not have to be mapped to a report parameter. Click on the ... for the
> dataset, parameters tab, right side of list select expression which takes
> you to the expression builder. Note that User!UserID returns the user with
> the domain i.e. domain\userid
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:A0AC7950-2EB1-4DE8-A466-B6A34CE5ACB6@.microsoft.com...
> > To all you experts out there, I have one hurdle I need help on.
> >
> > I've created a SQL report which has 3 parameters asking for a from_date, a
> > to_date & an employee. The report then returns all projects, tasks,
> > yadda-yadda, for the employee, within the date range from a time tracking
> > database. Very usefull come performance review time.
> >
> > My intention is to make this report available on Sharepoint via a link (or
> > Report Manager), where whoever logs into sharepoint (or Report Manager)
> > can
> > access this report for themselves and no one else, meaning they click on
> > the
> > report and get prompted for the date range parameters but not the employee
> > parameter. The report should recongize who is accessing the report by
> > virtue
> > of their login & find the corresponding employee information in the
> > database
> > & display the report for that employee.
> >
> > Should this functionality be implemented in the SQL report or should it be
> > implemented within Sharepoint? Based on that question, how can this
> > functionality be implemented?
> >
> > Thank you very much for the help.
>
>