Showing posts with label report. Show all posts
Showing posts with label report. Show all posts

Thursday, March 22, 2012

Build = Error: Value cannot be null.

Hello Everyone,
I am having a heck of a time with this report. I have five datasets,
all interacting with stored procedures in SQL Server 2005, all using
the same parameter. The datasets run fine individually. I can run the
report fine. When I try to build or deploy the report, I get this:
-- Build started: Project: JobApplication, Configuration: Debug
--
Error: Value cannot be null.
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
==========
Not very descriptive unfortunately :( I'm not even sure where to
look.
Any ideas? Any help would be greatly appreciated.
Have a great day,
RyanDid one of your datasets change 'mysteriously' to text, rather than Stored
Procedure? I think I have seen the error before, but I can't remember if it
is related to this problem or not.
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Just a guess, you have refered a value which is a PK and in the report
properties you have clicked "Allow Null"
Amarnath, MCTS
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Thank you both for answering . . . in case anybody is interested:
I tried both stored procedure and text type datasets . . . both with
the same result. I'll check to make sure when it is set as a stored
procedure, it stays that way :)
Hmmm, I assume the Allow Null is for a parameter? If so, there is one
the one parameter and the "Allow null value" is unchecked.
Again, thank you for the responses; I'll give them a whirl. In the
mean time, I found that uploading the report manually works fine.
Ryan
On Mar 23, 12:56 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Just a guess, you have refered a value which is a PK and in the report
> properties you have clicked "Allow Null"
> Amarnath, MCTS
> "Ryan" wrote:
> > Hello Everyone,
> > I am having a heck of a time with this report. I have five datasets,
> > all interacting with stored procedures in SQL Server 2005, all using
> > the same parameter. The datasets run fine individually. I can run the
> > report fine. When I try to build or deploy the report, I get this:
> > -- Build started: Project: JobApplication, Configuration: Debug
> > --
> > Error: Value cannot be null.
> > ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> > ==========> > Not very descriptive unfortunately :( I'm not even sure where to
> > look.
> > Any ideas? Any help would be greatly appreciated.
> > Have a great day,
> > Ryan

bugs in subreport

I have a table contain a sub report . when I run on preview (visual studio), no problem . but when I run from my application , the report viewer just show "Report been generated" , never show the report .

after try and error I discover that when the query from main report return no records , plus the table contain a sub report , the problem occur only if you run it from your application using report viewer . not from visual studio . is this a bugs? anyway to resolve this or work around?

thks

a couple of things..

on your main report, you should probobaly set up a static dataset that always returns data... some like select from table where 1=1 or something like that. You can hide that information.

Secondly, besure to set up a description in the no rows property of the main and sub reports.

Bugs

What do you do when you think that you've got a bug? Do
you have to call MS Support? Or do they have a free way
to report a bug?First, check the documentation and search the MS Knowledge Base to see if
it's a known issue:
http://support.microsoft.com/defaul...h;en-gb;kbhowto
Use Google to search the archives of the sqlserver newsgroups to see if your
problem has been discussed before.
If you're still not having any luck then post your problem here. If possible
post some some code that will actually reproduce the problem so that others
can test it out and suggest solutions.
You can also go via MS Product Support. If they acknowledge it as an
undocumented bug then you shouldn't be charged for the call.
David Portas
SQL Server MVP
--

Bugs

What do you do when you think that you've got a bug? Do
you have to call MS Support? Or do they have a free way
to report a bug?First, check the documentation and search the MS Knowledge Base to see if
it's a known issue:
http://support.microsoft.com/default.aspx?scid=fh;en-gb;kbhowto
Use Google to search the archives of the sqlserver newsgroups to see if your
problem has been discussed before.
If you're still not having any luck then post your problem here. If possible
post some some code that will actually reproduce the problem so that others
can test it out and suggest solutions.
You can also go via MS Product Support. If they acknowledge it as an
undocumented bug then you shouldn't be charged for the call.
--
David Portas
SQL Server MVP
--

Bugs

What do you do when you think that you've got a bug? Do
you have to call MS Support? Or do they have a free way
to report a bug?
First, check the documentation and search the MS Knowledge Base to see if
it's a known issue:
http://support.microsoft.com/default...;en-gb;kbhowto
Use Google to search the archives of the sqlserver newsgroups to see if your
problem has been discussed before.
If you're still not having any luck then post your problem here. If possible
post some some code that will actually reproduce the problem so that others
can test it out and suggest solutions.
You can also go via MS Product Support. If they acknowledge it as an
undocumented bug then you shouldn't be charged for the call.
David Portas
SQL Server MVP
sql

Bug?: rsMultiReportItemsInPageSectionExpression when one report item, but referenced twice

When I reference the same report item twice in my footer expression I get an
error saying that I've refereced two items. My motivation is the database
field on each page footer. I have a custom assembly instance that stores
the value from the first page, then returns it when I am passing in empty
values. I tried the page number from the globals collection but ran into
#Error...anyway, getting off base. My issue is that the report expression
references a single item twice. Should I be able to do this? If not,
should the error message be more specific?
Expression:
=IIf(IsNothing(ReportItems!txtSchoolNameHidden.Value)=False,
Code.loc.SchoolName(ReportItems!txtSchoolNameHidden.Value),
Code.loc.SchoolName(""))
Error:
[rsMultiReportItemsInPageSectionExpression] The Value expression for the
textbox 'txtSchoolName' refers to more than one report item. An expression
in a page header or footer can refer to only one report item.Submitted bug to Microsoft via Product Feedback
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:%23RtLLbKNGHA.3728@.tk2msftngp13.phx.gbl...
> When I reference the same report item twice in my footer expression I get
> an error saying that I've refereced two items. My motivation is the
> database field on each page footer. I have a custom assembly instance
> that stores the value from the first page, then returns it when I am
> passing in empty values. I tried the page number from the globals
> collection but ran into #Error...anyway, getting off base. My issue is
> that the report expression references a single item twice. Should I be
> able to do this? If not, should the error message be more specific?
> Expression:
> =IIf(IsNothing(ReportItems!txtSchoolNameHidden.Value)=False,
> Code.loc.SchoolName(ReportItems!txtSchoolNameHidden.Value),
> Code.loc.SchoolName(""))
> Error:
> [rsMultiReportItemsInPageSectionExpression] The Value expression for the
> textbox 'txtSchoolName' refers to more than one report item. An
> expression in a page header or footer can refer to only one report item.
>

BUG?: Deploy to non-trusted SSL site will fail

If I try to deploy report solution to SSL site which I do not trust
(certificate not in Trusted Root) the deployment will fail.
It would be better to pop-up the dialog to choose to trust or not (like the
dialog in IE).Hi Dinko:
I take it you are deploying with the web service? It's a pain, but you
can create a class implementing the ICertificatePolicy class to
determine what certificates you'll allow your script / application to
trust.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemneticertificatepolicyclasstopic.asp
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Tue, 2 Nov 2004 14:29:34 +0100, "Dinko Deranja" <dd@.dd.dd> wrote:
>If I try to deploy report solution to SSL site which I do not trust
>(certificate not in Trusted Root) the deployment will fail.
>It would be better to pop-up the dialog to choose to trust or not (like the
>dialog in IE).
>

Bug? Report Builder returning only unique records!

Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.

Example:

TransID Type TransQuantity

1 CS 4
2 CS 4
3 CP 2
4 CN 3

Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:

Type TransQuantity

CS 4
CP 2
CN 3

Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.

Thanks,
Taurkon

You need to be aware of what Report Builder is grouping on. If you don't want rows based on distinct values, add the ID field first to make sure you get an entity group, then remove it later.

See this blog post for more info:

http://blogs.msdn.com/bobmeyers/archive/2006/12/20/getting-grouping-right-in-report-builder.aspx

Hope this helps!

|||Thanks Bob for your response.

Tuesday, March 20, 2012

bug? DeviceInfo ColorDepth ignored for TIFF image

Hello!
I have a problem generating report (format = IMAGE) using following
DeviceInfo settings:
<DeviceInfo>
<ColorDepth>1</ColorDepth>
<OutputFormat>TIFF</OutputFormat>
</DeviceInfo>
Using this settings, generated report (TIFF) always has 24 bit color
depth which is default value, and I wanted to generate black & white
report (1 bit)
Documentation states that ColorDepth could be changed only for TIFF,
but it is not true, it always applies the default value and ignores
user settings ?!
Is this a bug in reporting server?
Any ideas on how to generate TIFF 1-bit with Reporting Server are
welcome...
Thanks,
JohnYour observation is correct and yes, this is a known bug. I have passed your
comments along to the appropriate parties.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Smith" <ng950@.yahoo.com> wrote in message
news:de84550e.0408200420.287ad689@.posting.google.com...
> Hello!
> I have a problem generating report (format = IMAGE) using following
> DeviceInfo settings:
> <DeviceInfo>
> <ColorDepth>1</ColorDepth>
> <OutputFormat>TIFF</OutputFormat>
> </DeviceInfo>
> Using this settings, generated report (TIFF) always has 24 bit color
> depth which is default value, and I wanted to generate black & white
> report (1 bit)
> Documentation states that ColorDepth could be changed only for TIFF,
> but it is not true, it always applies the default value and ignores
> user settings ?!
> Is this a bug in reporting server?
> Any ideas on how to generate TIFF 1-bit with Reporting Server are
> welcome...
> Thanks,
> John

Bug? Available Values list for boolean parameters

Hi,
I have defined a boolean parameter in my report and I wish to display a list
of possible values instead of the True/False radio button. I have defined
two possible values in the Available Values list (Report Parameters Window)
but it looks as though they are ignored, the parameter toolbar still
displaying the True/False radio buttons...
Hope someone can help.Looks like a bug to me...best chance is to programatically create your
own parameter page.
"webfrog" <webfrog@.discussions.microsoft.com> wrote in message news:<11B5246A-F6E6-4583-8945-A945851F58CD@.microsoft.com>...
> Hi,
> I have defined a boolean parameter in my report and I wish to display a list
> of possible values instead of the True/False radio button. I have defined
> two possible values in the Available Values list (Report Parameters Window)
> but it looks as though they are ignored, the parameter toolbar still
> displaying the True/False radio buttons...
> Hope someone can help.|||If you change your datatype to integer, then put in available values such
that true = 1 and false = 0, you will get a drop-down list with true & false
options.
"webfrog" wrote:
> Hi,
> I have defined a boolean parameter in my report and I wish to display a list
> of possible values instead of the True/False radio button. I have defined
> two possible values in the Available Values list (Report Parameters Window)
> but it looks as though they are ignored, the parameter toolbar still
> displaying the True/False radio buttons...
> Hope someone can help.
>

Bug?

I have a .rdl file which I opened in visual studio 2005. I answered Yes to the prompt Microsoft Report Designer. . . Do you want to convert this file? [Yes] Then I saved the file.

The issue is when I am in Visual Studio and click on the Preview tab, I get the following error:

[rsErrorLoadingCodeModule] Error while loading code module: ‘x.ReportDefinitionsHelper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’. Details: Could not load file or assembly 'AdvancedMD.Report.ReportDefinitionsHelper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

There was a <CodeModule> which existed prior to the conversion and the preview worked fine.

I tried to re-add the reference by clicking on the Layout tab then go to the report properties tab. Then, under references I clicked on the collection and browsed to the file which I wanted to reference and clicked ok, but I still get the error above when I click on the Preview tab. I tried to remove the reference save then come back in and add it again, but it still gives the error above.

The file I am trying to add to <CodeModule> (x.ReportDefinitionsHelper) references the following:

System

System.Data

System.Xml

Any help would be greatly appreciated.

Thanks

(I can load the .rdl to reporting services 2005 and go into Reporting Services Manager and view the report without any issues.)

I just had to move the file I was referenceing to the following location:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||This solved the problem for me too. I have part of the VS2005 installed on my D: drive instead of the C: drive. But regardless of this I think it is strange that we have to put our own dll's in the MS directories. I would like to have them in my own project files. I think it is a bug since the VS2005 has a project structure, and it seems this does not fit in.|||

I had the same problem and however I have installed VS2005 in C: so that isn't the problem... the solution for me, as for TMK and Jelle, has been to put my dll in:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

What i would like to know is the reason because of we have to put our dlls in that directory if in our references we specified another directory...

Can anyone explain it to us?

Bug?

I have a .rdl file which I opened in visual studio 2005. I answered Yes to the prompt Microsoft Report Designer. . . Do you want to convert this file? [Yes] Then I saved the file.

The issue is when I am in Visual Studio and click on the Preview tab, I get the following error:

[rsErrorLoadingCodeModule] Error while loading code module: ‘x.ReportDefinitionsHelper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’. Details: Could not load file or assembly 'AdvancedMD.Report.ReportDefinitionsHelper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

There was a <CodeModule> which existed prior to the conversion and the preview worked fine.

I tried to re-add the reference by clicking on the Layout tab then go to the report properties tab. Then, under references I clicked on the collection and browsed to the file which I wanted to reference and clicked ok, but I still get the error above when I click on the Preview tab. I tried to remove the reference save then come back in and add it again, but it still gives the error above.

The file I am trying to add to <CodeModule> (x.ReportDefinitionsHelper) references the following:

System

System.Data

System.Xml

Any help would be greatly appreciated.

Thanks

(I can load the .rdl to reporting services 2005 and go into Reporting Services Manager and view the report without any issues.)

I just had to move the file I was referenceing to the following location:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||This solved the problem for me too. I have part of the VS2005 installed on my D: drive instead of the C: drive. But regardless of this I think it is strange that we have to put our own dll's in the MS directories. I would like to have them in my own project files. I think it is a bug since the VS2005 has a project structure, and it seems this does not fit in.|||

I had the same problem and however I have installed VS2005 in C: so that isn't the problem... the solution for me, as for TMK and Jelle, has been to put my dll in:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

What i would like to know is the reason because of we have to put our dlls in that directory if in our references we specified another directory...

Can anyone explain it to us?

BUG: Reporting Services Custom Security and Subscriptions?

Basically we have implemented custom security so that our logins to the
Report Server are using details in a custom database. They are not Windows
usernames/passwords. However when we use the web service api
(ListSubscriptions) to return user's subscriptions we get nothing? After
digging further with Reflector I found the following code in an RS assembly:
public ArrayList ListSubscriptions(string user, string report)
{
ArrayList list1 = new ArrayList();
string text1 = "select
S.[SubscriptionID],
S.[Report_OID],
S.[Locale],
S.[InactiveFlags],
S.[DeliveryExtension],
S.[ExtensionSettings],
SUSER_SNAME(Modified.[Sid]),
Modified.[UserName],
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[EventType],
S.[MatchData],
S.[Parameters],
S.[DataSettings],
A.[TotalNotifications],
A.[TotalSuccesses],
A.[TotalFailures],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
CAT.[Path],
S.[LastRunTime],
CAT.[Type],
SD.NtSecDescPrimary
from
[Subscriptions] S inner join [Catalog] CAT on S.[Report_OID] = CAT.[ItemID]
inner join [Users] Owner on S.OwnerID = Owner.UserID
inner join [Users] Modified on S.ModifiedByID = Modified.UserID
left outer join [SecData] SD on CAT.[PolicyID] = SD.[PolicyID]
AND SD.AuthType = @.AuthType
left outer join [ActiveSubscriptions] A with (NOLOCK) on
S.[SubscriptionID] = A.[SubscriptionID]";
InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
CommandType.Text, base.Connection, base.Transaction,
base.SqlCommandTimeout);
command1.Parameters.Add("@.AuthType",
WebConfigUtil.AuthenticationType);
if ((user != null) && (user != ""))
{
this.AddClause(ref text1, ref flag1, "Owner.[Sid] = @.UserSid");
command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
}
<removed code>
}
Now the interesting bit is:
this.AddClause(ref text1, ref flag1, "Owner.[Sid] = @.UserSid");
command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
Why are they trying to lookup a SID for my custom username? Of course this
fails, which results in a WHERE clause that fails to grabs our
subscriptions.
Has anyone else found this? Am I completely wrong?
BTW: I'm using Reporting Services SP 2.
James Snape (for Ryan Stevens)Hello James,
Based on my scope, if a report server user is not mapped to a Windows user
sid, it shall be NULL. I think this shall work properly in SQL query.
select * from users where sid=NULL
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| Subject: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Fri, 7 Oct 2005 09:25:13 +0100
| Lines: 75
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net 213.92.131.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53918
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Basically we have implemented custom security so that our logins to the
| Report Server are using details in a custom database. They are not
Windows
| usernames/passwords. However when we use the web service api
| (ListSubscriptions) to return user's subscriptions we get nothing? After
| digging further with Reflector I found the following code in an RS
assembly:
|
| public ArrayList ListSubscriptions(string user, string report)
| {
| ArrayList list1 = new ArrayList();
| string text1 = "select
| S.[SubscriptionID],
| S.[Report_OID],
| S.[Locale],
| S.[InactiveFlags],
| S.[DeliveryExtension],
| S.[ExtensionSettings],
| SUSER_SNAME(Modified.[Sid]),
| Modified.[UserName],
| S.[ModifiedDate],
| S.[Description],
| S.[LastStatus],
| S.[EventType],
| S.[MatchData],
| S.[Parameters],
| S.[DataSettings],
| A.[TotalNotifications],
| A.[TotalSuccesses],
| A.[TotalFailures],
| SUSER_SNAME(Owner.[Sid]),
| Owner.[UserName],
| CAT.[Path],
| S.[LastRunTime],
| CAT.[Type],
| SD.NtSecDescPrimary
| from
| [Subscriptions] S inner join [Catalog] CAT on S.[Report_OID]
=| CAT.[ItemID]
| inner join [Users] Owner on S.OwnerID = Owner.UserID
| inner join [Users] Modified on S.ModifiedByID =Modified.UserID
| left outer join [SecData] SD on CAT.[PolicyID] =SD.[PolicyID]
| AND SD.AuthType = @.AuthType
| left outer join [ActiveSubscriptions] A with (NOLOCK) on
| S.[SubscriptionID] = A.[SubscriptionID]";
|
| InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
| CommandType.Text, base.Connection, base.Transaction,
| base.SqlCommandTimeout);
| command1.Parameters.Add("@.AuthType",
| WebConfigUtil.AuthenticationType);
| if ((user != null) && (user != ""))
| {
| this.AddClause(ref text1, ref flag1, "Owner.[Sid] =@.UserSid");
| command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
| }
|
| <removed code>
| }
|
| Now the interesting bit is:
|
| this.AddClause(ref text1, ref flag1, "Owner.[Sid] =@.UserSid");
| command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
|
|
| Why are they trying to lookup a SID for my custom username? Of course
this
| fails, which results in a WHERE clause that fails to grabs our
| subscriptions.
|
| Has anyone else found this? Am I completely wrong?
|
| BTW: I'm using Reporting Services SP 2.
|
| James Snape (for Ryan Stevens)
|
|
||||Hi Peter,
A comparison against NULL is always false so your query below never returns
any results.
Regards,
James Snape
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hello James,
> Based on my scope, if a report server user is not mapped to a Windows user
> sid, it shall be NULL. I think this shall work properly in SQL query.
> select * from users where sid=NULL
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | Subject: BUG: Reporting Services Custom Security and Subscriptions?
> | Date: Fri, 7 Oct 2005 09:25:13 +0100
> | Lines: 75
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> 213.92.131.1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:53918
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Basically we have implemented custom security so that our logins to the
> | Report Server are using details in a custom database. They are not
> Windows
> | usernames/passwords. However when we use the web service api
> | (ListSubscriptions) to return user's subscriptions we get nothing? After
> | digging further with Reflector I found the following code in an RS
> assembly:
> |
> | public ArrayList ListSubscriptions(string user, string report)
> | {
> | ArrayList list1 = new ArrayList();
> | string text1 = "select
> | S.[SubscriptionID],
> | S.[Report_OID],
> | S.[Locale],
> | S.[InactiveFlags],
> | S.[DeliveryExtension],
> | S.[ExtensionSettings],
> | SUSER_SNAME(Modified.[Sid]),
> | Modified.[UserName],
> | S.[ModifiedDate],
> | S.[Description],
> | S.[LastStatus],
> | S.[EventType],
> | S.[MatchData],
> | S.[Parameters],
> | S.[DataSettings],
> | A.[TotalNotifications],
> | A.[TotalSuccesses],
> | A.[TotalFailures],
> | SUSER_SNAME(Owner.[Sid]),
> | Owner.[UserName],
> | CAT.[Path],
> | S.[LastRunTime],
> | CAT.[Type],
> | SD.NtSecDescPrimary
> | from
> | [Subscriptions] S inner join [Catalog] CAT on
> S.[Report_OID]
> => | CAT.[ItemID]
> | inner join [Users] Owner on S.OwnerID = Owner.UserID
> | inner join [Users] Modified on S.ModifiedByID => Modified.UserID
> | left outer join [SecData] SD on CAT.[PolicyID] => SD.[PolicyID]
> | AND SD.AuthType = @.AuthType
> | left outer join [ActiveSubscriptions] A with (NOLOCK) on
> | S.[SubscriptionID] = A.[SubscriptionID]";
> |
> | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
> | CommandType.Text, base.Connection, base.Transaction,
> | base.SqlCommandTimeout);
> | command1.Parameters.Add("@.AuthType",
> | WebConfigUtil.AuthenticationType);
> | if ((user != null) && (user != ""))
> | {
> | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => @.UserSid");
> | command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
> | }
> |
> | <removed code>
> | }
> |
> | Now the interesting bit is:
> |
> | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => @.UserSid");
> | command1.Parameters.Add("@.UserSid", Native.NameToSid(user));
> |
> |
> | Why are they trying to lookup a SID for my custom username? Of course
> this
> | fails, which results in a WHERE clause that fails to grabs our
> | subscriptions.
> |
> | Has anyone else found this? Am I completely wrong?
> |
> | BTW: I'm using Reporting Services SP 2.
> |
> | James Snape (for Ryan Stevens)
> |
> |
> |
>|||Hello James,
If ANSI_NULLS is off, it shall return all users with NULL SID. I have
reported this issue to the proper channel but there is no feedback yet. If
we have any update on this, we will let you know.
Also, since the issue relates to source code evaluation, I recommend that
you open a Support incident with Microsoft Product Support Services so that
a dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
<te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Fri, 7 Oct 2005 12:23:31 +0100
| Lines: 140
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net 213.92.131.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53926
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi Peter,
|
| A comparison against NULL is always false so your query below never
returns
| any results.
|
| Regards,
| James Snape
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > Hello James,
| >
| > Based on my scope, if a report server user is not mapped to a Windows
user
| > sid, it shall be NULL. I think this shall work properly in SQL query.
| >
| > select * from users where sid=NULL
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| > --
| > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | Subject: BUG: Reporting Services Custom Security and Subscriptions?
| > | Date: Fri, 7 Oct 2005 09:25:13 +0100
| > | Lines: 75
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > 213.92.131.1
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:53918
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Basically we have implemented custom security so that our logins to
the
| > | Report Server are using details in a custom database. They are not
| > Windows
| > | usernames/passwords. However when we use the web service api
| > | (ListSubscriptions) to return user's subscriptions we get nothing?
After
| > | digging further with Reflector I found the following code in an RS
| > assembly:
| > |
| > | public ArrayList ListSubscriptions(string user, string report)
| > | {
| > | ArrayList list1 = new ArrayList();
| > | string text1 = "select
| > | S.[SubscriptionID],
| > | S.[Report_OID],
| > | S.[Locale],
| > | S.[InactiveFlags],
| > | S.[DeliveryExtension],
| > | S.[ExtensionSettings],
| > | SUSER_SNAME(Modified.[Sid]),
| > | Modified.[UserName],
| > | S.[ModifiedDate],
| > | S.[Description],
| > | S.[LastStatus],
| > | S.[EventType],
| > | S.[MatchData],
| > | S.[Parameters],
| > | S.[DataSettings],
| > | A.[TotalNotifications],
| > | A.[TotalSuccesses],
| > | A.[TotalFailures],
| > | SUSER_SNAME(Owner.[Sid]),
| > | Owner.[UserName],
| > | CAT.[Path],
| > | S.[LastRunTime],
| > | CAT.[Type],
| > | SD.NtSecDescPrimary
| > | from
| > | [Subscriptions] S inner join [Catalog] CAT on
| > S.[Report_OID]
| > =| > | CAT.[ItemID]
| > | inner join [Users] Owner on S.OwnerID = Owner.UserID
| > | inner join [Users] Modified on S.ModifiedByID =| > Modified.UserID
| > | left outer join [SecData] SD on CAT.[PolicyID] =| > SD.[PolicyID]
| > | AND SD.AuthType = @.AuthType
| > | left outer join [ActiveSubscriptions] A with (NOLOCK) on
| > | S.[SubscriptionID] = A.[SubscriptionID]";
| > |
| > | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
| > | CommandType.Text, base.Connection, base.Transaction,
| > | base.SqlCommandTimeout);
| > | command1.Parameters.Add("@.AuthType",
| > | WebConfigUtil.AuthenticationType);
| > | if ((user != null) && (user != ""))
| > | {
| > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > @.UserSid");
| > | command1.Parameters.Add("@.UserSid",
Native.NameToSid(user));
| > | }
| > |
| > | <removed code>
| > | }
| > |
| > | Now the interesting bit is:
| > |
| > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > @.UserSid");
| > | command1.Parameters.Add("@.UserSid",
Native.NameToSid(user));
| > |
| > |
| > | Why are they trying to lookup a SID for my custom username? Of course
| > this
| > | fails, which results in a WHERE clause that fails to grabs our
| > | subscriptions.
| > |
| > | Has anyone else found this? Am I completely wrong?
| > |
| > | BTW: I'm using Reporting Services SP 2.
| > |
| > | James Snape (for Ryan Stevens)
| > |
| > |
| > |
| >
|
|
||||It's OK Peter, this issue is not troubling us because the workaround we are
using is to list all subscriptions and filter in our app server. Not great
but it works. I just thought you would like to know of the bug for inclusion
in your next SP.
Regards,
James Snape
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
> Hello James,
> If ANSI_NULLS is off, it shall return all users with NULL SID. I have
> reported this issue to the proper channel but there is no feedback yet. If
> we have any update on this, we will let you know.
> Also, since the issue relates to source code evaluation, I recommend that
> you open a Support incident with Microsoft Product Support Services so
> that
> a dedicated Support Professional can assist with this case. If you need
> any
> help in this regard, please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> <te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
> | Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
> | Date: Fri, 7 Oct 2005 12:23:31 +0100
> | Lines: 140
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> 213.92.131.1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:53926
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi Peter,
> |
> | A comparison against NULL is always false so your query below never
> returns
> | any results.
> |
> | Regards,
> | James Snape
> |
> | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> | > Hello James,
> | >
> | > Based on my scope, if a report server user is not mapped to a Windows
> user
> | > sid, it shall be NULL. I think this shall work properly in SQL query.
> | >
> | > select * from users where sid=NULL
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > =====================================================> | >
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | >
> | > --
> | > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
> | > | Subject: BUG: Reporting Services Custom Security and Subscriptions?
> | > | Date: Fri, 7 Oct 2005 09:25:13 +0100
> | > | Lines: 75
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
> | > 213.92.131.1
> | > | Path:
> TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | > | Xref: TK2MSFTNGXA01.phx.gbl
> | > microsoft.public.sqlserver.reportingsvcs:53918
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | Basically we have implemented custom security so that our logins to
> the
> | > | Report Server are using details in a custom database. They are not
> | > Windows
> | > | usernames/passwords. However when we use the web service api
> | > | (ListSubscriptions) to return user's subscriptions we get nothing?
> After
> | > | digging further with Reflector I found the following code in an RS
> | > assembly:
> | > |
> | > | public ArrayList ListSubscriptions(string user, string report)
> | > | {
> | > | ArrayList list1 = new ArrayList();
> | > | string text1 = "select
> | > | S.[SubscriptionID],
> | > | S.[Report_OID],
> | > | S.[Locale],
> | > | S.[InactiveFlags],
> | > | S.[DeliveryExtension],
> | > | S.[ExtensionSettings],
> | > | SUSER_SNAME(Modified.[Sid]),
> | > | Modified.[UserName],
> | > | S.[ModifiedDate],
> | > | S.[Description],
> | > | S.[LastStatus],
> | > | S.[EventType],
> | > | S.[MatchData],
> | > | S.[Parameters],
> | > | S.[DataSettings],
> | > | A.[TotalNotifications],
> | > | A.[TotalSuccesses],
> | > | A.[TotalFailures],
> | > | SUSER_SNAME(Owner.[Sid]),
> | > | Owner.[UserName],
> | > | CAT.[Path],
> | > | S.[LastRunTime],
> | > | CAT.[Type],
> | > | SD.NtSecDescPrimary
> | > | from
> | > | [Subscriptions] S inner join [Catalog] CAT on
> | > S.[Report_OID]
> | > => | > | CAT.[ItemID]
> | > | inner join [Users] Owner on S.OwnerID = Owner.UserID
> | > | inner join [Users] Modified on S.ModifiedByID => | > Modified.UserID
> | > | left outer join [SecData] SD on CAT.[PolicyID] => | > SD.[PolicyID]
> | > | AND SD.AuthType = @.AuthType
> | > | left outer join [ActiveSubscriptions] A with (NOLOCK)
> on
> | > | S.[SubscriptionID] = A.[SubscriptionID]";
> | > |
> | > | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
> | > | CommandType.Text, base.Connection, base.Transaction,
> | > | base.SqlCommandTimeout);
> | > | command1.Parameters.Add("@.AuthType",
> | > | WebConfigUtil.AuthenticationType);
> | > | if ((user != null) && (user != ""))
> | > | {
> | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => | > @.UserSid");
> | > | command1.Parameters.Add("@.UserSid",
> Native.NameToSid(user));
> | > | }
> | > |
> | > | <removed code>
> | > | }
> | > |
> | > | Now the interesting bit is:
> | > |
> | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] => | > @.UserSid");
> | > | command1.Parameters.Add("@.UserSid",
> Native.NameToSid(user));
> | > |
> | > |
> | > | Why are they trying to lookup a SID for my custom username? Of
> course
> | > this
> | > | fails, which results in a WHERE clause that fails to grabs our
> | > | subscriptions.
> | > |
> | > | Has anyone else found this? Am I completely wrong?
> | > |
> | > | BTW: I'm using Reporting Services SP 2.
> | > |
> | > | James Snape (for Ryan Stevens)
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>|||Hello James,
Please rest assured that your feedback on this issue is routed to the
proper channel. Again, thank you for taking time to report this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
<te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
<uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
<r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Tue, 11 Oct 2005 15:54:54 +0100
| Lines: 219
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <upkv$OnzFHA.1264@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: exony-ltd-02.altohiway.com 213.83.66.226
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
3.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:61002
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| It's OK Peter, this issue is not troubling us because the workaround we
are
| using is to list all subscriptions and filter in our app server. Not
great
| but it works. I just thought you would like to know of the bug for
inclusion
| in your next SP.
|
| Regards,
| James Snape
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
| > Hello James,
| >
| > If ANSI_NULLS is off, it shall return all users with NULL SID. I have
| > reported this issue to the proper channel but there is no feedback yet.
If
| > we have any update on this, we will let you know.
| >
| > Also, since the issue relates to source code evaluation, I recommend
that
| > you open a Support incident with Microsoft Product Support Services so
| > that
| > a dedicated Support Professional can assist with this case. If you need
| > any
| > help in this regard, please let me know.
| >
| > For a complete list of Microsoft Product Support Services phone numbers,
| > please go to the following address on the World Wide Web:
| > http://support.microsoft.com/directory/overview.asp
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| > --
| > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | References: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > <te#MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl>
| > | Subject: Re: BUG: Reporting Services Custom Security and
Subscriptions?
| > | Date: Fri, 7 Oct 2005 12:23:31 +0100
| > | Lines: 140
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | Message-ID: <uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > 213.92.131.1
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:53926
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Hi Peter,
| > |
| > | A comparison against NULL is always false so your query below never
| > returns
| > | any results.
| > |
| > | Regards,
| > | James Snape
| > |
| > | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > | > Hello James,
| > | >
| > | > Based on my scope, if a report server user is not mapped to a
Windows
| > user
| > | > sid, it shall be NULL. I think this shall work properly in SQL
query.
| > | >
| > | > select * from users where sid=NULL
| > | >
| > | > Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner Support
| > | >
| > | > When responding to posts, please "Reply to Group" via your
newsreader
| > so
| > | > that others may learn and benefit from your issue.
| > | >
| > | > =====================================================| > | >
| > | >
| > | >
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > | > rights.
| > | >
| > | > --
| > | > | From: "James Snape" <jim_snape.at.hotmail.com@.online.nospam>
| > | > | Subject: BUG: Reporting Services Custom Security and
Subscriptions?
| > | > | Date: Fri, 7 Oct 2005 09:25:13 +0100
| > | > | Lines: 75
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | > | X-RFC2646: Format=Flowed; Original
| > | > | Message-ID: <OH8poixyFHA.2540@.TK2MSFTNGP09.phx.gbl>
| > | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > | > 213.92.131.1
| > | > | Path:
| > TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | > | Xref: TK2MSFTNGXA01.phx.gbl
| > | > microsoft.public.sqlserver.reportingsvcs:53918
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > | > |
| > | > | Basically we have implemented custom security so that our logins
to
| > the
| > | > | Report Server are using details in a custom database. They are not
| > | > Windows
| > | > | usernames/passwords. However when we use the web service api
| > | > | (ListSubscriptions) to return user's subscriptions we get nothing?
| > After
| > | > | digging further with Reflector I found the following code in an RS
| > | > assembly:
| > | > |
| > | > | public ArrayList ListSubscriptions(string user, string report)
| > | > | {
| > | > | ArrayList list1 = new ArrayList();
| > | > | string text1 = "select
| > | > | S.[SubscriptionID],
| > | > | S.[Report_OID],
| > | > | S.[Locale],
| > | > | S.[InactiveFlags],
| > | > | S.[DeliveryExtension],
| > | > | S.[ExtensionSettings],
| > | > | SUSER_SNAME(Modified.[Sid]),
| > | > | Modified.[UserName],
| > | > | S.[ModifiedDate],
| > | > | S.[Description],
| > | > | S.[LastStatus],
| > | > | S.[EventType],
| > | > | S.[MatchData],
| > | > | S.[Parameters],
| > | > | S.[DataSettings],
| > | > | A.[TotalNotifications],
| > | > | A.[TotalSuccesses],
| > | > | A.[TotalFailures],
| > | > | SUSER_SNAME(Owner.[Sid]),
| > | > | Owner.[UserName],
| > | > | CAT.[Path],
| > | > | S.[LastRunTime],
| > | > | CAT.[Type],
| > | > | SD.NtSecDescPrimary
| > | > | from
| > | > | [Subscriptions] S inner join [Catalog] CAT on
| > | > S.[Report_OID]
| > | > =| > | > | CAT.[ItemID]
| > | > | inner join [Users] Owner on S.OwnerID = Owner.UserID
| > | > | inner join [Users] Modified on S.ModifiedByID =| > | > Modified.UserID
| > | > | left outer join [SecData] SD on CAT.[PolicyID] =| > | > SD.[PolicyID]
| > | > | AND SD.AuthType = @.AuthType
| > | > | left outer join [ActiveSubscriptions] A with
(NOLOCK)
| > on
| > | > | S.[SubscriptionID] = A.[SubscriptionID]";
| > | > |
| > | > | InstrumentedSqlCommand command1 =Storage.NewSqlCommand(text1,
| > | > | CommandType.Text, base.Connection, base.Transaction,
| > | > | base.SqlCommandTimeout);
| > | > | command1.Parameters.Add("@.AuthType",
| > | > | WebConfigUtil.AuthenticationType);
| > | > | if ((user != null) && (user != ""))
| > | > | {
| > | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > | > @.UserSid");
| > | > | command1.Parameters.Add("@.UserSid",
| > Native.NameToSid(user));
| > | > | }
| > | > |
| > | > | <removed code>
| > | > | }
| > | > |
| > | > | Now the interesting bit is:
| > | > |
| > | > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =| > | > @.UserSid");
| > | > | command1.Parameters.Add("@.UserSid",
| > Native.NameToSid(user));
| > | > |
| > | > |
| > | > | Why are they trying to lookup a SID for my custom username? Of
| > course
| > | > this
| > | > | fails, which results in a WHERE clause that fails to grabs our
| > | > | subscriptions.
| > | > |
| > | > | Has anyone else found this? Am I completely wrong?
| > | > |
| > | > | BTW: I'm using Reporting Services SP 2.
| > | > |
| > | > | James Snape (for Ryan Stevens)
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

