report parameter with mdx

Hi,
I am running analysis services with SQL Server 2000. There I created a
cube which I am handling with reporting services. So far, no problem.
But now I also added some report parameter, which I would like to use
to define the dataset. But every time when I try to include a parameter
into the mdx-query I get an error-message, that named parameter are not
supported by OLE DB. I try to call a parameter by @.param_name
Is it possible to include reportparameter in mdx-queries?
If yes, how?
Thanksyes it is, show me your mdx query and i'll tell you how to do it.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134637862.017069.135030@.o13g2000cwo.googlegroups.com...
> Hi,
> I am running analysis services with SQL Server 2000. There I created a
> cube which I am handling with reporting services. So far, no problem.
> But now I also added some report parameter, which I would like to use
> to define the dataset. But every time when I try to include a parameter
> into the mdx-query I get an error-message, that named parameter are not
> supported by OLE DB. I try to call a parameter by @.param_name
> Is it possible to include reportparameter in mdx-queries?
> If yes, how?
> Thanks
>|||Hi Greg,
thanks for your response.
Thats my current query:
WITH
MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
FORMAT_STRING = 'Percent'
SELECT
{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
{client.members} ON AXIS(1),
{area.members} ON AXIS(2),
{date_month.members} ON AXIS(3),
{analysis.members} ON AXIS(4)
FROM ISDBIv2
something like that I would like to add:
... WHERE ([client].[@.paramname])
Thanks|||Hi Greg,
thanks for your response.
Thats my current query:
WITH
MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
FORMAT_STRING = 'Percent'
SELECT
{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
{client.members} ON AXIS(1),
{area.members} ON AXIS(2),
{date_month.members} ON AXIS(3),
{analysis.members} ON AXIS(4)
FROM ISDBIv2
something like that I would like to add:
... WHERE ([client].[@.paramname])
Thanks|||ok, no problem. i assume client is a dimension so,
you do this
1. run the query without the parameter in so you get the fields
2. create a second dataset which just gets out the clients
select {[Measures].[Analysis_count]} on AXIS(0),
{[client].Members} on AXIS(1)
from FROM ISDBIv2
3. set up the parameter as you would normally, i.e. click Report -> Report
Parameters etc etc, give it a name 'paramname' and get it to get the clients
from that dataset
4. change the main query to
= "WITH " &
"MEMBER [Measures].[percentage]" & "AS " &
"'[Measures].[Analysis_Count] / ([Measures].[Analysis_Count],
[analysis].[Alle Analysis])', " &
"FORMAT_STRING = 'Percent' " &
"SELECT " &
"{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0), "&
" {client.members} ON AXIS(1), " &
"{area.members} ON AXIS(2), " &
"{date_month.members} ON AXIS(3), " &
"{analysis.members} ON AXIS(4) " &
"FROM ISDBIv2 " &
"where ([client].[ " & Parameters!paramname.Value & "])"
that should do it, very long winded explanation, but thought i'd cover all
bases just incase. You will notice that you cannot run the query anymore
this is because of the = sign, but do a preview and it should work. Any
problems let me know, cos i've come accross quite a few random ones.
good luck Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134651740.902628.68600@.o13g2000cwo.googlegroups.com...
> Hi Greg,
> thanks for your response.
> Thats my current query:
> WITH
> MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
> ([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
> FORMAT_STRING = 'Percent'
> SELECT
> {[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
> {client.members} ON AXIS(1),
> {area.members} ON AXIS(2),
> {date_month.members} ON AXIS(3),
> {analysis.members} ON AXIS(4)
> FROM ISDBIv2
> something like that I would like to add:
> ... WHERE ([client].[@.paramname])
> Thanks
>|||Hi Greg,
thanks. that is the solution I were looking for. The first three steps
I had allready done. But the way to build the query by concatenating
the substrings was very useful.
But I got one big problem. Every time I try to do the preview I got an
error message:
"... double dimensions about (independent) axis - during calculating an
axis" (translated to english)
When I delete the " {client.members} ON AXIS(1), " &" - part the
preview is running. Unfortunatly I need this dimension in my matrix.
Do you have some ideas?
thanks|||I guess, I found the orginal english error message. It is:
"... duplicate dimensions accross independent axis..."|||sorry my fault, should've actually read the script.
Take the where clause out completely and put this in
"{[client].[ " & Parameters!paramname.Value & "]} ON AXIS(1), " &
instead of
" {client.members} ON AXIS(1), " &
The problem is that the original script was referring to the same dimension
twice. Sorry bout that,should work fine now. Let me know how it goes.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134725728.305647.143860@.z14g2000cwz.googlegroups.com...
> Hi Greg,
> thanks. that is the solution I were looking for. The first three steps
> I had allready done. But the way to build the query by concatenating
> the substrings was very useful.
> But I got one big problem. Every time I try to do the preview I got an
> error message:
> "... double dimensions about (independent) axis - during calculating an
> axis" (translated to english)
> When I delete the " {client.members} ON AXIS(1), " &" - part the
> preview is running. Unfortunatly I need this dimension in my matrix.
> Do you have some ideas?
> thanks
>|||Yes, indeed it is working pretty fine now :-) But one last question.
What do I have do to filter data where "date_month.members" are older
than "startdate" and younger than "enddate". I know that is normal mdx.
but i havent done this before.
thanks|||From what you've said i assume you have parameters 'startdate' and
'enddate'. So you can do this by applying a filter to the main dataset.
click the '...' button next to the dataset title and click the filters tab.
In the expressions put date field in the expressions box should look
something like Fields!datename.value then put >= in operator box and put the
startdate parameter in the value box, you'll find it by clicking on the
expression option on the drop down from value. Then repeat for the end date
but put <= in the operator box.
That should do it. I can't off the top of my head think how to do it mdx
without referring to the date_month.members twice and we know that will
cause problems.
cheers
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134728307.957458.193420@.g44g2000cwa.googlegroups.com...
> Yes, indeed it is working pretty fine now :-) But one last question.
> What do I have do to filter data where "date_month.members" are older
> than "startdate" and younger than "enddate". I know that is normal mdx.
> but i havent done this before.
> thanks
>|||Hi Greg,
maybe you are interested in a solution with mdx. It is possible to
declare a MEMBER-Field defining a startdate and enddate.
SET [filter] AS '[date].[2005].[12].[1] : [date].[2005].[12].[19] '
Unfortunatly this method creates some other problems. First the named
dates "2005/12/01" and "2005/12/19" have to exist in your database.
Besides, if your time dimension contains weeks instead of month this
way is not possible, unless you are programming a function which is
calculating a week number of a date. So this way is not the best one.
I tried to solve my problem by using filters. But that creates some
problems too. First of all, my dimension is a time-dimension. So I dont
know how to call the entire date, because the dimension is divided in
day, month, year. In addition I set the type of my parameter (startdate
and enddate) to DateTime. But everytime I when I am calling the
preview, I get the errormessage, that the type of the parameter is not
correct. Switching the type to string, the filter does not work
correctly.
Do you have some ideas?
Thanks|||sorry could you just clarify how your dates are stored in your database? Is
it three different columns, one for day, month and year?
If that is the case you could concatenate them into a string of one date in
reporting services, or you could add a new column to your database with the
full date in...
Sorry if i've misinterpretted the issue here.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134977793.241846.273900@.g44g2000cwa.googlegroups.com...
> Hi Greg,
> maybe you are interested in a solution with mdx. It is possible to
> declare a MEMBER-Field defining a startdate and enddate.
> SET [filter] AS '[date].[2005].[12].[1] : [date].[2005].[12].[19] '
> Unfortunatly this method creates some other problems. First the named
> dates "2005/12/01" and "2005/12/19" have to exist in your database.
> Besides, if your time dimension contains weeks instead of month this
> way is not possible, unless you are programming a function which is
> calculating a week number of a date. So this way is not the best one.
> I tried to solve my problem by using filters. But that creates some
> problems too. First of all, my dimension is a time-dimension. So I dont
> know how to call the entire date, because the dimension is divided in
> day, month, year. In addition I set the type of my parameter (startdate
> and enddate) to DateTime. But everytime I when I am calling the
> preview, I get the errormessage, that the type of the parameter is not
> correct. Switching the type to string, the filter does not work
> correctly.
> Do you have some ideas?
> Thanks
>

Report parameter validation from report manager in reporting services 2005

Hi,

Whenever user viewing a report using report manager,

how can we validate the input values from user for a datatime type report parameter.

i.e. validating whether the date is correct and in required format.

Thanks in Advance.

Regards

Pintu

The built in parameter UI performs only simple validation (making sure a float parameter has numeric characters, for example). The server will perform a full validation on the parameter value for formatting, culture, data type, etc. If you want more advanced client side validation, you will need to provide custom parameter UI in your own portal. Report Manager is not extensible in this regard.|||

Thanks Brian for providing the useful informaion

|||

Isn't there any built in functionality to prevent SQL injection? Or any build in functionality to prevent other scripts from being inserted i.e. scripts to prevent < > characters etc?

Can you provide a sample of a simple custom UI and tell us where/how to call it?

Thanks

Amy

Report Parameter Toolbar

Good day everyone...

I created a simple report in SQL Server 2005 Reporting Services. Its about getting the sales from specific date and store. My parameters are date and storename, the report works fine as I view it in the reportserver. But, when we incorporate the report in a custom webpage using a reportviewer, the parameter toolbar occupies half the space, now that's my problem. Is there anyway I can create a custom Toolbar in Visual Studio so I can just set the parameter property to hidden so the default parameter toolbar wont show?

Thanks in advance guys... c",)

Yes. There's nothing extra (like controls) supplied to allow this. Just create the parameters using standard controls (dropdowns, dat pickers, whatever you like) and then in code pass those parameters to the report viewer control.sql

Report Parameter error

Hi ,

This is vishal vidhale. when i pass parameter in reporting services that time it gives me an error "Foward dependancies are not allowed".

How can i overcome this error. Any one who know or having knowledge of SQL reporting services can help me..

Thanks......................

Hi Vishal. May be your parameter(child) is depending on another parameter(Parent) in the Report and that parameter(parent) is declared later this parameter(child).

so, if this is the situation, in the parameters form(Report->Parameters) move your paramter(child) to below the parent parameter and check it out.

Thanks,

Srinivasa Reddy.

Report Parameter Calendar Formatting PLEASE HELP

If anyone can help me with this, I would be really grateful. I am using
Reporting Services 2005 and Visual Studio 2005 for this problem.
A while back, I had a formatting problem with my reports that was fixed by
use of the Language property on the report. Although this fixed all
formatting within the report. However, there is still a problem with the
calendar controls that collect DateTime report parameters.
To check that this was not some kind of obscure problem with my particular
report, I put together a very simple example from the AdventureWorks
database.
The dataset was provided by the text query of:
SELECT Title, BirthDate
FROM HumanResources.Employee
WHERE (BirthDate BETWEEN @.StartDate AND @.EndDate)
The @.StartDate and @.EndDate parameters were set to type DateTime with
default value of null. I put a two column table on the report, displaying
Title and BirthDate. The Language of the report is set to English(United
Kingdom). The international setting under options in Visual Studio is set
to 'Same as Microsoft Windows'. My windows setting is English - United
Kingdom.
Once the parameters have been entered on the default calendar controls,
their values are converted to US date format (MM/dd/yyyy). Where the UK
date format will not convert, the following error displays:
"An error occurred during local report processing. The report parameter
'StartDate' is not valid for its type."
If anyone has any ideas of how to fix this, then I would be really grateful.
Virtually every report I run has the start and end date parameters, so
unless I fix this, the default method of collecting these parameters is
effectively useless to me.
Thanks in advance,
Ed AllisonInterestingly, this problem does not seem to occur when reports are deployed
to the report server.
Ed Allison
"Ed Allison" <ed@.optix.co.uk> wrote in message
news:O8B4xJlLGHA.3012@.TK2MSFTNGP14.phx.gbl...
> If anyone can help me with this, I would be really grateful. I am using
> Reporting Services 2005 and Visual Studio 2005 for this problem.
> A while back, I had a formatting problem with my reports that was fixed by
> use of the Language property on the report. Although this fixed all
> formatting within the report. However, there is still a problem with the
> calendar controls that collect DateTime report parameters.
> To check that this was not some kind of obscure problem with my particular
> report, I put together a very simple example from the AdventureWorks
> database.
> The dataset was provided by the text query of:
> SELECT Title, BirthDate
> FROM HumanResources.Employee
> WHERE (BirthDate BETWEEN @.StartDate AND @.EndDate)
> The @.StartDate and @.EndDate parameters were set to type DateTime with
> default value of null. I put a two column table on the report, displaying
> Title and BirthDate. The Language of the report is set to English(United
> Kingdom). The international setting under options in Visual Studio is set
> to 'Same as Microsoft Windows'. My windows setting is English - United
> Kingdom.
> Once the parameters have been entered on the default calendar controls,
> their values are converted to US date format (MM/dd/yyyy). Where the UK
> date format will not convert, the following error displays:
> "An error occurred during local report processing. The report parameter
> 'StartDate' is not valid for its type."
> If anyone has any ideas of how to fix this, then I would be really
> grateful. Virtually every report I run has the start and end date
> parameters, so unless I fix this, the default method of collecting these
> parameters is effectively useless to me.
> Thanks in advance,
> Ed Allison
>|||I have this question as well
"Ed Allison" wrote:
> Interestingly, this problem does not seem to occur when reports are deployed
> to the report server.
> Ed Allison
> "Ed Allison" <ed@.optix.co.uk> wrote in message
> news:O8B4xJlLGHA.3012@.TK2MSFTNGP14.phx.gbl...
> > If anyone can help me with this, I would be really grateful. I am using
> > Reporting Services 2005 and Visual Studio 2005 for this problem.
> >
> > A while back, I had a formatting problem with my reports that was fixed by
> > use of the Language property on the report. Although this fixed all
> > formatting within the report. However, there is still a problem with the
> > calendar controls that collect DateTime report parameters.
> >
> > To check that this was not some kind of obscure problem with my particular
> > report, I put together a very simple example from the AdventureWorks
> > database.
> >
> > The dataset was provided by the text query of:
> >
> > SELECT Title, BirthDate
> > FROM HumanResources.Employee
> > WHERE (BirthDate BETWEEN @.StartDate AND @.EndDate)
> >
> > The @.StartDate and @.EndDate parameters were set to type DateTime with
> > default value of null. I put a two column table on the report, displaying
> > Title and BirthDate. The Language of the report is set to English(United
> > Kingdom). The international setting under options in Visual Studio is set
> > to 'Same as Microsoft Windows'. My windows setting is English - United
> > Kingdom.
> >
> > Once the parameters have been entered on the default calendar controls,
> > their values are converted to US date format (MM/dd/yyyy). Where the UK
> > date format will not convert, the following error displays:
> >
> > "An error occurred during local report processing. The report parameter
> > 'StartDate' is not valid for its type."
> >
> > If anyone has any ideas of how to fix this, then I would be really
> > grateful. Virtually every report I run has the start and end date
> > parameters, so unless I fix this, the default method of collecting these
> > parameters is effectively useless to me.
> >
> > Thanks in advance,
> >
> > Ed Allison
> >
>
>|||I have found no solution to this, I am afraid. Since the problem does not
occur when reports are deployed, I just work around it when developing
reports.
Ed Allison
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:628FB199-1E9A-4723-8748-0C649E3683EA@.microsoft.com...
>I have this question as well
> "Ed Allison" wrote:
>> Interestingly, this problem does not seem to occur when reports are
>> deployed
>> to the report server.
>> Ed Allison
>> "Ed Allison" <ed@.optix.co.uk> wrote in message
>> news:O8B4xJlLGHA.3012@.TK2MSFTNGP14.phx.gbl...
>> > If anyone can help me with this, I would be really grateful. I am
>> > using
>> > Reporting Services 2005 and Visual Studio 2005 for this problem.
>> >
>> > A while back, I had a formatting problem with my reports that was fixed
>> > by
>> > use of the Language property on the report. Although this fixed all
>> > formatting within the report. However, there is still a problem with
>> > the
>> > calendar controls that collect DateTime report parameters.
>> >
>> > To check that this was not some kind of obscure problem with my
>> > particular
>> > report, I put together a very simple example from the AdventureWorks
>> > database.
>> >
>> > The dataset was provided by the text query of:
>> >
>> > SELECT Title, BirthDate
>> > FROM HumanResources.Employee
>> > WHERE (BirthDate BETWEEN @.StartDate AND @.EndDate)
>> >
>> > The @.StartDate and @.EndDate parameters were set to type DateTime with
>> > default value of null. I put a two column table on the report,
>> > displaying
>> > Title and BirthDate. The Language of the report is set to
>> > English(United
>> > Kingdom). The international setting under options in Visual Studio is
>> > set
>> > to 'Same as Microsoft Windows'. My windows setting is English - United
>> > Kingdom.
>> >
>> > Once the parameters have been entered on the default calendar controls,
>> > their values are converted to US date format (MM/dd/yyyy). Where the
>> > UK
>> > date format will not convert, the following error displays:
>> >
>> > "An error occurred during local report processing. The report
>> > parameter
>> > 'StartDate' is not valid for its type."
>> >
>> > If anyone has any ideas of how to fix this, then I would be really
>> > grateful. Virtually every report I run has the start and end date
>> > parameters, so unless I fix this, the default method of collecting
>> > these
>> > parameters is effectively useless to me.
>> >
>> > Thanks in advance,
>> >
>> > Ed Allison
>> >
>>|||Hi Ed,
I have the same problem and strangely - setting language property on
the report to "default" with international setting under options in
Visual Studio set to "English" sorted out the problem.
If for you this is not acceptable - the way arround (at least it works
for me) is not to press "View report" button - but the small green
refresh button on the toolbar on the preview report tab...
Hope this helps
Ivan

Report parameter - date format

I have developed few reports using reporting services (service pack 2), but
still facing some problem in the date parameter and would look for help /
share here. Thanks advance!
1) How RS treat the date format?
YYYY-MM-DD (the data is correct when i use this format)
DD/MM/YYYY
MM/DD/YYYY
What is the purpose to define the date parameter as "MM/DD/YYYY" from the
"Expression" windows?
Any way to limit the input date format or any method to convert the format
to YYYY-MM-DD if user really input the format as DD/MM/YYYY or MM/DD/YYYY?
2) Anyone know how to use date time picker in RS, or possible to use it? I
believe if RS could use the date time picker, I don't need to worry the
parameter formatHi,
have a look at this article:
http://www.databasejournal.com/features/mssql/article.php/2197931
You'll find the way to solve your problem (I had the same problem and by
converting the date with the CONVERT function in my SQL query I managed to
solve it).
If you have some time could you take a look at my post (just after yours),
maybe you have the solution.
Thanks,
--
JB
"Franklin" wrote:
> I have developed few reports using reporting services (service pack 2), but
> still facing some problem in the date parameter and would look for help /
> share here. Thanks advance!
> 1) How RS treat the date format?
> YYYY-MM-DD (the data is correct when i use this format)
> DD/MM/YYYY
> MM/DD/YYYY
> What is the purpose to define the date parameter as "MM/DD/YYYY" from the
> "Expression" windows?
> Any way to limit the input date format or any method to convert the format
> to YYYY-MM-DD if user really input the format as DD/MM/YYYY or MM/DD/YYYY?
> 2) Anyone know how to use date time picker in RS, or possible to use it? I
> believe if RS could use the date time picker, I don't need to worry the
> parameter format
>sql

