Internal Error / internal catalog exception

Hi,

I've read already every thread in this forum regarding this topic with no results.

SQLServer and the corresponding services are at actual update level. The reporting services worked for 4 months without this problem. Suddenly, most of the time all reports return an internal error (it is like 70-30 for internal error) not depending which render format is used.

The system logs (eventlog, iis) show no errors. The ExecutionLog of RS show rsInternalError.

Looking in the log files in RS log directory, there is following stacktrace:

w3wp!processing!1!05/11/2007-10:40:06:: a ASSERT: Assertion failed! Call stack:
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(IRenderingExtension renderer, DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc, CreateReportChunk cacheDataCallback, Boolean& dataCached)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc)
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Boolean isLinkedReport, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobType type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory streamFactory)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation operation)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.ProcessRequest(HttpContext context)
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
System.Web.HttpApplication.ResumeSteps(Exception error)
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
System.Web.HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)

I don't see any hint in stack trace, Maybe one of yours.

As it seems, after an iisreset the first rendering always works perfect. HD space is enough.

Any ideas?

Got it.

As the error occured not every time and not depending any actions or rules, we decided to check the hardware and their corresponding drivers.

For the AMD Opteron single core processors in a multi processor environment, we used the generic microsoft drivers. After changing these to the original AMD ones, it works perfectly again.

Why it occured suddenly without any update or reboot is still not solved, but it works now.

Greez

T.

Internal Error / internal catalog exception

Hi,

I've read already every thread in this forum regarding this topic with no results.

SQLServer and the corresponding services are at actual update level. The reporting services worked for 4 months without this problem. Suddenly, most of the time all reports return an internal error (it is like 70-30 for internal error) not depending which render format is used.

The system logs (eventlog, iis) show no errors. The ExecutionLog of RS show rsInternalError.

Looking in the log files in RS log directory, there is following stacktrace:

w3wp!processing!1!05/11/2007-10:40:06:: a ASSERT: Assertion failed! Call stack:
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(IRenderingExtension renderer, DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc, CreateReportChunk cacheDataCallback, Boolean& dataCached)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc)
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Boolean isLinkedReport, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobType type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory streamFactory)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation operation)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.ProcessRequest(HttpContext context)
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
System.Web.HttpApplication.ResumeSteps(Exception error)
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
System.Web.HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)

I don't see any hint in stack trace, Maybe one of yours.

As it seems, after an iisreset the first rendering always works perfect. HD space is enough.

Any ideas?

Got it.

As the error occured not every time and not depending any actions or rules, we decided to check the hardware and their corresponding drivers.

For the AMD Opteron single core processors in a multi processor environment, we used the generic microsoft drivers. After changing these to the original AMD ones, it works perfectly again.

Why it occured suddenly without any update or reboot is still not solved, but it works now.

Greez

T.

Intermittent email issue