BUG: provider cannot derive parameter information and setparameter

I create reports on an oracle db. I use the correct oracle provider. When i
create a report parameter and run it in the datatab, it says "provider cannot
derive parameter information and setparameterinfo has not been called".
However if I go to generic query designer or the layout tab, the report
parameter works correctly.
This appears to be a bug!!. Could someone from Microsoft please confirm this.
Much appreciated
Kevin WeirYou will need an Oracle Client 8.1.7 or later installed on the server and
the client machines. Please verify you are not using an older Oracle client.
Regardless of the data provider (OleDB or managed), they will need the
Oracle Client (OCI) to actually talk to the Oracle server.
As indicated in several other discussion threads on this newsgroup, the
visual query designer (with 4 panes) is a shared component of the VS
framework (which is unable to use the managed provider and uses the MS OleDB
provider for Oracle instead). Even if you select "Oracle" from the data
source dialog, the visual query designer will actually use the OleDB
provider, while the generic query designer will *always* use the data
provider you actually selected. I.e. "Oracle" is the managed provider of the
.NET framework. Most likely you are using named parameters (e.g. :par1)
which are only supported by the managed Oracle provider (hence the query
execution works successfully in the generic text-based query designer). The
OleDB provider only supports unnamed parameters.
Some information can also be found in this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Weir" <KevinWeir@.discussions.microsoft.com> wrote in message
news:D359816A-16DD-4CDD-959D-C5B066474C15@.microsoft.com...
> I create reports on an oracle db. I use the correct oracle provider. When
i
> create a report parameter and run it in the datatab, it says "provider
cannot
> derive parameter information and setparameterinfo has not been called".
> However if I go to generic query designer or the layout tab, the report
> parameter works correctly.
> This appears to be a bug!!. Could someone from Microsoft please confirm
this.
> Much appreciated
> Kevin Weir
>|||Just adding another comment: The graphical query designer in RS is the VDT
component which supports only a limited list of data providers as outlined
in this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;811241
The text-based generic query designer in RS supports almost any data
provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23AOfSgAsEHA.3396@.tk2msftngp13.phx.gbl...
> You will need an Oracle Client 8.1.7 or later installed on the server and
> the client machines. Please verify you are not using an older Oracle
client.
> Regardless of the data provider (OleDB or managed), they will need the
> Oracle Client (OCI) to actually talk to the Oracle server.
> As indicated in several other discussion threads on this newsgroup, the
> visual query designer (with 4 panes) is a shared component of the VS
> framework (which is unable to use the managed provider and uses the MS
OleDB
> provider for Oracle instead). Even if you select "Oracle" from the data
> source dialog, the visual query designer will actually use the OleDB
> provider, while the generic query designer will *always* use the data
> provider you actually selected. I.e. "Oracle" is the managed provider of
the
> .NET framework. Most likely you are using named parameters (e.g. :par1)
> which are only supported by the managed Oracle provider (hence the query
> execution works successfully in the generic text-based query designer).
The
> OleDB provider only supports unnamed parameters.
> Some information can also be found in this KB article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Kevin Weir" <KevinWeir@.discussions.microsoft.com> wrote in message
> news:D359816A-16DD-4CDD-959D-C5B066474C15@.microsoft.com...
> > I create reports on an oracle db. I use the correct oracle provider.
When
> i
> > create a report parameter and run it in the datatab, it says "provider
> cannot
> > derive parameter information and setparameterinfo has not been called".
> >
> > However if I go to generic query designer or the layout tab, the report
> > parameter works correctly.
> >
> > This appears to be a bug!!. Could someone from Microsoft please confirm
> this.
> >
> > Much appreciated
> > Kevin Weir
> >
>|||Hi Robert
Thanks for your response. Your comments have clarrified this issue for me.
I did not realize the OLE DB provider was being used by Visual Designer
which accounts for different behavior in Generic Query designer.
For others that may read this thread we are using Oracle 9.2.0.4 client with
named parameters. For example, one of our queries might look like this:
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :EMPLOYEE_ID
Regards
Kevin
"Robert Bruckner [MSFT]" wrote:
> You will need an Oracle Client 8.1.7 or later installed on the server and
> the client machines. Please verify you are not using an older Oracle client.
> Regardless of the data provider (OleDB or managed), they will need the
> Oracle Client (OCI) to actually talk to the Oracle server.
> As indicated in several other discussion threads on this newsgroup, the
> visual query designer (with 4 panes) is a shared component of the VS
> framework (which is unable to use the managed provider and uses the MS OleDB
> provider for Oracle instead). Even if you select "Oracle" from the data
> source dialog, the visual query designer will actually use the OleDB
> provider, while the generic query designer will *always* use the data
> provider you actually selected. I.e. "Oracle" is the managed provider of the
> ..NET framework. Most likely you are using named parameters (e.g. :par1)
> which are only supported by the managed Oracle provider (hence the query
> execution works successfully in the generic text-based query designer). The
> OleDB provider only supports unnamed parameters.
> Some information can also be found in this KB article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kevin Weir" <KevinWeir@.discussions.microsoft.com> wrote in message
> news:D359816A-16DD-4CDD-959D-C5B066474C15@.microsoft.com...
> > I create reports on an oracle db. I use the correct oracle provider. When
> i
> > create a report parameter and run it in the datatab, it says "provider
> cannot
> > derive parameter information and setparameterinfo has not been called".
> >
> > However if I go to generic query designer or the layout tab, the report
> > parameter works correctly.
> >
> > This appears to be a bug!!. Could someone from Microsoft please confirm
> this.
> >
> > Much appreciated
> > Kevin Weir
> >
>
>

