Report with default of all

Have a report with three datasets. The first dataset pulls the
department table and uses this as one of the parameters.
select DEPT from dept
The second dataset pulls the division table and uses that as one of the
parameters.
select DIV from div
The third dataset calls a stored procedure that passes the startdate,
enddate, division and department for the report. With the startdate and
enddate also being parameters.
exec wo_sstm @.startdate, @.enddate, @.division, @.department
How can I set the parameters for @.division and @.department to utilize a
default of all for each.for your first dataset for dept:
select 0 as DeptID, '<All>' as DEPT FROM dept
UNION ALL
select DeptID, DEPT from dept
Then your value will be DeptID and label will be DEPT in Parameters
In your second dataset, same thing:
select 0 AS DivisionID, '<All>' as DIV from div
UNION ALL
select DivisionID, DIV from div
Then in your stored procedure wo_sstm you will process 0 as the indicator
for all.
=-Chris
"chad" <chad.heilig@.gmail.com> wrote in message
news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
> Have a report with three datasets. The first dataset pulls the
> department table and uses this as one of the parameters.
> select DEPT from dept
> The second dataset pulls the division table and uses that as one of the
> parameters.
> select DIV from div
> The third dataset calls a stored procedure that passes the startdate,
> enddate, division and department for the report. With the startdate and
> enddate also being parameters.
> exec wo_sstm @.startdate, @.enddate, @.division, @.department
> How can I set the parameters for @.division and @.department to utilize a
> default of all for each.
>|||Chris,
Getting invalid column name 'DeptID'. How does this work with no DeptID
column?
Chris Conner wrote:
> for your first dataset for dept:
> select 0 as DeptID, '<All>' as DEPT FROM dept
> UNION ALL
> select DeptID, DEPT from dept
> Then your value will be DeptID and label will be DEPT in Parameters
> In your second dataset, same thing:
> select 0 AS DivisionID, '<All>' as DIV from div
> UNION ALL
> select DivisionID, DIV from div
>
> Then in your stored procedure wo_sstm you will process 0 as the indicator
> for all.
> =-Chris
> "chad" <chad.heilig@.gmail.com> wrote in message
> news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
> > Have a report with three datasets. The first dataset pulls the
> > department table and uses this as one of the parameters.
> > select DEPT from dept
> >
> > The second dataset pulls the division table and uses that as one of the
> > parameters.
> > select DIV from div
> >
> > The third dataset calls a stored procedure that passes the startdate,
> > enddate, division and department for the report. With the startdate and
> > enddate also being parameters.
> > exec wo_sstm @.startdate, @.enddate, @.division, @.department
> >
> > How can I set the parameters for @.division and @.department to utilize a
> > default of all for each.
> >|||No - DeptID is something I made up - Do you have a column that uniquely
identifies your departments? I assumed you would understand that DeptID in
this case was an ID that uniquely identifies that department.
=-Chris
"chad" <chad.heilig@.gmail.com> wrote in message
news:1163601753.813127.152770@.h54g2000cwb.googlegroups.com...
> Chris,
> Getting invalid column name 'DeptID'. How does this work with no DeptID
> column?
>
> Chris Conner wrote:
>> for your first dataset for dept:
>> select 0 as DeptID, '<All>' as DEPT FROM dept
>> UNION ALL
>> select DeptID, DEPT from dept
>> Then your value will be DeptID and label will be DEPT in Parameters
>> In your second dataset, same thing:
>> select 0 AS DivisionID, '<All>' as DIV from div
>> UNION ALL
>> select DivisionID, DIV from div
>>
>> Then in your stored procedure wo_sstm you will process 0 as the indicator
>> for all.
>> =-Chris
>> "chad" <chad.heilig@.gmail.com> wrote in message
>> news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
>> > Have a report with three datasets. The first dataset pulls the
>> > department table and uses this as one of the parameters.
>> > select DEPT from dept
>> >
>> > The second dataset pulls the division table and uses that as one of the
>> > parameters.
>> > select DIV from div
>> >
>> > The third dataset calls a stored procedure that passes the startdate,
>> > enddate, division and department for the report. With the startdate and
>> > enddate also being parameters.
>> > exec wo_sstm @.startdate, @.enddate, @.division, @.department
>> >
>> > How can I set the parameters for @.division and @.department to utilize a
>> > default of all for each.
>> >
>

Report Server Windows Service Problem

Hello,

I am my department's Sys Admin and a relativelty Noobie when it comes to SQL server. So I recently Installed SQL Server 2005 on a Win2003 Server. During install I set my domain account as the service account login. After the DB and Web Application were setup and running good I realized that the SQL service accounts were logging in as my domain account. Not a good thing right? So in SQL Server Configuration Manager I changed the services from logging on as my domain account to the local system account instead.