We are having a problem with a report that is scheduled to be emailed
only works sometimes. Other time it fails with a "Reporting Services
Failure sending mail: Cannot access a closed file" error message.
The report is running off a snapshot that is generated every half hour
and a subscription is setup to email the report to some users. The
snapshot is generated even when the email fails.
It seems the problem is pointed to by userName: [DOMAIN\username]not
found in the database. This seems to be a security problem - but why
would it work sometimes? The log file is below for reference. The user
in question is in the same domain as the report service is running
under.
Environment:
Report service SP2 running on a Windows 2003 EE with SQL Server 2000
Active Director Domain server running on Windows 2003 EE
The report server service is running under a domain account that is in
the local administrator group on the local server.
Has anyone seen this problem?
Thanks
ReportingServicesService!library!2b4!09/02/2005-09:09:38:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server
system properties.
ReportingServicesService!session!2b4!09/02/2005-09:09:38:: i INFO:
LoadSnapshot: Item with session: tc2ixx55bnczhjifyjr2z42q, reportPath:
/Company Reports/MyReport, userName: [DOMAIN\username]not found in the
database
ReportingServicesService!chunks!2b4!09/02/2005-09:09:38:: i INFO: ###
GetReportChunk('MyReport_style', 1), chunk was not found!
this=38c10bb1-1bae-4d41-a03e-f56437fc3c86
ReportingServicesService!emailextension!2b4!09/02/2005-09:09:38:: Error
sending email. System.ObjectDisposedException: Cannot access a closed
file.
at System.IO.__Error.FileNotOpen()
at System.IO.FileStream.Read(Byte[] array, Int32 offset, Int32
count)
at
Microsoft.ReportingServices.Library.PartitionFileStream.Read(Byte[]
buffer, Int32 offset, Int32 count)
at
Microsoft.ReportingServices.Library.MemoryUntilThresholdStream.Read(Byte[]
buffer, Int32 offset, Int32 count)
at Microsoft.ReportingServices.Library.RSStream.Read(Byte[] buffer,
Int32 offset, Int32 count)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.EmbedReport(IMessage
message, RenderedOutputFile[] reportData, Notification notification,
SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage
message, Notification notification, SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification
notification)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification
notification)
ReportingServicesService!notification!2b4!09/02/2005-09:09:38::
Notification 2180101f-6faa-41b7-b1a0-b3217c5d630e completed. Success:
False, Status: Failure sending mail: Cannot access a closed file.,
DeliveryExtension: Report Server Email, Report: MyReport, Attempt 0
ReportingServicesService!dbpolling!2b4!09/02/2005-09:09:38::
NotificationPolling finished processing item
2180101f-6faa-41b7-b1a0-b3217c5d630ejmbackup1024@.gmail.com wrote:
> We are having a problem with a report that is scheduled to be emailed
> only works sometimes. Other time it fails with a "Reporting Services
> Failure sending mail: Cannot access a closed file" error message.
> The report is running off a snapshot that is generated every half hour
> and a subscription is setup to email the report to some users. The
> snapshot is generated even when the email fails.
> It seems the problem is pointed to by userName: [DOMAIN\username]not
> found in the database. This seems to be a security problem - but why
> would it work sometimes? The log file is below for reference. The user
> in question is in the same domain as the report service is running
> under.
> Environment:
> Report service SP2 running on a Windows 2003 EE with SQL Server 2000
> Active Director Domain server running on Windows 2003 EE
> The report server service is running under a domain account that is in
> the local administrator group on the local server.
> Has anyone seen this problem?
> Thanks
> ReportingServicesService!library!2b4!09/02/2005-09:09:38:: i INFO:
> Initializing EnableExecutionLogging to 'True' as specified in Server
> system properties.
> ReportingServicesService!session!2b4!09/02/2005-09:09:38:: i INFO:
> LoadSnapshot: Item with session: tc2ixx55bnczhjifyjr2z42q, reportPath:
> /Company Reports/MyReport, userName: [DOMAIN\username]not found in the
> database
> ReportingServicesService!chunks!2b4!09/02/2005-09:09:38:: i INFO: ###
> GetReportChunk('MyReport_style', 1), chunk was not found!
> this=38c10bb1-1bae-4d41-a03e-f56437fc3c86
> ReportingServicesService!emailextension!2b4!09/02/2005-09:09:38:: Error
> sending email. System.ObjectDisposedException: Cannot access a closed
> file.
> at System.IO.__Error.FileNotOpen()
> at System.IO.FileStream.Read(Byte[] array, Int32 offset, Int32
> count)
> at
> Microsoft.ReportingServices.Library.PartitionFileStream.Read(Byte[]
> buffer, Int32 offset, Int32 count)
> at
> Microsoft.ReportingServices.Library.MemoryUntilThresholdStream.Read(Byte[]
> buffer, Int32 offset, Int32 count)
> at Microsoft.ReportingServices.Library.RSStream.Read(Byte[] buffer,
> Int32 offset, Int32 count)
> at
> Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.EmbedReport(IMessage
> message, RenderedOutputFile[] reportData, Notification notification,
> SubscriptionData data)
> at
> Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage
> message, Notification notification, SubscriptionData data)
> at
> Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification
> notification)
> at
> Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification
> notification)
> ReportingServicesService!notification!2b4!09/02/2005-09:09:38::
> Notification 2180101f-6faa-41b7-b1a0-b3217c5d630e completed. Success:
> False, Status: Failure sending mail: Cannot access a closed file.,
> DeliveryExtension: Report Server Email, Report: MyReport, Attempt 0
> ReportingServicesService!dbpolling!2b4!09/02/2005-09:09:38::
> NotificationPolling finished processing item
> 2180101f-6faa-41b7-b1a0-b3217c5d630e
Experiencing a very similar issue, running SP1. I created a
parameterized report, from which 93 link reports point to. Those 93
linked reports are set to run nightly at 2:00 am by generating a
snapshot (for historical purposes). All 93 snapshots were created
within about 3 mintutes of 2:00 am. The subscription is suposed to
fire an email whenever it is updated, but only half or so are
successful. On those that are unsuccessful, I get no error message at
all.
It seems timed subscriptions work just fine. Perhaps what I will do is
send the email based only on a timed subscription and also create a
snapshot for historical purposes...I would much rather not have to do
double the processor work, as many more reports are needed for
conversion.

Intermittent data source problem - trying to access a deleted data source