bug: PageBreak does not work when set Hidden (visibilty) using parameters

In my report project, I used many subreport in my master report. Each
subreport was put in a rectangle area so that I could setup
PageBreakAtEnd or PageBreakAtStart. In the meantime I also need to set
the Hidden property which is based on the parameters or conditional
expression for showing/hidding subreports. BUT PAGE BREAK does not work
at all.
If setting this HIDDEN property using False or True instead of
expression, page break still works fine. It's definitely such a big bug
that it makes subreports or other report items less useful. When
rendering report the layout of report is so strange. I knew this issue
last year.
Is there any solution or other alternative way to the issue or does SP2
resolve it?
Thanks,
LeonLeon I agree with you and have the same issue. We would appreciate really if
Reporting Service help desk give us some definitive response on this ASAP.
"leon" wrote:
> In my report project, I used many subreport in my master report. Each
> subreport was put in a rectangle area so that I could setup
> PageBreakAtEnd or PageBreakAtStart. In the meantime I also need to set
> the Hidden property which is based on the parameters or conditional
> expression for showing/hidding subreports. BUT PAGE BREAK does not work
> at all.
> If setting this HIDDEN property using False or True instead of
> expression, page break still works fine. It's definitely such a big bug
> that it makes subreports or other report items less useful. When
> rendering report the layout of report is so strange. I knew this issue
> last year.
> Is there any solution or other alternative way to the issue or does SP2
> resolve it?
> Thanks,
> Leon
>