Enter the problem: now I notice in application event viewer the Report Server Windows Service is erroring every minute. The error reads "Report server windows service cannot connect to the report server database." Im sure this was caused by me changing what account the report server service logs in as and local system account not having appropriate permissions. So what account should the report server windows service log in as? I see where to change the windows report service identity in Configure Report Server but I am not able to backup an encryption key? Thanks in advance!

If everything is installed locally I'd suggest you use LocalSystem although the setup suggests you use a domain account. Use the Reporting Services Configuration Manager (found under the Configuration Tools program group in the Microsoft SQL Server 2005 program group) to reset the Windows Service account. Since you don't have a backup of the encryption key, you may need to delete the encrypted content (Encryption Keys section) before you can reset the account. The following info may help. The moral of this story is that you should always back up the encryption key (you can use the config utility) as a last step after RS is set up.

Report Server 2005 - How to SUM value by condition

hi, i need some help on this.

I create Matrix that show the sales and cancels of the sales department.

The TOTAL in the Matrix are fine, but i need to show also how many Sales

(param is bigger then 0) and how many cancels (param is negative, smaller the 0) each

sales man have in the Matrix report. i try to use IIF statement but it only return absolute number:
--
="Sales :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Total :" & IIF(sum(Fields!SalesCommission.Value)<0, "(" & Format(ABS(sum(Fields!SalesCommission.Value)),"F1") & ")" ,sum(Fields!SalesCommission.Value))
--

I think i will need to use Costum Code on this one.

anny advice ?

Thanks

It looks like your IF statements are backwards from what you want. Your output for your "Sales:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is less then 0. Your output for your "Cancels:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is greater then 0. This is because IIF( [EXPRESSION], [TRUE PART], [FALSE PART]). So it should look like:

="Sales :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &

See what that does for you. Maybe I misunderstood/misread what you wanted for that part. Try and see what that does for you.

|||

Thanks, you are right, i replace the Sales and Cancels places.

I try your sample but again, the Matrix row have both Sales and Cancels in the Matrix TOTAL boxes and the EXPRESSION return the Total of the Sales O.K but the Total of the Cancels is 0 (zero).

What i need is to show in the MATRIX table the totals per salesman (Sales - (Minus) Cancels) and the clculatet Braek of

For exsaple:

Salse: 12.5

Cancels: -6.5

Total: 6

Anny Advice ?

|||So a "Cancels" is commision under 0 dollars. And a "Sales" is commission over 0 dollars. So you want to find how much commission total each sales person made? If I understand you correctly, all you need to do is sum(Fields!SalesCommission.Value) for your total. That should take care of everything. I hope this helps!

Report Server 2005 - How to SUM value by condition

hi, i need some help on this.

I create Matrix that show the sales and cancels of the sales department.

The TOTAL in the Matrix are fine, but i need to show also how many Sales

(param is bigger then 0) and how many cancels (param is negative, smaller the 0) each

sales man have in the Matrix report. i try to use IIF statement but it only return absolute number:
--
="Sales :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Total :" & IIF(sum(Fields!SalesCommission.Value)<0, "(" & Format(ABS(sum(Fields!SalesCommission.Value)),"F1") & ")" ,sum(Fields!SalesCommission.Value))
--

I think i will need to use Costum Code on this one.

anny advice ?

Thanks

It looks like your IF statements are backwards from what you want. Your output for your "Sales:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is less then 0. Your output for your "Cancels:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is greater then 0. This is because IIF( [EXPRESSION], [TRUE PART], [FALSE PART]). So it should look like:

="Sales :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &

See what that does for you. Maybe I misunderstood/misread what you wanted for that part. Try and see what that does for you.

|||

Thanks, you are right, i replace the Sales and Cancels places.

I try your sample but again, the Matrix row have both Sales and Cancels in the Matrix TOTAL boxes and the EXPRESSION return the Total of the Sales O.K but the Total of the Cancels is 0 (zero).

What i need is to show in the MATRIX table the totals per salesman (Sales - (Minus) Cancels) and the clculatet Braek of

For exsaple:

Salse: 12.5

Cancels: -6.5

Total: 6

Anny Advice ?

|||So a "Cancels" is commision under 0 dollars. And a "Sales" is commission over 0 dollars. So you want to find how much commission total each sales person made? If I understand you correctly, all you need to do is sum(Fields!SalesCommission.Value) for your total. That should take care of everything. I hope this helps!