Hello
I am using Reporting Services within Visual Studio .Net 2003, connecting to
a SQL Server 2000 database.
I am having an intermittent problem with a report misbehaving. The report
has 5 subreports, all of which work fine on their own (for the most part).
The problem is that, sometimes, when I run the main report I get the
following error:-
"An error occurred while executing the subreport ¡OppsHotProspects¢: An
error has occurred during report processing.
Cannot create a connection to data source 'GuessDB'."
This error sometimes only appears for one report, sometimes for all. The
issue is that the 'GuessDB' data source is no longer used; in its place the
'GuessDBLive' data source is now used. I have removed the 'GuessDB' data
source.
When I run each subreport by itself they run fine, and the data source used
in both the Data tab and the Preview tab is the correct one. One
subreport, however, intermittently misbehaves and gives the same error on
its own.
I am very confused as to why this is happening. Presumably something in
each subreport is referencing the deleted data source, but I can't figure
it out. The deleted one should not be getting used at all.
I'd appreciate any help!
Thanks
DeniseFor anyone else having the same problem, I think I've found the answer.
Out of sheer frustration, I examined the code on each report, and on one
there was a chunk of code which referenced the obsolete data source. I've
no idea why this code remained on one report and not the others, but it
did. I removed the offending code, and the problem (touch wood!) has been
resolved.
On Thu, 3 Nov 2005 12:03:38 +0000, Denise wrote:
> Hello
> I am using Reporting Services within Visual Studio .Net 2003, connecting to
> a SQL Server 2000 database.
> I am having an intermittent problem with a report misbehaving. The report
> has 5 subreports, all of which work fine on their own (for the most part).
> The problem is that, sometimes, when I run the main report I get the
> following error:-
> "An error occurred while executing the subreport ¡OppsHotProspects¢: An
> error has occurred during report processing.
> Cannot create a connection to data source 'GuessDB'."
> This error sometimes only appears for one report, sometimes for all. The
> issue is that the 'GuessDB' data source is no longer used; in its place the
> 'GuessDBLive' data source is now used. I have removed the 'GuessDB' data
> source.
> When I run each subreport by itself they run fine, and the data source used
> in both the Data tab and the Preview tab is the correct one. One
> subreport, however, intermittently misbehaves and gives the same error on
> its own.
> I am very confused as to why this is happening. Presumably something in
> each subreport is referencing the deleted data source, but I can't figure
> it out. The deleted one should not be getting used at all.
> I'd appreciate any help!
> Thanks
> Denise

Intermittent connection timeout / general network error

We have a SQL 2005 clustered server (Microsoft Cluster Services) that is queried from 4 IIS6 Windows Server 2003 frontends. Each frontend runs both classic ASP apps connection with SQL Server ODBC and .NET2 apps connecting with System.Data.SqlClient. Ocassionaly we get a string of errors/timeouts opening a connection lasting maybe 2 minutes.

One the classic ASP apps we log one of these two errors:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation

or

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver]Timeout expired

on the ADODB.Connection.Open

On the .NET2 apps we log

Message Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

StackTrace at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)

Source .Net SqlClient Data Provider

Any ideas for further debugging?

What does your connection string look like? Please try add "Connect Timeout=120" or "Connection Timeout = 120" in it, and see wether the problems go away.

Thanks!

Ming.

|||<add name="default" connectionString="Data Source=server;Initial Catalog=db;Integrated Security=False; uid=user; password=pw" providerName="System.Data.SqlClient"/>

for .NET via TCP/IP

The regular ASP websites are having problems as well though using ODBC.

Upping the timeout seems like a band-aid at best and may lead to connections piling up and making the situation worse. There isn't a good reason for communication between the frontends and db server over a private Gigabit link to take over 30 seconds to connect.
|||You can use your HOSTS file to define the servername-IP address combination to resolve DNS names. If you have a DNS server, define this in your DNS server. Try a simple test. Try installing any SQL Server client tool(Query Analyzer, Enterprise Manager, etc) on the server running your .NET app. Connect to your database server using this client tool. If it takes more than 5 seconds to connect, there's something wrong with your network|||

Did you get an answer for this?

we are facign similar issues.

We have 4 way cluster sql server 2005 on SP2

|||No I haven't had any answers. The problem is definitely more pronounced during heavier traffic so I wasn't able to debug much during the lull during the summer.

Intermittent connection timeout / general network error

We have a SQL 2005

clustered server (Microsoft Cluster Services) that is queried from 4 IIS6

Windows Server 2003 frontends. Each frontend runs both classic ASP apps

connection with SQL Server ODBC and .NET2 apps connecting with

System.Data.SqlClient. Ocassionaly we get a string of errors/timeouts opening a

connection lasting maybe 2 minutes.

One the classic ASP

apps we log one of these two errors:

Microsoft OLE DB Provider for ODBC

Drivers (0x80004005)
[Microsoft][ODBC SQL Server

Driver][DBNETLIB]General network error. Check your network

documentation

or

Microsoft OLE DB Provider for

ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server