BUG: Height property reverts to inches

Just thought I'd report a little bug I'd found.
My system is set to UK regional settings which includes metric measurements.
When I add section to a report, i.e Header or Footer the height property is in inches.
If I change the height, say to 2 it correctly treats it as centimetres. A small oversight me thinks!
Regards
Chris McGuiganYou are correct. Thanks for catching this one.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> wrote in message
news:A45D03E9-4B69-4FB9-B056-8B9A8683E23F@.microsoft.com...
> Just thought I'd report a little bug I'd found.
> My system is set to UK regional settings which includes metric
measurements.
> When I add section to a report, i.e Header or Footer the height property
is in inches.
> If I change the height, say to 2 it correctly treats it as centimetres. A
small oversight me thinks!
> Regards
> Chris McGuigan

Bug: default value showing as query based when it is not (report m

I have a report which uses a query to populate a parameter.
The default value is not queried, this is just set to "=1".
When the report is deployed, the parameter in report manager is showing as
'query based' which is wrong.
This is a pain in the ass as when I try linking to this report, I am not
able to set the value to something else.If you use a query to specify a range of parameters then it seems to show
default as 'query based' in the report manager.
What I have discovered is that if you change something to do with the
parameters on the report and redeploy, you can pick up the changes in the
linked report by repointing the linked report at the same report, this forces
some kind of refresh to occur and although very annoying, is easier than
recreating the linked report.
<despair>Thanks to MS for providing a UI of such high quality.</despair>
"adolf garlic" wrote:
> I have a report which uses a query to populate a parameter.
> The default value is not queried, this is just set to "=1".
> When the report is deployed, the parameter in report manager is showing as
> 'query based' which is wrong.
> This is a pain in the ass as when I try linking to this report, I am not
> able to set the value to something else.

bug: data-driven subscription generates deadlock in ReportServer D

I've got a report that can have about 50 possible parameter combinations. The
underlying query is fairly expensive, so I want to cache the dataset every
morning after I expire yesterday's cache. So I set up a data-driven
subscription to run every morning and execute all the parameter combinations
and deliver it to the Null delivery extension. The problem is that when the
scheduled time rolls around, it tries to run all 50 parameter combinations at
once. (Actually, it looks like it runs 2 or 3 combinations a second without
waiting for the first one to finish.) The query of the data sources run fine,
but I get a deadlock when Reporting Services tries to write the dataset to
the cache. Somewhere between 10% and 50% of the 50 fail every day. The logs
show a standard deadlock error. The error is below.
Is there any config file setting to tell RS to retry writing to the cache a
few times before giving up because of a deadlock? Is this possibly a SP2 fix?
Or is there any way to tell Reporting Services to run the 50 executions
serially instead of concurrently? Or do I need to investigate creating some
custom code to execute and cache the reports on schedule (so I can make it
execute them serially)?
Please let me know if you need more info... or if you need something from
the SQL Server logs. Thanks in advance.
--
ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
132) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Guid
reportId, ReportSnapshot snapshotData, DateTime executionDateTime, DateTime&
expirationDateTime)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
-- End of inner exception stack trace --Hi,
Try setting MaxQueueThreads to 1 in the RSReportServer.config file. You may
want to escalate this to the RS PSS since it looks like a bug to me.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:516B456B-0E41-46AA-B775-D3BA1B13C1F0@.microsoft.com...
> I've got a report that can have about 50 possible parameter combinations.
The
> underlying query is fairly expensive, so I want to cache the dataset every
> morning after I expire yesterday's cache. So I set up a data-driven
> subscription to run every morning and execute all the parameter
combinations
> and deliver it to the Null delivery extension. The problem is that when
the
> scheduled time rolls around, it tries to run all 50 parameter combinations
at
> once. (Actually, it looks like it runs 2 or 3 combinations a second
without
> waiting for the first one to finish.) The query of the data sources run
fine,
> but I get a deadlock when Reporting Services tries to write the dataset to
> the cache. Somewhere between 10% and 50% of the 50 fail every day. The
logs
> show a standard deadlock error. The error is below.
> Is there any config file setting to tell RS to retry writing to the cache
a
> few times before giving up because of a deadlock? Is this possibly a SP2
fix?
> Or is there any way to tell Reporting Services to run the 50 executions
> serially instead of concurrently? Or do I need to investigate creating
some
> custom code to execute and cache the reports on schedule (so I can make it
> execute them serially)?
> Please let me know if you need more info... or if you need something from
> the SQL Server logs. Thanks in advance.
> --
> ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
> Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details., ;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
> 132) was deadlocked on lock resources with another process and has been
> chosen as the deadlock victim. Rerun the transaction.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Gu
id
> reportId, ReportSnapshot snapshotData, DateTime executionDateTime,
DateTime&
> expirationDateTime)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
> -- End of inner exception stack trace --
>|||That did it. Thanks SO much for your response. Now the executions of each
individual parameter combination run serially and don't cause deadlocks.
I did some research on that config file setting you suggested, Teo. Here's
what I found:
MaxQueueThreads
Specifies the maximum number of threads dedicated to polling the event table
in the report server database.
Range: 0 to max integer.
The default is 0.
So if I understand correctly, this setting will ONLY affect the number of
threads used to process events like the fulfilling of subscriptions. Is that
correct? It will not impact performance of multiple users executing and
viewing reports on demand and at the same time, right?|||This is my understanding as well but verify it using the Performance
Monitor.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:C9EF39EE-1638-47E9-AE5F-24896700A73D@.microsoft.com...
> That did it. Thanks SO much for your response. Now the executions of each
> individual parameter combination run serially and don't cause deadlocks.
> I did some research on that config file setting you suggested, Teo. Here's
> what I found:
> MaxQueueThreads
> Specifies the maximum number of threads dedicated to polling the event
table
> in the report server database.
> Range: 0 to max integer.
> The default is 0.
> So if I understand correctly, this setting will ONLY affect the number of
> threads used to process events like the fulfilling of subscriptions. Is
that
> correct? It will not impact performance of multiple users executing and
> viewing reports on demand and at the same time, right?

