I'm trying to see if this is possible. I have an employee table that
contains employee ID and manager ID. I'm trying to string together the
complete hierarchy for an employee in one row. So, if you have employee ID
1
reports to 2. Employee 2 reports to 3, then the result would be:
EmployeeID ReportingTo
1 2/3
Is this possible using nested sets? Can someone please give me some samples
of this?Have you searched google groups to see if there are solutions for similar
problems? If not look for "Joe Celko"+"nested sets".
Anith|||Susannah,
You should find a few useful things here:
http://groups.google.co.uk/groups? ...er+itzi
k
Steve Kass
Drew University
Susannah wrote:
>I'm trying to see if this is possible. I have an employee table that
>contains employee ID and manager ID. I'm trying to string together the
>complete hierarchy for an employee in one row. So, if you have employee ID
1
>reports to 2. Employee 2 reports to 3, then the result would be:
>EmployeeID ReportingTo
>1 2/3
>Is this possible using nested sets? Can someone please give me some sample
s
>of this?
>
Showing posts with label employee. Show all posts
Showing posts with label employee. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Nested report?
I want to create a report that retrieves data when I click on different
items on the same report page.
What I want to do is to first get an employee, and the hours registered for
this person at this period.
When I click on the person's name, I want to show a new report, that shows
all the clients that this person has registered hours working with this
period.
When I click on a client, I want to show a new report, that shows all the
projects that this person has worked on for this client during this period.
The projects should preferably be showed inside the table showing the
clients, so you can see what client the projects are connected to.
I've managed to create this with 3 different datasets in one report.
Unfortunately, the report will get all the data at once, but I want it to
get the project data when you click on a client, not when you load the
report. Is this possible?
All help appreciated!
Kaisa M. LindahlYou are describing a scenario where RS shines. From what I can tell you what
to do what is called drill through (open up new report) and drill down.
First drill through. Create a new report called Client. The client should
have a query parameter that accepts employee. When you create a query
parameter RS automatically creates a report parameter. Test this report
standalone and make sure it works. Now, in your employee report click on the
employee field and change the text to blue and underlined (so your users
know to click on it). The right mouse click, properties, advanced
properties, navigation. Jump to Report. Pick the client report and then map
the parameter for the client report to your employee field value.
Drill down works by have a single dataset with all the information. So it
would contain both client and project information. Then you add groups and
change row visiblity appropriately and what you end up with is where there
will be a plus sign on the row showing the client. When clicked on it
expands and shows all the projects for that client. Read up in help on how
to do this.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
What you are describing is called drill through reports. You should have
three reports: employee, clients, Instead of having all the datasets in one
report,
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:uWSLa3v0EHA.1860@.TK2MSFTNGP15.phx.gbl...
>I want to create a report that retrieves data when I click on different
> items on the same report page.
> What I want to do is to first get an employee, and the hours registered
> for
> this person at this period.
> When I click on the person's name, I want to show a new report, that shows
> all the clients that this person has registered hours working with this
> period.
> When I click on a client, I want to show a new report, that shows all the
> projects that this person has worked on for this client during this
> period.
> The projects should preferably be showed inside the table showing the
> clients, so you can see what client the projects are connected to.
> I've managed to create this with 3 different datasets in one report.
> Unfortunately, the report will get all the data at once, but I want it to
> get the project data when you click on a client, not when you load the
> report. Is this possible?
> All help appreciated!
> Kaisa M. Lindahl
>|||There are two ways in which you can achieve this, either using drill-down or
using linked reports. Or you could do a combination of both methods. Take the
drill-down method first:
Write a single query that returns employee, client and project information.
For simplicity, if we assume there is one table for each, the query will look
something like:
select col1, col2,.. from employee join client ... join project ...
Next create a grouped report with employee information in the top level
group, client information in the second group and project information in the
details. highlight the entire detail row (not just the textboxes) by clicking
on the row header on the left (it has three dashes). In the properties
window, make the Hidden property true (the property is a subproperty of the
Visibility property), and specify the ToggleItem as a textbox in the second
group row. This means that initially all detail rows containing project rows
will be hidden until you click a textbox in the group above.
You want to do this for the second group row too. Highlight the entire row
by clicking on the row selector. make the Hidden property true and specify
the ToggleItem property to be a textbox in the outer group row.
Now when you open your report, initially only employee information will
show. There will be a + icon to the left of each employee name to show that
there is hidden information. Click on the + and you will see all the clients
for that employee. Click on the + to the left of a client and you will see
all the projects for that client.
The second way is to create a separate report say for the projects
information. It should have a parameter that takes ClientID. In your first
report, just have the employee and client rows. In the first report, go to
the row with client information, click on a textbox and access the Action
property. Click on the ellipsis button to the right of it, and specify Jump
to Report option. Specify the report with project information, and specify
the parameters. This time the project information will appear as a popup
report.
HTH
Charles Kangai, MCT, MCDBA
"Kaisa M. Lindahl" wrote:
> I want to create a report that retrieves data when I click on different
> items on the same report page.
> What I want to do is to first get an employee, and the hours registered for
> this person at this period.
> When I click on the person's name, I want to show a new report, that shows
> all the clients that this person has registered hours working with this
> period.
> When I click on a client, I want to show a new report, that shows all the
> projects that this person has worked on for this client during this period.
> The projects should preferably be showed inside the table showing the
> clients, so you can see what client the projects are connected to.
> I've managed to create this with 3 different datasets in one report.
> Unfortunately, the report will get all the data at once, but I want it to
> get the project data when you click on a client, not when you load the
> report. Is this possible?
> All help appreciated!
> Kaisa M. Lindahl
>
>|||Thank you for answering, but at least the first method doesn't do what I
want to do. It makes a splendid report, I've already made one that does
this. My problem is that the report is a really big OLAP-based report, so
having a query that returns ALL data from all projects and clients takes a
few minutes. That's why I want a report that doesn't retrieve the project
data before you click on a link, to shorten the time for the time it takes
to open the report and see the top level data.
The second method looks interesting, so I might try that one out.
Also, Bruce L-C has made some suggestions in another reply, so I've got even
more things to try out.
Thanks, guys!
Kaisa M. Lindahl
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:8EC66727-246F-48B9-B2DC-E2E521B45804@.microsoft.com...
> There are two ways in which you can achieve this, either using drill-down
or
> using linked reports. Or you could do a combination of both methods. Take
the
> drill-down method first:
> Write a single query that returns employee, client and project
information.
> For simplicity, if we assume there is one table for each, the query will
look
> something like:
> select col1, col2,.. from employee join client ... join project ...
> Next create a grouped report with employee information in the top level
> group, client information in the second group and project information in
the
> details. highlight the entire detail row (not just the textboxes) by
clicking
> on the row header on the left (it has three dashes). In the properties
> window, make the Hidden property true (the property is a subproperty of
the
> Visibility property), and specify the ToggleItem as a textbox in the
second
> group row. This means that initially all detail rows containing project
rows
> will be hidden until you click a textbox in the group above.
> You want to do this for the second group row too. Highlight the entire row
> by clicking on the row selector. make the Hidden property true and specify
> the ToggleItem property to be a textbox in the outer group row.
> Now when you open your report, initially only employee information will
> show. There will be a + icon to the left of each employee name to show
that
> there is hidden information. Click on the + and you will see all the
clients
> for that employee. Click on the + to the left of a client and you will see
> all the projects for that client.
> The second way is to create a separate report say for the projects
> information. It should have a parameter that takes ClientID. In your first
> report, just have the employee and client rows. In the first report, go to
> the row with client information, click on a textbox and access the Action
> property. Click on the ellipsis button to the right of it, and specify
Jump
> to Report option. Specify the report with project information, and specify
> the parameters. This time the project information will appear as a popup
> report.
> HTH
> Charles Kangai, MCT, MCDBA
> "Kaisa M. Lindahl" wrote:
> > I want to create a report that retrieves data when I click on different
> > items on the same report page.
> > What I want to do is to first get an employee, and the hours registered
for
> > this person at this period.
> > When I click on the person's name, I want to show a new report, that
shows
> > all the clients that this person has registered hours working with this
> > period.
> > When I click on a client, I want to show a new report, that shows all
the
> > projects that this person has worked on for this client during this
period.
> > The projects should preferably be showed inside the table showing the
> > clients, so you can see what client the projects are connected to.
> >
> > I've managed to create this with 3 different datasets in one report.
> > Unfortunately, the report will get all the data at once, but I want it
to
> > get the project data when you click on a client, not when you load the
> > report. Is this possible?
> >
> > All help appreciated!
> >
> > Kaisa M. Lindahl
> >
> >
> >
items on the same report page.
What I want to do is to first get an employee, and the hours registered for
this person at this period.
When I click on the person's name, I want to show a new report, that shows
all the clients that this person has registered hours working with this
period.
When I click on a client, I want to show a new report, that shows all the
projects that this person has worked on for this client during this period.
The projects should preferably be showed inside the table showing the
clients, so you can see what client the projects are connected to.
I've managed to create this with 3 different datasets in one report.
Unfortunately, the report will get all the data at once, but I want it to
get the project data when you click on a client, not when you load the
report. Is this possible?
All help appreciated!
Kaisa M. LindahlYou are describing a scenario where RS shines. From what I can tell you what
to do what is called drill through (open up new report) and drill down.
First drill through. Create a new report called Client. The client should
have a query parameter that accepts employee. When you create a query
parameter RS automatically creates a report parameter. Test this report
standalone and make sure it works. Now, in your employee report click on the
employee field and change the text to blue and underlined (so your users
know to click on it). The right mouse click, properties, advanced
properties, navigation. Jump to Report. Pick the client report and then map
the parameter for the client report to your employee field value.
Drill down works by have a single dataset with all the information. So it
would contain both client and project information. Then you add groups and
change row visiblity appropriately and what you end up with is where there
will be a plus sign on the row showing the client. When clicked on it
expands and shows all the projects for that client. Read up in help on how
to do this.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
What you are describing is called drill through reports. You should have
three reports: employee, clients, Instead of having all the datasets in one
report,
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:uWSLa3v0EHA.1860@.TK2MSFTNGP15.phx.gbl...
>I want to create a report that retrieves data when I click on different
> items on the same report page.
> What I want to do is to first get an employee, and the hours registered
> for
> this person at this period.
> When I click on the person's name, I want to show a new report, that shows
> all the clients that this person has registered hours working with this
> period.
> When I click on a client, I want to show a new report, that shows all the
> projects that this person has worked on for this client during this
> period.
> The projects should preferably be showed inside the table showing the
> clients, so you can see what client the projects are connected to.
> I've managed to create this with 3 different datasets in one report.
> Unfortunately, the report will get all the data at once, but I want it to
> get the project data when you click on a client, not when you load the
> report. Is this possible?
> All help appreciated!
> Kaisa M. Lindahl
>|||There are two ways in which you can achieve this, either using drill-down or
using linked reports. Or you could do a combination of both methods. Take the
drill-down method first:
Write a single query that returns employee, client and project information.
For simplicity, if we assume there is one table for each, the query will look
something like:
select col1, col2,.. from employee join client ... join project ...
Next create a grouped report with employee information in the top level
group, client information in the second group and project information in the
details. highlight the entire detail row (not just the textboxes) by clicking
on the row header on the left (it has three dashes). In the properties
window, make the Hidden property true (the property is a subproperty of the
Visibility property), and specify the ToggleItem as a textbox in the second
group row. This means that initially all detail rows containing project rows
will be hidden until you click a textbox in the group above.
You want to do this for the second group row too. Highlight the entire row
by clicking on the row selector. make the Hidden property true and specify
the ToggleItem property to be a textbox in the outer group row.
Now when you open your report, initially only employee information will
show. There will be a + icon to the left of each employee name to show that
there is hidden information. Click on the + and you will see all the clients
for that employee. Click on the + to the left of a client and you will see
all the projects for that client.
The second way is to create a separate report say for the projects
information. It should have a parameter that takes ClientID. In your first
report, just have the employee and client rows. In the first report, go to
the row with client information, click on a textbox and access the Action
property. Click on the ellipsis button to the right of it, and specify Jump
to Report option. Specify the report with project information, and specify
the parameters. This time the project information will appear as a popup
report.
HTH
Charles Kangai, MCT, MCDBA
"Kaisa M. Lindahl" wrote:
> I want to create a report that retrieves data when I click on different
> items on the same report page.
> What I want to do is to first get an employee, and the hours registered for
> this person at this period.
> When I click on the person's name, I want to show a new report, that shows
> all the clients that this person has registered hours working with this
> period.
> When I click on a client, I want to show a new report, that shows all the
> projects that this person has worked on for this client during this period.
> The projects should preferably be showed inside the table showing the
> clients, so you can see what client the projects are connected to.
> I've managed to create this with 3 different datasets in one report.
> Unfortunately, the report will get all the data at once, but I want it to
> get the project data when you click on a client, not when you load the
> report. Is this possible?
> All help appreciated!
> Kaisa M. Lindahl
>
>|||Thank you for answering, but at least the first method doesn't do what I
want to do. It makes a splendid report, I've already made one that does
this. My problem is that the report is a really big OLAP-based report, so
having a query that returns ALL data from all projects and clients takes a
few minutes. That's why I want a report that doesn't retrieve the project
data before you click on a link, to shorten the time for the time it takes
to open the report and see the top level data.
The second method looks interesting, so I might try that one out.
Also, Bruce L-C has made some suggestions in another reply, so I've got even
more things to try out.
Thanks, guys!
Kaisa M. Lindahl
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:8EC66727-246F-48B9-B2DC-E2E521B45804@.microsoft.com...
> There are two ways in which you can achieve this, either using drill-down
or
> using linked reports. Or you could do a combination of both methods. Take
the
> drill-down method first:
> Write a single query that returns employee, client and project
information.
> For simplicity, if we assume there is one table for each, the query will
look
> something like:
> select col1, col2,.. from employee join client ... join project ...
> Next create a grouped report with employee information in the top level
> group, client information in the second group and project information in
the
> details. highlight the entire detail row (not just the textboxes) by
clicking
> on the row header on the left (it has three dashes). In the properties
> window, make the Hidden property true (the property is a subproperty of
the
> Visibility property), and specify the ToggleItem as a textbox in the
second
> group row. This means that initially all detail rows containing project
rows
> will be hidden until you click a textbox in the group above.
> You want to do this for the second group row too. Highlight the entire row
> by clicking on the row selector. make the Hidden property true and specify
> the ToggleItem property to be a textbox in the outer group row.
> Now when you open your report, initially only employee information will
> show. There will be a + icon to the left of each employee name to show
that
> there is hidden information. Click on the + and you will see all the
clients
> for that employee. Click on the + to the left of a client and you will see
> all the projects for that client.
> The second way is to create a separate report say for the projects
> information. It should have a parameter that takes ClientID. In your first
> report, just have the employee and client rows. In the first report, go to
> the row with client information, click on a textbox and access the Action
> property. Click on the ellipsis button to the right of it, and specify
Jump
> to Report option. Specify the report with project information, and specify
> the parameters. This time the project information will appear as a popup
> report.
> HTH
> Charles Kangai, MCT, MCDBA
> "Kaisa M. Lindahl" wrote:
> > I want to create a report that retrieves data when I click on different
> > items on the same report page.
> > What I want to do is to first get an employee, and the hours registered
for
> > this person at this period.
> > When I click on the person's name, I want to show a new report, that
shows
> > all the clients that this person has registered hours working with this
> > period.
> > When I click on a client, I want to show a new report, that shows all
the
> > projects that this person has worked on for this client during this
period.
> > The projects should preferably be showed inside the table showing the
> > clients, so you can see what client the projects are connected to.
> >
> > I've managed to create this with 3 different datasets in one report.
> > Unfortunately, the report will get all the data at once, but I want it
to
> > get the project data when you click on a client, not when you load the
> > report. Is this possible?
> >
> > All help appreciated!
> >
> > Kaisa M. Lindahl
> >
> >
> >
Nested Query for newbie?
Hi
thanks for reading
I have 2 tables. The first has employee information and
the second has
payroll information. I need to find out people who are not
in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter
it and find
out for each payroll.
I don't think i have explained it very well so here is the
data set.
hope someone can help me with this.
Thanks in advance
prit
Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333
TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333
Required RESULT required(Missing employees for each pay
period)
IDNumSSN
1333333333
2111111111
If you have a table with all the IDNum values (IDNum represents a pay
period?), use it, or if not,
(select Distinct IDNum from TblPayrolldetail) IDs
where I have
IDs
select IDs.IDNum, [Tbl Employee] SSN
from IDs, [Tbl Employee]
where not exists (
select * from TblPayrolldetail D
where D.IDNum = IDs.IDNum
and D.SSN = [TblEmployee].SSN
)
Steve Kass
Drew University
PM wrote:
>Hi
>thanks for reading
>I have 2 tables. The first has employee information and
>the second has
>payroll information. I need to find out people who are not
>in the
>payroll but in the employee table.
>Since the payroll has multiple instances i have to filter
>it and find
>out for each payroll.
>I don't think i have explained it very well so here is the
>data set.
>hope someone can help me with this.
>Thanks in advance
>prit
>
>Tbl Employee
>PlanIDSSN
>1001111111111
>1001222222222
>1001333333333
>TblPayrolldetail
>IDNumPlanID SSN
>11001111111111
>11001222222222
>21001222222222
>21001333333333
>Required RESULT required(Missing employees for each pay
>period)
>IDNumSSN
>1333333333
>2111111111
>
>
thanks for reading
I have 2 tables. The first has employee information and
the second has
payroll information. I need to find out people who are not
in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter
it and find
out for each payroll.
I don't think i have explained it very well so here is the
data set.
hope someone can help me with this.
Thanks in advance
prit
Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333
TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333
Required RESULT required(Missing employees for each pay
period)
IDNumSSN
1333333333
2111111111
If you have a table with all the IDNum values (IDNum represents a pay
period?), use it, or if not,
(select Distinct IDNum from TblPayrolldetail) IDs
where I have
IDs
select IDs.IDNum, [Tbl Employee] SSN
from IDs, [Tbl Employee]
where not exists (
select * from TblPayrolldetail D
where D.IDNum = IDs.IDNum
and D.SSN = [TblEmployee].SSN
)
Steve Kass
Drew University
PM wrote:
>Hi
>thanks for reading
>I have 2 tables. The first has employee information and
>the second has
>payroll information. I need to find out people who are not
>in the
>payroll but in the employee table.
>Since the payroll has multiple instances i have to filter
>it and find
>out for each payroll.
>I don't think i have explained it very well so here is the
>data set.
>hope someone can help me with this.
>Thanks in advance
>prit
>
>Tbl Employee
>PlanIDSSN
>1001111111111
>1001222222222
>1001333333333
>TblPayrolldetail
>IDNumPlanID SSN
>11001111111111
>11001222222222
>21001222222222
>21001333333333
>Required RESULT required(Missing employees for each pay
>period)
>IDNumSSN
>1333333333
>2111111111
>
>
Wednesday, March 7, 2012
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?
>
Subscribe to:
Posts (Atom)