Driver]Timeout expired

on the

ADODB.Connection.Open

On the .NET2 apps we

log

Message Timeout expired. The timeout

period elapsed prior to completion of the operation or the server is not

responding.

StackTrace at

System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection

owningObject)
at

System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection

owningConnection)
at

System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection

outerConnection, DbConnectionFactory connectionFactory)
at

System.Data.SqlClient.SqlConnection.Open()
at

System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo

sqlPartitionInfo)

Source .Net SqlClient Data Provider

Any ideas for further debugging?

What does your connection string look like? Please try add "Connect Timeout=120" or "Connection Timeout = 120" in it, and see wether the problems go away.

Thanks!

Ming.

|||<add name="default" connectionString="Data Source=server;Initial Catalog=db;Integrated Security=False; uid=user; password=pw" providerName="System.Data.SqlClient"/>

for .NET via TCP/IP

The regular ASP websites are having problems as well though using ODBC.

Upping the timeout seems like a band-aid at best and may lead to connections piling up and making the situation worse. There isn't a good reason for communication between the frontends and db server over a private Gigabit link to take over 30 seconds to connect.|||You can use your HOSTS file to define the servername-IP address combination to resolve DNS names. If you have a DNS server, define this in your DNS server. Try a simple test. Try installing any SQL Server client tool(Query Analyzer, Enterprise Manager, etc) on the server running your .NET app. Connect to your database server using this client tool. If it takes more than 5 seconds to connect, there's something wrong with your network|||

Did you get an answer for this?

we are facign similar issues.

We have 4 way cluster sql server 2005 on SP2

|||No I haven't had any answers. The problem is definitely more pronounced during heavier traffic so I wasn't able to debug much during the lull during the summer.

|||We have a similar issue with our Clustered SQL Server 2005. We are seein it a lot in a VB 6 executable, but I have gotten the errors in Server Management Studio, too. We get one of two errors:
Timeout expired
or
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

We have run Sysinternals Process Monitor on a PC that receives these errors and have seen a NAME NOT FOUND result on queryopen operation for CLUSAPI.DLL, RESUTILS.DLL and ntdsapi.dll. The path it is looking in is the application path C:\Program Files\ROAS. These DLLs don't exist in this folder, but do exist in C:\Windows\System32.

The first two are MS Clustering DLLS and i asume with a name like ntdsapi the other is a server OS DLL.

Interlektual property

Hello all,

How do I protect my Interlektual property for a BI solution ( analysis services) ?

I do not want others to be able to view and change my coding ?

Thanks heaps for any advise.

To deny others to see definition of your database, you should only grant Read rights to your users.
Others can copy your design if given Admin rights to your database or ReadDefinition rights.

Create a role in your database and grant Read permissions to your users.

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

|||

I do not have access to the database but only the design of the BI solution.

It means the customer will own the database and I will deliver the BI solution.

How do I protect the solution not to be copied by someone else ?

|||

Think of BI solution as the source code for you application. The same way if you giving out application source code, same BI Solution will be open for anyone to copy your design.

Giving out BI solution is not the way to protect your intellectual property. The minute customer owns a database customer can see the metadata and the logic implemented by it.

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

Interlektual property

Hello all,

How do I protect my Interlektual property for a BI solution ( analysis services) ?

I do not want others to be able to view and change my coding ?

Thanks heaps for any advise.

To deny others to see definition of your database, you should only grant Read rights to your users.
Others can copy your design if given Admin rights to your database or ReadDefinition rights.

Create a role in your database and grant Read permissions to your users.

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

|||

I do not have access to the database but only the design of the BI solution.

It means the customer will own the database and I will deliver the BI solution.

How do I protect the solution not to be copied by someone else ?

|||

Think of BI solution as the source code for you application. The same way if you giving out application source code, same BI Solution will be open for anyone to copy your design.

Giving out BI solution is not the way to protect your intellectual property. The minute customer owns a database customer can see the metadata and the logic implemented by it.

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

Interim Summing every x number of rows

Using SQL Server 2005 Standard & SQL Server Reporting Services.

First off, here is the application.

An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)

Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6

i 3 2 3 4 2 1

i+15min 2 3 4 2 2 2

i+30min etc.....................

i+45min

i+60min

etc...

The management team wants a throughput report which will take the following parameters in order to filter the results:

Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)

My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?

Thanks for your help and let me know if I need to clarify anything

For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.

AMB

|||

OK, I'm a newbie at all of this so I will try and give you what you asked for:

In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.

Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.

Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes

