Showing posts with label reporting. Show all posts
Showing posts with label reporting. Show all posts

Thursday, March 29, 2012

Filter in stored procedure

I am trying to edit this query i already have that is fully functional for another report in reporting services. The report has visits sales reps made to stores, broken down by period, week and then date. Now, i need to filter it out by Visits. They want the Sales Reps who had less then 6 visits a day. How would i code that out in my stored procedure? When i have Stores and Account Status( which makes it pretty broken down). Any suggestions. Thanks..heres the code.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

END

SET NOCOUNT OFF

If dbo.Qry_Visits.Visits stores the cumulative count of the visits made then you can add 'Qry_Visits.Visits < 6' to the where clause. To make it more generic instead of hard coding values, you may want to have to edit the signature of the proc to have an input parameter that represents the number of visits also.

|||

try:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.Visits >= @.visit_req

END

SET NOCOUNT OFF

|||it does not show the cumulative. It shows one visit per sales person, per date. So its really broken down. So if i put <6 it will show everything, because each record has one visit. I only want the ones with less then 6 visits by a Sales Rep. Which means the sum of visits per Sales Rep, per day. How would i get that?|||

I don't really understand the data structure, but is possible to just do an aggregate similar to:

Code Snippet

Pseudo Code:

SELECT YourColumns, SUM(Visits) AS Visits
FROM AllThoseTables
GROUP BY YourColumns
HAVING SUM(Visits) > 5

|||

Then do:

ALTER PROCEDURE [dbo].[Testing_Visits_Exception]

(@.Region_Key int=null)

AS

BEGIN

declare @.visit_req int

set @.visit_req=6

--only return rows that have at least 6 visits

SELECT dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code]

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

WHERE REGION_KEY=@.Region_Key

and dbo.Qry_Visits.[SR Code] NOT IN(select [SR Code] from dbo.Qry_Visits

group by [SR Code]

having count(Visits) < @.visit_req)

END

SET NOCOUNT OFF

filter in Reporting Services: Best way?

Hi there,
I'm a newbe to reportig services and try to make up a financial report:
store currentYear pastYear Delta DeltaIn%PastYear
I tried for several hours with the table control:
- Gain the date with a query from ssas
- set a parameter for the year
- put several tables on the report and set a filter to the year dimension
The problem I have with this approach is:
- Formatting: I would prefer one table
- I don't know how to calculate the deltas (currentYear-pastYear)
Regards
TomPressed the post button to fast...
My questions are:
- Can I set a filter on a column in a table control?
- Can I calculate in a table control a value, that derives from other
columns in the same or different tables?
- Or is it better to use different controls (like the matrix or textboxes)?
Please help me - I have a deadline and running out of time!
Thanks in advance,
Tom
"Tomilee" wrote:
> Hi there,
> I'm a newbe to reportig services and try to make up a financial report:
> store currentYear pastYear Delta DeltaIn%PastYear
> I tried for several hours with the table control:
> - Gain the date with a query from ssas
> - set a parameter for the year
> - put several tables on the report and set a filter to the year dimension
> The problem I have with this approach is:
> - Formatting: I would prefer one table
> - I don't know how to calculate the deltas (currentYear-pastYear)
> Regards
> Tom|||Hi,
Just answering to your questions.
1. Can I set a filter on a column in a table control?
No for the full row you can set filter.
2. Can I calculate in a table control a value, that derives from other
columns in the same or different tables?
Yes very much. using <Expressions> option.
Regards
Amarnath
"Tomilee" wrote:
> Pressed the post button to fast...
> My questions are:
> - Can I set a filter on a column in a table control?
> - Can I calculate in a table control a value, that derives from other
> columns in the same or different tables?
> - Or is it better to use different controls (like the matrix or textboxes)?
> Please help me - I have a deadline and running out of time!
> Thanks in advance,
> Tom
> "Tomilee" wrote:
> > Hi there,
> >
> > I'm a newbe to reportig services and try to make up a financial report:
> >
> > store currentYear pastYear Delta DeltaIn%PastYear
> >
> > I tried for several hours with the table control:
> > - Gain the date with a query from ssas
> > - set a parameter for the year
> > - put several tables on the report and set a filter to the year dimension
> >
> > The problem I have with this approach is:
> > - Formatting: I would prefer one table
> > - I don't know how to calculate the deltas (currentYear-pastYear)
> >
> > Regards
> >
> > Tom|||Tomilee,
to get store currentYear pastYear Delta DeltaIn%PastYear
you should do all of this in the MDX query ( you mentioned using SSAS)...
To get the PastYear info, look into the ParallelPeriod function in MDX. the
Delta is current - ParallelPeriod, etc
in answer to your other questions... you can NOT put a filter on a textbox,
but you can filter on a group, table, etc... But what you can do is use a
conditional if on the textbox expression... ie
IIF (Fields!fieldname.Value > 5,truepart, falsepart)
Using this you might can get the effect of a filter..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tomilee" wrote:
> Hi there,
> I'm a newbe to reportig services and try to make up a financial report:
> store currentYear pastYear Delta DeltaIn%PastYear
> I tried for several hours with the table control:
> - Gain the date with a query from ssas
> - set a parameter for the year
> - put several tables on the report and set a filter to the year dimension
> The problem I have with this approach is:
> - Formatting: I would prefer one table
> - I don't know how to calculate the deltas (currentYear-pastYear)
> Regards
> Tom|||Thanks for your Help! Tom
"Wayne Snyder" wrote:
> Tomilee,
> to get store currentYear pastYear Delta DeltaIn%PastYear
> you should do all of this in the MDX query ( you mentioned using SSAS)...
> To get the PastYear info, look into the ParallelPeriod function in MDX. the
> Delta is current - ParallelPeriod, etc
> in answer to your other questions... you can NOT put a filter on a textbox,
> but you can filter on a group, table, etc... But what you can do is use a
> conditional if on the textbox expression... ie
> IIF (Fields!fieldname.Value > 5,truepart, falsepart)
> Using this you might can get the effect of a filter..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Tomilee" wrote:
> > Hi there,
> >
> > I'm a newbe to reportig services and try to make up a financial report:
> >
> > store currentYear pastYear Delta DeltaIn%PastYear
> >
> > I tried for several hours with the table control:
> > - Gain the date with a query from ssas
> > - set a parameter for the year
> > - put several tables on the report and set a filter to the year dimension
> >
> > The problem I have with this approach is:
> > - Formatting: I would prefer one table
> > - I don't know how to calculate the deltas (currentYear-pastYear)
> >
> > Regards
> >
> > Tom