Report parameter

I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum. I just want to know what im doing wrong, that the totals are completely off now. Did i set up the parameter wrong in reporting services. I have the 3 parameters list in the report parameter section, and even have them cascading off of each other. That seems to work fine. but for the first matrix in my report, but the second matrix with this stored proc, is way off. Please help!!.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]

@.Question char(80)

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]

(@.Region_Key int=null,@.QuestionCode char(5),@.Question char(80))

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo'

AND REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

AND Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Try this:

AND (@.Region_Key is null or REGION_KEY=@.Region_Key)

BobP

Report Paging

We are converting from Crystal Reports to Reporting Services. We are
encountering what appears to be an issue with the Report Viewer. If Table
rows in the body of the report are conditionally made Hidden (depending on
parameter input), the number of pages differs between the viewer and the
printed (either printer or pdf). In fact, some reports show a single page in
the viewer, but many pages when printed or exported to pdf. If the Table rows
are always visible, the pagination is consistent between viewer and printing.
Is there any way to work around this?Hello HK,
I would like to know the following things:
1. What's the SQL server version did you use? 2000 or 2005?
2. Since this issue only occured for the conditional Hidden, it is a known
issue in SQL 2005. And the product team is researching this issue. If I get
any update, I will let you know.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Thank you for your response.
We are using sql server 2005 sp2, visual studio 2005.
It appears to be occuring only with the conditional Hidden. If I remove the
conditional Hidden, we appear to get correct pagination. I (and our many
report users) would greatly appreciate any solution to this.
"Wei Lu [MSFT]" wrote:
> Hello HK,
> I would like to know the following things:
> 1. What's the SQL server version did you use? 2000 or 2005?
> 2. Since this issue only occured for the conditional Hidden, it is a known
> issue in SQL 2005. And the product team is researching this issue. If I get
> any update, I will let you know.
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello HK,
Product Team confirmed this is a known issue and will be addressed in the
next version of Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||I'm glad to hear that - thanks for your response.
"Wei Lu [MSFT]" wrote:
> Hello HK,
> Product Team confirmed this is a known issue and will be addressed in the
> next version of Reporting Services.
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>

