Hi All,
I hope someone can help me. I am wondering if there is anyway to use the
WebService to Render Reports without the users password. I do nothave it and
since we use TN Authenticationwe should nto need it. the issue is if I use
the Default Crddentials on the Web Service it trys to run the reports as the
asp.net worker process not the authenticated user.
Is there a nyway to fix this? I tried setting the credentials but I do not
have thre password and I hate to ask the user in some sort of pop up.
All ideas would be great.
Thanks,
Sal
--
SalSal,
Wondering if you have figured this out. I too would like to use the ASP.net
worker proccess or use the [MachineName]\ASPNET or
[MachineName]\IUSR_MachnineName or NT Authority\NETWork Service to authticate
for viewing a report.
We have LDAP groups managing our security and I can deny/grant access to an
ASPX page via Allow/Deny in the Webconfig file. If they are allowed, we don't
want to have to have them logg in (provide creditials.)
I can add the group to the Security node in RS2005
Let me know if you find anything out.
Thanks,
rwiethorn
"Sal" wrote:
> Hi All,
> I hope someone can help me. I am wondering if there is anyway to use the
> WebService to Render Reports without the users password. I do nothave it and
> since we use TN Authenticationwe should nto need it. the issue is if I use
> the Default Crddentials on the Web Service it trys to run the reports as the
> asp.net worker process not the authenticated user.
> Is there a nyway to fix this? I tried setting the credentials but I do not
> have thre password and I hate to ask the user in some sort of pop up.
> All ideas would be great.
> Thanks,
> Sal
> --
> Sal
Showing posts with label reports. Show all posts
Showing posts with label reports. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Nested views
When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?
When you say "4 levels deep" do you mean 4 joins? you might want to test it out. There will be an overhead of creating a temp table, inserting data into it, followed by a SELECT from the temp table. If your report will be run by thousands of users simultaneously you might even see a degradation in performance due to tempdb contention.
|||By 4 levels deep I mean View1 is a query of 6 tables and several joins, then view2 use view1 with some more tables and joins or calculations etc etc.
Basically the end result in view4 is too complex to write in 1 query or 1 view so you go as far as you can in view1, then expand that result using view2 etc.
So any ideas on performance??
|||
Off the top of the head, since its not a simple query, I cant think of any, so your best bet is to test it out quickly. You can fire up profiler or use SET STATS IO ON and some others like TIME etc and see if there is any improvement/degradation in performance.
Wednesday, March 21, 2012
Nested tables in client side .RDLC reports
We are developing client side reports (.rdlc) that will require a nested
Parent-Child structure on the report.
The structure shown on the report would appear something like this:
PO# PO Date Invoice #
UPC # Catalog #
UCC-128# Catalog #
The above example would be the Column headings.
Our initial thought was:
Table
Table
Table
Each level needs to be formatted in a table-like format with column headers
and row data.
The data for the above is contained in a DataSet with multiple tables.
Each level can be one or many rows and needs to be grouped accordingly.
After adding a report(.rdlc) to the Visual Studio 2005 project, the report
designer and controls(Table, List, Matrix etc) do not seem to support this
type of nested structure.
You cannot put a table inside another tables detail section.
What would be the best way to accomplish this?Hello,
I would like to know whether the Tables have any relationship between them.
Basically, my suggestion is using the Group in the Table control. You could
include all the three tables in one resultset and using Group to group the
value.
You could refer the BOL to check whether the Group is suitable for your
scenario.
How to: Add a Group to a Table (Report Designer)
http://msdn2.microsoft.com/en-us/library/ms156487.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thank you for your response. What I have to work with is a Dataset with
multiple datatables representing the schema for the data, which I will
receive in xml. So the data will be returned to me in xml also. I have 6 Data
Tables in the dataset with relations between them via foreign keys.
Am I correct in determining what you are suggesting is combining all 6
datatables into one datatable and using this one datatable as the dataset for
my report. And then set up grouping?
If this is correct, what would be the easier way to take datatables with
relations defined and combine them?
Would I need to create a master dataset and then loop through all 6
DataTables and manually build the main table?
Thanks in advance!
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to know whether the Tables have any relationship between them.
> Basically, my suggestion is using the Group in the Table control. You could
> include all the three tables in one resultset and using Group to group the
> value.
> You could refer the BOL to check whether the Group is suitable for your
> scenario.
> How to: Add a Group to a Table (Report Designer)
> http://msdn2.microsoft.com/en-us/library/ms156487.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
Why not using the SQL statement to retrive the data from the database
directly?
Also, you could add some column in the datatable and using the Expression
to refer the other table's data.
For more detailed information about the expression, please refer following
article:
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.a
spx
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hello,
Does my suggestion make any sense to you?
If you have any concerns, please feel free to let me know
Sincerely,
Wei Lu
Microsoft Online Community Support|||Wei Lu,
I'm not sure if I understand what you are suggesting with column expressions.
We are developing an application that will display Reporting Servives clinet
(.rdlc) files via the ReportViewer.
These reports need to be completely dynamic. The Datset Schema, data, and
the .rdlc Report format file must all be dynamic.
The Dataset Schema Xml is stored on the client and will consist of several
Data Tables with relationships defined connectiong all of the tables.
An .rdlc report can only be bound to one datatable as the source for the
report data.
What we need to accomplish is to pull all of the data from all of the
DataTables into one to use as the report source.
This is normally done through a SQL join against the database. Since the
schema is completely dynamic, we do not have any database tables to run a
JOIN.
We need to be able to simulate this join using only DataSet and DataTables.
We've investigated using the JoinView class to join fields from all of these
tables into one but it looks as if JoinView can not support joining all of
these tables. Joinview only takes one table and operates on Parent and Child
relationships from this one table.
If I start at the table at the lowest level in the heirarchy, it seems as if
I can only pull fields from the parent of this table, and not fields from the
rest of the tables (Parent of Parent, Parent of thisParent etc).
1) Is there a way to do this using JoinView to pull data from all tables
into one for the report?
2) Can this be done using relationships by loopiong through all tables?
3) Am I approaching this the wrong way and if so do you have any suggestions
The dynamic schema is read into a Dataset at runtime with the data. So I
will not even know how many tables, columns, fields, and what data is
contained until then.
"Wei Lu [MSFT]" wrote:
> Hello,
> Does my suggestion make any sense to you?
> If you have any concerns, please feel free to let me know
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
>|||Hello,
I am not sure why you want to dynamic the dataset.
If so, I think it will be very hard to build up the report. My suggestion
is to use the .NET to generate a rdlc file by your custom application.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
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.|||Hi Wei Lu,
I have the exact same problem but it's not possible for me at this
point to create a new datatable in the dataset with a single level
structure.
I have a DataSet with two datatables and a relation between them. Let's
say Invoice and InvoiceDetails.
I also have a local report with a grouping structure to represent that.
The thing is that only the InvoiceDetails lines are shown in the
report. Is there a way I can tell a textbox that the Field I want to
show is from a particular datatable? otherwise, is there a way to tell
a Field which datatable it belongs to?
Any help would be appreciated
TIA,
Sebasti=E1n
On Oct 9, 8:09 am, w...@.online.microsoft.com (Wei Lu [MSFT]) wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E
Parent-Child structure on the report.
The structure shown on the report would appear something like this:
PO# PO Date Invoice #
UPC # Catalog #
UCC-128# Catalog #
The above example would be the Column headings.
Our initial thought was:
Table
Table
Table
Each level needs to be formatted in a table-like format with column headers
and row data.
The data for the above is contained in a DataSet with multiple tables.
Each level can be one or many rows and needs to be grouped accordingly.
After adding a report(.rdlc) to the Visual Studio 2005 project, the report
designer and controls(Table, List, Matrix etc) do not seem to support this
type of nested structure.
You cannot put a table inside another tables detail section.
What would be the best way to accomplish this?Hello,
I would like to know whether the Tables have any relationship between them.
Basically, my suggestion is using the Group in the Table control. You could
include all the three tables in one resultset and using Group to group the
value.
You could refer the BOL to check whether the Group is suitable for your
scenario.
How to: Add a Group to a Table (Report Designer)
http://msdn2.microsoft.com/en-us/library/ms156487.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thank you for your response. What I have to work with is a Dataset with
multiple datatables representing the schema for the data, which I will
receive in xml. So the data will be returned to me in xml also. I have 6 Data
Tables in the dataset with relations between them via foreign keys.
Am I correct in determining what you are suggesting is combining all 6
datatables into one datatable and using this one datatable as the dataset for
my report. And then set up grouping?
If this is correct, what would be the easier way to take datatables with
relations defined and combine them?
Would I need to create a master dataset and then loop through all 6
DataTables and manually build the main table?
Thanks in advance!
"Wei Lu [MSFT]" wrote:
> Hello,
> I would like to know whether the Tables have any relationship between them.
> Basically, my suggestion is using the Group in the Table control. You could
> include all the three tables in one resultset and using Group to group the
> value.
> You could refer the BOL to check whether the Group is suitable for your
> scenario.
> How to: Add a Group to a Table (Report Designer)
> http://msdn2.microsoft.com/en-us/library/ms156487.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello,
Why not using the SQL statement to retrive the data from the database
directly?
Also, you could add some column in the datatable and using the Expression
to refer the other table's data.
For more detailed information about the expression, please refer following
article:
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.a
spx
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hello,
Does my suggestion make any sense to you?
If you have any concerns, please feel free to let me know
Sincerely,
Wei Lu
Microsoft Online Community Support|||Wei Lu,
I'm not sure if I understand what you are suggesting with column expressions.
We are developing an application that will display Reporting Servives clinet
(.rdlc) files via the ReportViewer.
These reports need to be completely dynamic. The Datset Schema, data, and
the .rdlc Report format file must all be dynamic.
The Dataset Schema Xml is stored on the client and will consist of several
Data Tables with relationships defined connectiong all of the tables.
An .rdlc report can only be bound to one datatable as the source for the
report data.
What we need to accomplish is to pull all of the data from all of the
DataTables into one to use as the report source.
This is normally done through a SQL join against the database. Since the
schema is completely dynamic, we do not have any database tables to run a
JOIN.
We need to be able to simulate this join using only DataSet and DataTables.
We've investigated using the JoinView class to join fields from all of these
tables into one but it looks as if JoinView can not support joining all of
these tables. Joinview only takes one table and operates on Parent and Child
relationships from this one table.
If I start at the table at the lowest level in the heirarchy, it seems as if
I can only pull fields from the parent of this table, and not fields from the
rest of the tables (Parent of Parent, Parent of thisParent etc).
1) Is there a way to do this using JoinView to pull data from all tables
into one for the report?
2) Can this be done using relationships by loopiong through all tables?
3) Am I approaching this the wrong way and if so do you have any suggestions
The dynamic schema is read into a Dataset at runtime with the data. So I
will not even know how many tables, columns, fields, and what data is
contained until then.
"Wei Lu [MSFT]" wrote:
> Hello,
> Does my suggestion make any sense to you?
> If you have any concerns, please feel free to let me know
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
>|||Hello,
I am not sure why you want to dynamic the dataset.
If so, I think it will be very hard to build up the report. My suggestion
is to use the .NET to generate a rdlc file by your custom application.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
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.|||Hi Wei Lu,
I have the exact same problem but it's not possible for me at this
point to create a new datatable in the dataset with a single level
structure.
I have a DataSet with two datatables and a relation between them. Let's
say Invoice and InvoiceDetails.
I also have a local report with a grouping structure to represent that.
The thing is that only the InvoiceDetails lines are shown in the
report. Is there a way I can tell a textbox that the Field I want to
show is from a particular datatable? otherwise, is there a way to tell
a Field which datatable it belongs to?
Any help would be appreciated
TIA,
Sebasti=E1n
On Oct 9, 8:09 am, w...@.online.microsoft.com (Wei Lu [MSFT]) wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E
Monday, March 12, 2012
Nested filtering in Ad Hoc reports?
There are many business users in my office who would like an AD HOC reporting system. I am not in a position to install the entire suite and check it out myself. I have seen some presentations and i like the tool for the most part ...I have some questions if anyone can help....
1. Can the user specify more than one condition in the filter. Like Name = X AND Address = Y AND Job = Z
2. Can the users sort on more than one column ?
3. Can the users group by on more than one column. Nested grouping ?
I'm assuming by "Ad Hoc" reports you mean creating reports using Report Builder.
If so, then the answer to all three of your questions is YES.
Friday, March 9, 2012
Negative #Abort from SSIS Event Log report
I am using the sample SSIS Event Log reports provided by Microsoft: http://www.microsoft.com/downloads/details.aspx?familyid=526e1fce-7ad5-4a54-b62c-13ffcd114a73&displaylang=en
The Event Log Summary report is showing a negative value for #Abort. Why is the aborted count negative?
I found the answer. Abort is calculated using this formula:
Executions - Succeed - Fail = Abort
I started a package just before midnight on day 1 that completed just after midnight on day 2. The date from and date to parameters I used only included day 2. It looked like a package succeeded that had not been started.
Wednesday, March 7, 2012
Needed software for designing reports
We currently have SQL 2005 (enterprise) reporting services hitting an
odbc based backend (mysql usually). It works great. I run Visual
Studio Pro (and have msdn sql server on my local machine), develop
reports locally and deploy them.
I know the express edition of SQL 2005 reporting services cannot use
an ODBC datasource. Only the standard and up versions.
My question is: For other employees to create and deploy reports what
software (and licenses) are needed? Ideally, I'd love to just deploy
Visual Studio Express and SQL 2005 Express for the users that need to
modify reports, but I have a feeling that won't work...
any ideas?
thank you!If they are just designing reports then you can give the BI Studio (not sure
if that is what it is called). When you install the designer it installs a
version of VS if you do not have it installed. This is free. Also, there are
no licensing issue either. RS is licensed by the server, not by where you
have the client tools installed.
You will want to have a server they can deploy to as part of the development
process, however, for most of their testing they can do this from the
development environment.
If the users can't handle Report Designer (which is really a developer
targeted tool) then check out report builder.
Note, you do not need SQL Server install locally in order to design reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<eric.gay@.gmail.com> wrote in message
news:1179174786.894457.142220@.h2g2000hsg.googlegroups.com...
> We currently have SQL 2005 (enterprise) reporting services hitting an
> odbc based backend (mysql usually). It works great. I run Visual
> Studio Pro (and have msdn sql server on my local machine), develop
> reports locally and deploy them.
> I know the express edition of SQL 2005 reporting services cannot use
> an ODBC datasource. Only the standard and up versions.
> My question is: For other employees to create and deploy reports what
> software (and licenses) are needed? Ideally, I'd love to just deploy
> Visual Studio Express and SQL 2005 Express for the users that need to
> modify reports, but I have a feeling that won't work...
> any ideas?
> thank you!
>
odbc based backend (mysql usually). It works great. I run Visual
Studio Pro (and have msdn sql server on my local machine), develop
reports locally and deploy them.
I know the express edition of SQL 2005 reporting services cannot use
an ODBC datasource. Only the standard and up versions.
My question is: For other employees to create and deploy reports what
software (and licenses) are needed? Ideally, I'd love to just deploy
Visual Studio Express and SQL 2005 Express for the users that need to
modify reports, but I have a feeling that won't work...
any ideas?
thank you!If they are just designing reports then you can give the BI Studio (not sure
if that is what it is called). When you install the designer it installs a
version of VS if you do not have it installed. This is free. Also, there are
no licensing issue either. RS is licensed by the server, not by where you
have the client tools installed.
You will want to have a server they can deploy to as part of the development
process, however, for most of their testing they can do this from the
development environment.
If the users can't handle Report Designer (which is really a developer
targeted tool) then check out report builder.
Note, you do not need SQL Server install locally in order to design reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<eric.gay@.gmail.com> wrote in message
news:1179174786.894457.142220@.h2g2000hsg.googlegroups.com...
> We currently have SQL 2005 (enterprise) reporting services hitting an
> odbc based backend (mysql usually). It works great. I run Visual
> Studio Pro (and have msdn sql server on my local machine), develop
> reports locally and deploy them.
> I know the express edition of SQL 2005 reporting services cannot use
> an ODBC datasource. Only the standard and up versions.
> My question is: For other employees to create and deploy reports what
> software (and licenses) are needed? Ideally, I'd love to just deploy
> Visual Studio Express and SQL 2005 Express for the users that need to
> modify reports, but I have a feeling that won't work...
> any ideas?
> thank you!
>
Need VB.NET code to generate snapshot reports automatically
I need to generate hundreds of snapshot reports, which would be
refreshed every night. Each employee would view a snapshot report
pertaining to his employee number (which is the parameter in the
report). The employee is not allowed to look at anyone else's report,
and the company doesn't want employees to be refreshing reports all
day long.
So, here's what I need:
1. VB.NET code that calls the Reporting Services web service to
generate a linked snapshot report for each employee report and every
employee number (for the employee parameter) in my SQL database
2. Code to automatically schedule these snapshots for a nightly run
using a shared scheduled execution time
3. A way to name each linked snapshot report using some kind of naming
convention (e.g. "Employee Report - Employee 100", "Employee Report -
Employee 205", etc.)
Can anyone help? Does anyone have any sample VB.NET code to share?Just another way to do this. Depending on the size of the reports would
determine if this would work for you. Create a filter that uses the global
user!userid. Then instead of having to have a report snapshot for every
employee, the report would be shared but the employee would only see their
data, nobody elses. Then you would not even have to have the app you are
looking for. Then you could just handle the report normally, i.e. schedule
it to run nightly.
Bruce L-C
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409231824.5aee8e85@.posting.google.com...
> I need to generate hundreds of snapshot reports, which would be
> refreshed every night. Each employee would view a snapshot report
> pertaining to his employee number (which is the parameter in the
> report). The employee is not allowed to look at anyone else's report,
> and the company doesn't want employees to be refreshing reports all
> day long.
> So, here's what I need:
> 1. VB.NET code that calls the Reporting Services web service to
> generate a linked snapshot report for each employee report and every
> employee number (for the employee parameter) in my SQL database
> 2. Code to automatically schedule these snapshots for a nightly run
> using a shared scheduled execution time
> 3. A way to name each linked snapshot report using some kind of naming
> convention (e.g. "Employee Report - Employee 100", "Employee Report -
> Employee 205", etc.)
> Can anyone help? Does anyone have any sample VB.NET code to share?|||Bruce, I wish that I could use the global user!userid value, but I
need to produce snapshot reports for a whole slew of parameter
combinations. For instance, we have some reports that use a Goal ID
and Organization ID parameter that might produce a combination such as
"Goal X Results for Region 1" or "Goal Y Results for Department 200".
Our Department Manager for Department 200 won't be allowed to see the
regional reports, but he will be allowed to see the dozens of Goal
reports for his department. Even though his userid is useful in
regards to sorting out what he can see, it doesn't solve the dilemma
with having to produce snapshots for all the goal report combinations.
You may be wondering why on earth we need thousands of snapshot
reports. Basically, users are not allowed to refresh reports during
the day because of processing concerns from upper management. So, a
snapshot report for each parameter combination must be produced at
night.
I just need the VB.NET code to automatically create and eliminate
snapshot reports based on new employees coming on board, employees
transferring to new departments, and employees leaving the company.
Any help would be appreciated.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<ulzfQwjoEHA.1800@.TK2MSFTNGP15.phx.gbl>...
> Just another way to do this. Depending on the size of the reports would
> determine if this would work for you. Create a filter that uses the global
> user!userid. Then instead of having to have a report snapshot for every
> employee, the report would be shared but the employee would only see their
> data, nobody elses. Then you would not even have to have the app you are
> looking for. Then you could just handle the report normally, i.e. schedule
> it to run nightly.
> Bruce L-C
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409231824.5aee8e85@.posting.google.com...
> > I need to generate hundreds of snapshot reports, which would be
> > refreshed every night. Each employee would view a snapshot report
> > pertaining to his employee number (which is the parameter in the
> > report). The employee is not allowed to look at anyone else's report,
> > and the company doesn't want employees to be refreshing reports all
> > day long.
> >
> > So, here's what I need:
> >
> > 1. VB.NET code that calls the Reporting Services web service to
> > generate a linked snapshot report for each employee report and every
> > employee number (for the employee parameter) in my SQL database
> > 2. Code to automatically schedule these snapshots for a nightly run
> > using a shared scheduled execution time
> > 3. A way to name each linked snapshot report using some kind of naming
> > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > Employee 205", etc.)
> >
> > Can anyone help? Does anyone have any sample VB.NET code to share?
refreshed every night. Each employee would view a snapshot report
pertaining to his employee number (which is the parameter in the
report). The employee is not allowed to look at anyone else's report,
and the company doesn't want employees to be refreshing reports all
day long.
So, here's what I need:
1. VB.NET code that calls the Reporting Services web service to
generate a linked snapshot report for each employee report and every
employee number (for the employee parameter) in my SQL database
2. Code to automatically schedule these snapshots for a nightly run
using a shared scheduled execution time
3. A way to name each linked snapshot report using some kind of naming
convention (e.g. "Employee Report - Employee 100", "Employee Report -
Employee 205", etc.)
Can anyone help? Does anyone have any sample VB.NET code to share?Just another way to do this. Depending on the size of the reports would
determine if this would work for you. Create a filter that uses the global
user!userid. Then instead of having to have a report snapshot for every
employee, the report would be shared but the employee would only see their
data, nobody elses. Then you would not even have to have the app you are
looking for. Then you could just handle the report normally, i.e. schedule
it to run nightly.
Bruce L-C
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409231824.5aee8e85@.posting.google.com...
> I need to generate hundreds of snapshot reports, which would be
> refreshed every night. Each employee would view a snapshot report
> pertaining to his employee number (which is the parameter in the
> report). The employee is not allowed to look at anyone else's report,
> and the company doesn't want employees to be refreshing reports all
> day long.
> So, here's what I need:
> 1. VB.NET code that calls the Reporting Services web service to
> generate a linked snapshot report for each employee report and every
> employee number (for the employee parameter) in my SQL database
> 2. Code to automatically schedule these snapshots for a nightly run
> using a shared scheduled execution time
> 3. A way to name each linked snapshot report using some kind of naming
> convention (e.g. "Employee Report - Employee 100", "Employee Report -
> Employee 205", etc.)
> Can anyone help? Does anyone have any sample VB.NET code to share?|||Bruce, I wish that I could use the global user!userid value, but I
need to produce snapshot reports for a whole slew of parameter
combinations. For instance, we have some reports that use a Goal ID
and Organization ID parameter that might produce a combination such as
"Goal X Results for Region 1" or "Goal Y Results for Department 200".
Our Department Manager for Department 200 won't be allowed to see the
regional reports, but he will be allowed to see the dozens of Goal
reports for his department. Even though his userid is useful in
regards to sorting out what he can see, it doesn't solve the dilemma
with having to produce snapshots for all the goal report combinations.
You may be wondering why on earth we need thousands of snapshot
reports. Basically, users are not allowed to refresh reports during
the day because of processing concerns from upper management. So, a
snapshot report for each parameter combination must be produced at
night.
I just need the VB.NET code to automatically create and eliminate
snapshot reports based on new employees coming on board, employees
transferring to new departments, and employees leaving the company.
Any help would be appreciated.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<ulzfQwjoEHA.1800@.TK2MSFTNGP15.phx.gbl>...
> Just another way to do this. Depending on the size of the reports would
> determine if this would work for you. Create a filter that uses the global
> user!userid. Then instead of having to have a report snapshot for every
> employee, the report would be shared but the employee would only see their
> data, nobody elses. Then you would not even have to have the app you are
> looking for. Then you could just handle the report normally, i.e. schedule
> it to run nightly.
> Bruce L-C
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409231824.5aee8e85@.posting.google.com...
> > I need to generate hundreds of snapshot reports, which would be
> > refreshed every night. Each employee would view a snapshot report
> > pertaining to his employee number (which is the parameter in the
> > report). The employee is not allowed to look at anyone else's report,
> > and the company doesn't want employees to be refreshing reports all
> > day long.
> >
> > So, here's what I need:
> >
> > 1. VB.NET code that calls the Reporting Services web service to
> > generate a linked snapshot report for each employee report and every
> > employee number (for the employee parameter) in my SQL database
> > 2. Code to automatically schedule these snapshots for a nightly run
> > using a shared scheduled execution time
> > 3. A way to name each linked snapshot report using some kind of naming
> > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > Employee 205", etc.)
> >
> > Can anyone help? Does anyone have any sample VB.NET code to share?
Need VB.NET code to generate snapshot reports automatically
I need to generate hundreds of snapshot reports, which would be
refreshed every night. Each employee would view a snapshot report
pertaining to his employee number (which is the parameter in the
report). The employee is not allowed to look at anyone else's report,
and the company doesn't want employees to be refreshing reports all
day long.
So, here's what I need:
1. VB.NET code that calls the Reporting Services web service to
generate a linked snapshot report for each employee report and every
employee number (for the employee parameter) in my SQL database
2. Code to automatically schedule these snapshots for a nightly run
using a shared scheduled execution time
3. A way to name each linked snapshot report using some kind of naming
convention (e.g. "Employee Report - Employee 100", "Employee Report -
Employee 205", etc.)
Can anyone help? Does anyone have any sample VB.NET code to share?question. How will you set up security for filter out employee's to read
only the
report snaped from their ID?
dlr
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> I need to generate hundreds of snapshot reports, which would be
> refreshed every night. Each employee would view a snapshot report
> pertaining to his employee number (which is the parameter in the
> report). The employee is not allowed to look at anyone else's report,
> and the company doesn't want employees to be refreshing reports all
> day long.
> So, here's what I need:
> 1. VB.NET code that calls the Reporting Services web service to
> generate a linked snapshot report for each employee report and every
> employee number (for the employee parameter) in my SQL database
> 2. Code to automatically schedule these snapshots for a nightly run
> using a shared scheduled execution time
> 3. A way to name each linked snapshot report using some kind of naming
> convention (e.g. "Employee Report - Employee 100", "Employee Report -
> Employee 205", etc.)
> Can anyone help? Does anyone have any sample VB.NET code to share?|||Dennis, when the user logs in to the web application, a stored
procedure fires to retrieve the ID for the employee, where the
employee works, where the employee is in the management food chain,
and what reports the user is authorized to see.
So, when the user enters the reports page in the web application, the
user would see all the reports he/she is permitted to see that the
stored procedure brought back from that report table I mentioned.
Because the web application has the employee and workplace ID in
memory, it would call the respective snapshot by taking the report
name and concatenating the employee ID and workplace ID, which then
references the snapshot report name. Here's an example...
Let's assume that the user's employee ID is 205 and workplace ID is
5000. If the user clicks on a report called "Sales by Employee", the
web application would then construct the snapshot report name (e.g.
"Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
snapshots available in the Reporting Services database (i.e. one
snapshot combination for every employee ID and work place ID) and
display the correct snapshot.
Unfortunately, we don't know how to do the VB.NET code to
automatically build all the snapshots from our database table of
employee and workplace IDs. We need a means for automatically
generating and eliminating snapshots as employees come on board,
switch departments, or leave the organization.
"Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> question. How will you set up security for filter out employee's to read
> only the
> report snaped from their ID?
> dlr
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > I need to generate hundreds of snapshot reports, which would be
> > refreshed every night. Each employee would view a snapshot report
> > pertaining to his employee number (which is the parameter in the
> > report). The employee is not allowed to look at anyone else's report,
> > and the company doesn't want employees to be refreshing reports all
> > day long.
> >
> > So, here's what I need:
> >
> > 1. VB.NET code that calls the Reporting Services web service to
> > generate a linked snapshot report for each employee report and every
> > employee number (for the employee parameter) in my SQL database
> > 2. Code to automatically schedule these snapshots for a nightly run
> > using a shared scheduled execution time
> > 3. A way to name each linked snapshot report using some kind of naming
> > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > Employee 205", etc.)
> >
> > Can anyone help? Does anyone have any sample VB.NET code to share?|||ok Steve. I am a little more pluged in to your design.
The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
you to name your output snapshots anything different from the base name of
the report (see BOL on this function and the section of snapshots with
parameterized reports).
I think, based on what you are telling me is that you will want to
(0) identify the user and her report parameters
(1) use the Web Service "Render" method (which returns a stream of bytes) to
create the report stream
(2) write the bytes to a file share (and name it using your paramater
values) and then
(3) redirect the user to that file.
[you will want to skip (1) and (2) if a valid file on share exists when the
user jumps in]
does this sound correct?
dlr
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409241745.6fa9b007@.posting.google.com...
> Dennis, when the user logs in to the web application, a stored
> procedure fires to retrieve the ID for the employee, where the
> employee works, where the employee is in the management food chain,
> and what reports the user is authorized to see.
> So, when the user enters the reports page in the web application, the
> user would see all the reports he/she is permitted to see that the
> stored procedure brought back from that report table I mentioned.
> Because the web application has the employee and workplace ID in
> memory, it would call the respective snapshot by taking the report
> name and concatenating the employee ID and workplace ID, which then
> references the snapshot report name. Here's an example...
> Let's assume that the user's employee ID is 205 and workplace ID is
> 5000. If the user clicks on a report called "Sales by Employee", the
> web application would then construct the snapshot report name (e.g.
> "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> snapshots available in the Reporting Services database (i.e. one
> snapshot combination for every employee ID and work place ID) and
> display the correct snapshot.
> Unfortunately, we don't know how to do the VB.NET code to
> automatically build all the snapshots from our database table of
> employee and workplace IDs. We need a means for automatically
> generating and eliminating snapshots as employees come on board,
> switch departments, or leave the organization.
> "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > question. How will you set up security for filter out employee's to
read
> > only the
> > report snaped from their ID?
> >
> > dlr
> > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > I need to generate hundreds of snapshot reports, which would be
> > > refreshed every night. Each employee would view a snapshot report
> > > pertaining to his employee number (which is the parameter in the
> > > report). The employee is not allowed to look at anyone else's report,
> > > and the company doesn't want employees to be refreshing reports all
> > > day long.
> > >
> > > So, here's what I need:
> > >
> > > 1. VB.NET code that calls the Reporting Services web service to
> > > generate a linked snapshot report for each employee report and every
> > > employee number (for the employee parameter) in my SQL database
> > > 2. Code to automatically schedule these snapshots for a nightly run
> > > using a shared scheduled execution time
> > > 3. A way to name each linked snapshot report using some kind of naming
> > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > Employee 205", etc.)
> > >
> > > Can anyone help? Does anyone have any sample VB.NET code to share?|||Dennis, I'll need to research more on the Web Service method you
referred to. It seems like the web service has everything I would
need to do generate snapshot reports, but I'd like to see some sample
VB.NET code to help me along.
As for your numbered items below, I would have to say that we already
have the logic to identify the user and get the right snapshot (e.g.
"Sales by Employee - 36", where "Sales by Employee" is the base report
name, "36" is the parameter value for the employee number, and "Sales
by Employee - 36" is the saved snapshot name).
I've successfully created some snapshots manually and retrieved the
right snapshot based on the employee ID of the user logged in...so,
rendering the snapshot report is no problem.
The problem is generating all the snapshots I need via an automated
process. I'm sure with the web service, there are available methods
to do this. I've already created a console application that
automatically hides parameters for all 50 of my reports.
So, the VB.NET code will need the following:
1. Retrieve a collection of reports
2. Set a default parameter for the Employee ID to each report
3. Create a linked report for each base report and respective Employee
ID value and concatenate the parameter value to the report name (e.g.
"Sales by Employee - 36")
4. Create a snapshot from the linked report
5. Set the snapshot to use the shared schedule for my nightly refresh
6. Remove existing snapshots for those employees who have left the
company
7. Remove the default value for the Employee ID from the base reports
so they can be refreshed separately from the snapshot reports
"Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<OcjTCRLpEHA.3552@.TK2MSFTNGP15.phx.gbl>...
> ok Steve. I am a little more pluged in to your design.
> The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
> you to name your output snapshots anything different from the base name of
> the report (see BOL on this function and the section of snapshots with
> parameterized reports).
> I think, based on what you are telling me is that you will want to
> (0) identify the user and her report parameters
> (1) use the Web Service "Render" method (which returns a stream of bytes) to
> create the report stream
> (2) write the bytes to a file share (and name it using your paramater
> values) and then
> (3) redirect the user to that file.
> [you will want to skip (1) and (2) if a valid file on share exists when the
> user jumps in]
> does this sound correct?
>
> dlr
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409241745.6fa9b007@.posting.google.com...
> > Dennis, when the user logs in to the web application, a stored
> > procedure fires to retrieve the ID for the employee, where the
> > employee works, where the employee is in the management food chain,
> > and what reports the user is authorized to see.
> >
> > So, when the user enters the reports page in the web application, the
> > user would see all the reports he/she is permitted to see that the
> > stored procedure brought back from that report table I mentioned.
> > Because the web application has the employee and workplace ID in
> > memory, it would call the respective snapshot by taking the report
> > name and concatenating the employee ID and workplace ID, which then
> > references the snapshot report name. Here's an example...
> >
> > Let's assume that the user's employee ID is 205 and workplace ID is
> > 5000. If the user clicks on a report called "Sales by Employee", the
> > web application would then construct the snapshot report name (e.g.
> > "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> > snapshots available in the Reporting Services database (i.e. one
> > snapshot combination for every employee ID and work place ID) and
> > display the correct snapshot.
> >
> > Unfortunately, we don't know how to do the VB.NET code to
> > automatically build all the snapshots from our database table of
> > employee and workplace IDs. We need a means for automatically
> > generating and eliminating snapshots as employees come on board,
> > switch departments, or leave the organization.
> >
> > "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
> news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > > question. How will you set up security for filter out employee's to
> read
> > > only the
> > > report snaped from their ID?
> > >
> > > dlr
> > > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > > I need to generate hundreds of snapshot reports, which would be
> > > > refreshed every night. Each employee would view a snapshot report
> > > > pertaining to his employee number (which is the parameter in the
> > > > report). The employee is not allowed to look at anyone else's report,
> > > > and the company doesn't want employees to be refreshing reports all
> > > > day long.
> > > >
> > > > So, here's what I need:
> > > >
> > > > 1. VB.NET code that calls the Reporting Services web service to
> > > > generate a linked snapshot report for each employee report and every
> > > > employee number (for the employee parameter) in my SQL database
> > > > 2. Code to automatically schedule these snapshots for a nightly run
> > > > using a shared scheduled execution time
> > > > 3. A way to name each linked snapshot report using some kind of naming
> > > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > > Employee 205", etc.)
> > > >
> > > > Can anyone help? Does anyone have any sample VB.NET code to share?|||Are you using integrated security?
"Steve Pantazis" wrote:
> Dennis, I'll need to research more on the Web Service method you
> referred to. It seems like the web service has everything I would
> need to do generate snapshot reports, but I'd like to see some sample
> VB.NET code to help me along.
> As for your numbered items below, I would have to say that we already
> have the logic to identify the user and get the right snapshot (e.g.
> "Sales by Employee - 36", where "Sales by Employee" is the base report
> name, "36" is the parameter value for the employee number, and "Sales
> by Employee - 36" is the saved snapshot name).
> I've successfully created some snapshots manually and retrieved the
> right snapshot based on the employee ID of the user logged in...so,
> rendering the snapshot report is no problem.
> The problem is generating all the snapshots I need via an automated
> process. I'm sure with the web service, there are available methods
> to do this. I've already created a console application that
> automatically hides parameters for all 50 of my reports.
> So, the VB.NET code will need the following:
> 1. Retrieve a collection of reports
> 2. Set a default parameter for the Employee ID to each report
> 3. Create a linked report for each base report and respective Employee
> ID value and concatenate the parameter value to the report name (e.g.
> "Sales by Employee - 36")
> 4. Create a snapshot from the linked report
> 5. Set the snapshot to use the shared schedule for my nightly refresh
> 6. Remove existing snapshots for those employees who have left the
> company
> 7. Remove the default value for the Employee ID from the base reports
> so they can be refreshed separately from the snapshot reports
>
> "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<OcjTCRLpEHA.3552@.TK2MSFTNGP15.phx.gbl>...
> > ok Steve. I am a little more pluged in to your design.
> >
> > The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
> > you to name your output snapshots anything different from the base name of
> > the report (see BOL on this function and the section of snapshots with
> > parameterized reports).
> >
> > I think, based on what you are telling me is that you will want to
> > (0) identify the user and her report parameters
> > (1) use the Web Service "Render" method (which returns a stream of bytes) to
> > create the report stream
> > (2) write the bytes to a file share (and name it using your paramater
> > values) and then
> > (3) redirect the user to that file.
> >
> > [you will want to skip (1) and (2) if a valid file on share exists when the
> > user jumps in]
> >
> > does this sound correct?
> >
> >
> > dlr
> > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > news:437b6286.0409241745.6fa9b007@.posting.google.com...
> > > Dennis, when the user logs in to the web application, a stored
> > > procedure fires to retrieve the ID for the employee, where the
> > > employee works, where the employee is in the management food chain,
> > > and what reports the user is authorized to see.
> > >
> > > So, when the user enters the reports page in the web application, the
> > > user would see all the reports he/she is permitted to see that the
> > > stored procedure brought back from that report table I mentioned.
> > > Because the web application has the employee and workplace ID in
> > > memory, it would call the respective snapshot by taking the report
> > > name and concatenating the employee ID and workplace ID, which then
> > > references the snapshot report name. Here's an example...
> > >
> > > Let's assume that the user's employee ID is 205 and workplace ID is
> > > 5000. If the user clicks on a report called "Sales by Employee", the
> > > web application would then construct the snapshot report name (e.g.
> > > "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> > > snapshots available in the Reporting Services database (i.e. one
> > > snapshot combination for every employee ID and work place ID) and
> > > display the correct snapshot.
> > >
> > > Unfortunately, we don't know how to do the VB.NET code to
> > > automatically build all the snapshots from our database table of
> > > employee and workplace IDs. We need a means for automatically
> > > generating and eliminating snapshots as employees come on board,
> > > switch departments, or leave the organization.
> > >
> > > "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
> > news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > > > question. How will you set up security for filter out employee's to
> > read
> > > > only the
> > > > report snaped from their ID?
> > > >
> > > > dlr
> > > > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > > > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > > > I need to generate hundreds of snapshot reports, which would be
> > > > > refreshed every night. Each employee would view a snapshot report
> > > > > pertaining to his employee number (which is the parameter in the
> > > > > report). The employee is not allowed to look at anyone else's report,
> > > > > and the company doesn't want employees to be refreshing reports all
> > > > > day long.
> > > > >
> > > > > So, here's what I need:
> > > > >
> > > > > 1. VB.NET code that calls the Reporting Services web service to
> > > > > generate a linked snapshot report for each employee report and every
> > > > > employee number (for the employee parameter) in my SQL database
> > > > > 2. Code to automatically schedule these snapshots for a nightly run
> > > > > using a shared scheduled execution time
> > > > > 3. A way to name each linked snapshot report using some kind of naming
> > > > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > > > Employee 205", etc.)
> > > > >
> > > > > Can anyone help? Does anyone have any sample VB.NET code to share?
>
refreshed every night. Each employee would view a snapshot report
pertaining to his employee number (which is the parameter in the
report). The employee is not allowed to look at anyone else's report,
and the company doesn't want employees to be refreshing reports all
day long.
So, here's what I need:
1. VB.NET code that calls the Reporting Services web service to
generate a linked snapshot report for each employee report and every
employee number (for the employee parameter) in my SQL database
2. Code to automatically schedule these snapshots for a nightly run
using a shared scheduled execution time
3. A way to name each linked snapshot report using some kind of naming
convention (e.g. "Employee Report - Employee 100", "Employee Report -
Employee 205", etc.)
Can anyone help? Does anyone have any sample VB.NET code to share?question. How will you set up security for filter out employee's to read
only the
report snaped from their ID?
dlr
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> I need to generate hundreds of snapshot reports, which would be
> refreshed every night. Each employee would view a snapshot report
> pertaining to his employee number (which is the parameter in the
> report). The employee is not allowed to look at anyone else's report,
> and the company doesn't want employees to be refreshing reports all
> day long.
> So, here's what I need:
> 1. VB.NET code that calls the Reporting Services web service to
> generate a linked snapshot report for each employee report and every
> employee number (for the employee parameter) in my SQL database
> 2. Code to automatically schedule these snapshots for a nightly run
> using a shared scheduled execution time
> 3. A way to name each linked snapshot report using some kind of naming
> convention (e.g. "Employee Report - Employee 100", "Employee Report -
> Employee 205", etc.)
> Can anyone help? Does anyone have any sample VB.NET code to share?|||Dennis, when the user logs in to the web application, a stored
procedure fires to retrieve the ID for the employee, where the
employee works, where the employee is in the management food chain,
and what reports the user is authorized to see.
So, when the user enters the reports page in the web application, the
user would see all the reports he/she is permitted to see that the
stored procedure brought back from that report table I mentioned.
Because the web application has the employee and workplace ID in
memory, it would call the respective snapshot by taking the report
name and concatenating the employee ID and workplace ID, which then
references the snapshot report name. Here's an example...
Let's assume that the user's employee ID is 205 and workplace ID is
5000. If the user clicks on a report called "Sales by Employee", the
web application would then construct the snapshot report name (e.g.
"Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
snapshots available in the Reporting Services database (i.e. one
snapshot combination for every employee ID and work place ID) and
display the correct snapshot.
Unfortunately, we don't know how to do the VB.NET code to
automatically build all the snapshots from our database table of
employee and workplace IDs. We need a means for automatically
generating and eliminating snapshots as employees come on board,
switch departments, or leave the organization.
"Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> question. How will you set up security for filter out employee's to read
> only the
> report snaped from their ID?
> dlr
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > I need to generate hundreds of snapshot reports, which would be
> > refreshed every night. Each employee would view a snapshot report
> > pertaining to his employee number (which is the parameter in the
> > report). The employee is not allowed to look at anyone else's report,
> > and the company doesn't want employees to be refreshing reports all
> > day long.
> >
> > So, here's what I need:
> >
> > 1. VB.NET code that calls the Reporting Services web service to
> > generate a linked snapshot report for each employee report and every
> > employee number (for the employee parameter) in my SQL database
> > 2. Code to automatically schedule these snapshots for a nightly run
> > using a shared scheduled execution time
> > 3. A way to name each linked snapshot report using some kind of naming
> > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > Employee 205", etc.)
> >
> > Can anyone help? Does anyone have any sample VB.NET code to share?|||ok Steve. I am a little more pluged in to your design.
The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
you to name your output snapshots anything different from the base name of
the report (see BOL on this function and the section of snapshots with
parameterized reports).
I think, based on what you are telling me is that you will want to
(0) identify the user and her report parameters
(1) use the Web Service "Render" method (which returns a stream of bytes) to
create the report stream
(2) write the bytes to a file share (and name it using your paramater
values) and then
(3) redirect the user to that file.
[you will want to skip (1) and (2) if a valid file on share exists when the
user jumps in]
does this sound correct?
dlr
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409241745.6fa9b007@.posting.google.com...
> Dennis, when the user logs in to the web application, a stored
> procedure fires to retrieve the ID for the employee, where the
> employee works, where the employee is in the management food chain,
> and what reports the user is authorized to see.
> So, when the user enters the reports page in the web application, the
> user would see all the reports he/she is permitted to see that the
> stored procedure brought back from that report table I mentioned.
> Because the web application has the employee and workplace ID in
> memory, it would call the respective snapshot by taking the report
> name and concatenating the employee ID and workplace ID, which then
> references the snapshot report name. Here's an example...
> Let's assume that the user's employee ID is 205 and workplace ID is
> 5000. If the user clicks on a report called "Sales by Employee", the
> web application would then construct the snapshot report name (e.g.
> "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> snapshots available in the Reporting Services database (i.e. one
> snapshot combination for every employee ID and work place ID) and
> display the correct snapshot.
> Unfortunately, we don't know how to do the VB.NET code to
> automatically build all the snapshots from our database table of
> employee and workplace IDs. We need a means for automatically
> generating and eliminating snapshots as employees come on board,
> switch departments, or leave the organization.
> "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > question. How will you set up security for filter out employee's to
read
> > only the
> > report snaped from their ID?
> >
> > dlr
> > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > I need to generate hundreds of snapshot reports, which would be
> > > refreshed every night. Each employee would view a snapshot report
> > > pertaining to his employee number (which is the parameter in the
> > > report). The employee is not allowed to look at anyone else's report,
> > > and the company doesn't want employees to be refreshing reports all
> > > day long.
> > >
> > > So, here's what I need:
> > >
> > > 1. VB.NET code that calls the Reporting Services web service to
> > > generate a linked snapshot report for each employee report and every
> > > employee number (for the employee parameter) in my SQL database
> > > 2. Code to automatically schedule these snapshots for a nightly run
> > > using a shared scheduled execution time
> > > 3. A way to name each linked snapshot report using some kind of naming
> > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > Employee 205", etc.)
> > >
> > > Can anyone help? Does anyone have any sample VB.NET code to share?|||Dennis, I'll need to research more on the Web Service method you
referred to. It seems like the web service has everything I would
need to do generate snapshot reports, but I'd like to see some sample
VB.NET code to help me along.
As for your numbered items below, I would have to say that we already
have the logic to identify the user and get the right snapshot (e.g.
"Sales by Employee - 36", where "Sales by Employee" is the base report
name, "36" is the parameter value for the employee number, and "Sales
by Employee - 36" is the saved snapshot name).
I've successfully created some snapshots manually and retrieved the
right snapshot based on the employee ID of the user logged in...so,
rendering the snapshot report is no problem.
The problem is generating all the snapshots I need via an automated
process. I'm sure with the web service, there are available methods
to do this. I've already created a console application that
automatically hides parameters for all 50 of my reports.
So, the VB.NET code will need the following:
1. Retrieve a collection of reports
2. Set a default parameter for the Employee ID to each report
3. Create a linked report for each base report and respective Employee
ID value and concatenate the parameter value to the report name (e.g.
"Sales by Employee - 36")
4. Create a snapshot from the linked report
5. Set the snapshot to use the shared schedule for my nightly refresh
6. Remove existing snapshots for those employees who have left the
company
7. Remove the default value for the Employee ID from the base reports
so they can be refreshed separately from the snapshot reports
"Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<OcjTCRLpEHA.3552@.TK2MSFTNGP15.phx.gbl>...
> ok Steve. I am a little more pluged in to your design.
> The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
> you to name your output snapshots anything different from the base name of
> the report (see BOL on this function and the section of snapshots with
> parameterized reports).
> I think, based on what you are telling me is that you will want to
> (0) identify the user and her report parameters
> (1) use the Web Service "Render" method (which returns a stream of bytes) to
> create the report stream
> (2) write the bytes to a file share (and name it using your paramater
> values) and then
> (3) redirect the user to that file.
> [you will want to skip (1) and (2) if a valid file on share exists when the
> user jumps in]
> does this sound correct?
>
> dlr
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409241745.6fa9b007@.posting.google.com...
> > Dennis, when the user logs in to the web application, a stored
> > procedure fires to retrieve the ID for the employee, where the
> > employee works, where the employee is in the management food chain,
> > and what reports the user is authorized to see.
> >
> > So, when the user enters the reports page in the web application, the
> > user would see all the reports he/she is permitted to see that the
> > stored procedure brought back from that report table I mentioned.
> > Because the web application has the employee and workplace ID in
> > memory, it would call the respective snapshot by taking the report
> > name and concatenating the employee ID and workplace ID, which then
> > references the snapshot report name. Here's an example...
> >
> > Let's assume that the user's employee ID is 205 and workplace ID is
> > 5000. If the user clicks on a report called "Sales by Employee", the
> > web application would then construct the snapshot report name (e.g.
> > "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> > snapshots available in the Reporting Services database (i.e. one
> > snapshot combination for every employee ID and work place ID) and
> > display the correct snapshot.
> >
> > Unfortunately, we don't know how to do the VB.NET code to
> > automatically build all the snapshots from our database table of
> > employee and workplace IDs. We need a means for automatically
> > generating and eliminating snapshots as employees come on board,
> > switch departments, or leave the organization.
> >
> > "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
> news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > > question. How will you set up security for filter out employee's to
> read
> > > only the
> > > report snaped from their ID?
> > >
> > > dlr
> > > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > > I need to generate hundreds of snapshot reports, which would be
> > > > refreshed every night. Each employee would view a snapshot report
> > > > pertaining to his employee number (which is the parameter in the
> > > > report). The employee is not allowed to look at anyone else's report,
> > > > and the company doesn't want employees to be refreshing reports all
> > > > day long.
> > > >
> > > > So, here's what I need:
> > > >
> > > > 1. VB.NET code that calls the Reporting Services web service to
> > > > generate a linked snapshot report for each employee report and every
> > > > employee number (for the employee parameter) in my SQL database
> > > > 2. Code to automatically schedule these snapshots for a nightly run
> > > > using a shared scheduled execution time
> > > > 3. A way to name each linked snapshot report using some kind of naming
> > > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > > Employee 205", etc.)
> > > >
> > > > Can anyone help? Does anyone have any sample VB.NET code to share?|||Are you using integrated security?
"Steve Pantazis" wrote:
> Dennis, I'll need to research more on the Web Service method you
> referred to. It seems like the web service has everything I would
> need to do generate snapshot reports, but I'd like to see some sample
> VB.NET code to help me along.
> As for your numbered items below, I would have to say that we already
> have the logic to identify the user and get the right snapshot (e.g.
> "Sales by Employee - 36", where "Sales by Employee" is the base report
> name, "36" is the parameter value for the employee number, and "Sales
> by Employee - 36" is the saved snapshot name).
> I've successfully created some snapshots manually and retrieved the
> right snapshot based on the employee ID of the user logged in...so,
> rendering the snapshot report is no problem.
> The problem is generating all the snapshots I need via an automated
> process. I'm sure with the web service, there are available methods
> to do this. I've already created a console application that
> automatically hides parameters for all 50 of my reports.
> So, the VB.NET code will need the following:
> 1. Retrieve a collection of reports
> 2. Set a default parameter for the Employee ID to each report
> 3. Create a linked report for each base report and respective Employee
> ID value and concatenate the parameter value to the report name (e.g.
> "Sales by Employee - 36")
> 4. Create a snapshot from the linked report
> 5. Set the snapshot to use the shared schedule for my nightly refresh
> 6. Remove existing snapshots for those employees who have left the
> company
> 7. Remove the default value for the Employee ID from the base reports
> so they can be refreshed separately from the snapshot reports
>
> "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<OcjTCRLpEHA.3552@.TK2MSFTNGP15.phx.gbl>...
> > ok Steve. I am a little more pluged in to your design.
> >
> > The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
> > you to name your output snapshots anything different from the base name of
> > the report (see BOL on this function and the section of snapshots with
> > parameterized reports).
> >
> > I think, based on what you are telling me is that you will want to
> > (0) identify the user and her report parameters
> > (1) use the Web Service "Render" method (which returns a stream of bytes) to
> > create the report stream
> > (2) write the bytes to a file share (and name it using your paramater
> > values) and then
> > (3) redirect the user to that file.
> >
> > [you will want to skip (1) and (2) if a valid file on share exists when the
> > user jumps in]
> >
> > does this sound correct?
> >
> >
> > dlr
> > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > news:437b6286.0409241745.6fa9b007@.posting.google.com...
> > > Dennis, when the user logs in to the web application, a stored
> > > procedure fires to retrieve the ID for the employee, where the
> > > employee works, where the employee is in the management food chain,
> > > and what reports the user is authorized to see.
> > >
> > > So, when the user enters the reports page in the web application, the
> > > user would see all the reports he/she is permitted to see that the
> > > stored procedure brought back from that report table I mentioned.
> > > Because the web application has the employee and workplace ID in
> > > memory, it would call the respective snapshot by taking the report
> > > name and concatenating the employee ID and workplace ID, which then
> > > references the snapshot report name. Here's an example...
> > >
> > > Let's assume that the user's employee ID is 205 and workplace ID is
> > > 5000. If the user clicks on a report called "Sales by Employee", the
> > > web application would then construct the snapshot report name (e.g.
> > > "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> > > snapshots available in the Reporting Services database (i.e. one
> > > snapshot combination for every employee ID and work place ID) and
> > > display the correct snapshot.
> > >
> > > Unfortunately, we don't know how to do the VB.NET code to
> > > automatically build all the snapshots from our database table of
> > > employee and workplace IDs. We need a means for automatically
> > > generating and eliminating snapshots as employees come on board,
> > > switch departments, or leave the organization.
> > >
> > > "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
> > news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > > > question. How will you set up security for filter out employee's to
> > read
> > > > only the
> > > > report snaped from their ID?
> > > >
> > > > dlr
> > > > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > > > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > > > I need to generate hundreds of snapshot reports, which would be
> > > > > refreshed every night. Each employee would view a snapshot report
> > > > > pertaining to his employee number (which is the parameter in the
> > > > > report). The employee is not allowed to look at anyone else's report,
> > > > > and the company doesn't want employees to be refreshing reports all
> > > > > day long.
> > > > >
> > > > > So, here's what I need:
> > > > >
> > > > > 1. VB.NET code that calls the Reporting Services web service to
> > > > > generate a linked snapshot report for each employee report and every
> > > > > employee number (for the employee parameter) in my SQL database
> > > > > 2. Code to automatically schedule these snapshots for a nightly run
> > > > > using a shared scheduled execution time
> > > > > 3. A way to name each linked snapshot report using some kind of naming
> > > > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > > > Employee 205", etc.)
> > > > >
> > > > > Can anyone help? Does anyone have any sample VB.NET code to share?
>
Monday, February 20, 2012
Need to use MID function in SQL
When I try to use the MID statement in a SQL view, it reports 'function not recognized'. Is there some other way to execute the following?
CASE WHEN Mid(SearchID , 4 , 1) = '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END.
I have a column with two data set possibilities: aaa-bbbbb and aaaa-bbbbb. I only want the data to the left of the dash.
Thanks.
Ernie
You have to combine sql sever string function
like "left" and "right" to achive you requirements
I think the equivalent of vb mid function is the "substring" function
This example shows how to return only a portion of a character string. From the authors table, this query returns the last name in one column with only the first initial in the second column.
USE pubs SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors ORDER BY au_lname create table #test (SearchID varchar(49))
insert into #test values('aaa-bbbbb')
insert into #test values('aaaa-bbbbb')
one way using ParseName
Select ParseName(Replace(SearchID , '-', '.'), 2)
from #test
and another using left and charindex
select distinct LEFT(SearchID ,CHARINDEX('-',SearchID )-1 )
from #test
and a third using case substring and left
select CASE substring(SearchID , 4 , 1) when '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END
from #test
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Subscribe to:
Posts (Atom)