Report parameter using Member_Key

I am trying to create a parameter driven SSRS report agains my cube but I want the parameter to contain only the Key value of the member I am trying to select. When I use the query designer, I get something like this:

SELECT ( STRTOSET(@.PlantStructureDepartment, CONSTRAINED)

Where @.PlantStructureDepartment is my paramter and contains the Unique Name of the member like this "[Plant Structure].[Department].&[39952]" where "39952" is the Key value.

What I want is to be able to pass the paramter in as "39952" and build the rest of the string. I tried this:

SELECT ( STRTOSET('[Plant Structure].[Department].&[' + @.PlantStructureDepartment + ']', CONSTRAINED)

but that didn't work.

The reason I'm trying to do this is that we are using SharePoint integrated filtering and we have a number of basic relational reports using the same filters as the reports against the cubes. We would like to keep them all using the integer values.

For sake of completeness, here is the full query as it sits right now:

SELECT NON EMPTY { [Measures].[FTT Percent] } ON COLUMNS, NON EMPTY { ([Plant Structure].[Prod Line].[Prod Line].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.PlantStructureDepartment, CONSTRAINED) ) ON COLUMNS FROM [FTT]) WHERE ( IIF( STRTOSET(@.PlantStructureDepartment, CONSTRAINED).Count = 1, STRTOSET(@.PlantStructureDepartment, CONSTRAINED), [Plant Structure].[Department].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I am fairly new to MDX and would appreciate any help or suggestions.

Here is a copy of the query modified to use Adventure Works but illustrating what you requested. Where I've used '1', you would use your report parameter value.

Hope that helps,
Bryan

Code Snippet

SELECT

NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS,

NON EMPTY { ([Product].[SubCategory].[SubCategory].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM (

SELECT ( STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED) ) ON COLUMNS

FROM [Adventure Works])

WHERE (

IIF( STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED).Count = 1,

STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED),

[Product].[Category].currentmember

)

)

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

Bryan,

Thanks for the reply. I think you are stating the same thing that I tried already. When I hardcode the value as you suggest, the query works. However, when I set up the parameter, I get this message:

Parser: The query contains the Department parameter, which is not declared. (msmgdsrv)

This is because the paramter isn't declared in the data tab of the report. When I declare the parameter, I get this error:

Query (6, 23) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated. (Microsoft SQL Server 2005 Analysis Services)

The parameters page of the Data tab only allows you to tie a parameter to a dimension of the cube. I think I need a way around that.

Thanks again for the idea. If you have another, I'd appreciate it.

Chris

|||

The CONSTRAINED keyword is there to prevent an injection of a function call into the statement. While I think this is a good idea, I get the same error as you do when no injection is occuring. While I would recommend digging into the MDX statement a little deeper to understand the issue, I have in the past simply removed the CONSTRAINED keyword.

Not sure this is the best of ideas, but .....

Bryan

|||

That's what it was.

Thanks!

|||

You have CONSTRAINED in several areas, do you need to remove them all. Can you post your modified code?

Thanks,

Shari

|||I don't have access to my query right at the moment (I'm at a different location) but suffice it to say, I removed all of the CONSTRAINED flags and it worked. To the point made earlier, I'm not sure if it was a good idea, but it got me around the issue I was having.|||

I tried that, removing all the CONSTRAINED but got another error about STRTOSET function expects a tuple set, string was supplied .... . I am anxious to see your query if you could share (when you have access).

Thanks,

Shari

Report Parameter Limitation

Hi all,

I create one report with one report parameter call “Report_Parameter_0”.I access this report though HTML Form.Ex

<FORM action=http://hodb04/reportserver?/report/Report12 method="post">

<INPUT type="hidden" value="Render" name="rs:Command" ID="Hidden1">

<INPUT type="hidden" value="HTML4.0" name="rs:Format" ID="Hidden2">

<INPUT type="hidden" value="true" name="rc:Toolbar" ID="Hidden3">

<INPUT type="hidden" value="true" name="rcarameters" ID="Hidden4">

<INPUT type="hidden" value="request " name="Report_Parameter_0" ID="Hidden5"> <inputtype="Submit>

</FORM>

If I update the Report_Parameter_0 value from “request” to the string with more then 2000 characters, then I will receive the follow error message from the Internet Explorer

“res://C:\WINDOWS\system32\shdoclc.dll/dnserror.htm#http://hodb04/reportserver?/DmReport/Report12”

My question is what it the limitation on the parameter passing thought HTML.What are the max characters I call pass to the report parameter?

Thanks,

tomas

In IE you can have a maximum of 2083 characters, of which no more than 2048 (2KB) can be in the path (the bit after the host). You will also have to watch out for characters that get url encoded, as these don't use up one character position, they use up three (i.e. a space gets url encoded from " " to "%20").

Other browsers have different (and generally much higher) limits.

|||Thanks for your respond. This is not good since all our reports access thought IE, and we require to pass the parameter more than 2083 characters. How can I solve this limitaion? Sould I use Web Service?|||

A web service could work, as those limitations are for IE, not IIS. Alternatively, you could redesign the HTML form or the report - 2KB of parameters is a lot!

|||Thanks, what do you means to redesign the HTML form. Is there a way to remove the 2083 characters limitation. The reason is we need to pass the SQL query to the report parameter to generate the report. Some of the SQL queries have more than 2083 characters.|||

Could you pass the SQL query to a field in a table in the database before you run the report, and use an identifier to retrieve the query afterwards?

Here is another posting that may help.

http://openacs.org/forums/message-view?message_id=110868

cheers,

Andrew

|||

TomasLeung wrote:

Thanks, what do you means to redesign the HTML form. Is there a way to remove the 2083 characters limitation.

There is no way to remove the limitation - it is a limitation with IE. Other browsers have much larger limits. Other technologies don't have those limits. But as i said before, 2KB of query string parameters is a lot.

TomasLeung wrote:

The reason is we need to pass the SQL query to the report parameter to generate the report. Some of the SQL queries have more than 2083 characters.

Maybe i am missing something here, but i find it incredible that you are passing SQL in this way - it is a big security hole and you are asking for trouble. What happens when someone changes their query string to read "delete from products"? I would change the design so that you are only passing *parameters* to the report, which are then passed to a stored procedure to get the data.

|||thanks for your advise, actually the query we passing to the parameter is the MDX query. Futhermore, the MDX query is create by another set of controls for example time and product control. So we know what kind of MDX we passing in. But the MDX query can be exceed 2083 characters.|||

There is no reason why you cannot use another browser to view the HTML form that puts together the MDX, this will get around the 2083 character limitation (did you eliminate unneccessary characters like whitespace?). But i have no idea how the report will render in that browser (i have never tried using a non IE browser to access a report - why don't you give it a try?).

Another option would be to use something like an ASP.Net page, and using either Ajax or postbacks you could build up the query string on the server, then do a call from the server to RS to generate the report, then redirect the client to the generated report.

These are just some ideas i'm throwing around - they may or may not work. Like i said back at the start, the only real way to fix your problem is to redesign the process you are using (unless the change of browser works).

Report Parameter in Report Builder

How do you create report parameter in Report Builder? I want to create a new field and add to the report. When I run the report, it'll prompt for entering the value.

Thanks in advance!!

Brian wrote a blog article about this: http://blogs.msdn.com/bwelcker/archive/2006/07/29/682444.aspx

-- Robert

Report Parameter help

In am trying to create a report using a stored procedure having two
parameters. The Avalilable values for the parameters come from queries. The
user while running the report may not select one of the parameters i.e. leave
it blank. When I leave one of the parameters blank the report does not run, I
have to select some value for both parameters for report to run.
Can Someone let me know how to run a report without passing a value in
parameter.
Thanks
AnoopHi Anoop
You will always have to pass the parameter to the report because the
stored procedure is expecting it and won't run unless it has it.
The way round this is to pass a null value and allow for it in your
stored procedure.
Wherever you use the parameter in the stored procedure, you could use
the following syntax:
(@.QueueId IS NULL OR QueueId = @.QueueId)
eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
This would mean that if the @.QueueId parameter is null, it will ignore
it and return all the queues.
Hope this helps?
Regards
Dave|||Hi Dave,
Thanks for the response.
I am already handling NULLs in the Stored procedure. The problem that I am
having is that my report parameters are populated using queries which do not
have null, also user may not select all report parameters. When user does not
select a parameter the report does not pass NULL to sp infact it does not run
at all. Is there a way around this.
Thanks and Regards,
Anoop
"Bungle" wrote:
> Hi Anoop
> You will always have to pass the parameter to the report because the
> stored procedure is expecting it and won't run unless it has it.
> The way round this is to pass a null value and allow for it in your
> stored procedure.
> Wherever you use the parameter in the stored procedure, you could use
> the following syntax:
> (@.QueueId IS NULL OR QueueId = @.QueueId)
> eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> This would mean that if the @.QueueId parameter is null, it will ignore
> it and return all the queues.
> Hope this helps?
> Regards
> Dave
>|||Unless you allow null or blank in the report, or have a default value , the
user MUST select a value for each parameter or the report will not run...
If your issue is that you wish to account for Null or blanks, then the prior
respondant's method is commonly used, although it may perform badly..
--
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
"Anoop" <Anoop@.discussions.microsoft.com> wrote in message
news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> Hi Dave,
> Thanks for the response.
> I am already handling NULLs in the Stored procedure. The problem that I am
> having is that my report parameters are populated using queries which do
not
> have null, also user may not select all report parameters. When user does
not
> select a parameter the report does not pass NULL to sp infact it does not
run
> at all. Is there a way around this.
> Thanks and Regards,
> Anoop
>
> "Bungle" wrote:
> > Hi Anoop
> >
> > You will always have to pass the parameter to the report because the
> > stored procedure is expecting it and won't run unless it has it.
> >
> > The way round this is to pass a null value and allow for it in your
> > stored procedure.
> >
> > Wherever you use the parameter in the stored procedure, you could use
> > the following syntax:
> >
> > (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > This would mean that if the @.QueueId parameter is null, it will ignore
> > it and return all the queues.
> >
> > Hope this helps?
> >
> > Regards
> >
> > Dave
> >
> >|||I think I cannot do what I am trying to do through reporting services as each
parameter must be supplied with a value for a report to run.
Thanks a lot for your help.
"Wayne Snyder" wrote:
> Unless you allow null or blank in the report, or have a default value , the
> user MUST select a value for each parameter or the report will not run...
> If your issue is that you wish to account for Null or blanks, then the prior
> respondant's method is commonly used, although it may perform badly..
> --
> 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
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > Hi Dave,
> > Thanks for the response.
> >
> > I am already handling NULLs in the Stored procedure. The problem that I am
> > having is that my report parameters are populated using queries which do
> not
> > have null, also user may not select all report parameters. When user does
> not
> > select a parameter the report does not pass NULL to sp infact it does not
> run
> > at all. Is there a way around this.
> >
> > Thanks and Regards,
> >
> > Anoop
> >
> >
> > "Bungle" wrote:
> >
> > > Hi Anoop
> > >
> > > You will always have to pass the parameter to the report because the
> > > stored procedure is expecting it and won't run unless it has it.
> > >
> > > The way round this is to pass a null value and allow for it in your
> > > stored procedure.
> > >
> > > Wherever you use the parameter in the stored procedure, you could use
> > > the following syntax:
> > >
> > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > it and return all the queues.
> > >
> > > Hope this helps?
> > >
> > > Regards
> > >
> > > Dave
> > >
> > >
>
>|||Can you modify the query that loads the parameters to add another line like
'None' that has a value of NULL? This would be similar to the method used to
add an 'All' selection.
Actually, I may need to try this myself for 1 of my reports.
Neil
"Anoop" wrote:
> I think I cannot do what I am trying to do through reporting services as each
> parameter must be supplied with a value for a report to run.
> Thanks a lot for your help.
> "Wayne Snyder" wrote:
> > Unless you allow null or blank in the report, or have a default value , the
> > user MUST select a value for each parameter or the report will not run...
> >
> > If your issue is that you wish to account for Null or blanks, then the prior
> > respondant's method is commonly used, although it may perform badly..
> >
> > --
> > 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
> >
> > "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> > news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > > Hi Dave,
> > > Thanks for the response.
> > >
> > > I am already handling NULLs in the Stored procedure. The problem that I am
> > > having is that my report parameters are populated using queries which do
> > not
> > > have null, also user may not select all report parameters. When user does
> > not
> > > select a parameter the report does not pass NULL to sp infact it does not
> > run
> > > at all. Is there a way around this.
> > >
> > > Thanks and Regards,
> > >
> > > Anoop
> > >
> > >
> > > "Bungle" wrote:
> > >
> > > > Hi Anoop
> > > >
> > > > You will always have to pass the parameter to the report because the
> > > > stored procedure is expecting it and won't run unless it has it.
> > > >
> > > > The way round this is to pass a null value and allow for it in your
> > > > stored procedure.
> > > >
> > > > Wherever you use the parameter in the stored procedure, you could use
> > > > the following syntax:
> > > >
> > > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > > it and return all the queues.
> > > >
> > > > Hope this helps?
> > > >
> > > > Regards
> > > >
> > > > Dave
> > > >
> > > >
> >
> >
> >|||Hi,
I will try to be as clear as possible, excuse my english
What you will need to do is for the SP's that are used to generate the
parameter list add a value that will mean "All Possible value" like so:
create procedure <ParamList>
AS
declare @.paramtable TABLE (ordersequence TINYINT,
id INT,
VALUE VARCHAR(100))
insert into @.paramtable values (1,0,"All Possible values")
insert into @.paramtable
select 1,id,value
from table1
select id,value
from @.paramtable
order by ordersequence,value
Then you will need to setup your parameter in RS and define the default
value of this parameter to 0.
You will also need to modify your report SP to manage the case of the "All
Possible values" param value...
Something like this should work
Create Procedure <Report>
(
@.ParamValue INT
)
AS
select col1,col2,col3,col4
from table1
where col1 = case when @.ParamValue = 0 then col1
else @.ParamValue
end
HTH,
Aiwa
"Anoop" wrote:
> In am trying to create a report using a stored procedure having two
> parameters. The Avalilable values for the parameters come from queries. The
> user while running the report may not select one of the parameters i.e. leave
> it blank. When I leave one of the parameters blank the report does not run, I
> have to select some value for both parameters for report to run.
> Can Someone let me know how to run a report without passing a value in
> parameter.
> Thanks
> Anoop|||Hi,
Thanks for your help.
As I have 10 parameters for my report. I have now created tables which have
0 or blank as a value. These tables are used to populate the Lists for report
parameters. It is a long process but it is working.
Hope there was a simpler solution. Anyways..
Thanks
Anoop
"Aiwa" wrote:
> Hi,
> I will try to be as clear as possible, excuse my english
> What you will need to do is for the SP's that are used to generate the
> parameter list add a value that will mean "All Possible value" like so:
> create procedure <ParamList>
> AS
> declare @.paramtable TABLE (ordersequence TINYINT,
> id INT,
> VALUE VARCHAR(100))
> insert into @.paramtable values (1,0,"All Possible values")
> insert into @.paramtable
> select 1,id,value
> from table1
> select id,value
> from @.paramtable
> order by ordersequence,value
>
> Then you will need to setup your parameter in RS and define the default
> value of this parameter to 0.
> You will also need to modify your report SP to manage the case of the "All
> Possible values" param value...
> Something like this should work
> Create Procedure <Report>
> (
> @.ParamValue INT
> )
> AS
> select col1,col2,col3,col4
> from table1
> where col1 = case when @.ParamValue = 0 then col1
> else @.ParamValue
> end
> HTH,
> Aiwa
> "Anoop" wrote:
> > In am trying to create a report using a stored procedure having two
> > parameters. The Avalilable values for the parameters come from queries. The
> > user while running the report may not select one of the parameters i.e. leave
> > it blank. When I leave one of the parameters blank the report does not run, I
> > have to select some value for both parameters for report to run.
> >
> > Can Someone let me know how to run a report without passing a value in
> > parameter.
> >
> > Thanks
> > Anoopsql

Report parameter dropdown

Hello,

I need to create a MyPar parameter dropdown list, that is why I created a new dataset that gets data through a stored procedure. I also add @.MyPar into the query string of MyMainDataSet . I defined this parameter in Parameter screen for both available and default values.

When I run the report, I get the following error.

Query execution failed for data set MyMainDataSet.

Must declare the variable @.MyPar

What is problem?

Hello:

If I understand correctly:
The first stored procedure that displays the parameters for MyPar is a straight SQL Select Statement - So this SQL Select just gets the data for the available parameters to display in the dropdown list.
The next step the user selects one value from all of the available parameters in the dropdown list - so once this parameter is selected you need to pass this selected parameter to your next SQL Select statement so you only get the data based on the parameter the user selected!
Include the following in the Stored Procedure (if your are using one for the selection of data based on the parameter the user selected)
CREATE PROCEDURE "Whatever the Procedure name is"
@.MyPar nvarchar(10) or whatever length the parameter is

-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on

Hope this helps!
Best Regards - Joe

Report Parameter Display Formatting

I have a report where I want to create a boolean parameter that has two radio
buttons, where one radio botton has label "With Approval" and the other radio
buttons label says "No Approval (Under Review or No Request), plus there is
not leading text prior to the first radio. Basically I want my report
parameter display to look like this:
X - With Approval X - No Approval (Under Review or No Request)
Note: X in the above text is the radio button.
How is this done?
I can easily set up the two radio buttons, with a single boolean report
parameter, but I can only get the labels to say "true", or "false", plus a
leading label that says "With Approval". Some thing like this:
With Approval X true X falseAre you using a custom viewer you wrote to render the reports? This is the
only way I know of to use webcontrols other than text boxes and drop down
lists.
"Greg Larsen" wrote:
> I have a report where I want to create a boolean parameter that has two radio
> buttons, where one radio botton has label "With Approval" and the other radio
> buttons label says "No Approval (Under Review or No Request), plus there is
> not leading text prior to the first radio. Basically I want my report
> parameter display to look like this:
> X - With Approval X - No Approval (Under Review or No Request)
> Note: X in the above text is the radio button.
> How is this done?
> I can easily set up the two radio buttons, with a single boolean report
> parameter, but I can only get the labels to say "true", or "false", plus a
> leading label that says "With Approval". Some thing like this:
> With Approval X true X false|||No. I'm just using the standard viewer in Reporting Services.
"Aaron Williams" wrote:
> Are you using a custom viewer you wrote to render the reports? This is the
> only way I know of to use webcontrols other than text boxes and drop down
> lists.
> "Greg Larsen" wrote:
> > I have a report where I want to create a boolean parameter that has two radio
> > buttons, where one radio botton has label "With Approval" and the other radio
> > buttons label says "No Approval (Under Review or No Request), plus there is
> > not leading text prior to the first radio. Basically I want my report
> > parameter display to look like this:
> >
> > X - With Approval X - No Approval (Under Review or No Request)
> >
> > Note: X in the above text is the radio button.
> >
> > How is this done?
> >
> > I can easily set up the two radio buttons, with a single boolean report
> > parameter, but I can only get the labels to say "true", or "false", plus a
> > leading label that says "With Approval". Some thing like this:
> >
> > With Approval X true X false

Report out an html string

Is it possible to report out an html string into the rich format?
--> I use an html control (RichTextBox 2.5) that allows the user to create
the main body of a quote in a wysiwyg environment, and that formatting is
successfully saved as an html string to the SQL db and successfully displayed
when opening back up in the control.
--> Will Reporting Services allow me to take that html string and display in
the main body of a report, with the formatting applied (i.e. bold, bullets,
etc.)?
Any help would be greatly appreciated, from saving as a certain datatype in
SQL to rendering the code in the Reporting Services.
Many thanks,
AndyNo. This would most likely would be a security hole if reporting services
allowed that.
However, you could build your own ASP page around reporting services that
might give you a little of the functionality you want.
--
| Thread-Topic: Report out an html string
| thread-index: AcWie3YqffCUH69lRbybl0xkS1lurw==| X-WBNR-Posting-Host: 69.160.170.174
| From: =?Utf-8?B?cGFwYXJoaW5v?= <paparhino@.discussions.microsoft.com>
| Subject: Report out an html string
| Date: Tue, 16 Aug 2005 08:59:19 -0700
| Lines: 16
| Message-ID: <035749C9-7F6C-49E4-BFCD-2DA040AC5CAE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50470
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Is it possible to report out an html string into the rich format?
|
| --> I use an html control (RichTextBox 2.5) that allows the user to
create
| the main body of a quote in a wysiwyg environment, and that formatting is
| successfully saved as an html string to the SQL db and successfully
displayed
| when opening back up in the control.
|
| --> Will Reporting Services allow me to take that html string and display
in
| the main body of a report, with the formatting applied (i.e. bold,
bullets,
| etc.)?
|
| Any help would be greatly appreciated, from saving as a certain datatype
in
| SQL to rendering the code in the Reporting Services.
|
| Many thanks,
| Andy
|

report only displays one row

I added a report viewer, clicked create new report, and followed the wizards. My report only displays one row on one page. I can see all the rows if I preview contents on the data table adapter. There are no filters and no where clause in the SQL. What might the reason be that the report only shows one row? Thanks

HI,dmcdivitt:

Try to use a simple sql statement without any parameter and filter and following the Add New Report Wizard. If you still face to the same sitution, please feel free to post your question here for more discussion.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

I created a new report with a simple SQL statement and got the same result - one row on one page. No more. The table used has 1300 rows. No filters, no parameters, and no where clause was used anywhere.

|||

I will ask this in another forum. Nobody seems to read this one.

|||

HI,dmcdivitt:

I am sorry that i have came back home early and have not read your posts in time due to the different time region.

It is really weird. I hope you can send me your project to me and i will have a check with it.

Please use the simple query string and the common datatable(NORTHWIND).

Here is my address:sighlen@.sina.com

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Resolved. See threadhttp://forums.asp.net/t/1128061.aspx

Report on successful sql logins

Looking for how to create a report for sql'05 to see who's logging in succes
fully and then failed user logins as well perhaps on a second report.
Is there a way to capture logins historically with just sql server? How do
you set this up? Could someone point me in the right direction or, possibly
an example?
ThanksGo to Server Properties, Security tab, Login auditing. Login information
will be written to SQL Server Log and Windows Event Viewer.
Ben Nevarez, MCDBA, OCP
Database Administrator
"James Wuerflein" wrote:

> Looking for how to create a report for sql'05 to see who's logging in succ
esfully and then failed user logins as well perhaps on a second report.
> Is there a way to capture logins historically with just sql server? How d
o you set this up? Could someone point me in the right direction or, possib
ly an example?
> Thanks
>|||What an easy way to take that an create a report forthose logins that I coul
d give to management?
Thanks

Go to Server Properties, Security tab, Login auditing. Login information
will be written to SQL Server Log and Windows Event Viewer.
Ben Nevarez, MCDBA, OCP
Database Administrator
"James Wuerflein" wrote:
[vbcol=seagreen]
> Looking for how to create a report for sql'05 to see who's logging in succ
esfully and then failed user logins as well perhaps on a second report.
>
> Is there a way to capture logins historically with just sql server? How d
o you set this up? Could someone point me in the right direction or, possib
ly an example?
>
> Thanks
>

Report on successful sql logins

--____SBTWNWYYMXLIXECXQHOY____
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Looking for how to create a report for sql'05 to see who's logging in = succesfully and then failed user logins as well perhaps on a second = report.
Is there a way to capture logins historically with just sql server? How = do you set this up? Could someone point me in the right direction or, = possibly an example?
Thanks
--____SBTWNWYYMXLIXECXQHOY____
Content-Type: multipart/related; boundary="____NDKLCVQYBPHYCAFJJABM____"
--____NDKLCVQYBPHYCAFJJABM____
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
&
Looking for how to create a report for sql'05 to see who's logging in = succesfully and then failed user logins as well perhaps on a second = report.

Is there a way to capture logins historically with just sql server?&nb= sp; How do you set this up? Could someone point me in the right = direction or, possibly an example?

Thanks
--____NDKLCVQYBPHYCAFJJABM____--
--____SBTWNWYYMXLIXECXQHOY____--Go to Server Properties, Security tab, Login auditing. Login information
will be written to SQL Server Log and Windows Event Viewer.
Ben Nevarez, MCDBA, OCP
Database Administrator
"James Wuerflein" wrote:
> Looking for how to create a report for sql'05 to see who's logging in succesfully and then failed user logins as well perhaps on a second report.
> Is there a way to capture logins historically with just sql server? How do you set this up? Could someone point me in the right direction or, possibly an example?
> Thanks
>|||--____OGQMDAXZFWTVEKBBRRSF____
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
What an easy way to take that an create a report forthose logins that I =could give to management?
Thanks
>> Ben Nevarez<BenNevarez@.discussions.microsoft.com> 05/03 6:37 PM >>
Go to Server Properties, Security tab, Login auditing. Login information=20=
will be written to SQL Server Log and Windows Event Viewer.
Ben Nevarez, MCDBA, OCP
Database Administrator
"James Wuerflein" wrote:
> Looking for how to create a report for sql'05 to see who's logging in =succesfully and then failed user logins as well perhaps on a second =report. > > Is there a way to capture logins historically with just sql server? How =do you set this up? Could someone point me in the right direction or, =possibly an example?
> > Thanks
>
--____OGQMDAXZFWTVEKBBRRSF____
Content-Type: multipart/related; boundary="____CECFBOUWBBNUBTTKVERM____"
--____CECFBOUWBBNUBTTKVERM____
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
&

---

What an easy way to take that an create a report forthose logins that =I could give to management?
Thanks> Ben Nevarez 05/03 6:37 PM >>
Go to Server Properties, Security tab, =Login auditing. Login information will be written to SQL Server Log =and Windows Event Viewer.Ben Nevarez, MCDBA, OCPDatabase =Administrator"James Wuerflein" wrote:> Looking for =how to create a report for sql'05 to see who's logging in succesfully and =then failed user logins as well perhaps on a second report. > => Is there a way to capture logins historically with just sql =server? How do you set this up? Could someone point me in the =right direction or, possibly an example?> > Thanks> =

---

--____CECFBOUWBBNUBTTKVERM____--
--____OGQMDAXZFWTVEKBBRRSF____--

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.

Report of every user for every database on server

For auditing purposes, I create a list of all the users who have access to
my databases. I do this by issuing this:
exec sp_MSForEachDB sp_helpuser
However, this doesn't include the name of the database that the list of
users is for. How can I include this info?
Thanks."Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23vGdjyRmFHA.2080@.TK2MSFTNGP10.phx.gbl...
> For auditing purposes, I create a list of all the users who have access to
> my databases. I do this by issuing this:
> exec sp_MSForEachDB sp_helpuser
> However, this doesn't include the name of the database that the list of
> users is for. How can I include this info?
> Thanks.
>
I use some dynamic sql and a cursor for this. In Query Analyzer, set your
output to Text (as opposed to grid).
BEGIN
SET NOCOUNT ON
-- Create some variables
DECLARE @.currentdb sysname, -- Current database that we are working with
@.sql varchar(4000) -- Dynamic SQL statement
-- Load the cursor with the current list of database names. Exclude SQL
Server databases.
DECLARE dbnames CURSOR FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'Northwind',
'pubs')
ORDER BY [name] ASC
-- For each database, perform your query
OPEN dbnames
FETCH NEXT
FROM dbnames
INTO @.currentdb
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- The @.sql statement should be in the form of:
SET @.sql = @.currentdb + '.dbo.sp_helpuser'
-- Print a header to the messages tab
PRINT '=======================================
=================='
PRINT @.currentdb
PRINT ''
-- Execute the sql statement
EXECUTE(@.sql)
PRINT ''
PRINT '=======================================
=================='
PRINT ''
PRINT ''
-- Get the next database name
FETCH NEXT
FROM dbnames
INTO @.currentdb
END
-- Cleanup
CLOSE dbnames
DEALLOCATE dbnames
END
Rick Sawtell|||Nice script! However, it seems to have an issue with databases that begin
with the number zero (0). I get the folowing error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '0'.
Any ideas?
Thanks.|||"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:uykxweSmFHA.572@.TK2MSFTNGP15.phx.gbl...
> Nice script! However, it seems to have an issue with databases that begin
> with the number zero (0). I get the folowing error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '0'.
> Any ideas?
> Thanks.
>
Ummm.. Find a better naming convention.. <wink>
I was under the impression that all object qualifiers in SQL Server 2000
require a letter as the first character, followed by numbers and some
symbols...
Guess I was wrong.
You could modify the script to include the square brackets. That may fix
it.
So adjust as follows:
Old line
SET @.sql = @.currentdb + '.dbo.sp_helpuser'
New line
SET @.sql = '[' + @.currentdb + '].dbo.sp_helpuser'
I should have probably done that in the first place for those who insist on
putting spaces etc. in their naming conventions.
Rick Sawtell|||Use QUOTENAME(@.currentdb) instead of @.currentdb
Steve Kass
Drew University
Joshua Campbell wrote:

>Nice script! However, it seems to have an issue with databases that begin
>with the number zero (0). I get the folowing error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '0'.
>Any ideas?
>Thanks.
>
>|||It's not sufficient. Because I had to something very similar but including
all the permissions per each user using sp_helpuser
"Steve Kass" wrote:

> Use QUOTENAME(@.currentdb) instead of @.currentdb
> Steve Kass
> Drew University
> Joshua Campbell wrote:
>
>|||I'm sorry, but I don't understand you. If there is problem with
QUOTENAME, could you show sample code where it doesn't
work as expected?
SK
Enric wrote:
>It's not sufficient. Because I had to something very similar but including
>all the permissions per each user using sp_helpuser
>"Steve Kass" wrote:
>
>

Report Model with XML datasource

Is there a way to create a Report Model with XML / WebService as datasource?

I am able to generate reports that connect to a WebService through Report Designer, but unable to find similar functionality in Model Designer.

JDee,

Here's a link for setting the Report Service XML datasource. I hope this works for you.

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

Ham

|||I'm interested in the same possibility. As far as I've understood, it's possible to generate models only from relational data sources. It would be a great surprise for me if not.|||RS 2005 supports only SQL Server (2000 and up), Analysis Services 2005 and starting with SP2 Oracle as data sources.|||Yes, from my understanding models are generated from relational data sources. I believe XML can represent relational database (http://www.w3.org/XML/RDB.html). I think a data provider which supplies XML as data source would be a neat feature in the upcoming versions.

Report Model with XML datasource

Is there a way to create a Report Model with XML / WebService as datasource?

I am able to generate reports that connect to a WebService through Report Designer, but unable to find similar functionality in Model Designer.

JDee,

Here's a link for setting the Report Service XML datasource. I hope this works for you.

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

Ham

|||I'm interested in the same possibility. As far as I've understood, it's possible to generate models only from relational data sources. It would be a great surprise for me if not.|||RS 2005 supports only SQL Server (2000 and up), Analysis Services 2005 and starting with SP2 Oracle as data sources.|||Yes, from my understanding models are generated from relational data sources. I believe XML can represent relational database (http://www.w3.org/XML/RDB.html). I think a data provider which supplies XML as data source would be a neat feature in the upcoming versions.

Report Model Timeout

I am trying to create a report model consisting of one main table with
several views connecting to the main table. The main table is rather large
(20-30 million records). I keep getting the following error message below.
Any ideas?
An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.i have same problem,
there must be a timeout setting somewhere
"retkow" wrote:
> I am trying to create a report model consisting of one main table with
> several views connecting to the main table. The main table is rather large
> (20-30 million records). I keep getting the following error message below.
> Any ideas?
> An error occurred while executing a command.
> Message: Timeout expired. The timeout period elapsed prior to completion of
> the operation or the server is not responding.
>|||I am getting the same error message after 30 minutes into my report
execution. The execution timeout in site settings is set to 3 hours.
SO dont seem to understand what can be causing this timeout.
Any ideas on where to look is greatly appreciated.
Thanks
"Tango" wrote:
> i have same problem,
> there must be a timeout setting somewhere
> "retkow" wrote:
> > I am trying to create a report model consisting of one main table with
> > several views connecting to the main table. The main table is rather large
> > (20-30 million records). I keep getting the following error message below.
> > Any ideas?
> >
> > An error occurred while executing a command.
> > Message: Timeout expired. The timeout period elapsed prior to completion of
> > the operation or the server is not responding.
> >

report model template is missing

I want to create a new report model project, however in VS Business intelligence Development Studio, I could find the report model template in the New project dialog box. Anyone coulf tell me how can I add the templeate? Thanks

Do you have any other business intelligence projects installed like report server project?

Try this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1479717&SiteID=1

Report Model Template

HI,

When I create a new Business Intelligence Projects, I can not find the "Report Model Project" template. I am not sure If I failed to install a few components ( client components). If this is the problem, how can I add these templates?

Thanks,

RR

I'm having the same problem. I've chosen to install all components and yet no Report Model Project Template. Is there somewhere we could download this template. I don't know if this makes a difference, but I have a licensed copy of Visual Studio 2005 on my computer. Thanks in advance for your advice.|||

Ok. I've figured out what was wrong here.

The Report Model template appears to be related to integration services and is not included in the Express Edition.

I had installed Visual Studio, but not the SQL Server developer tools.

Later, I installed the express version when starting to work on SQL Server Reports.

After removing the Express Edition and then installing the SQL Server developer tools from the Visual Studio disks, the Report Model project is listed as an available option.

Report Model Template

HI,

When I create a new Business Intelligence Projects, I can not find the "Report Model Project" template. I am not sure If I failed to install a few components ( client components). If this is the problem, how can I add these templates?

Thanks,

RR

I'm having the same problem. I've chosen to install all components and yet no Report Model Project Template. Is there somewhere we could download this template. I don't know if this makes a difference, but I have a licensed copy of Visual Studio 2005 on my computer. Thanks in advance for your advice.|||

Ok. I've figured out what was wrong here.

The Report Model template appears to be related to integration services and is not included in the Express Edition.

I had installed Visual Studio, but not the SQL Server developer tools.

Later, I installed the express version when starting to work on SQL Server Reports.

After removing the Express Edition and then installing the SQL Server developer tools from the Visual Studio disks, the Report Model project is listed as an available option.

Report Model on tables without primary key

I am trying to create a report model on some tables that do not have a
primary key. The wizard keeps failing with the message "Table does not have a
primary key". Is a primary key mandatory?You could define logic primary key in your dsv.
"Dan Varozza" wrote:
> I am trying to create a report model on some tables that do not have a
> primary key. The wizard keeps failing with the message "Table does not have a
> primary key". Is a primary key mandatory?|||WHen I run the DSV wizard it tells me that "no foreign keys were found. You
can create logical relationships on matching columns."
Then it gives me the choice to "Create logical relationships by matching
columns" and 3 Foreign Key Matches to choose from:
"Same as Primary key"
or
"Same name as destination table name"
or
"Destination table name + primary key name"
I have tried all 3 of the choices above and still the wizard fails with the
same error. "Table does not have a primary key".
Any ideas?
"yongli" wrote:
> You could define logic primary key in your dsv.
> "Dan Varozza" wrote:
> > I am trying to create a report model on some tables that do not have a
> > primary key. The wizard keeps failing with the message "Table does not have a
> > primary key". Is a primary key mandatory?sql

Report Model from multiple data source

Creating a Data Source View from multiple Data Sources is no problem but what happens when you try to create the actuall Report Model. Using the primary Data Source doesn't give access to other databases, since each table name starts with dbo. Is there a option/flag to set to include database name?

Report Model DSVs only support one database, to pull in data from other databases you'll need to create a Named Query.

Analysis Services, on the other hand, don't have that problem, except for being limited to one database per DSV.

Note: I'm pretty sure I'm correct, but not 100%

sql