Report page size when printing

I use SQL Server 2005 Reporting Services. The clients use Internet Explorer 6 or higher on Windows XP.

When i access a report by ReportServer URL, the report viewer show me this report with my default parameters selected. If i choose "PDF" format and i click "Export", my report is perfect on printing.

My problem is the button print of the ReportViewer page, if i click on it, the printed report is very too large (on two page). I presume that some settings are wrong in my report, the maximum width of the content never exceed 31 cm, i want the report in LandScape.

There are my settings for this report :

InteractiveSize : 11in; 8,5in

Margins : 1,5cm; 1,5cm; 1,5cm; 1,5cm

PageSize : 35cm; 25cm

Where's the problem when i use the print button directly into the ReportViewer ? Internet Explorer ? The printer drivers or settings ? The report settings ?

Sorry for my english...

What happens when you set InteractiveWidth to 35cm and InteractiveHeight to 25cm? InteractiveHeight/Width are used to generate soft page breaks with HTML and Excel formats.|||Same problem, i have deleted the report before deploying again with the updated report (InteractiveWidth to 35cm and InteractiveHeight to 25cm) and the report stay on 2 page in width.|||At what width does the report break across pages? This usually happens when the report body width exceeds the page width minus (left and right margins).|||ok i will change the width and height of the page. It seem's to be 27.5 X 21.5 cm, so i will reduce the width and the content will fit on one page. Thanks.