Filter Help Needed

Good Day to All,
Need some help with Filters in SQL Reporting 2000.
Have a table report setup with a DS going to a Store Proc. need to add a
filter that is a bit complex. Don't know which filter section I should be
putting this in but in all cases I've run into errors.
Basically when the data is brought back from my Store Proc, I need to filter
out the data according to the following:
IF CBalance < 0
THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
ELSE (TTLBalance > (CBalance*0.25))
I keep getting this error that the filter comparison fails and I should
check the data Types returned by the filter expression.
Please help...you might want to cast your fields as varchar in your query. This has
been the answer to many filter comparison problems I have had in the
past.
Eric wrote:
> Good Day to All,
> Need some help with Filters in SQL Reporting 2000.
> Have a table report setup with a DS going to a Store Proc. need to add a
> filter that is a bit complex. Don't know which filter section I should be
> putting this in but in all cases I've run into errors.
> Basically when the data is brought back from my Store Proc, I need to filter
> out the data according to the following:
> IF CBalance < 0
> THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> ELSE (TTLBalance > (CBalance*0.25))
> I keep getting this error that the filter comparison fails and I should
> check the data Types returned by the filter expression.
> Please help...|||Thanks Topher that was it. Odd the filter section can't handle comparisons
other then varchar / string like values. I wonder if this carried over into
the newer version.
Thanks again!!!
"Topher" wrote:
> you might want to cast your fields as varchar in your query. This has
> been the answer to many filter comparison problems I have had in the
> past.
>
> Eric wrote:
> > Good Day to All,
> > Need some help with Filters in SQL Reporting 2000.
> > Have a table report setup with a DS going to a Store Proc. need to add a
> > filter that is a bit complex. Don't know which filter section I should be
> > putting this in but in all cases I've run into errors.
> >
> > Basically when the data is brought back from my Store Proc, I need to filter
> > out the data according to the following:
> > IF CBalance < 0
> > THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> > ELSE (TTLBalance > (CBalance*0.25))
> >
> > I keep getting this error that the filter comparison fails and I should
> > check the data Types returned by the filter expression.
> >
> > Please help...
>

filter expressions combined with OR instead of AND