RecordID Conv1 Conv2 Conv3 TimeStampVal 1 2 2 2 2007-06-29 07:00:00.000 2 2 2 2 2007-06-29 07:15:00.000 3 2 2 2 2007-06-29 07:30:00.000 4 2 2 2 2007-06-29 07:45:00.000 5 2 2 2 2007-06-29 08:00:00.000 6 2 2 2 2007-06-29 08:15:00.000 7 2 2 2 2007-06-29 08:30:00.000 8 2 2 2 2007-06-29 08:45:00.000 9 2 2 2 2007-06-29 09:00:00.000 10 2 2 2 2007-06-29 09:15:00.000 11 2 2 2 2007-06-29 09:30:00.000 12 2 2 2 2007-06-29 09:45:00.000

Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)

Expected result Data for Query ran with Interval Parameter set to 30 Mins

Conv1 Conv2 Conv3 TimeStampVal 4 4 4 2007-06-29 07:00:00.000 4 4 4 2007-06-29 07:30:00.000 4 4 4 2007-06-29 08:00:00.000 4 4 4 2007-06-29 08:30:00.000 4 4 4 2007-06-29 09:00:00.000 4 4 4 2007-06-29 09:30:00.000

Expected Result Set for Query Ran with Interval Parameter set to 60 Mins

Conv1 Conv2 Conv3 TimeStampVal 8 8 8 2007-06-29 07:00:00.000 8 8 8 2007-06-29 08:00:00.000 8 8 8 2007-06-29 09:00:00.000

Hope this helps explain it a little more, if not let me know how I can clarify.

Thanks for your time.

|||

This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.

Code Snippet

CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime

)
GO

INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO

I will see if I can come up with a procedure to show you how to get what you want.

|||

Try:

Code Snippet

createtable #t (

RecordID intnotnulluniqueclustered,

Conv1 smallintnotnull,

Conv2 smallintnotnull,

Conv3 smallintnotnull,

TimeStampVal datetimenotnull

)

go

setnocounton

insertinto #t values(1, 2, 2, 2,'2007-06-29 07:00:00.000')

insertinto #t values(2, 2, 2, 2,'2007-06-29 07:15:00.000')

insertinto #t values(3, 2, 2, 2,'2007-06-29 07:30:00.000')

insertinto #t values(4, 2, 2, 2,'2007-06-29 07:45:00.000')

insertinto #t values(5, 2, 2, 2,'2007-06-29 08:00:00.000')

insertinto #t values(6, 2, 2, 2,'2007-06-29 08:15:00.000')

insertinto #t values(7, 2, 2, 2,'2007-06-29 08:30:00.000')

insertinto #t values(8, 2, 2, 2,'2007-06-29 08:45:00.000')

insertinto #t values(9, 2, 2, 2,'2007-06-29 09:00:00.000')

insertinto #t values(10, 2, 2, 2,'2007-06-29 09:15:00.000')

insertinto #t values(11, 2, 2, 2,'2007-06-29 09:30:00.000')

insertinto #t values(12, 2, 2, 2,'2007-06-29 09:45:00.000')

setnocountoff

go

declare @.sd datetime

declare @.interval int

set @.sd = '2007-06-29 07:00:00.000'

set @.interval = 30

;with cte_1

as

(

select

*,

datediff(minute, @.sd, TimeStampVal)/ @.interval as grp

from

#t

)

select

sum(Conv1)as Conv1,

sum(Conv2)as Conv2,

sum(Conv3)as Conv3,

min(TimeStampVal)as TimeStampVal

from

cte_1

group by

grp

order by

min(TimeStampVal)

go

droptable #t

go

AMB

|||

Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).

I do have a quick question for clarification though if you would indulge me:

In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.

Code Snippet

with cte_1

as

(

select

*,

datediff(minute, @.sd, TimeStampVal) / @.interval as grp

from

#t

)

I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?

By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.

Thanks again

Colin

|||

Hi Colin,

Glad it helped.

> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view

> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?

The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.

select 1 / 2, 1 / 2.

go

I will suggest, if you want to learn more T-SQL, to get the books:

- Inside SQL server 2005: T-SQL Querying

- Inside SQL server 2005: T-SQL Programming

you will not regret having those books.

AMB

Interim Summing every x number of rows

Using SQL Server 2005 Standard & SQL Server Reporting Services.

First off, here is the application.

An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)

Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6

i 3 2 3 4 2 1

i+15min 2 3 4 2 2 2

i+30min etc.....................

i+45min

i+60min

etc...

The management team wants a throughput report which will take the following parameters in order to filter the results:

Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)

My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?

Thanks for your help and let me know if I need to clarify anything

For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.

AMB

|||

OK, I'm a newbie at all of this so I will try and give you what you asked for:

In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.

Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.

Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes

