Nested CASE statements Problem

I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'

I think there are two problems:

(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE

(b) Each CASE WHEN needs an END

I was not able to completely modify your T-SQL...but your changes should look similar to:

select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end

Peter

|||I think I probably should just use IF statements inside my first CASE statement|||

Well, this throws an error also.

The error is:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'IF'.

Msg 147, Level 15, State 1, Line 5

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Msg 147, Level 15, State 1, Line 9

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

END

ELSE

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + (rf.rmstranamt)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)

END

END

END AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

|||

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB < ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax

|||

typo on my last statement (change is bolditalic)

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB > ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.

otherwise, just take off the case words from inside (to have a correct syntax)

Nested CASE statements Problem

I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'

I think there are two problems:

(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE

(b) Each CASE WHEN needs an END

I was not able to completely modify your T-SQL...but your changes should look similar to:

select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end

Peter

|||I think I probably should just use IF statements inside my first CASE statement|||

Well, this throws an error also.

The error is:

Msg 156, Level 15, State 1, Line 5

Incorrect syntax near the keyword 'IF'.

Msg 147, Level 15, State 1, Line 5

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Msg 147, Level 15, State 1, Line 9

SELECT rm.rmsacctnum AS [Rms Acct Num],

SUM(rf.rmstranamt) AS [TranSum],

SUM(rf10.rmstranamt10) AS [10Sum],

CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

END

ELSE

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + (rf.rmstranamt)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0

BEGIN

SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)

END

IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0

BEGIN

SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)

END

END

END AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

|||

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB < ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax

|||

typo on my last statement (change is bolditalic)

yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .

case 1 and Case 4 of your outer else are the same. . .

should it have read:

ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then

now if your first test in the outer case:

if ArgA > ArgB then this is never true:

ArgA < 0 AND ArgB > 0

then wouldn't all of the first half of your outer case logic translate to

if ArgA>ArgB then ArgA - Abs(ArgB)

now the else part:

if ArgB > ArgA then this is never true:

ArgA > 0 AND ArgB < 0

then wouldn't all of the else half of your outer case logic translate to

ArgB - abs(ArgA)

so simply put, wont this do it -

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

|||

sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.

otherwise, just take off the case words from inside (to have a correct syntax)

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?

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?
>