I need to combine filter expressions for my report item with the OR-logic -
but the Reporting Services dialog window is set on the AND-logic. How can I
get past this?
Example
======
What I need is the following:
Fields!Name1 = Parameters!Name
OR
Fields!Name2 = Parameters!Name
What I get from Reporting Services is the following:
Fields!Name1 = Parameters!Name
AND
Fields!Name2 = Parameters!Name
Thank you for your help.I think you can set that in the Expression builder, there is a column called
condition... But if for some reason it doesn't work properly go to the
generic query builder and simply type your expression
Sorry - you are talking about the filter... You could make a function in
the code section, pass both of the field values and parameter values into
the function and return 1 to include the row and 0 to exclude it...Then your
filter expression would be
GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
> I need to combine filter expressions for my report item with the
OR-logic -
> but the Reporting Services dialog window is set on the AND-logic. How can
I
> get past this?
> Example
> ======> What I need is the following:
> Fields!Name1 = Parameters!Name
> OR
> Fields!Name2 = Parameters!Name
> What I get from Reporting Services is the following:
> Fields!Name1 = Parameters!Name
> AND
> Fields!Name2 = Parameters!Name
> Thank you for your help.
>
>|||Dear Wayne
Thank you for your advice. However, I am a controller, not a programmer.
When I am working with RS, I only use the graphic interface - never the code
section... (-;
Is there not a more user-friendly (controller-friendly...) way around this
problem?
Thanks again.
"Wayne Snyder" wrote:
> I think you can set that in the Expression builder, there is a column called
> condition... But if for some reason it doesn't work properly go to the
> generic query builder and simply type your expression
> Sorry - you are talking about the filter... You could make a function in
> the code section, pass both of the field values and parameter values into
> the function and return 1 to include the row and 0 to exclude it...Then your
> filter expression would be
> GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
> news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
> > I need to combine filter expressions for my report item with the
> OR-logic -
> > but the Reporting Services dialog window is set on the AND-logic. How can
> I
> > get past this?
> >
> > Example
> > ======> >
> > What I need is the following:
> >
> > Fields!Name1 = Parameters!Name
> > OR
> > Fields!Name2 = Parameters!Name
> >
> > What I get from Reporting Services is the following:
> >
> > Fields!Name1 = Parameters!Name
> > AND
> > Fields!Name2 = Parameters!Name
> >
> > Thank you for your help.
> >
> >
> >
>
>|||Hi,
I also had this problem and discovered that if you put the common filed
first Reporting Services will give you an OR.
So if youy enter your parameters as follows:
Parameters!Name = Fields!Name1
then
Parameters!Name = Fields!Name2
Reporting Services will generate:
Parameters!Name = Fields!Name1
OR
Parameters!Name = Fields!Name2
Steve
"j schuetz" wrote:
> I need to combine filter expressions for my report item with the OR-logic -
> but the Reporting Services dialog window is set on the AND-logic. How can I
> get past this?
> Example
> ======> What I need is the following:
> Fields!Name1 = Parameters!Name
> OR
> Fields!Name2 = Parameters!Name
> What I get from Reporting Services is the following:
> Fields!Name1 = Parameters!Name
> AND
> Fields!Name2 = Parameters!Name
> Thank you for your help.
>
>|||Too bad, but at least you know the differences between AND and OR :))
In any case, you'd be in a much better shape if you do filtering in
your SQL query, not in the report itself. RS filtering is evil,
because it makes SQL server pull ALL the data from the query, just to
throw away some of it later. It would be much more efficient (and
easier) to apply your filtering in your query (dataset):
SELECT ...
FROM ...
WHERE Name1 = @.paramName OR Name2 = @.paramName
On Mon, 24 Jan 2005 07:13:06 -0800, j schuetz
<jschuetz@.discussions.microsoft.com> wrote:
>Dear Wayne
>Thank you for your advice. However, I am a controller, not a programmer.
>When I am working with RS, I only use the graphic interface - never the code
>section... (-;
>Is there not a more user-friendly (controller-friendly...) way around this
>problem?
>Thanks again.
>
>"Wayne Snyder" wrote:
>> I think you can set that in the Expression builder, there is a column called
>> condition... But if for some reason it doesn't work properly go to the
>> generic query builder and simply type your expression
>> Sorry - you are talking about the filter... You could make a function in
>> the code section, pass both of the field values and parameter values into
>> the function and return 1 to include the row and 0 to exclude it...Then your
>> filter expression would be
>> GetFilter(Fields!Name1.Value,Fields!Name2.Value, Parameters!Name) = 1
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "j schuetz" <j schuetz@.discussions.microsoft.com> wrote in message
>> news:E5CA33E2-B55D-496D-A48D-C94892135151@.microsoft.com...
>> > I need to combine filter expressions for my report item with the
>> OR-logic -
>> > but the Reporting Services dialog window is set on the AND-logic. How can
>> I
>> > get past this?
>> >
>> > Example
>> > ======>> >
>> > What I need is the following:
>> >
>> > Fields!Name1 = Parameters!Name
>> > OR
>> > Fields!Name2 = Parameters!Name
>> >
>> > What I get from Reporting Services is the following:
>> >
>> > Fields!Name1 = Parameters!Name
>> > AND
>> > Fields!Name2 = Parameters!Name
>> >
>> > Thank you for your help.
>> >
>> >
>> >
>>|||Thanks to Steve and Usenet User for their helpful piece of advice. Now I am
getting along!

Filter Data using parameters (in vb.net)

I am trying to use reporting services with vb.net. Since there is no EASY way
to pass dataset to the reporting services (i understand until Vs.net2005). As
an alternative below is what I am thinking of.
I am using vb.net and trying to dynamically generate the SQL to control the
filter. In the initial report definition I have the base SQL get a large set
of data and filter the data within the report format.
How do i pass filter the report data by using parameters. Has anybody got
any sample code?
I have checked the available paramters and what they are supposed to be used
for. They are:
rs prefix (which uses the Command, Format, Snapshot paramters)
rc prefix - i.e type of format
Not sure how I can use the existing parameters so that I am able to filter.
Please help,
Rgs,
MehulI just had a similar issue, I was returning a large set of data for a graph,
and wanted to use the same data to show only a single row, so I put a table
and filtered the data based on the parameter passed into the report.
1.) add the table
2.) set upt he rows and headers to show
3.) click on table and select properties
4.) click filters
5.) in left column, select the field name that you want to filter on
6.) in second column choose relationship
7.) in third columns, select expression, then choose parameters from the
tree and select the actual parameter to filter based on
8.) the filter looks something like Fields!filedName.Value =Parameters!parameterName.Value
you may have to explicitly cast these both to ints
CINT(Fields!filedName.Value) = CINT(Parameters!parameterName.Value)
And at this point, you have your form element showing only data that matches
the parameter that was passed to the report.
Hope this helps
"melu" <melu@.discussions.microsoft.com> wrote in message
news:5C51CC73-3424-47E1-95E7-9E9EE73684A3@.microsoft.com...
> I am trying to use reporting services with vb.net. Since there is no EASY
way
> to pass dataset to the reporting services (i understand until Vs.net2005).
As
> an alternative below is what I am thinking of.
>
> I am using vb.net and trying to dynamically generate the SQL to control
the
> filter. In the initial report definition I have the base SQL get a large
set
> of data and filter the data within the report format.
>
> How do i pass filter the report data by using parameters. Has anybody got
> any sample code?
>
> I have checked the available paramters and what they are supposed to be
used
> for. They are:
> rs prefix (which uses the Command, Format, Snapshot paramters)
> rc prefix - i.e type of format
> Not sure how I can use the existing parameters so that I am able to
filter.
>
> Please help,
> Rgs,
> Mehul

Tuesday, March 27, 2012

Filter and parameter