RecordID Conv1 Conv2 Conv3 TimeStampVal 1 2 2 2 2007-06-29 07:00:00.000 2 2 2 2 2007-06-29 07:15:00.000 3 2 2 2 2007-06-29 07:30:00.000 4 2 2 2 2007-06-29 07:45:00.000 5 2 2 2 2007-06-29 08:00:00.000 6 2 2 2 2007-06-29 08:15:00.000 7 2 2 2 2007-06-29 08:30:00.000 8 2 2 2 2007-06-29 08:45:00.000 9 2 2 2 2007-06-29 09:00:00.000 10 2 2 2 2007-06-29 09:15:00.000 11 2 2 2 2007-06-29 09:30:00.000 12 2 2 2 2007-06-29 09:45:00.000

Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)

Expected result Data for Query ran with Interval Parameter set to 30 Mins

Conv1 Conv2 Conv3 TimeStampVal 4 4 4 2007-06-29 07:00:00.000 4 4 4 2007-06-29 07:30:00.000 4 4 4 2007-06-29 08:00:00.000 4 4 4 2007-06-29 08:30:00.000 4 4 4 2007-06-29 09:00:00.000 4 4 4 2007-06-29 09:30:00.000

Expected Result Set for Query Ran with Interval Parameter set to 60 Mins

Conv1 Conv2 Conv3 TimeStampVal 8 8 8 2007-06-29 07:00:00.000 8 8 8 2007-06-29 08:00:00.000 8 8 8 2007-06-29 09:00:00.000

Hope this helps explain it a little more, if not let me know how I can clarify.

Thanks for your time.

|||

This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.

Code Snippet

CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime

)
GO

INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO

I will see if I can come up with a procedure to show you how to get what you want.

|||

Try:

Code Snippet

create table #t (

RecordID int not null unique clustered,

Conv1 smallint not null,

Conv2 smallint not null,

Conv3 smallint not null,

TimeStampVal datetime not null

)

go

set nocount on

insert into #t values(1, 2, 2, 2, '2007-06-29 07:00:00.000')

insert into #t values(2, 2, 2, 2, '2007-06-29 07:15:00.000')

insert into #t values(3, 2, 2, 2, '2007-06-29 07:30:00.000')

insert into #t values(4, 2, 2, 2, '2007-06-29 07:45:00.000')

insert into #t values(5, 2, 2, 2, '2007-06-29 08:00:00.000')

insert into #t values(6, 2, 2, 2, '2007-06-29 08:15:00.000')

insert into #t values(7, 2, 2, 2, '2007-06-29 08:30:00.000')

insert into #t values(8, 2, 2, 2, '2007-06-29 08:45:00.000')

insert into #t values(9, 2, 2, 2, '2007-06-29 09:00:00.000')

insert into #t values(10, 2, 2, 2, '2007-06-29 09:15:00.000')

insert into #t values(11, 2, 2, 2, '2007-06-29 09:30:00.000')

insert into #t values(12, 2, 2, 2, '2007-06-29 09:45:00.000')

set nocount off

go

declare @.sd datetime

declare @.interval int

set @.sd = '2007-06-29 07:00:00.000'

set @.interval = 30

;with cte_1

as

(

select

*,

datediff(minute, @.sd, TimeStampVal) / @.interval as grp

from

#t

)

select

sum(Conv1) as Conv1,

sum(Conv2) as Conv2,

sum(Conv3) as Conv3,

min(TimeStampVal) as TimeStampVal

from

cte_1

group by

grp

order by

min(TimeStampVal)

go

drop table #t

go

AMB

|||

Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).

I do have a quick question for clarification though if you would indulge me:

In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.

Code Snippet

with cte_1

as

(

select

*,

datediff(minute, @.sd, TimeStampVal) / @.interval as grp

from

#t

)

I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?

By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.

Thanks again

Colin

|||

Hi Colin,

Glad it helped.

> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view

> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?

The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.

select 1 / 2, 1 / 2.

go

I will suggest, if you want to learn more T-SQL, to get the books:

- Inside SQL server 2005: T-SQL Querying

- Inside SQL server 2005: T-SQL Programming

you will not regret having those books.

AMB

intergration services comportant

Can you creat a SSIS package and run the package without installing the SQL server intergration services?

During the SQL server installation, you can choose which component you want to install ( SQL server services, intergration services, and analysis services, etc..)..

I didn't check to install the intergration services comportant..but I can still be able to create a intergration services project thru the business intelligent studio... ( but i don;t see it under SQL server configuration maanger)

what the intergration services if for? is it seperated from SQL server services.. why i can still create a SSIS package even though i didn;t install the intergration services?

You may have installed Workstation Components which includes the designer support, but you have not installed the engine. Do your packages run properly? Probably not, see http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx

Intergration Services

Does anyone know any good resource sites on Integration Services ?www.sqlis.com

Intergation Services Templates for Visual Studio 2005