Report Packs and SQL Server Express

I am trying to use the IISLog report packs with SQL Server Express reporting services. The guidance in http://blogs.msdn.com/sqlexpress/ indicates that I need to change the target URL and the connection string so that the report packs work with SQL Express. I cannot see how to centrally change the connection string, so that it will affect all the Datasets in the report project. If I try and open a report page and click on the Data tab I get the following error.

A connection cannot be made to the database.
Set and test the connection string.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

I have seen this error before when I have mistakenly used "data source=(local); initial catalog=AdventureWorks" instead of "data source=.\SQLExpress; initial catalog=AdventureWorks" as a connection string when creating a new Dataset.

How can I get the IISLog report pack to work with SQL Express?

The Readme.doc file provided with the samples provide two options for deploying the reports. Option 2, using the Report Manager actually gives instructions on how to correctly create a new datasource and then point the uploaded reports to that datasource. I'll leave you to review the readme if you're using this method.

Option 1, describing using Business Intellegence Development Studio (BIDS) doesn't actually address modifying the datasource, so I'll cover that here. If you're using an all Express system, you'll need to have the following installed to do this:

SQL Express Advanced|||need to add another quickstart tutorial for this subject matter because installation on a winxp pro sp2 machine isn't yielding ability to deploy reports to sqlexpress.