I am using SQL Server reporting service against Oracle DB. Is there any way
I can make filters optional? I want that if user does not select any value
from filter dropdown list then the query should run only with query
parameters. In my report I have 3 query parameters and 1 filter. Filter is
using expression (=Iif(Parameters!Equip1.Value is Nothing, true,
Fields!AC_AIRCRAFT_ID.Value=Parameters!Equip1.Value)) to use one of the
report parameters(not query parameter). Now when I deploy the report, I am
forced to provide value for the filter. Please note that I have allowed null
and blank value for the report parameter I am using for this filter.
--
ThanksOpen the report in report designer and add a default value for the report
parameter Equip1, e.g. =Nothing
Before republishing to the server, you have to delete the existing report
from the server. Then you are no longer forced to provide an explicit value.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"tiwanap" <tiwanap@.discussions.microsoft.com> wrote in message
news:DB936A62-60E9-4B66-9A3F-D2D2308B6A36@.microsoft.com...
>I am using SQL Server reporting service against Oracle DB. Is there any
>way
> I can make filters optional? I want that if user does not select any value
> from filter dropdown list then the query should run only with query
> parameters. In my report I have 3 query parameters and 1 filter. Filter is
> using expression (=Iif(Parameters!Equip1.Value is Nothing, true,
> Fields!AC_AIRCRAFT_ID.Value=Parameters!Equip1.Value)) to use one of the
> report parameters(not query parameter). Now when I deploy the report, I am
> forced to provide value for the filter. Please note that I have allowed
> null
> and blank value for the report parameter I am using for this filter.
> --
> Thanks

Filter a Report's Table using multiple Like conditions using Or

I would like to filter a report using multiple Like conditions

or - Change a filter in Reporting Services to OR rather than AND..

Example: (SameFieldName Like *1100) or (SameFieldName Like *1200).

When I try doing this on the Report's table properties - Filters tab - the And/Or automatically changes to "And" with using the "Like" Operator.

The only time "Or" appears is when I use the " = " Operator.

Or can someone show me how to use an expression to filter on multiple Like conditions.

Thanks!

Try the following filter:

Filter expression:
=(Fields!FName.Value like "*1100" OR Fields!FName.Value like "*1200")

Filter operator:
=

Filter value:
=True

-- Robert

|||You got it! It works!

Thank you

BBK

Filter & Parameter pane missing in MDX Query Designer in SQL Reporting Services

I've posted this in the SQL Reporting Services forum as well, but thought I'd try here as some of you may be using Analysis server with SRS also. I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?

Additional information on this thread. The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?

Filter & *

Hi I am wandering if it is possible to achieve the following:

I am using Reporting Services and Reporting services I got a report with a text parameter.

I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...

I am doing the following:

Filter([DIM Product].[Product].ALLMEMBERS,

,IIF(INSTR(@.Product, "*") > 0

,[DIM Product].[Product].currentmember.name = Replace(@.Product, "%", "")

,INSTR([DIM Product].[Product].currentmember.name, @.Product)

)

)

The problem ist that the function Replace does not work!

Does someone know how to do it?

Smile

I don't think the problem is with the replace, it looks like you have the logic back-to-front, something like the following should get you closer.

Code Snippet