We are setting up developer machines with Visual Studio 2005 and SQL SERVER 2005 Client tools. My question is how do I get the templates for Integration Services into Visual Studio 2005 without installing BIDS?

You cannot do this. The Visual Studio IDE hosts packages, one of which is the SSIS designer for example. The only way to install that package is to install the Workstation components for SSIS from SQL Server Setup.

If you do not already have the VS IDE it is included as part of this setup. If you installed SQL first, then would you expect to be able to install the C# or VB package without running the full Visual Studio setup? The packages are slightly different, but the logic is the same.

There are also package templates, but I don't think that is what you mean (http://wiki.sqlis.com/default.aspx/SQLISWiki/PackageTemplates.html)

Interface for Analysis Services 2005

Dear friends,

I have a problem…

1. How can I show the data from Analysis Services 2005? In ASP.Net 2.0 or Sharepoint 2007?
2. I have users and groups in tables from one database, how can control it in the interface in ASP.NET 2.0 or Sharepoint? (I’m cant use the asp.net 2.0 security controls) It’s easy to allow or deny users or groups to view some items as we can do in asp.net 2.0 controls?

Thanks!!

Pedro,

I do not know about ASP.NET, but you can build reports using Excel 2007 and use Sharepoint as report repository.

High level design:

- In Sharepoint you create connection library(s) that point to appropriate cubes/perspectives in SSAS 2005

- In Sharepoint you create report library(s)

- You create reports using connections from Sharepoint connection library and save them in Sharepoint report library

- You can use SharePoint Excel services for users to see reports with some limited interactivity. Users can use these reports just using browser, no need for Excel. And reports/charts look very similar to what you would see in Excel.

In Sharepoint you would use Integrated security to allow/deny access to reports. But of course you would have to setup proper role security in SSAS 2005 first.

Vidas Matelis

|||

Thanks Vidas for your help.

Note, that I'm using excel 2003, and I will allow users to use the report builder... and I'm very confused aboout the previligies for each user...

I have the user and groups in a OLTP database...

regards

|||

In SSAS 2005 you have security roles where you assing permissions to windows users group. So there you will define if specific windows user/group can access that cube.

Another level of security will be for reports. In sharepoint you can put reports to different report libraries or different report folders and assign if user/group can access that library or folder.

Keep in mind that if user have access to cube, he/she can just start Excel and connect to SSAS to query that cube. Report permissions should go hand in hand with cube/dimension permissions.

If you have excel 2003, I do not believe you will be able to use Excel Services in SharePoint 2007.

As you already know, Excel 2003 does not work very well with SSAS 2005. It is more in presentation, than calculation. You see all attributes as dimensions, and measures are not groupped by measure group and/or folder. So for bigger databases this is big problem.

Vidas Matelis

|||

Thanks for your post...

But how can I control the access to the cubes or reports, if I have the users and groups inside tables and not in the active directory?

Thanks

|||

Pedro,

Analysis Services can use just integrated security. So you cannot change SSAS security based on users saved in SQL Server table.

Vidas Matelis

|||

> Analysis Services can use just integrated security. So you cannot change SSAS security based on users saved in SQL Server table.

Unless you control middle tier, which seems to be a scenario here. It would involve some ASP.NET coding, but it is possible to use users from SQL table. For more information, please read about Roles property and for more dynamic scenarios - CustomData property and MDX function.

|||

Pedro, sorry I misguided you. I based my answer on BOL information, but Mosha pointed that there are ways to do it.

Mosha,

Any published papers on this? Any examples on how this could be done?

I googled it, but cannot find much more detail information.

Thank you,

Vidas Matelis

|||

yes... mosha, do you have some example? or links about the subject?

Thanks both!

|||

MOsha,

Supose I use integrated security, where I can have the groups and roles? The groups are in Active Directory as the users?

Regards!

|||In the middle tier (usually ASP.NET app), after you authenticated the user, you can look up in the SQL table or in AD or somewhere else what are the roles he should belong to. Then you create ADOMD.NET connection for that user passing "Roles=Role1,Role2,Role3" connection string parameter. If static role assignment doesn't work for you, you can pass user id through CustomData property, i.e. "CustomData=appuser1", and then inside security definitions you can use CustomData() MDX function which will resolve to the value passed in the property.|||

Mosha,

Could you please confirm if my understanding for Role property is right:

- For this to work middle tier should have full (or at least some) access to SSAS database.

- User does not need to have access to SSAS database, as it is queried by middle tier.

- Including role parameter in connection string will further limit middle tier access to SSAS based on roles specified.

- Will there be any penalty for performance using this? I know that years back when connection string to relational DB did not matched exactly, then you could not reuse cache betten connection.

Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

- I would create calculated measures and use CustomData() function to limit what values are available.

- With this approach I cannot hide dimensions, hierarchies, cubes, just calculated measure values.

