Hide Folders

Hi All,
I have a list of folders per department
i.e.
HR
Sales
etc
Each of these folders are assigned a group and have been given the default
role of browswer
However when the user logs into the reporting services site, they can see
all the folders - go into them however if they try to browse a report which
they do not have permissions to they get a Page cannot be dsiplayed error?
Surely there is a better way of doing this.
When my user goes into RS, I would either like them to see only the folder
they have been assigned to or at least not even be able to access the
folders at all that they have not been assigned to.
How do I do this.
Please help.
Thanks
KNot sure what you mean. If a user does not have access to an item on the
server, it will not display in Report Manager. Try removing all role
assignments from an item and see if they can see it.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kathy" <joe@.soap.com> wrote in message
news:poydnaDzeqHOL3rcRVn-pQ@.is.co.za...
> Hi All,
> I have a list of folders per department
> i.e.
> HR
> Sales
> etc
> Each of these folders are assigned a group and have been given the default
> role of browswer
> However when the user logs into the reporting services site, they can see
> all the folders - go into them however if they try to browse a report
> which
> they do not have permissions to they get a Page cannot be dsiplayed error?
> Surely there is a better way of doing this.
> When my user goes into RS, I would either like them to see only the folder
> they have been assigned to or at least not even be able to access the
> folders at all that they have not been assigned to.
> How do I do this.
> Please help.
> Thanks
> K
>sql

Hide an entire row if data in one firld is 0 or null

I want to be able to remove rows from the report if certain data in the row is 0. If a sales division has no sales for a district. there is no reason to show the district name or even to show an empty space for the row. I want to suppress the entire row without the user being able to see it or unhide it.

Is this possible?

You can accomplish this using filters. You can filter at the dataset, data region and grouping levels. You could also modify the SQL query to filter the data using the WHERE clause.

Here is more information on filter data in a Report.

http://msdn2.microsoft.com/en-us/library/ms157307.aspx

Ian|||This will not work in the current context. There are multiple fields that get grouped at a division/region level

Hide 0 value

Hello,

I made a calculed member

with this formule distinctcount([sales order].members)

I would like that the "0" value doesn't appear.

Is it possible ?

Sure. Use the IIF statement in your calculated member to evaluate the value. If yoe return a value of 0, substitute a another value in it's place. You could substitute NULL or even a string such as "n/a".

Good luck,

Bryan

|||

I tried this formule

iif(distinctcount([no appels].members)=0,NULL,distinctcount([no appels].members))

and I have an error :

distinctcount must be used as a function of higher level (most external)

|||

I find the answer,

I created a new calculed member and I could use the iif to exclude the 0 values.

sql

heterogeneous Function

Hello Everyone,
I have a procedure which returns profits made by sales persons.
for this I need to access 2 data bases.
to calculate the profit I have written a function on one of the databases
but I get an error asking me to set ANSI_NULLS and ANSI_WARNINGS. when I set
these I get syntax error.
Am I missing to do something here.
Any help is greatly appreciated.
Rose.Without a repro, here is a guess: Make sure you have the settings while
creating the function.
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE FUNCTION ufn_test(...
...
GO
Anith

HelpColumnar Report with 3 Groupings?

Hi

I have sales data that needs to be grouped by Company, State and
Date. I would like to format the report as shown in image.

I am using Crystal Reports XI. I am new to Crystal Reports and
would like to find out whether this report is possible using either
columnar format or cross-tab?

Many ThanksPost a sample of your table(s). I can't help without knowing what you have to start with.

I have a feeling you'll need a crosstab|||Hi

I have data in two tables - Sales and Dealers.

Sales
-------
DealerCode
WeekEnding
NumberSold

Dealers
-------
DealerCode
CompanyName
State

I have a command in Crystal as follows:

SELECT d.CompanyName, d.State, s.WeekEnding, s.NumberSold
FROM sales s
LEFT JOIN dealers d
ON s.DealerCode = d.DealerCode
AND s.WeekEnding BETWEEN '2006-04-01' AND '2006-04-30'

I would like to group the data returned by CompanyName, State and Date to get a Total of NumberSold.

I would like to format the report in columns (as in image attachment). I would also like to have a two week total and a week-on-week figure.

If you need any further info, let me know. Thanks for your help!|||Look for cross tab reports in help file

Help: query to get total last 7 day sales for each day

Let say I have this table in MS SQL server
table transaction(date,sales)

how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.

note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day

thanksselect date,sum(sales) from table where date between (getdate()-7) and getdate()
group by date|||That will get you sales within the last 7x24 hours, because getdate returns the full date and time. Maybe that's what you want, but if you are looking at full sales days you should try the datediff function:

select convert(char(10), date, 120), sum(sales)
from table
where datediff(yourdate, getdate()) < 7 --(or 6, depending upon whether you want to count the current day)
group by convert(char(10), date, 120)