Monday, March 19, 2012

BUG when export to PDF

I have this bug when tring to export a report to pdf...
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Index and length must refer to a location within the string. Parameter name:
length
Any ideas? This only happens with 1 report, all other reports work fine...I finally found the cause of the bug if anyone is interested..
In the rdl code of the report I saw that there were 2 <language> parts, the
<Report> had a langauge setting of en-IRL but there was a textbox somewhere
else on the report that also had its own <language> setting which was
different to the report setting. I removed the <language> setting from the
textbox and redeplyed the report and now the report exports to PDF.
"NH" wrote:
> I have this bug when tring to export a report to pdf...
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Index and length must refer to a location within the string. Parameter name:
> length
>
> Any ideas? This only happens with 1 report, all other reports work fine...

Bug when a table is adjacent to a matrix

I am trying to create a report that has a table adjacent to a matrix, with
the tops of each aligned together. I am trying to create a report where the
rows of the table and the matrix "line up" with each other. I have included
an example below where I have selected the customerID, orderID, and shipper
name from the rows of the Northwind orders table. I'd like to put the
customerID and orderID in a table that is adjacent to a matrix where each
column is a particular shipper. I'd like each row of this combination to
show the customerId, orderID with the shipper name in the appropriate
column.
HOWEVER -- When I render this report in HTML, the matrix terminates and
continues to the next page before the table does, causing the two to get out
of sync on the second page. I can't figure out a way to make the table and
the matrix stay aligned from the first page onto the second.
The report below should duplicate this behavior (I assume that you have the
Northwind DB installed).
Thanks,
Michael Carr
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>0.25in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.5in</Height>
<ZIndex>1</ZIndex>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>CustomerID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CustomerID_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CustomerID_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CustomerID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Sorting>
<SortBy>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>1.875in</Width>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
</Table>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>CustomerID, OrderID</Value>
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CompanyName.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.375in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_CompanyName">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CompanyName_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>CompanyName_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.25in</Width>
<Left>2in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_CustomerID">
<GroupExpressions>
<GroupExpression>=Fields!CustomerID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="CustomerID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CustomerID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CustomerID.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.875in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_OrderID">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>3in</Height>
</Body>
<TopMargin>0.25in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>d83220b8-b683-4ff8-970d-134479e0e9d3</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>8in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT
OrderID,
Orders.CustomerID,
Shippers.CompanyName FROM Orders
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
INNER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>0.25in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>82805f4c-8060-4d41-a1d0-bba1d089578f</rd:ReportID>
<BottomMargin>0.25in</BottomMargin>
<Language>en-US</Language>
</Report>This looks like a bug to me. I've let the dev team know. Thanks.
Possible workaround: Add explicit page breaks every N rows by adding an
outer grouping by =Ceiling(RowNumber(Nothing)/20)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Michael Carr" <mcarr@.umich.edu> wrote in message
news:%23hRajEjiEHA.1040@.TK2MSFTNGP09.phx.gbl...
> I am trying to create a report that has a table adjacent to a matrix, with
> the tops of each aligned together. I am trying to create a report where
the
> rows of the table and the matrix "line up" with each other. I have
included
> an example below where I have selected the customerID, orderID, and
shipper
> name from the rows of the Northwind orders table. I'd like to put the
> customerID and orderID in a table that is adjacent to a matrix where each
> column is a particular shipper. I'd like each row of this combination to
> show the customerId, orderID with the shipper name in the appropriate
> column.
> HOWEVER -- When I render this report in HTML, the matrix terminates and
> continues to the next page before the table does, causing the two to get
out
> of sync on the second page. I can't figure out a way to make the table and
> the matrix stay aligned from the first page onto the second.
> The report below should duplicate this behavior (I assume that you have
the
> Northwind DB installed).
> Thanks,
> Michael Carr
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>0.25in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>0.5in</Height>
> <ZIndex>1</ZIndex>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>CustomerID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Order ID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="CustomerID_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>CustomerID_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CustomerID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="OrderID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>OrderID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!OrderID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!CustomerID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> <SortBy>
> <SortExpression>=Fields!OrderID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <Width>1.875in</Width>
> <TableColumns>
> <TableColumn>
> <Width>1in</Width>
> </TableColumn>
> <TableColumn>
> <Width>0.875in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> <Matrix Name="matrix1">
> <Corner>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>CustomerID, OrderID</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <Style />
> <MatrixRows>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="CompanyName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>CompanyName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!CompanyName.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.25in</Height>
> </MatrixRow>
> </MatrixRows>
> <MatrixColumns>
> <MatrixColumn>
> <Width>1.375in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <DataSetName>DataSet1</DataSetName>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <Grouping Name="matrix1_CompanyName">
> <GroupExpressions>
> <GroupExpression>=Fields!CompanyName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="CompanyName_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>CompanyName_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CompanyName.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <Width>3.25in</Width>
> <Left>2in</Left>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_CustomerID">
> <GroupExpressions>
> <GroupExpression>=Fields!CustomerID.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
>
<SortExpression>=Fields!CustomerID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="CustomerID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>CustomerID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CustomerID.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>0.875in</Width>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_OrderID">
> <GroupExpressions>
> <GroupExpression>=Fields!OrderID.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!OrderID.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!OrderID.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>1in</Width>
> </RowGrouping>
> </RowGroupings>
> </Matrix>
> </ReportItems>
> <Style />
> <Height>3in</Height>
> </Body>
> <TopMargin>0.25in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>d83220b8-b683-4ff8-970d-134479e0e9d3</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>8in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CompanyName">
> <DataField>CompanyName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT
> OrderID,
> Orders.CustomerID,
> Shippers.CompanyName FROM Orders
> INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>0.25in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>82805f4c-8060-4d41-a1d0-bba1d089578f</rd:ReportID>
> <BottomMargin>0.25in</BottomMargin>
> <Language>en-US</Language>
> </Report>
>