The problem is how to configure the report server. seems the report designer will create a report and has ability to preview it, but cannot connect to server for deployment after the build.

Even though I can connect to the server after I go through the report server configuration tool setup.

==================================error
TITLE: Microsoft Report Designer

A connection could not be made to the report server http://localhost/ReportServer$SQLExpress.

ADDITIONAL INFORMATION:

Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response. (Microsoft.ReportingServices.Designer)

================================error

Your above guidelines are not specifically applicable to IIs 5.1, as there is no " IISDatabase.rds Data Source listed under the Shared Data Sources folder"

|||I'm having the exact same inability to deploy report to SQL Server 2005 on an XP system. Using http:/localhost/ReportServer and receive "Object reference not set to an instance of an object"sql

Report Packs and SQL Server Express

I am trying to use the IISLog report packs with SQL Server Express reporting services. The guidance in http://blogs.msdn.com/sqlexpress/ indicates that I need to change the target URL and the connection string so that the report packs work with SQL Express. I cannot see how to centrally change the connection string, so that it will affect all the Datasets in the report project. If I try and open a report page and click on the Data tab I get the following error.

A connection cannot be made to the database.
Set and test the connection string.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

I have seen this error before when I have mistakenly used "data source=(local); initial catalog=AdventureWorks" instead of "data source=.\SQLExpress; initial catalog=AdventureWorks" as a connection string when creating a new Dataset.