Filter([DIM Product].[Product].ALLMEMBERS,

,IIF(INSTR(@.Product, "*") > 0

,INSTR([DIM Product].[Product].currentmember.name, Replace(@.Product, "*", "")

,[DIM Product].[Product].currentmember.name = @.Product

)

)

However there is an issue that you may or may not be concerned with - If someone types in "A*B" the expression will strip out the "*" and return anything containing "AB".

Another alternative, if you want more sophisticated behaviour might be to look at using a stored procedure. There is a string match example which I wrote in the Analysis Services Stored Procedure project at www.codeplex.com/ASStoredProcedures . I have one in there that reproduces the equivalent of the t-sql LIKE operator.

Friday, March 23, 2012

fileshare subscription erro

My report subscription got the following error. Before the subscription was
run, I changed the Reporting Service's Windows Service logon account from
local system account to a local user with adm rights.
I'm using developer editions for sql server and reporting service, running
everything from one single development machine of XP Pro.
I'd appreciate any help.
jf
************ log info *********************************
ReportingServicesService!crypto!1600!01/11/2005-17:27:03:: e ERROR:
DBUnProtectData failed: System.Runtime.InteropServices.COMException
(0x80090005): Bad Data.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSManagedCrypto.RSCrypto.DecryptData(Byte[] pCipherText)
at
Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(String
data)
ReportingServicesService!crypto!1600!01/11/2005-17:27:03:: i INFO: Current
user: jf\john
ReportingServicesService!notification!1600!01/11/2005-17:27:03:: e ERROR:
Error occured processing notification. Bad Data.
ReportingServicesService!notification!1600!01/11/2005-17:27:03::
Notification 882b52f3-40de-486e-a7ef-4e72e6efd6ea completed. Success: True,
Status: Bad Data., DeliveryExtension: Report Server FileShare, Report:
NWCustomers, Attempt 0
ReportingServicesService!dbpolling!1600!01/11/2005-17:27:03::
NotificationPolling finished processing item
882b52f3-40de-486e-a7ef-4e72e6efd6ea
***************** end of log info ***************************Hi, you can see the documentation (BOL) of reporting services about change
the account using rsconfig.exe
The problem is that the new user would be changed in the RSCONFIG file, but
trougth rsconfig.exe not over file directly
this is the command
RSConfig -c -s Server name -d Reporting Services database name -a
Sql|Windows -u User name -p User password
Best Regards
John Bocachica
Colombia
"John Fant" <JohnFant@.discussions.microsoft.com> wrote in message
news:3CE5CAEF-7553-457A-B072-70BA04EF84B2@.microsoft.com...
> My report subscription got the following error. Before the subscription
> was
> run, I changed the Reporting Service's Windows Service logon account from
> local system account to a local user with adm rights.
> I'm using developer editions for sql server and reporting service, running
> everything from one single development machine of XP Pro.
> I'd appreciate any help.
> jf
> ************ log info *********************************
> ReportingServicesService!crypto!1600!01/11/2005-17:27:03:: e ERROR:
> DBUnProtectData failed: System.Runtime.InteropServices.COMException
> (0x80090005): Bad Data.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSManagedCrypto.RSCrypto.DecryptData(Byte[] pCipherText)
> at
> Microsoft.ReportingServices.Diagnostics.DataProtection.DBUnprotectData(String
> data)
> ReportingServicesService!crypto!1600!01/11/2005-17:27:03:: i INFO: Current
> user: jf\john
> ReportingServicesService!notification!1600!01/11/2005-17:27:03:: e ERROR:
> Error occured processing notification. Bad Data.
> ReportingServicesService!notification!1600!01/11/2005-17:27:03::
> Notification 882b52f3-40de-486e-a7ef-4e72e6efd6ea completed. Success:
> True,
> Status: Bad Data., DeliveryExtension: Report Server FileShare, Report:
> NWCustomers, Attempt 0
> ReportingServicesService!dbpolling!1600!01/11/2005-17:27:03::
> NotificationPolling finished processing item
> 882b52f3-40de-486e-a7ef-4e72e6efd6ea
> ***************** end of log info ***************************

Wednesday, March 21, 2012

Files and file extensions

Hello.

I am attempting to document the various files that are incorporated into a reporting services project and need a more official explanation or defintion of the particular file and it's purpose. I understand what most of the files are and what they serve but would prefer to document it using Microsoft's official explanation so that we can decide which files may require that we source control them.

I have tried searching MSDN for 'file extentions',' file types', and typing in the individual .xxx extensions in to see if there is a documented definition for those files but seemed to get results that do not give me an official definition, didn't come close or were entirely not related.

.sln, .suo, .rds, .rdl, .rdl.data, .rptproj, .rptproj.vspscc, etc

Any links to the official explantion or definition of the files that make up a reporting services report project and their function/importance to the project would be appreciated.

Thanks.

tryin to help u.

http://filext.com/detaillist.php?extdetail=SLN

msdn2.microsoft.com/en-us/df8st53z(VS.80).aspx

msdn2.microsoft.com/en-us/bb165909(VS.80).aspx

filext.com/detaillist.php?extdetail=SUO

i think filext.com can work for u..

Wednesday, March 7, 2012

File System Error: The record ID is incorrect

Hi,

We have deployed SQL Server 2005 with Analysis Services and Reporting Services.

When running a report, we get the following error:

An error has occurred during report processing.

Cannot read the next data row for the data set DM_GASTOS.

File system error:

The record ID is incorrect. Physical file: . Logical file: .

The error occurs both with a local user account and domain user account. The user account is member of a role which has Cell Data Security restriction in Analysis Services. When other member run the report, the error goes away.

Anybody has seen this before? Any solutions?

Cheers

This looks like a data corruption issue. Try fully re-processing your entire database.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi. I'm encountering the same problem. I have re-processed my database but I still get the error message when I generate my report. Any other suggestions?

Thanks

|||

I'm having the same problem.

I have the same OLAP data base in 3 servers, and the error is the same.

Any other suggestions?

File System Error: The record ID is incorrect

Hi,

We have deployed SQL Server 2005 with Analysis Services and Reporting Services.

When running a report, we get the following error:

An error has occurred during report processing.

Cannot read the next data row for the data set DM_GASTOS.

File system error:

The record ID is incorrect. Physical file: . Logical file: .

The error occurs both with a local user account and domain user account. The user account is member of a role which has Cell Data Security restriction in Analysis Services. When other member run the report, the error goes away.

Anybody has seen this before? Any solutions?

Cheers

This looks like a data corruption issue. Try fully re-processing your entire database.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi. I'm encountering the same problem. I have re-processed my database but I still get the error message when I generate my report. Any other suggestions?

Thanks

|||

I'm having the same problem.

I have the same OLAP data base in 3 servers, and the error is the same.

Any other suggestions?

File System Error: The record ID is incorrect

Hi,

We have deployed SQL Server 2005 with Analysis Services and Reporting Services.

When running a report, we get the following error:

An error has occurred during report processing.

Cannot read the next data row for the data set DM_GASTOS.

File system error:

The record ID is incorrect. Physical file: . Logical file: .

The error occurs both with a local user account and domain user account. The user account is member of a role which has Cell Data Security restriction in Analysis Services. When other member run the report, the error goes away.

Anybody has seen this before? Any solutions?

Cheers

This looks like a data corruption issue. Try fully re-processing your entire database.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi. I'm encountering the same problem. I have re-processed my database but I still get the error message when I generate my report. Any other suggestions?

Thanks

|||

I'm having the same problem.

I have the same OLAP data base in 3 servers, and the error is the same.

Any other suggestions?

Sunday, February 26, 2012

File size big when download report

Hi
I download my report using SQL Server Reporting service export function
(excel format), The file size is very big, when compare to my previous asp
download function.
Note: The no of rec is same.
Ex: Using ASP, the file size is about 1000 kb
Using RS, the file size is about 23,000 kb.
Pls help me, what should I do to reduce the file size to normal.
Thanks
Regards
Kumar.Are you using SP1 version of RS? SP1 introduced excel using its native
store, while RTM used MHTML which was much larger.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:5126C810-2785-4B32-A46C-6FB085FCAADD@.microsoft.com...
> Hi
> I download my report using SQL Server Reporting service export function
> (excel format), The file size is very big, when compare to my previous asp
> download function.
> Note: The no of rec is same.
> Ex: Using ASP, the file size is about 1000 kb
> Using RS, the file size is about 23,000 kb.
> Pls help me, what should I do to reduce the file size to normal.
> Thanks
> Regards
> Kumar.
>

File Share Delivery Error

I have given subscription access to a domain group in reporting services.
Well when the users create a file share subscription and the subscription
launches they are getting the message "Failure writing file .pdf : Error
impersonating user." Thanks,
--
Andrew
MCSA,MCDBAThe error is stating that RS can not log on as the user that you configured
the subscription to use. It has nothing to do with the share permissions.
You should make sure that user can log onto the machine that RS is running
on.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:6E473576-5F7A-430D-AB70-A0EB8A4C7C60@.microsoft.com...
>I have given subscription access to a domain group in reporting services.
> Well when the users create a file share subscription and the subscription
> launches they are getting the message "Failure writing file .pdf : Error
> impersonating user." Thanks,
> --
> Andrew
> MCSA,MCDBA|||Daniel,
Thanks for the advice I discovered the only group granted log on locally
rights was the administrators group. Thanks again,
--
Andrew
MCSA,MCDBA
"Daniel Reib [MSFT]" wrote:
> The error is stating that RS can not log on as the user that you configured
> the subscription to use. It has nothing to do with the share permissions.
> You should make sure that user can log onto the machine that RS is running
> on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:6E473576-5F7A-430D-AB70-A0EB8A4C7C60@.microsoft.com...
> >I have given subscription access to a domain group in reporting services.
> > Well when the users create a file share subscription and the subscription
> > launches they are getting the message "Failure writing file .pdf : Error
> > impersonating user." Thanks,
> >
> > --
> > Andrew
> > MCSA,MCDBA
>
>

File share delivery

Hi All,
I am new to SQL 2005 Reporting Services. I have done the reports and now I
want to publish them to a folder in then local machine. I even tried that. I
created a subscription and when it is to rum gives an error "Unable to write
file <abc>, Report Server Configuration error, see log files for more
details". I am not able to understand the problem. Can any one of you please
help me what to do in this and get my problem sorted.
ThanksRajeev,
1. What does the log say? Did you look?
2. Did you specify a UNC path to the folder?
3. Did the user you set up for the subscription have rights to the folder?
--
Andy Potter
blog: http://sqlreportingservices.spaces.live.com/
"Rajeev" wrote:
> Hi All,
> I am new to SQL 2005 Reporting Services. I have done the reports and now I
> want to publish them to a folder in then local machine. I even tried that. I
> created a subscription and when it is to rum gives an error "Unable to write
> file <abc>, Report Server Configuration error, see log files for more
> details". I am not able to understand the problem. Can any one of you please
> help me what to do in this and get my problem sorted.
> Thanks|||Hi,
I think Your folder which contains report files having Readonly. Check it
out by opening Properties of folder and uncheck read only box
"Rajeev" wrote:
> Hi All,
> I am new to SQL 2005 Reporting Services. I have done the reports and now I
> want to publish them to a folder in then local machine. I even tried that. I
> created a subscription and when it is to rum gives an error "Unable to write
> file <abc>, Report Server Configuration error, see log files for more
> details". I am not able to understand the problem. Can any one of you please
> help me what to do in this and get my problem sorted.
> Thanks

File Share Data Processing Extension Sample

Hi all, I am very new to this Reporting Services and I want to follow this
sample that comes with SQL Server 2005 Beta. However, I tried everything
according to the documentation (build the sample, deploy it by modifying the
config files, etc.). Unfortunately, when I create a new report (using the
Report Wizard) in Visual Studio 2005, I still cannot see the File Share
Information.
Searching this newsgroup pointed me to this
http://blogs.msdn.com/bryanke/archive/2004/05/14/132110.aspx#148541
However, this does not help me either.
Any help is greatly appreciated!
Thanks a lot!
JennyHi Jenny,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to deploy
FsiDataExtension and then use it in the Report Manager. If I have
misunderstood your concern, please feel free to point it out.
Based on my knowledge, here is a sample in Reporting Services 2000 but I am
not sure whether it is available in SQL Server 2005 Reporting Services.
For SQL Server 2005's issue, please post the question in the link below
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&sl
cid=us
For SQL Server 2000 Reporting Services, you could follow the steps below as
described in MSDN Online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/ht
m/rss_sampleapps_v1_16g2.asp
- Copy Microsoft.Samples.ReportingServices.FsiDataExtension.dll to the
report server. The default location for report server extensions is
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin.
- Copy the assembly to Report Designer. The default location for
extensions for Report Designer is C:\Program Files\Microsoft SQL
Server\80\Tools\ReportDesigner.
- Add the following entry to both the RSReportServer.config and
RSReportDesigner.config files under the Data element.
<Extension Name="FSI"
Type="Microsoft.Samples.ReportingServices.FsiDataExtension.FsiConnection,Mic
rosoft.Samples.ReportingServices.FsiDataExtension"/>
- To enable the generic query designer for the sample data processing
extension, add the following entry to the RSReportDesigner.config file
under the Designer element.
<Extension Name="FSI"
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
ngServices.Designer"/>
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael
Thanks for the reply. Please see my questions inline. Thanks in advance!!
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi Jenny,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to deploy
> FsiDataExtension and then use it in the Report Manager. If I have
> misunderstood your concern, please feel free to point it out.
Yes you're right.
> Based on my knowledge, here is a sample in Reporting Services 2000 but I am
> not sure whether it is available in SQL Server 2005 Reporting Services.
> For SQL Server 2005's issue, please post the question in the link below
> http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&sl
> cid=us
It's available and the steps look the same to me! Anyway, I have posted my
question in that beta newsgroup as well. Thanks!
> For SQL Server 2000 Reporting Services, you could follow the steps below as
> described in MSDN Online
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/ht
> m/rss_sampleapps_v1_16g2.asp
> - Copy Microsoft.Samples.ReportingServices.FsiDataExtension.dll to the
> report server. The default location for report server extensions is
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\bin.
> - Copy the assembly to Report Designer. The default location for
> extensions for Report Designer is C:\Program Files\Microsoft SQL
> Server\80\Tools\ReportDesigner.
I interpreted 'Copy the assembly' as 'copy the dll' as in the previous step.
Is this correct?
> - Add the following entry to both the RSReportServer.config and
> RSReportDesigner.config files under the Data element.
> <Extension Name="FSI"
> Type="Microsoft.Samples.ReportingServices.FsiDataExtension.FsiConnection,Mic
> rosoft.Samples.ReportingServices.FsiDataExtension"/>
> - To enable the generic query designer for the sample data processing
> extension, add the following entry to the RSReportDesigner.config file
> under the Designer element.
> <Extension Name="FSI"
> Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
> ngServices.Designer"/>
>
I did all the above plus the steps in 'To configure code access security for
the sample'. I didn't know where exactly to put the CodeGroup info in the
config files unfortunately. I have tried a few spots and none succeeds. :-(
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>|||Hi
For you questions
>>I interpreted 'Copy the assembly' as 'copy the dll' as in
>>the previous step. Is this correct?
Yes, I copied Microsoft.ReportingServices.Interfaces.dll from C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Extensions\FsiDataExtension\cs\bin\Debug to the
destination.
>> I did all the above plus the steps in 'To configure code access security
for
>> the sample'. I didn't know where exactly to put the CodeGroup info in
the
>> config files unfortunately. I have tried a few spots and none succeeds.
:-(
snippet from my RSReportServer.config in C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer for your reference.
<Data>
<Extension Name="SQL"
Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Micros
oft.ReportingServices.DataExtensions"/>
<Extension Name="OLEDB"
Type="Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Micr
osoft.ReportingServices.DataExtensions"/>
<Extension Name="ORACLE"
Type="Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapp
er,Microsoft.ReportingServices.DataExtensions"/>
<Extension Name="ODBC"
Type="Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper,Micro
soft.ReportingServices.DataExtensions"/>
<Extension Name="FSI"
Type="Microsoft.Samples.ReportingServices.FsiDataExtension.FsiConnection,Mic
rosoft.Samples.ReportingServices.FsiDataExtension"/>
</Data>
snippet from my RSReportDesigner.config file in C:\Program Files\Microsoft
SQL Server\80\Tools\Report Designer for your reference.
<Data>
<Extension Name="SQL"
Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Micros
oft.ReportingServices.DataExtensions" />
<Extension Name="OLEDB"
Type="Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Micr
osoft.ReportingServices.DataExtensions"/>
<Extension Name="ORACLE"
Type="Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapp
er,Microsoft.ReportingServices.DataExtensions"/>
<Extension Name="ODBC"
Type="Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper,Micro
soft.ReportingServices.DataExtensions"/>
<Extension Name="FSI"
Type="Microsoft.Samples.ReportingServices.FsiDataExtension.FsiConnection,Mic
rosoft.Samples.ReportingServices.FsiDataExtension"/>
</Data>
<Designer>
<Extension Name="SQL"
Type="Microsoft.ReportDesigner.Design.VDTQueryDesigner,Microsoft.ReportingSe
rvices.Designer"/>
<Extension Name="OLEDB"
Type="Microsoft.ReportDesigner.Design.VDTQueryDesigner,Microsoft.ReportingSe
rvices.Designer"/>
<Extension Name="ORACLE"
Type="Microsoft.ReportDesigner.Design.VDTQueryDesigner,Microsoft.ReportingSe
rvices.Designer"/>
<Extension Name="ODBC"
Type="Microsoft.ReportDesigner.Design.VDTQueryDesigner,Microsoft.ReportingSe
rvices.Designer"/>
<Extension Name="FSI"
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
ngServices.Designer"/>
</Designer>
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi there
Thank you for your help, but what I wanted was where to put the following
code group info in both the rssrvpolicy.config and rspreviewpolicy.config.
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="FSICodeGroup"
Description="Code group for my FSI data processing extension">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\Microsoft.Samples.ReportingServices.FsiDataExtension.dll"
/>
</CodeGroup>
Thanks again and I appreciate your help!
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi
> For you questions
> >>I interpreted 'Copy the assembly' as 'copy the dll' as in
> >>the previous step. Is this correct?
> Yes, I copied Microsoft.ReportingServices.Interfaces.dll from C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FsiDataExtension\cs\bin\Debug to the
> destination.
> >> I did all the above plus the steps in 'To configure code access security
> for
> >> the sample'. I didn't know where exactly to put the CodeGroup info in
> the
> >> config files unfortunately. I have tried a few spots and none succeeds.
> :-(
> snippet from my RSReportServer.config in C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer for your reference.
> <Data>
> <Extension Name="SQL"
> Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Micros
> oft.ReportingServices.DataExtensions"/>
> <Extension Name="OLEDB"
> Type="Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Micr
> osoft.ReportingServices.DataExtensions"/>
> <Extension Name="ORACLE"
> Type="Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapp
> er,Microsoft.ReportingServices.DataExtensions"/>
> <Extension Name="ODBC"
> Type="Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper,Micro
> soft.ReportingServices.DataExtensions"/>
> <Extension Name="FSI"
> Type="Microsoft.Samples.ReportingServices.FsiDataExtension.FsiConnection,Mic
> rosoft.Samples.ReportingServices.FsiDataExtension"/>
> </Data>
> snippet from my RSReportDesigner.config file in C:\Program Files\Microsoft
> SQL Server\80\Tools\Report Designer for your reference.
> <Data>
> <Extension Name="SQL"
> Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Micros
> oft.ReportingServices.DataExtensions" />
> <Extension Name="OLEDB"
> Type="Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Micr
> osoft.ReportingServices.DataExtensions"/>
> <Extension Name="ORACLE"
> Type="Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapp
> er,Microsoft.ReportingServices.DataExtensions"/>
> <Extension Name="ODBC"
> Type="Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper,Micro
> soft.ReportingServices.DataExtensions"/>
> <Extension Name="FSI"
> Type="Microsoft.Samples.ReportingServices.FsiDataExtension.FsiConnection,Mic
> rosoft.Samples.ReportingServices.FsiDataExtension"/>
> </Data>
> <Designer>
> <Extension Name="SQL"
> Type="Microsoft.ReportDesigner.Design.VDTQueryDesigner,Microsoft.ReportingSe
> rvices.Designer"/>
> <Extension Name="OLEDB"
> Type="Microsoft.ReportDesigner.Design.VDTQueryDesigner,Microsoft.ReportingSe
> rvices.Designer"/>
> <Extension Name="ORACLE"
> Type="Microsoft.ReportDesigner.Design.VDTQueryDesigner,Microsoft.ReportingSe
> rvices.Designer"/>
> <Extension Name="ODBC"
> Type="Microsoft.ReportDesigner.Design.VDTQueryDesigner,Microsoft.ReportingSe
> rvices.Designer"/>
> <Extension Name="FSI"
> Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
> ngServices.Designer"/>
> </Designer>
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Oh I see! Thanks for your help. I finally see the File Share Extension in
the list, but when I want to do more with that, I got this error:
"The designer extension FSI could not be loaded. Check the configuration
file RSReportDesigner.config." [OK]
Am I still missing something?
Thanks a lot!
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi Jenny,
> I have attached rssrvpolicy.config and rspreviewpolicy.config for your
> reference.
> Put the <CodeGroup > like other <CodeGroup> does will be OK. Something like
> this
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="Dundas_Strong_Name"
> Description="This code group grants
> Dundas assemblies. ">
> <IMembershipCondition
> class="StrongNameMembershipCondition"
> version="1"
> PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000
> 10043D98F8A9067EF3BCD442ADE2DD48CC6A6FACBCEB1C42DE2847B0A464096C02EEBF6FD87E
> 3889BEED32B9ABD1525A11A282232CD4C46CC8123F8CC08A113CD435429646220969FF444734
> 8D1C21874670834C7A7E89EA8956FC00E0F84FD3DF6FB3EBF21774438AF9E760414FDE06BC2C
> 3AF35FF3DD87578630ED13FE12CBDBC"
> />
> </CodeGroup>
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="FSICodeGroup"
> Description="Code group for my FSI data processing extension">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL
> Server\80\Tools\ReportDesigner\bin\Microsoft.Samples.ReportingServices.FsiDa
> taExtension.dll"
> />
> </CodeGroup>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights|||Actually, I have figured it out. I got the Assembly name wrong. Once I
fixed it, I could see it in the list and load it as well. The report preview
looks good too. :-)
Unfortunately, I couldn't deploy the report. The Report Server cannot be
started for unknown reasons. When I browse http://localhost/ReportServer,
the error is
"The report server has encountered a configuration error. See the report
server log files for more information. (rsServerConfigurationError) "
What did I do wrong? I have tried re-setting these config files but it
still doesn't work. :-(
rssrvpolicy.config
rsreportserver.config
Please help!! Thanks!!
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi Jenny,
> I have attached rssrvpolicy.config and rspreviewpolicy.config for your
> reference.
> Put the <CodeGroup > like other <CodeGroup> does will be OK. Something like
> this
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="Dundas_Strong_Name"
> Description="This code group grants
> Dundas assemblies. ">
> <IMembershipCondition
> class="StrongNameMembershipCondition"
> version="1"
> PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000
> 10043D98F8A9067EF3BCD442ADE2DD48CC6A6FACBCEB1C42DE2847B0A464096C02EEBF6FD87E
> 3889BEED32B9ABD1525A11A282232CD4C46CC8123F8CC08A113CD435429646220969FF444734
> 8D1C21874670834C7A7E89EA8956FC00E0F84FD3DF6FB3EBF21774438AF9E760414FDE06BC2C
> 3AF35FF3DD87578630ED13FE12CBDBC"
> />
> </CodeGroup>
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="FSICodeGroup"
> Description="Code group for my FSI data processing extension">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL
> Server\80\Tools\ReportDesigner\bin\Microsoft.Samples.ReportingServices.FsiDa
> taExtension.dll"
> />
> </CodeGroup>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights|||Hi Jenny,
The error message seems to indicate that you do not set congifuration file
rssrvpolicy.config and rsreportserver.config correctly. Please double check
configuration files again and my attachment file is just a sample file for
your reference. You cannot use them directly.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.