Did I understand right?

Thank you,

Vidas Matelis

|||

For Roles property your understanding is correct.

> Will there be any penalty for performance using this? I know that years back when connection string to relational DB did not matched exactly, then you could not reuse cache betten connection.

You won't be able to reuse connections with different Roles set on them. The FE caches cannot be reused as well (SE caches can be reused sometimes). For more information check out the SQL Server 2005 Analysis Services book, page 511 - it has good explanation of this subject.

> Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

- I would create calculated measures and use CustomData() function to limit what values are available.

- With this approach I cannot hide dimensions, hierarchies, cubes, just calculated measure values.

You misunderstood how CustomData can be used for security. Just think about it as a replacement for the UserName() function in dynamic security when your authentication is not Windows Integrated but a custom one.

|||

Mosha,

Thank you! This was very helpful.

Vidas Matelis

Interesting M-M Relationship

I have a need to model an interesting M-N relationship within Analysis Services 2005. However, this M-N relationship is different from that given as the example in the AdventureWorksDW database. So, I'm wondering how best to go about it.

In the AdventureWorkDW database, the FactInternetSales table is related to the FactInternetSalesReasons fact table such that a sale in the first table can be related to many reasons in the second table. Also, the DimSalesReason table is related to the FactInternetSalesReasons fact table such that a reason in the dim table can be related to many facts in the second. Thus, the FactInternetSalesReasons fact table serves as a classic bridge table, creating a M-N relationship between FactInternetSales and DimSalesReason. This is then modeled in AS using an intermediate measure group and a many-to-many relationship.

Here's my situation. I have a standard fact table with a standard relationship to a dimension table (ie, M-1 between the fact table and dimension table). The dimension table is then related to another table, which contains possible parents for the dimension members. Thus, a single dimension member can be related to multiple parents. The fact records need to correctly rollup to each parent based on which members belong to each parent. Thus, a 1-M relationship exists between a dimension member and possible parents.

So, the question is how to model this. There doesn't seem to be an intermediate measure group to create given the fact table is correctly related to the dimension table. Its just that the fact table needs to be connected to the parent table as well, through the dimesion table.

Anyone know how to correctly model this? Thanks in advance!!

Dave Fackler
Hi Dave

Check this out. I think it is just what you are looking for.

http://www.sqlserveranalysisservices.com/OLAPPapers/DuplicateMembers.htmsql

Interactive Sorting/Execution of query

Does clicking interactive sort button in a column reporting services 2005 result re-execution of the query.

Or will it just re-print the rendered data in the layout and so perform better in comparison to the implementation which can be done using drill down to same report with the help of some extra parameters

Priyank

If the user session hasn't expired, interactive sorting doesn't result in re-executing of the query. The server simply re-uses the cached report.

|||I'd definately prefer the cache over generating another report. But if you feel so inclined, try both and time them to see which one is faster.

|||

Thanks, I tried this, rendered data got re-printed without execution of query.

Interactive Size/Report Size

Hello,

This is SQL 2005 Reporting Services. I have several reports that are repeating tables with grouped information and subtotals. When deployed, we are noticing that the interactive HTML view renders a great deal more detail per page than the printed version of the report.

This didn't really become a problem until a user reported an issue where they wanted to print pages 93-97 of a 104-page report. She based the page number selection on the page numbers she was seeing in the report viewer. When printed, she did not get the data she wanted to print... and the full printed version is about 50 pages longer than the interactive viewer version.

In my report I have the following settings (no page breaks on the groups or anything, it's just a table with a header, footer, and detail rows per group:

Interactive Size: 11"wide by 8.5" long (landscape)
PageSize: 11" wide by 8.5" long (same as above)
Margins: .5in (all 4 sides)

I get the same thing when previewing via Visual Studio... so I don't think it's a web viewer problem. Are there any options to work around this problem?

I guess not. The reporting rendering behaviour of the HTML is different than the printed one. I didn′t investigate that in detail so far, but I know about this issue that the page numbers don′t match in comparison with the HTML and the PDF export.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Intelligent Report Viewer Question

Good Morning,

I have a dumb question. I'm used to working in the VS.NET 2003 environment (C#), and I'm getting into some reporting services stuff for a new client.

Ideally, they'd like to be able to do some code specific stuff with their reports, like only allowing parameters to be changed by certain Active Directory groups, etc...

Ultimately, I'd like to combine C# logic with report viewing capability.

Here's my question: Can I use C# and vanilla web forms with the ReportViewer control to display reports on them?

Thanks in advance for your replies!

Justin

Yes you can. To do what mention you'd want control the parameter rendering manually rather than having RS render the parameter toolbar.

You want to look into displaying the report using URL access to the report server vs rendering the report using the web service API. The API gives you all the information you need about the report to perform your own logic.