How can I get the IISLog report pack to work with SQL Express?

The Readme.doc file provided with the samples provide two options for deploying the reports. Option 2, using the Report Manager actually gives instructions on how to correctly create a new datasource and then point the uploaded reports to that datasource. I'll leave you to review the readme if you're using this method.

Option 1, describing using Business Intellegence Development Studio (BIDS) doesn't actually address modifying the datasource, so I'll cover that here. If you're using an all Express system, you'll need to have the following installed to do this:

SQL Express Advanced|||need to add another quickstart tutorial for this subject matter because installation on a winxp pro sp2 machine isn't yielding ability to deploy reports to sqlexpress.

The problem is how to configure the report server. seems the report designer will create a report and has ability to preview it, but cannot connect to server for deployment after the build.

Even though I can connect to the server after I go through the report server configuration tool setup.

==================================error
TITLE: Microsoft Report Designer

A connection could not be made to the report server http://localhost/ReportServer$SQLExpress.

ADDITIONAL INFORMATION:

Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response. (Microsoft.ReportingServices.Designer)

================================error

Your above guidelines are not specifically applicable to IIs 5.1, as there is no " IISDatabase.rds Data Source listed under the Shared Data Sources folder"

|||I'm having the exact same inability to deploy report to SQL Server 2005 on an XP system. Using http:/localhost/ReportServer and receive "Object reference not set to an instance of an object"

Report Pack for Reporting Services

Has anyone seen report-pack for Reporting Services? Similar to one that is
available for IIS, GPS etc.
Or other option is there any database schema description of the "ReportDB"
for Reporting Services 2005?
--
Thanks in advance - Sunil.The closest thing to a report pack for reporting services is the Report
Execution log reports. The package includes a SSIS package, DB and several
reports.
The reports included will tell you about report execution times, parameters
used, etc, etc
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/RSMnRptExPf.asp
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Sunil K" <SunilK@.discussions.microsoft.com> wrote in message
news:8650684C-FBBD-4D94-A662-CAC937654801@.microsoft.com...
> Has anyone seen report-pack for Reporting Services? Similar to one that is
> available for IIS, GPS etc.
> Or other option is there any database schema description of the "ReportDB"
> for Reporting Services 2005?
> --
> Thanks in advance - Sunil.
>

Report Pack for IIS

Hi,
I am wondering if I can run the "Microsoft SQL Server Report Pack for
Internet Information Services (IIS)" on a SQL 2005 64 bit.
2nd question, where can I find the script as well as some DTS or jobs to
populate the IISlog DB.
ThanksHello,
Based on my research, Microsoft SQL Server Report Pack for Internet
Information Services (IIS) only support SQL Server 2000.
To confirm that, I tried to tested on my side and I could not install it on
the SQL 2005 server.
As for the second question, I am not sure what you want to do with the
IISLog DB. Would you please try to make it more clear?
Please NOTE: Microsoft SQL Server Report Pack for Internet Information
Services (IIS) is licensed "as-is" It is not supported, and you bear the
risk of using it. Microsoft gives no express warranties, guarantees or
conditions.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi, I found the package for sql 2005 at
"http://www.microsoft.com/downloads/details.aspx?FamilyId=D81722CE-408C-4FB6-A429-2A7ECD62F674&displaylang=en
I managed to make it work, For the second question, I need to know if there
is any process has been devloped to import data from log files to the Database
Thansk
"Wei Lu [MSFT]" wrote:
> Hello,
> Based on my research, Microsoft SQL Server Report Pack for Internet
> Information Services (IIS) only support SQL Server 2000.
> To confirm that, I tried to tested on my side and I could not install it on
> the SQL 2005 server.
> As for the second question, I am not sure what you want to do with the
> IISLog DB. Would you please try to make it more clear?
> Please NOTE: Microsoft SQL Server Report Pack for Internet Information
> Services (IIS) is licensed "as-is" It is not supported, and you bear the
> risk of using it. Microsoft gives no express warranties, guarantees or
> conditions.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>|||Hello
Based on my test, you could the Log Parser which included with the IIS 6.0
Resource Kit to populated with your own IIS Log to your SQL Server.
Internet Information Services (IIS) 6.0 Resource Kit Tools
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=56fc
92ee-a71a-4c73-b628-ade629c89499
The following example exports data from W3C Extended log files and writes
it to a SQL table:
logparser "Select TO_TIMESTAMP(date, time) as Timestamp,
cs-uri-stem as UriStem, cs-uri-query as UriQuery FROM ex000123.log TO
TestTable"
¨Ci:W3C -o:SQL -server:SERVERSQL
-driver:"SQL Server" -database:LogDB -username:user
-password:xxx
-createtable:ON
Sincerely,
Wei Lu
Microsoft Online Community 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.sql

Report only generates 1 page output

I'm fairly new to Reporting Services, so I'm sure this is a dumb question. I have a stored proc that returns 38 rows that I am using in a report. The report is fairly simple, with no page headers or footers. I used the Report Wizard to create the report, but ended up deleting all the generated fields and started with a blank report. Now the report only generates 1 page of output. Any ideas?

Thanks,
MikeDid you get the right data with the report created by the Wizard? After you deleted all the generated fields, what did you replace them with? Does your report contain a repeating data region such as a Table?

Thanks,
Albert

Report only generates 1 page output

I'm fairly new to Reporting Services, so I'm sure this is a dumb question. I have a stored proc that returns 38 rows that I am using in a report. The report is fairly simple, with no page headers or footers. I used the Report Wizard to create the report, but ended up deleting all the generated fields and started with a blank report. Now the report only generates 1 page of output. Any ideas?

Thanks,
MikeDid you get the right data with the report created by the Wizard? After you deleted all the generated fields, what did you replace them with? Does your report contain a repeating data region such as a Table?

Thanks,
Albert

report on two database

hi all

i m facing a problem in reporting services. i have a global database, in which i m mainting user inormation like database name, server name and all credentails about user database. i want that when my report is open a user parameter will display, on the basis of that users id report will display data from his database. server name can be different, database name can be different but schema of database will be same for all user. so pls help me

The DataSource string of the dataset is dynmaic which means that you are able to use Expressions within the Data Source, using the syntax ="DataSource=" & Parameter!SomeParameter.Value

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks jens for response

but first time there should be fixed connection so that my user list will display. after selecting a particular customer my connection string will change. how this is possible because all dataset use same connection string. pls suggest me .

|||

Don't forget you can have and use multiple data sources. And these can either be shared or private to the report.

If your customer list and the actual data are coming from different data sources then you should make the dataset that returns the customer list for the parameter based on a static data source connection and the other dataset that brings back the actual report data based on a different data source connection.

If the the data source varies depending on which customer you choose then the second connection can be made expression based as Jens pointed out.

|||

ya adam

i was using same apprach, but when i m changing connection string of one dataset another dataset's connection string is automatically changes. can u show me that how can i change connection string of one dataset so that another dataset's connection string will not change.

|||

Don't use a single shared data source. If all datasets use the same shared data source then naturally having that expression based will affect all datasets that use it. Use multiple data sources, a different one for each dataset. Try not using a shared data source.

On the data tab, when you click the elipsis (...) button next to the datasets dropdown, you will see the properties of the dataset. In there you specify which data source it connects to. Don't choose the shared data source option. Instead define the data connection for that dataset.

If you're already doing this then maybe whats happening is that both data sources happen to have the same connection string and reporting services is trying to be clever and just treating it as one.

|||

thanku adam

actually i was sharing same data source. now i change it and its working properly

thank u again.

Report on a Sharepoint table in SRS

I'm not sure this is possible, but... I was asked to write a SQL Reporting Services Report that reports on a SQL table that is joined to a Sharepoint Table. In trying to look up how to do this, I'm not finding any how-to posts. Is this possible and if so, could you point me in the direction of a step-by-step how -to white paper?

Thanks,

Jennifer

WSS exposes its feature set as a web service. I think the one that returns the data in a list is called Lists.asmx. From there, you can use the XML data provider in SSRS 2005 to connect to the service and consume the data in XML. This post should help you to get started with the SSRS XML data provider. If you need to create a report that joins data from two lists on the report, you could try a third-party provider such as Enesys or build your own custom data extension.

As a side note, a native provider that reports from WSS is on top of the wish list for a next release as discussed in Brian Welcker's blog.

Report on a Sharepoint table in SRS

I'm not sure this is possible, but... I was asked to write a SQL Reporting Services Report that reports on a SQL table that is joined to a Sharepoint Table. In trying to look up how to do this, I'm not finding any how-to posts. Is this possible and if so, could you point me in the direction of a step-by-step how -to white paper?

Thanks,

Jennifer

WSS exposes its feature set as a web service. I think the one that returns the data in a list is called Lists.asmx. From there, you can use the XML data provider in SSRS 2005 to connect to the service and consume the data in XML. This post should help you to get started with the SSRS XML data provider. If you need to create a report that joins data from two lists on the report, you could try a third-party provider such as Enesys or build your own custom data extension.

As a side note, a native provider that reports from WSS is on top of the wish list for a next release as discussed in Brian Welcker's blog.

sql

Report OK in local development, won't run on server

I am attempting to migrate a report I developed locally in my development
environment to the Reporting Services server in preparation for deployment.
The report gets its data from a stored procedure that requires no
parameters; it simply runs and returns data.
I am able to upload the RDL file to the server without incident and the
report is shown properly in the report listing on the reporting server web
page.
However when I try to render the report on the reporting server I receive
the following errors:
An error has occurred during report processing. (rsProcessingAborted)
Cannot set the command type for data set 'DataSet1'.
(rsErrorSettingCommandType)
Stored Procedure command type is not supported.
The report itself is very simple, only one matrix control exists on the page.
I did not install the server environment myself however I have been able to
determine that Reporting Services SP2 has been installed - as it has been in
my development environment.
Any help you could give would be appreciated, thanks.
-ChuckAlthough I haven't see this exact error I have seen an issue with the stored
procedure security rights not allowing the credentials your report is
running under to execute it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ExNooYorka" <ExNooYorka@.discussions.microsoft.com> wrote in message
news:3447A77A-5DB5-4D1B-A067-3E576605F484@.microsoft.com...
>I am attempting to migrate a report I developed locally in my development
> environment to the Reporting Services server in preparation for
> deployment.
> The report gets its data from a stored procedure that requires no
> parameters; it simply runs and returns data.
> I am able to upload the RDL file to the server without incident and the
> report is shown properly in the report listing on the reporting server web
> page.
> However when I try to render the report on the reporting server I receive
> the following errors:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command type for data set 'DataSet1'.
> (rsErrorSettingCommandType)
> Stored Procedure command type is not supported.
> The report itself is very simple, only one matrix control exists on the
> page.
> I did not install the server environment myself however I have been able
> to
> determine that Reporting Services SP2 has been installed - as it has been
> in
> my development environment.
> Any help you could give would be appreciated, thanks.
> -Chuck|||Bruce:
Thanks for the reply.
When I read your reply I thought you hit the nail right on the head
because on the remote SQL Server I had indeed not granted credentials
to anyone - only admins could run my stored procedure.
However even granting rights to the PUBLIC group did not resolve the
problem described below.
I have, however, resolved it.
The data source present on the reporting server was pointed to a custom
data provider that did not support the Stored Procedure command type.
I believe it was the custom data provider (or the layer that interacts
with the custom data provider) that was throwing the error described
below.
I created a new data source on the report server that points to the
native SQL Server data provider and that resolved the issue.
Thanks again for your help - a fresh pair of eyes is always most
helpful.
-Chuck
Bruce L-C [MVP] wrote:
> Although I haven't see this exact error I have seen an issue with the stored
> procedure security rights not allowing the credentials your report is
> running under to execute it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "ExNooYorka" <ExNooYorka@.discussions.microsoft.com> wrote in message
> news:3447A77A-5DB5-4D1B-A067-3E576605F484@.microsoft.com...
> >I am attempting to migrate a report I developed locally in my development
> > environment to the Reporting Services server in preparation for
> > deployment.
> >
> > The report gets its data from a stored procedure that requires no
> > parameters; it simply runs and returns data.
> >
> > I am able to upload the RDL file to the server without incident and the
> > report is shown properly in the report listing on the reporting server web
> > page.
> >
> > However when I try to render the report on the reporting server I receive
> > the following errors:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Cannot set the command type for data set 'DataSet1'.
> > (rsErrorSettingCommandType)
> > Stored Procedure command type is not supported.
> >
> > The report itself is very simple, only one matrix control exists on the
> > page.
> >
> > I did not install the server environment myself however I have been able
> > to
> > determine that Reporting Services SP2 has been installed - as it has been
> > in
> > my development environment.
> >
> > Any help you could give would be appreciated, thanks.
> >
> > -Chuck

Report of my reports

I would like to create a report in Reporting Services that lists the reports
in my SRS, including their descriptions, specified by the folders they reside
in, by pulling the data directly from the SRS? Is this possible? The data's
there. How can I get to it dynamically? Thanks.Its easy using the API:
http://odetocode.com/Articles/95.aspx
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"David" <David@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C6C91A2C-A270-4C18-980C-607923E180D0@.microsoft.com...
>I would like to create a report in Reporting Services that lists the
>reports
> in my SRS, including their descriptions, specified by the folders they
> reside
> in, by pulling the data directly from the SRS? Is this possible? The
> data's
> there. How can I get to it dynamically? Thanks.|||I think you could also query the ReportServer tables directly in a strored
proc then use that as the datasource for the "report of reports"
"David" wrote:
> I would like to create a report in Reporting Services that lists the reports
> in my SRS, including their descriptions, specified by the folders they reside
> in, by pulling the data directly from the SRS? Is this possible? The data's
> there. How can I get to it dynamically? Thanks.