When I attempt to install sql reporting services enterprise, i receive the
following:
"System Check results--asp.net is not installed or is not registered with
your web server".
I review services and iisadmin is there and running as is also asp.net state
services which I had started to run automatically. Microsoft .net framework
1.1 is confirmed installed in the add/remove programs.
Anyone come across this before?
thanksDid you by any chance remove the default website? RS needs the default
website to be able to install.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:3ABC1970-87A4-47D2-8CFC-11A613861F72@.microsoft.com...
> When I attempt to install sql reporting services enterprise, i receive the
> following:
> "System Check results--asp.net is not installed or is not registered with
> your web server".
> I review services and iisadmin is there and running as is also asp.net
state
> services which I had started to run automatically. Microsoft .net
framework
> 1.1 is confirmed installed in the add/remove programs.
> Anyone come across this before?
> thanks
>|||Run this from a command prompt:
%SYSTEMROOT%\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis.exe -i
This will install ASP.NET.
On a side note, you may want to turn off the ASP.NET State Service as this
is used for servers that act as session state servers for ASP.NET
applications that are configured to maintain session state out of process.
So this service is probably just using resources for nothing.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ui3TDyx$EHA.2880@.TK2MSFTNGP14.phx.gbl...
> Did you by any chance remove the default website? RS needs the default
> website to be able to install.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
> news:3ABC1970-87A4-47D2-8CFC-11A613861F72@.microsoft.com...
> > When I attempt to install sql reporting services enterprise, i receive
the
> > following:
> > "System Check results--asp.net is not installed or is not registered
with
> > your web server".
> >
> > I review services and iisadmin is there and running as is also asp.net
> state
> > services which I had started to run automatically. Microsoft .net
> framework
> > 1.1 is confirmed installed in the add/remove programs.
> >
> > Anyone come across this before?
> >
> > thanks
> >
> >
>|||Your error happens when, for example, ASP.NET is installed and IIS isn't on
the machine or IIS is installed later. The aspnet_regiis.exe utility with
the -i parameter installs the correct IIS script maps so IIS associates
asp.net requests with the desired version of the framework. (Each framework
version has it's own aspnet_regiis.exe utility).
You should also be able to MSDN search that message and you will get more
information on the aspnet_regiis tool.
Adrian M.
"tom frost" <tomfrost@.discussions.microsoft.com> wrote in message
news:3ABC1970-87A4-47D2-8CFC-11A613861F72@.microsoft.com...
> When I attempt to install sql reporting services enterprise, i receive the
> following:
> "System Check results--asp.net is not installed or is not registered with
> your web server".
> I review services and iisadmin is there and running as is also asp.net
> state
> services which I had started to run automatically. Microsoft .net
> framework
> 1.1 is confirmed installed in the add/remove programs.
> Anyone come across this before?
> thanks
>
Showing posts with label services. Show all posts
Showing posts with label services. Show all posts
Sunday, March 25, 2012
ASP.Net
Looking for some good examples of how folks used ASP.Net to pass in
parameters to SQL2005 Reporting Services - not liking how the parameters
have to be coded within the designer itself.We used a single string parameter and embedded an XML document within
that, translating it in a wrapping stored procedure to be the
parameters required by the real sp on the database.
This allowed us to do things like data driven parameters to the report
(in the ASP.Net web application we wrote as the front end)
Cheers
Greg
Joe wrote:
> Looking for some good examples of how folks used ASP.Net to pass in
> parameters to SQL2005 Reporting Services - not liking how the parameters
> have to be coded within the designer itself.|||I use stored procedures as the datasource and set the parameters with the
procedure variables. Using report parameters takes way too long on large data
sets from what I have experienced. All the data is loaded before the
filtering takes place (from what I was told).
"Joe" wrote:
> Looking for some good examples of how folks used ASP.Net to pass in
> parameters to SQL2005 Reporting Services - not liking how the parameters
> have to be coded within the designer itself.
>
>
parameters to SQL2005 Reporting Services - not liking how the parameters
have to be coded within the designer itself.We used a single string parameter and embedded an XML document within
that, translating it in a wrapping stored procedure to be the
parameters required by the real sp on the database.
This allowed us to do things like data driven parameters to the report
(in the ASP.Net web application we wrote as the front end)
Cheers
Greg
Joe wrote:
> Looking for some good examples of how folks used ASP.Net to pass in
> parameters to SQL2005 Reporting Services - not liking how the parameters
> have to be coded within the designer itself.|||I use stored procedures as the datasource and set the parameters with the
procedure variables. Using report parameters takes way too long on large data
sets from what I have experienced. All the data is loaded before the
filtering takes place (from what I was told).
"Joe" wrote:
> Looking for some good examples of how folks used ASP.Net to pass in
> parameters to SQL2005 Reporting Services - not liking how the parameters
> have to be coded within the designer itself.
>
>
Thursday, March 22, 2012
ASP Memory Crash
Hi all,
I am running reporting services on a dedicated server with 2GB of memory,
when ever a report is run that contains over 90,000 records the server
crashes and restarts. We have looked at the reports, and are to reduce the
criteria range to make smaller reports, but it then affects role the system
was designed to play in my organisation. If anyone has come across these
memory crashes and come across any ways of resolving them or ways that
haven't resolved them, including an increase in the memory size, it would be
greatly appreciated if you could reply. We are willing and able to throw
another 2GB at the problem, but are concerned this may only be a short term
solution and the problem will reoccur when the reporting size reaches 180,000
records.
Any assistance you may be able to provide would be greatly appreciatedIf you mean that the result set has 90,000 records or 180,000 records then
you have the wrong product. RS is not designed to generate reports that are
1500+ pages. Rendering is done in RAM so there is a direct correlation to
between number of records and the amount of RAM used. If the rendering
output is Excel or PDF then the amount of RAM consumed is even more. If the
destination is another program then there are better ways to do this. I know
that sometimes people are wanting to get a large amount of rows into Excel
for further analysis but it would be better to be using DTS and getting the
data out in CSV for them. Much much faster process.
If the output is not that many records but you have that many because you
are using filters then try to move away from filters. Filters brings over
all the data and then filters it. Use a query parameter instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> Hi all,
> I am running reporting services on a dedicated server with 2GB of memory,
> when ever a report is run that contains over 90,000 records the server
> crashes and restarts. We have looked at the reports, and are to reduce the
> criteria range to make smaller reports, but it then affects role the
system
> was designed to play in my organisation. If anyone has come across these
> memory crashes and come across any ways of resolving them or ways that
> haven't resolved them, including an increase in the memory size, it would
be
> greatly appreciated if you could reply. We are willing and able to throw
> another 2GB at the problem, but are concerned this may only be a short
term
> solution and the problem will reoccur when the reporting size reaches
180,000
> records.
> Any assistance you may be able to provide would be greatly appreciated|||You might try CSV and see if it takes up less memory than Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> If you mean that the result set has 90,000 records or 180,000 records then
> you have the wrong product. RS is not designed to generate reports that
are
> 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> between number of records and the amount of RAM used. If the rendering
> output is Excel or PDF then the amount of RAM consumed is even more. If
the
> destination is another program then there are better ways to do this. I
know
> that sometimes people are wanting to get a large amount of rows into Excel
> for further analysis but it would be better to be using DTS and getting
the
> data out in CSV for them. Much much faster process.
> If the output is not that many records but you have that many because you
> are using filters then try to move away from filters. Filters brings over
> all the data and then filters it. Use a query parameter instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
message
> news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > Hi all,
> >
> > I am running reporting services on a dedicated server with 2GB of
memory,
> > when ever a report is run that contains over 90,000 records the server
> > crashes and restarts. We have looked at the reports, and are to reduce
the
> > criteria range to make smaller reports, but it then affects role the
> system
> > was designed to play in my organisation. If anyone has come across
these
> > memory crashes and come across any ways of resolving them or ways that
> > haven't resolved them, including an increase in the memory size, it
would
> be
> > greatly appreciated if you could reply. We are willing and able to
throw
> > another 2GB at the problem, but are concerned this may only be a short
> term
> > solution and the problem will reoccur when the reporting size reaches
> 180,000
> > records.
> >
> > Any assistance you may be able to provide would be greatly appreciated
>|||Bruce,
Thanks for your responses.
Do you know if there is away to detect if the report is going to generate
over 200 pages and return a message to the user to say something like 'This
report may crash the server, are you sure you want to continue' ? From your
responses, and others I have read about memory, and RS limitations are the
only issue I am encountering, not programming issues, so if we confirm with
users before they run large reports it could solve our problems, unless they
select 'Yes' of course.
Any further assistance you may be able to provide would be greatly
appreciated.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>|||What you could do is have an intermediary report that does a count and then
provides the appropriate message and link. You can hide the real report from
the user in list view so they have to go through this. Also you could use
jump to url and render to Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||Thanks for the feedback Bruce I will give it a go !|||Hi There,
We are having a problem in listing subscriptions in Report manager and
getting "OutOfMemory" exception.
Our Application has an event-based subscription management system, and
every-time an event gets fired on the Application-side, it creates an
one-off subscription on the Reporting Services. So overtime the number of
Subscriptions created on the reporting services has grown, and a particular
report has about 8000+ subscriptions to date now.
So when we try to manage the subscriptions (try to delete the irrelevant
ones) in the RS "Report Manager", for that particular report, System gives
an OutOfMemory exception. I think Report Manager Calls internally
"ListSubscriptions" method (as explained in 840709) and couldn't cope up
with.
And i looked at MSDN Knowledgebase Article:840709, and increased the
"MemoryLimit" setting in RSReportServer.config, but there was no use.
By the way our Server has 2GB of RAM and we use Custom Authentication on
Reporting Services.
I posted this question here, because I thought the problem is similar to
what you were talking (Memory management Issue).
Any Suggestions are appreciated.
Regards
Raj Chidipudi
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||I have exact same problem. My report is about 28000 rows and it takes about
15 minutes to render and end user gets frustrated and he tries to "End Task"
the browser - every thing hangs up. I can't put drill down, etc., so that
is not my option. Is there any way I can stream data, instead of wait to
retreive all rows from SQL? - just like SQL Query analyzer window - it starts
producing results as soon as you execute the query. This is really a big
issue in our organization. Because of this, people started to hate RS. I
need some kind of solution asap. Please help.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>
I am running reporting services on a dedicated server with 2GB of memory,
when ever a report is run that contains over 90,000 records the server
crashes and restarts. We have looked at the reports, and are to reduce the
criteria range to make smaller reports, but it then affects role the system
was designed to play in my organisation. If anyone has come across these
memory crashes and come across any ways of resolving them or ways that
haven't resolved them, including an increase in the memory size, it would be
greatly appreciated if you could reply. We are willing and able to throw
another 2GB at the problem, but are concerned this may only be a short term
solution and the problem will reoccur when the reporting size reaches 180,000
records.
Any assistance you may be able to provide would be greatly appreciatedIf you mean that the result set has 90,000 records or 180,000 records then
you have the wrong product. RS is not designed to generate reports that are
1500+ pages. Rendering is done in RAM so there is a direct correlation to
between number of records and the amount of RAM used. If the rendering
output is Excel or PDF then the amount of RAM consumed is even more. If the
destination is another program then there are better ways to do this. I know
that sometimes people are wanting to get a large amount of rows into Excel
for further analysis but it would be better to be using DTS and getting the
data out in CSV for them. Much much faster process.
If the output is not that many records but you have that many because you
are using filters then try to move away from filters. Filters brings over
all the data and then filters it. Use a query parameter instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> Hi all,
> I am running reporting services on a dedicated server with 2GB of memory,
> when ever a report is run that contains over 90,000 records the server
> crashes and restarts. We have looked at the reports, and are to reduce the
> criteria range to make smaller reports, but it then affects role the
system
> was designed to play in my organisation. If anyone has come across these
> memory crashes and come across any ways of resolving them or ways that
> haven't resolved them, including an increase in the memory size, it would
be
> greatly appreciated if you could reply. We are willing and able to throw
> another 2GB at the problem, but are concerned this may only be a short
term
> solution and the problem will reoccur when the reporting size reaches
180,000
> records.
> Any assistance you may be able to provide would be greatly appreciated|||You might try CSV and see if it takes up less memory than Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> If you mean that the result set has 90,000 records or 180,000 records then
> you have the wrong product. RS is not designed to generate reports that
are
> 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> between number of records and the amount of RAM used. If the rendering
> output is Excel or PDF then the amount of RAM consumed is even more. If
the
> destination is another program then there are better ways to do this. I
know
> that sometimes people are wanting to get a large amount of rows into Excel
> for further analysis but it would be better to be using DTS and getting
the
> data out in CSV for them. Much much faster process.
> If the output is not that many records but you have that many because you
> are using filters then try to move away from filters. Filters brings over
> all the data and then filters it. Use a query parameter instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
message
> news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > Hi all,
> >
> > I am running reporting services on a dedicated server with 2GB of
memory,
> > when ever a report is run that contains over 90,000 records the server
> > crashes and restarts. We have looked at the reports, and are to reduce
the
> > criteria range to make smaller reports, but it then affects role the
> system
> > was designed to play in my organisation. If anyone has come across
these
> > memory crashes and come across any ways of resolving them or ways that
> > haven't resolved them, including an increase in the memory size, it
would
> be
> > greatly appreciated if you could reply. We are willing and able to
throw
> > another 2GB at the problem, but are concerned this may only be a short
> term
> > solution and the problem will reoccur when the reporting size reaches
> 180,000
> > records.
> >
> > Any assistance you may be able to provide would be greatly appreciated
>|||Bruce,
Thanks for your responses.
Do you know if there is away to detect if the report is going to generate
over 200 pages and return a message to the user to say something like 'This
report may crash the server, are you sure you want to continue' ? From your
responses, and others I have read about memory, and RS limitations are the
only issue I am encountering, not programming issues, so if we confirm with
users before they run large reports it could solve our problems, unless they
select 'Yes' of course.
Any further assistance you may be able to provide would be greatly
appreciated.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>|||What you could do is have an intermediary report that does a count and then
provides the appropriate message and link. You can hide the real report from
the user in list view so they have to go through this. Also you could use
jump to url and render to Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||Thanks for the feedback Bruce I will give it a go !|||Hi There,
We are having a problem in listing subscriptions in Report manager and
getting "OutOfMemory" exception.
Our Application has an event-based subscription management system, and
every-time an event gets fired on the Application-side, it creates an
one-off subscription on the Reporting Services. So overtime the number of
Subscriptions created on the reporting services has grown, and a particular
report has about 8000+ subscriptions to date now.
So when we try to manage the subscriptions (try to delete the irrelevant
ones) in the RS "Report Manager", for that particular report, System gives
an OutOfMemory exception. I think Report Manager Calls internally
"ListSubscriptions" method (as explained in 840709) and couldn't cope up
with.
And i looked at MSDN Knowledgebase Article:840709, and increased the
"MemoryLimit" setting in RSReportServer.config, but there was no use.
By the way our Server has 2GB of RAM and we use Custom Authentication on
Reporting Services.
I posted this question here, because I thought the problem is similar to
what you were talking (Memory management Issue).
Any Suggestions are appreciated.
Regards
Raj Chidipudi
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:29D8180F-C3A4-414B-BE75-1FE9E908AA0F@.microsoft.com...
> Bruce,
> Thanks for your responses.
> Do you know if there is away to detect if the report is going to generate
> over 200 pages and return a message to the user to say something like
'This
> report may crash the server, are you sure you want to continue' ? From
your
> responses, and others I have read about memory, and RS limitations are the
> only issue I am encountering, not programming issues, so if we confirm
with
> users before they run large reports it could solve our problems, unless
they
> select 'Yes' of course.
> Any further assistance you may be able to provide would be greatly
> appreciated.
> "Bruce L-C [MVP]" wrote:
> > You might try CSV and see if it takes up less memory than Excel.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > > If you mean that the result set has 90,000 records or 180,000 records
then
> > > you have the wrong product. RS is not designed to generate reports
that
> > are
> > > 1500+ pages. Rendering is done in RAM so there is a direct correlation
to
> > > between number of records and the amount of RAM used. If the rendering
> > > output is Excel or PDF then the amount of RAM consumed is even more.
If
> > the
> > > destination is another program then there are better ways to do this.
I
> > know
> > > that sometimes people are wanting to get a large amount of rows into
Excel
> > > for further analysis but it would be better to be using DTS and
getting
> > the
> > > data out in CSV for them. Much much faster process.
> > >
> > > If the output is not that many records but you have that many because
you
> > > are using filters then try to move away from filters. Filters brings
over
> > > all the data and then filters it. Use a query parameter instead.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> > message
> > > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > > Hi all,
> > > >
> > > > I am running reporting services on a dedicated server with 2GB of
> > memory,
> > > > when ever a report is run that contains over 90,000 records the
server
> > > > crashes and restarts. We have looked at the reports, and are to
reduce
> > the
> > > > criteria range to make smaller reports, but it then affects role the
> > > system
> > > > was designed to play in my organisation. If anyone has come across
> > these
> > > > memory crashes and come across any ways of resolving them or ways
that
> > > > haven't resolved them, including an increase in the memory size, it
> > would
> > > be
> > > > greatly appreciated if you could reply. We are willing and able to
> > throw
> > > > another 2GB at the problem, but are concerned this may only be a
short
> > > term
> > > > solution and the problem will reoccur when the reporting size
reaches
> > > 180,000
> > > > records.
> > > >
> > > > Any assistance you may be able to provide would be greatly
appreciated
> > >
> > >
> >
> >
> >|||I have exact same problem. My report is about 28000 rows and it takes about
15 minutes to render and end user gets frustrated and he tries to "End Task"
the browser - every thing hangs up. I can't put drill down, etc., so that
is not my option. Is there any way I can stream data, instead of wait to
retreive all rows from SQL? - just like SQL Query analyzer window - it starts
producing results as soon as you execute the query. This is really a big
issue in our organization. Because of this, people started to hate RS. I
need some kind of solution asap. Please help.
"Bruce L-C [MVP]" wrote:
> You might try CSV and see if it takes up less memory than Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u5P0mDYUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > If you mean that the result set has 90,000 records or 180,000 records then
> > you have the wrong product. RS is not designed to generate reports that
> are
> > 1500+ pages. Rendering is done in RAM so there is a direct correlation to
> > between number of records and the amount of RAM used. If the rendering
> > output is Excel or PDF then the amount of RAM consumed is even more. If
> the
> > destination is another program then there are better ways to do this. I
> know
> > that sometimes people are wanting to get a large amount of rows into Excel
> > for further analysis but it would be better to be using DTS and getting
> the
> > data out in CSV for them. Much much faster process.
> >
> > If the output is not that many records but you have that many because you
> > are using filters then try to move away from filters. Filters brings over
> > all the data and then filters it. Use a query parameter instead.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in
> message
> > news:57A0B71F-F342-45E4-B6A7-885577B71E96@.microsoft.com...
> > > Hi all,
> > >
> > > I am running reporting services on a dedicated server with 2GB of
> memory,
> > > when ever a report is run that contains over 90,000 records the server
> > > crashes and restarts. We have looked at the reports, and are to reduce
> the
> > > criteria range to make smaller reports, but it then affects role the
> > system
> > > was designed to play in my organisation. If anyone has come across
> these
> > > memory crashes and come across any ways of resolving them or ways that
> > > haven't resolved them, including an increase in the memory size, it
> would
> > be
> > > greatly appreciated if you could reply. We are willing and able to
> throw
> > > another 2GB at the problem, but are concerned this may only be a short
> > term
> > > solution and the problem will reoccur when the reporting size reaches
> > 180,000
> > > records.
> > >
> > > Any assistance you may be able to provide would be greatly appreciated
> >
> >
>
>
Tuesday, March 20, 2012
ASP 1.1 & Report Server Login Problems
Hello everyone
we're having some strage trouble concerning ASP 1.1 and reporting services. We use a ASP 1.1 solution in which we authenticate Users using a self-written Login Mask. One page of the project consists of a report which is provided by report server. now, after displaying this report once, all other ASP 1.1 Forms do not work anymore, they simply keep empty.
My guess is that while conecting to the report server, an athenticated connection is established and kept up, even if the report is closed again.
thanks for any help
exmap
exmap wrote:
One page of the project consists of a report which is provided by report server. now, after displaying this report once, all other ASP 1.1 Forms do not work anymore, they simply keep empty.
I'd have to see it to believe it. Can you post a screenshot of what you are seeing? If not, then can you describe the situation a bit further. The problem description appears to be much too general.
sqlASP + reporting services
I want create a handle on an ASPX page for emailing a report that was created with SQL reporting services.
eg. on a button click, the report should be run and emailed.
can someone please help me out.
thanks!Edit: Sorry! Gotta watch which link I'm hitting!!
eg. on a button click, the report should be run and emailed.
can someone please help me out.
thanks!Edit: Sorry! Gotta watch which link I'm hitting!!
asking for user's input before report generation - plz help?
Hi guys,
I am very new to Rep Services so need your help or suggestion:
Question: Let's say I have a very simple set of data, but before the
report is generated, userviewing the report has to enter a number and
then the report takes this number and subtracts from it a number
sitting in the database - and displayes the result in the report.
Let me explain. Let's say here's my data:
Item Bill
1 $30
2 $50
3 $45
Before the report is generated, a popup will come up, asking the user
to input a Total Bill on Paper Invoice $.
Then the report will show:
Internal Total: $125 (this is the number calculated from the data
table.
Total Bill on the Paper Invoice: $130 (this is the number that the
report viewer inputted)
TOTAL AMOUNT DUE: ($130) - ($125) = $5 (this is the difference
between user's inputted number and the number from the table.
How would I create a report such as the above so that prior to report
generating, a popup would come up asking for the user to input $130 and
then the report would take the difference between user's input number
and the number sitting in the database table?
This should be very easy, but since I'm so new, I need your help.
Thanks in advance.The HTML report header can't pop-up a window to prompt users for input but
you can use a parameter that will show-up in the header toolbar. Add a
report parameter and then reference it in an expression as (textbox value)
=Parameters!TotalBill.Value-Fields!InternalTotal.Value
"Kartuli" <kartuli.com@.gmail.com> wrote in message
news:1114454790.571054.87820@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I am very new to Rep Services so need your help or suggestion:
> Question: Let's say I have a very simple set of data, but before the
> report is generated, userviewing the report has to enter a number and
> then the report takes this number and subtracts from it a number
> sitting in the database - and displayes the result in the report.
> Let me explain. Let's say here's my data:
> Item Bill
> 1 $30
> 2 $50
> 3 $45
> Before the report is generated, a popup will come up, asking the user
> to input a Total Bill on Paper Invoice $.
> Then the report will show:
> Internal Total: $125 (this is the number calculated from the data
> table.
> Total Bill on the Paper Invoice: $130 (this is the number that the
> report viewer inputted)
> TOTAL AMOUNT DUE: ($130) - ($125) = $5 (this is the difference
> between user's inputted number and the number from the table.
> How would I create a report such as the above so that prior to report
> generating, a popup would come up asking for the user to input $130 and
> then the report would take the difference between user's input number
> and the number sitting in the database table?
> This should be very easy, but since I'm so new, I need your help.
> Thanks in advance.
>
I am very new to Rep Services so need your help or suggestion:
Question: Let's say I have a very simple set of data, but before the
report is generated, userviewing the report has to enter a number and
then the report takes this number and subtracts from it a number
sitting in the database - and displayes the result in the report.
Let me explain. Let's say here's my data:
Item Bill
1 $30
2 $50
3 $45
Before the report is generated, a popup will come up, asking the user
to input a Total Bill on Paper Invoice $.
Then the report will show:
Internal Total: $125 (this is the number calculated from the data
table.
Total Bill on the Paper Invoice: $130 (this is the number that the
report viewer inputted)
TOTAL AMOUNT DUE: ($130) - ($125) = $5 (this is the difference
between user's inputted number and the number from the table.
How would I create a report such as the above so that prior to report
generating, a popup would come up asking for the user to input $130 and
then the report would take the difference between user's input number
and the number sitting in the database table?
This should be very easy, but since I'm so new, I need your help.
Thanks in advance.The HTML report header can't pop-up a window to prompt users for input but
you can use a parameter that will show-up in the header toolbar. Add a
report parameter and then reference it in an expression as (textbox value)
=Parameters!TotalBill.Value-Fields!InternalTotal.Value
"Kartuli" <kartuli.com@.gmail.com> wrote in message
news:1114454790.571054.87820@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I am very new to Rep Services so need your help or suggestion:
> Question: Let's say I have a very simple set of data, but before the
> report is generated, userviewing the report has to enter a number and
> then the report takes this number and subtracts from it a number
> sitting in the database - and displayes the result in the report.
> Let me explain. Let's say here's my data:
> Item Bill
> 1 $30
> 2 $50
> 3 $45
> Before the report is generated, a popup will come up, asking the user
> to input a Total Bill on Paper Invoice $.
> Then the report will show:
> Internal Total: $125 (this is the number calculated from the data
> table.
> Total Bill on the Paper Invoice: $130 (this is the number that the
> report viewer inputted)
> TOTAL AMOUNT DUE: ($130) - ($125) = $5 (this is the difference
> between user's inputted number and the number from the table.
> How would I create a report such as the above so that prior to report
> generating, a popup would come up asking for the user to input $130 and
> then the report would take the difference between user's input number
> and the number sitting in the database table?
> This should be very easy, but since I'm so new, I need your help.
> Thanks in advance.
>
Monday, March 19, 2012
Ask: Feature of Reporting Service
Dear All,
Since Reporting Service is part of SQL Server 2000.
Is there any features difference of Reporting Services which is part of
SQL Server 2000 Enterprise Edition and Reporting Services which is part
of SQL Server 2000?
Thanks
Robert LieThere are indeed differences between the Enterprise and Standard Editions of
Reporting Services. Have a look at the feature comparison at
http://www.microsoft.com/sql/reporting/productinfo/features.asp
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<robert.lie24@.gmail.com> wrote in message
news:1115953206.069272.152450@.z14g2000cwz.googlegroups.com...
> Dear All,
> Since Reporting Service is part of SQL Server 2000.
> Is there any features difference of Reporting Services which is part of
> SQL Server 2000 Enterprise Edition and Reporting Services which is part
> of SQL Server 2000?
> Thanks
> Robert Lie
>
Since Reporting Service is part of SQL Server 2000.
Is there any features difference of Reporting Services which is part of
SQL Server 2000 Enterprise Edition and Reporting Services which is part
of SQL Server 2000?
Thanks
Robert LieThere are indeed differences between the Enterprise and Standard Editions of
Reporting Services. Have a look at the feature comparison at
http://www.microsoft.com/sql/reporting/productinfo/features.asp
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<robert.lie24@.gmail.com> wrote in message
news:1115953206.069272.152450@.z14g2000cwz.googlegroups.com...
> Dear All,
> Since Reporting Service is part of SQL Server 2000.
> Is there any features difference of Reporting Services which is part of
> SQL Server 2000 Enterprise Edition and Reporting Services which is part
> of SQL Server 2000?
> Thanks
> Robert Lie
>
ASExecutionLog ?
Is there a tool for Analysis Services that acts like the RSExecutionLog database for Reporting Services? Basically, I'm looking for out of the box functionality to save off the OlapQueryLog records into a different db similar to what is available for Reporting Services.
have you ever seen this?http://www.databasejournal.com/features/mssql/article.php/10894_3618581_3
|||
Thanks for the reply. Perhaps the OlapQueryLog is not the ideal place to find the functionality I'm looking for. I'm looking for something similar to what is offered with Reporting Services and the RSExecutionLog database to capture who is hitting the cubes and what their requests are.
|||There is not exactly the same functionality, but you could probably get similar functionality using a trace. If you are just after recent activity there is the automatic flight recorder trace, but this only has a recent window of activity. If you want more control over what data is captured and how long the data is retained, then you would want to setup your own trace.
Ideally you would not want the hassle or overhead of continuously running the Profiler UI somewhere. I have not explored the options with unattended traces to be sure what is possible here. Judging by the output of a DISCOVER_TRACES call it should be possible to get SSAS to dump to a file which you could read in later with SSIS. You might need to use XMLA or AMO to create these traces, I could not see a way to do it from Profiler. I don't think you can get SSAS to dump directly to a SQL table directly, you would need to write an application to listen for the trace events and then write them out if you needed this type of functionality. With AMO, it's not too hard to write your own trace listener.
ASCII and Reporting Services
Can Reporting Services create an ascii file? We are searchring for a tool that can handle with predefinied queries to generate week figures, month figures etc. These figures will be used in paper publications as well as in electronical publications.
Is it better to use DTS?
--
Message posted via http://www.sqlmonster.comOne of the formats you can pick to render in is csv. You can also render in
XML. So, it depends on what you are wanting to do.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> Can Reporting Services create an ascii file? We are searchring for a tool
that can handle with predefinied queries to generate week figures, month
figures etc. These figures will be used in paper publications as well as in
electronical publications.
> Is it better to use DTS?
> --
> Message posted via http://www.sqlmonster.com|||Even if you render in csv, its not a true csv. If you open up that csv in
excel, all the colums appears in one column, you have to than use that wizard
- Data-> text to column to create true csv. Another avidence of that file
not being ASCII, if you save that file you exported as csv, you will see
UNICODE in your save as dialogue. This has been a problem since day one.
This is the biggest issue why we can't use RS for our reports, because all of
our customer needs true csv and don't know how to use Data -> text to column
wizard of excel.
Thanks
Vipul
"Bruce L-C [MVP]" wrote:
> One of the formats you can pick to render in is csv. You can also render in
> XML. So, it depends on what you are wanting to do.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> > Can Reporting Services create an ascii file? We are searchring for a tool
> that can handle with predefinied queries to generate week figures, month
> figures etc. These figures will be used in paper publications as well as in
> electronical publications.
> > Is it better to use DTS?
> >
> > --
> > Message posted via http://www.sqlmonster.com
>
>|||You can specify the format ascii as CSV Rendering Device Information Setting
if you add
&rc:Encoding=ASCII to the url generating the export
"Vipul Shah" wrote:
> Even if you render in csv, its not a true csv. If you open up that csv in
> excel, all the colums appears in one column, you have to than use that wizard
> - Data-> text to column to create true csv. Another avidence of that file
> not being ASCII, if you save that file you exported as csv, you will see
> UNICODE in your save as dialogue. This has been a problem since day one.
> This is the biggest issue why we can't use RS for our reports, because all of
> our customer needs true csv and don't know how to use Data -> text to column
> wizard of excel.
> Thanks
> Vipul
> "Bruce L-C [MVP]" wrote:
> > One of the formats you can pick to render in is csv. You can also render in
> > XML. So, it depends on what you are wanting to do.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> > message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> > > Can Reporting Services create an ascii file? We are searchring for a tool
> > that can handle with predefinied queries to generate week figures, month
> > figures etc. These figures will be used in paper publications as well as in
> > electronical publications.
> > > Is it better to use DTS?
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> >
> >
> >|||Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RDC" <RDC @.discussions.microsoft.com> wrote in message
news:9F3DC72D-8965-4B78-91C3-8D61B23C7E36@.microsoft.com...
> You can specify the format ascii as CSV Rendering Device Information
> Setting
> if you add
> &rc:Encoding=ASCII to the url generating the export
>
> "Vipul Shah" wrote:
>> Even if you render in csv, its not a true csv. If you open up that csv
>> in
>> excel, all the colums appears in one column, you have to than use that
>> wizard
>> - Data-> text to column to create true csv. Another avidence of that
>> file
>> not being ASCII, if you save that file you exported as csv, you will see
>> UNICODE in your save as dialogue. This has been a problem since day
>> one.
>> This is the biggest issue why we can't use RS for our reports, because
>> all of
>> our customer needs true csv and don't know how to use Data -> text to
>> column
>> wizard of excel.
>> Thanks
>> Vipul
>> "Bruce L-C [MVP]" wrote:
>> > One of the formats you can pick to render in is csv. You can also
>> > render in
>> > XML. So, it depends on what you are wanting to do.
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> >
>> > "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com>
>> > wrote in
>> > message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
>> > > Can Reporting Services create an ascii file? We are searchring for a
>> > > tool
>> > that can handle with predefinied queries to generate week figures,
>> > month
>> > figures etc. These figures will be used in paper publications as well
>> > as in
>> > electronical publications.
>> > > Is it better to use DTS?
>> > >
>> > > --
>> > > Message posted via http://www.sqlmonster.com
>> >
>> >
>> >
Is it better to use DTS?
--
Message posted via http://www.sqlmonster.comOne of the formats you can pick to render in is csv. You can also render in
XML. So, it depends on what you are wanting to do.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> Can Reporting Services create an ascii file? We are searchring for a tool
that can handle with predefinied queries to generate week figures, month
figures etc. These figures will be used in paper publications as well as in
electronical publications.
> Is it better to use DTS?
> --
> Message posted via http://www.sqlmonster.com|||Even if you render in csv, its not a true csv. If you open up that csv in
excel, all the colums appears in one column, you have to than use that wizard
- Data-> text to column to create true csv. Another avidence of that file
not being ASCII, if you save that file you exported as csv, you will see
UNICODE in your save as dialogue. This has been a problem since day one.
This is the biggest issue why we can't use RS for our reports, because all of
our customer needs true csv and don't know how to use Data -> text to column
wizard of excel.
Thanks
Vipul
"Bruce L-C [MVP]" wrote:
> One of the formats you can pick to render in is csv. You can also render in
> XML. So, it depends on what you are wanting to do.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> > Can Reporting Services create an ascii file? We are searchring for a tool
> that can handle with predefinied queries to generate week figures, month
> figures etc. These figures will be used in paper publications as well as in
> electronical publications.
> > Is it better to use DTS?
> >
> > --
> > Message posted via http://www.sqlmonster.com
>
>|||You can specify the format ascii as CSV Rendering Device Information Setting
if you add
&rc:Encoding=ASCII to the url generating the export
"Vipul Shah" wrote:
> Even if you render in csv, its not a true csv. If you open up that csv in
> excel, all the colums appears in one column, you have to than use that wizard
> - Data-> text to column to create true csv. Another avidence of that file
> not being ASCII, if you save that file you exported as csv, you will see
> UNICODE in your save as dialogue. This has been a problem since day one.
> This is the biggest issue why we can't use RS for our reports, because all of
> our customer needs true csv and don't know how to use Data -> text to column
> wizard of excel.
> Thanks
> Vipul
> "Bruce L-C [MVP]" wrote:
> > One of the formats you can pick to render in is csv. You can also render in
> > XML. So, it depends on what you are wanting to do.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> > message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
> > > Can Reporting Services create an ascii file? We are searchring for a tool
> > that can handle with predefinied queries to generate week figures, month
> > figures etc. These figures will be used in paper publications as well as in
> > electronical publications.
> > > Is it better to use DTS?
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> >
> >
> >|||Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Very nice and very fast.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RDC" <RDC @.discussions.microsoft.com> wrote in message
news:9F3DC72D-8965-4B78-91C3-8D61B23C7E36@.microsoft.com...
> You can specify the format ascii as CSV Rendering Device Information
> Setting
> if you add
> &rc:Encoding=ASCII to the url generating the export
>
> "Vipul Shah" wrote:
>> Even if you render in csv, its not a true csv. If you open up that csv
>> in
>> excel, all the colums appears in one column, you have to than use that
>> wizard
>> - Data-> text to column to create true csv. Another avidence of that
>> file
>> not being ASCII, if you save that file you exported as csv, you will see
>> UNICODE in your save as dialogue. This has been a problem since day
>> one.
>> This is the biggest issue why we can't use RS for our reports, because
>> all of
>> our customer needs true csv and don't know how to use Data -> text to
>> column
>> wizard of excel.
>> Thanks
>> Vipul
>> "Bruce L-C [MVP]" wrote:
>> > One of the formats you can pick to render in is csv. You can also
>> > render in
>> > XML. So, it depends on what you are wanting to do.
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> >
>> > "Helma Schapendonk-Maas via SQLMonster.com" <forum@.SQLMonster.com>
>> > wrote in
>> > message news:eb94c01cd0524e8b8a7ef893548c9ddf@.SQLMonster.com...
>> > > Can Reporting Services create an ascii file? We are searchring for a
>> > > tool
>> > that can handle with predefinied queries to generate week figures,
>> > month
>> > figures etc. These figures will be used in paper publications as well
>> > as in
>> > electronical publications.
>> > > Is it better to use DTS?
>> > >
>> > > --
>> > > Message posted via http://www.sqlmonster.com
>> >
>> >
>> >
Thursday, March 8, 2012
As2000 and Analysis Services provider
I can design the dataset using the graphical tools but the report refuses to
run, is there a work around. Presumably the problem will be fixed in the
next service pack?Hello Phil,
If you change the design mode to text, could you get the proper MDX? If you
run the MDX, could you see the correct results?
It seems you custom the data processing extension. Is this true and how you
perform this customization?
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: "Phil Nicholas" <PhilNicholas@.nospam.nospam>
>Subject: As2000 and Analysis Services provider
>Date: Tue, 7 Mar 2006 10:21:40 -0000
>Lines: 5
>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: <#8D4eCdQGHA.5592@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: cpc2-norw3-4-0-cust28.pete.cable.ntl.com 82.25.55.28
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:69941
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I can design the dataset using the graphical tools but the report refuses
to
>run, is there a work around. Presumably the problem will be fixed in the
>next service pack?
>
>
run, is there a work around. Presumably the problem will be fixed in the
next service pack?Hello Phil,
If you change the design mode to text, could you get the proper MDX? If you
run the MDX, could you see the correct results?
It seems you custom the data processing extension. Is this true and how you
perform this customization?
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: "Phil Nicholas" <PhilNicholas@.nospam.nospam>
>Subject: As2000 and Analysis Services provider
>Date: Tue, 7 Mar 2006 10:21:40 -0000
>Lines: 5
>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: <#8D4eCdQGHA.5592@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: cpc2-norw3-4-0-cust28.pete.cable.ntl.com 82.25.55.28
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:69941
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I can design the dataset using the graphical tools but the report refuses
to
>run, is there a work around. Presumably the problem will be fixed in the
>next service pack?
>
>
AS/OLAP data with RS
Hi,
I'm totally new to reporting services (but worked with mdx/asy2k). Tried
today to build RS reports to an AS database. First, is it true you have to
make a whole mdx query (as in mdx sample application) to create the query
string/dataset ? No help or wizard ? Second, is there a way to get
drill-to-detail-functionality, either in the same list as a more detailed
level or in another list in the same report ?I assume you are asking for Analysis Services 2000 and Reporting Services
2000. Did you check the following MSDN article - it should get you started:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
RS 2005 provides new graphical drag & drop MDX and DMX query designers when
connecting to a AS 2005 or AS 2000 cube. Please check this link for a screen
shot of the MDX query designer:
http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx#EEAA
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"/jerome k" </jerome k@.discussions.microsoft.com> wrote in message
news:3CE8E430-FE18-4B31-AA04-2433D4DBE764@.microsoft.com...
> Hi,
> I'm totally new to reporting services (but worked with mdx/asy2k). Tried
> today to build RS reports to an AS database. First, is it true you have to
> make a whole mdx query (as in mdx sample application) to create the query
> string/dataset ? No help or wizard ? Second, is there a way to get
> drill-to-detail-functionality, either in the same list as a more detailed
> level or in another list in the same report ?
>
I'm totally new to reporting services (but worked with mdx/asy2k). Tried
today to build RS reports to an AS database. First, is it true you have to
make a whole mdx query (as in mdx sample application) to create the query
string/dataset ? No help or wizard ? Second, is there a way to get
drill-to-detail-functionality, either in the same list as a more detailed
level or in another list in the same report ?I assume you are asking for Analysis Services 2000 and Reporting Services
2000. Did you check the following MSDN article - it should get you started:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
RS 2005 provides new graphical drag & drop MDX and DMX query designers when
connecting to a AS 2005 or AS 2000 cube. Please check this link for a screen
shot of the MDX query designer:
http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx#EEAA
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"/jerome k" </jerome k@.discussions.microsoft.com> wrote in message
news:3CE8E430-FE18-4B31-AA04-2433D4DBE764@.microsoft.com...
> Hi,
> I'm totally new to reporting services (but worked with mdx/asy2k). Tried
> today to build RS reports to an AS database. First, is it true you have to
> make a whole mdx query (as in mdx sample application) to create the query
> string/dataset ? No help or wizard ? Second, is there a way to get
> drill-to-detail-functionality, either in the same list as a more detailed
> level or in another list in the same report ?
>
Wednesday, March 7, 2012
AS/400 DB2 and Analysis services
I am trying to build a cube.
The data is on the AS/400
I can make a successful connection and can see the table.
When I pull the tables in a Data Source View and try to make my own relationships,
there is no columns. In fact when I try to Explore data, it return an error:
Object reference not set to an instance of an object.
Can someone tell me how to resolve this?
Hi,Can you please tell me if this happens with Analysis Services SP1 and using Microsoft's OleDB provider for DB2 (that is the only provider supported)? Also, in case you tried, does it work against UDB?
--
Raymond
This posting is provided "AS IS" with no warranties, and confers no rights.|||
Hi,
Try to use the IBM Client Access ODBC Driver.
Regards, Christian
|||Looks like I don have the proper version. I download the Microsoft OLEDB Provider for DB2.
Went to install it and got this:
Setup cannot continue because a supported version of SQL Server 2005 is not installed. Supported versions include Enterprise, Developer, or Enterprise Evaluation.
Thanks.
BTW I did try the IBM DB2 UDB version 5.x and that is what I was trying to use. Apparently, IBM has reported a bug. They working on it and it won't be available until the next fix. Whenever that will be.....
AS Processing Task
When using the AS processing task with a connection to "an Analysis Services project in this solution", only some processing options are available for processing dimensions. For instance, it is not possible to select "Process Update". Once I change the connection manager to point to the deployed cube database, I can choose from all the options. Is this by design?I have searched the documentation and the net for information on this. I have not been able to find an explanation. Anyone? At all? 
AS hangs when running a complex mdx query
Hi all,
I am having a problem with Analysis Services. Currently we are developing an analysis application that enables a user to view up to 5 year survival for patients.
I have written the following MDX to perform the calculated measure:
Code Snippet
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL
// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)
CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case
/*
At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):
Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).
NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.
The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.
*/
When IsEmpty( [Survival Time].CurrentMember.PrevMember )
Then ( Root( [Survival Time] ), [N Total Deaths] )
/*
This is the survival censor count at the top ("root") level of survival time,
*/
Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:
(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month
( [Survival Time].CurrentMember.PrevMember, [Number at risk] )
- [N Total Deaths]
)
End
);
END SCOPE;
//--
CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case
// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty( [Survival Time].CurrentMember.PrevMember )
Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)
Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case
// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0
// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )
Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].CurrentMember.PrevMember, [Number at risk] ) - ( [Survival Time].CurrentMember,[N Noncensored Deaths]) ) /
([Survival Time].CurrentMember.PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].CurrentMember.PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;
When I put the Calculated Measure then the survival time dimension in the x axis in the pivot table, it works fine. But when I put a dimension in the filter and choose multiple items (not all and not only one) it tries to build and run the query but seems to hang.
Has anyone else experienced this sort of thing before?
Would it be something to do with the MDX?
I have been struggling with this for a while and not been able to work it out. ANY suggestions would be fantastic.
If you need further information, let me know.
Steve
I have not gone through your code in too much detail, but you main issue is going to be the .CurrentMember function.
When you have mulitple members on the WHERE clause (which is what happens when you filter by more than one member), you do not have a single current member, you actually have a set of current members. SSAS 2005 just does not handle this well. See this blog post for more details: http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx it might give you some hints on how to alter your code.
|||Darren,
Thanks for your reply. I tried going to that blog post but couldn't open the page. Are you able to provide another location for the blog?
Cheers,
Stephen
|||Yeah, I found out that the whole of sqljunkies.com appears to have gone off the air, no word on when or if it will return.
You can find an archived copy of this post in the web archive at http://web.archive.org/web/20070515145719/http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
|||Thanks Darren. I found the blog but am not sure how to fix my code.
I actually tried a couple of things with no success. I removed the .CurrentMember from my code (see code snippet) and it worked for single select queries but it still hangs for multiselect.
Code Snippet
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL
// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)
CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case
/*
At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):
Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).
NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.
The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.
*/
When IsEmpty([Survival Time].PrevMember )
Then ( Root( [Survival Time] ), [N Total Deaths] )
/*
This is the survival censor count at the top ("root") level of survival time,
*/
Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:
(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month
([Survival Time].PrevMember, [Number at risk] )
- [N Total Deaths]
)
End
);
END SCOPE;
//--
CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case
// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty([Survival Time].PrevMember )
Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)
Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case
// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0
// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )
Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].PrevMember, [Number at risk] ) - ([Survival Time],[N Noncensored Deaths]) ) /
([Survival Time].PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;
I appreciate you helping me on this.
Cheers,
Steve
|||Sorry, I was not entirely clear. While removing the explicit CurrentMember calls is good, there is still an implied one for the .PrevMember calls. The query engine still needs a single member context to calculate a PrevMember. There is a way to do this, but it has the unforutnate side effect of slowing down your calc for single member select. What you would need to do is to wrap your case statement (the bit after the equals inside your scope statement) as follows:
GENERATE( EXISTING [Survival Time].Members, <case statement> )
The Existing operator returns the set of one or more survival time members that are in context for the current query and the generate function essentially loops over the set and evaluates the case statement for each one. The results are then aggregated together and returned.
|||Hi Darren,
My guess is that multi-select is not occurring on the [Survival Time] dimension, otherwise there should have been an error like: "The MDX function CURRENTMEMBER failed because the coordinate for .. attribute contains a set", as mentioned in Mosha's blog. The scenario described by Steve suggests that [Survival Time] is on columns, and some other dimension(s) (which aren't mentioned in the script) are being multi-selected in the filter field - but Steve could confirm this? It might be useful to know the client tool (presumably some flavor of Excel).
|||Hi Deepak,
You are right. We are selecting dimensions such as age, location etc.
We are using Dundas for our client tool.
Cheers,
Steve
|||Steve,
Thanks for the clarification - to confirm the exact Dundas product, is it Dundas Chart for .NET - OLAP Services?
|||Deepak,
It is Dundas Chart for .NET - OLAP Service (5.5).
Thanks,
Steve
|||Here are some initial ideas for improving script performance - with more information, these could be elaborated:
It might be possible to handle the first [Survival Time] member using static scoping, rather than a run-time case statement, as discussed in this article. What is the structure of [Survival Time] - how many levels does it have?
Not sure which measures are cube vs. calculated, but if [Measures].[N Total Deaths] and [Measures].[N Censored Deaths] are cube measures, [MEASURES].[N Noncensored Deaths] could be implemented as a cube measure as well. Could you describe the fact table/measure group and associated measures?
Using running sum calculations which leverage block computation - it looks like [MEASURES].[Number at risk] could be implemented using a running sum of [N Total Deaths]?
|||Hi Deepak,
I have run the query when it works and when it hangs. And the following MDX queries are what resulted:
Working:
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( ( [Year of diagnosis].[Year of diagnosis].[All] ), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
Not Working (hangs/uses up all resources):
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( VISUALTOTALS({ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
Thanks for your help so fay.
Steve
|||Hi Steve,
Just wanted to clarify a couple of things:
- Is [Survival Time] just a single level (month) dimension - and approx. how many members?
- The VisualTotals() seem to be superfluous, so could you check whether the 2nd query still hangs without VisualTotals(), like:
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, { [Measures].[Survival] } ) } ON ROWS FROM [Survival] WHERE ( { [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }, ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
|||Hi Deepak,
The [Survival Time] dimension is a single level dimension. It is a count of months from a starting date to a death date.
I also ran the query from the browser in SQL Server Management Studio which dropped Visual Totals and it still hung.
Thanks again for your help.
Cheers,
Steve
|||I'm not sure you need any of the VisualTotals functions. And unless you have overriden the default member settings on some of your dimensions, explicit referencing the default members would be redundant. The following two queries should be equivalent to the last 2 that you posted.
SELECT
NON EMPTY
{
{[Survival Time].[Survival Time].[All]}
, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS
, NON EMPTY
{[Cancer].[Cancers].[All]
, [Cancer].[Cancers].[Stream].members}
ON ROWS
FROM [Survival]
WHERE
( [Measures].[Survival],[Year of diagnosis].[Year of diagnosis].[All] );
--Was not Working
SELECT
NON EMPTY
{
{[Survival Time].[Survival Time].[All]}
, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS
, NON EMPTY
{[Cancer].[Cancers].[All]
, [Cancer].[Cancers].[Stream].members}
ON ROWS
FROM [Survival]
WHERE ( [Measures].[Survival],
{ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001]
, [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] })
AS hangs when running a complex mdx querie
Hi all,
I am having a problem with Analysis Services. Currently we are developing an analysis application that enables a user to view up to 5 year survival for patients.
I have written the following MDX to perform the calculated measure:
Code Snippet
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL
// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)
CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case
/*
At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):
Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).
NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.
The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.
*/
When IsEmpty( [Survival Time].CurrentMember.PrevMember )
Then ( Root( [Survival Time] ), [N Total Deaths] )
/*
This is the survival censor count at the top ("root") level of survival time,
*/
Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:
(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month
( [Survival Time].CurrentMember.PrevMember, [Number at risk] )
- [N Total Deaths]
)
End
);
END SCOPE;
//--
CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case
// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty( [Survival Time].CurrentMember.PrevMember )
Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)
Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case
// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0
// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )
Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].CurrentMember.PrevMember, [Number at risk] ) - ( [Survival Time].CurrentMember,[N Noncensored Deaths]) ) /
([Survival Time].CurrentMember.PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].CurrentMember.PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;
When I put the Calculated Measure then the survival time dimension in the x axis in the pivot table, it works fine. But when I put a dimension in the filter and choose multiple items (not all and not only one) it tries to build and run the query but seems to hang.
Has anyone else experienced this sort of thing before?
Would it be something to do with the MDX?
I have been struggling with this for a while and not been able to work it out. ANY suggestions would be fantastic.
If you need further information, let me know.
Steve
I have not gone through your code in too much detail, but you main issue is going to be the .CurrentMember function.
When you have mulitple members on the WHERE clause (which is what happens when you filter by more than one member), you do not have a single current member, you actually have a set of current members. SSAS 2005 just does not handle this well. See this blog post for more details: http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx it might give you some hints on how to alter your code.
|||Darren,
Thanks for your reply. I tried going to that blog post but couldn't open the page. Are you able to provide another location for the blog?
Cheers,
Stephen
|||Yeah, I found out that the whole of sqljunkies.com appears to have gone off the air, no word on when or if it will return.
You can find an archived copy of this post in the web archive at http://web.archive.org/web/20070515145719/http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
|||Thanks Darren. I found the blog but am not sure how to fix my code.
I actually tried a couple of things with no success. I removed the .CurrentMember from my code (see code snippet) and it worked for single select queries but it still hangs for multiselect.
Code Snippet
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL
// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)
CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case
/*
At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):
Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).
NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.
The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.
*/
When IsEmpty([Survival Time].PrevMember )
Then ( Root( [Survival Time] ), [N Total Deaths] )
/*
This is the survival censor count at the top ("root") level of survival time,
*/
Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:
(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month
([Survival Time].PrevMember, [Number at risk] )
- [N Total Deaths]
)
End
);
END SCOPE;
//--
CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case
// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty([Survival Time].PrevMember )
Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)
Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case
// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0
// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )
Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].PrevMember, [Number at risk] ) - ([Survival Time],[N Noncensored Deaths]) ) /
([Survival Time].PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;
I appreciate you helping me on this.
Cheers,
Steve
|||Sorry, I was not entirely clear. While removing the explicit CurrentMember calls is good, there is still an implied one for the .PrevMember calls. The query engine still needs a single member context to calculate a PrevMember. There is a way to do this, but it has the unforutnate side effect of slowing down your calc for single member select. What you would need to do is to wrap your case statement (the bit after the equals inside your scope statement) as follows:
GENERATE( EXISTING [Survival Time].Members, <case statement> )
The Existing operator returns the set of one or more survival time members that are in context for the current query and the generate function essentially loops over the set and evaluates the case statement for each one. The results are then aggregated together and returned.
|||Hi Darren,
My guess is that multi-select is not occurring on the [Survival Time] dimension, otherwise there should have been an error like: "The MDX function CURRENTMEMBER failed because the coordinate for .. attribute contains a set", as mentioned in Mosha's blog. The scenario described by Steve suggests that [Survival Time] is on columns, and some other dimension(s) (which aren't mentioned in the script) are being multi-selected in the filter field - but Steve could confirm this? It might be useful to know the client tool (presumably some flavor of Excel).
|||Hi Deepak,
You are right. We are selecting dimensions such as age, location etc.
We are using Dundas for our client tool.
Cheers,
Steve
|||Steve,
Thanks for the clarification - to confirm the exact Dundas product, is it Dundas Chart for .NET - OLAP Services?
|||Deepak,
It is Dundas Chart for .NET - OLAP Service (5.5).
Thanks,
Steve
|||Here are some initial ideas for improving script performance - with more information, these could be elaborated:
It might be possible to handle the first [Survival Time] member using static scoping, rather than a run-time case statement, as discussed in this article. What is the structure of [Survival Time] - how many levels does it have?
Not sure which measures are cube vs. calculated, but if [Measures].[N Total Deaths] and [Measures].[N Censored Deaths] are cube measures, [MEASURES].[N Noncensored Deaths] could be implemented as a cube measure as well. Could you describe the fact table/measure group and associated measures?
Using running sum calculations which leverage block computation - it looks like [MEASURES].[Number at risk] could be implemented using a running sum of [N Total Deaths]?
|||Hi Deepak,
I have run the query when it works and when it hangs. And the following MDX queries are what resulted:
Working:
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( ( [Year of diagnosis].[Year of diagnosis].[All] ), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
Not Working (hangs/uses up all resources):
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( VISUALTOTALS({ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
Thanks for your help so fay.
Steve
|||Hi Steve,
Just wanted to clarify a couple of things:
- Is [Survival Time] just a single level (month) dimension - and approx. how many members?
- The VisualTotals() seem to be superfluous, so could you check whether the 2nd query still hangs without VisualTotals(), like:
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, { [Measures].[Survival] } ) } ON ROWS FROM [Survival] WHERE ( { [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }, ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
|||Hi Deepak,
The [Survival Time] dimension is a single level dimension. It is a count of months from a starting date to a death date.
I also ran the query from the browser in SQL Server Management Studio which dropped Visual Totals and it still hung.
Thanks again for your help.
Cheers,
Steve
|||I'm not sure you need any of the VisualTotals functions. And unless you have overriden the default member settings on some of your dimensions, explicit referencing the default members would be redundant. The following two queries should be equivalent to the last 2 that you posted.
SELECT
NON EMPTY
{
{[Survival Time].[Survival Time].[All]}
, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS
, NON EMPTY
{[Cancer].[Cancers].[All]
, [Cancer].[Cancers].[Stream].members}
ON ROWS
FROM [Survival]
WHERE
( [Measures].[Survival],[Year of diagnosis].[Year of diagnosis].[All] );
--Was not Working
SELECT
NON EMPTY
{
{[Survival Time].[Survival Time].[All]}
, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS
, NON EMPTY
{[Cancer].[Cancers].[All]
, [Cancer].[Cancers].[Stream].members}
ON ROWS
FROM [Survival]
WHERE ( [Measures].[Survival],
{ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001]
, [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] })
AS hangs when running a complex mdx querie
Hi all,
I am having a problem with Analysis Services. Currently we are developing an analysis application that enables a user to view up to 5 year survival for patients.
I have written the following MDX to perform the calculated measure:
Code Snippet
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL
// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)
CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case
/*
At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):
Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).
NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.
The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.
*/
When IsEmpty( [Survival Time].CurrentMember.PrevMember )
Then ( Root( [Survival Time] ), [N Total Deaths] )
/*
This is the survival censor count at the top ("root") level of survival time,
*/
Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:
(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month
( [Survival Time].CurrentMember.PrevMember, [Number at risk] )
- [N Total Deaths]
)
End
);
END SCOPE;
//--
CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case
// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty( [Survival Time].CurrentMember.PrevMember )
Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)
Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case
// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0
// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )
Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].CurrentMember.PrevMember, [Number at risk] ) - ( [Survival Time].CurrentMember,[N Noncensored Deaths]) ) /
([Survival Time].CurrentMember.PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].CurrentMember.PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;
When I put the Calculated Measure then the survival time dimension in the x axis in the pivot table, it works fine. But when I put a dimension in the filter and choose multiple items (not all and not only one) it tries to build and run the query but seems to hang.
Has anyone else experienced this sort of thing before?
Would it be something to do with the MDX?
I have been struggling with this for a while and not been able to work it out. ANY suggestions would be fantastic.
If you need further information, let me know.
Steve
I have not gone through your code in too much detail, but you main issue is going to be the .CurrentMember function.
When you have mulitple members on the WHERE clause (which is what happens when you filter by more than one member), you do not have a single current member, you actually have a set of current members. SSAS 2005 just does not handle this well. See this blog post for more details: http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx it might give you some hints on how to alter your code.
|||Darren,
Thanks for your reply. I tried going to that blog post but couldn't open the page. Are you able to provide another location for the blog?
Cheers,
Stephen
|||Yeah, I found out that the whole of sqljunkies.com appears to have gone off the air, no word on when or if it will return.
You can find an archived copy of this post in the web archive at http://web.archive.org/web/20070515145719/http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
|||Thanks Darren. I found the blog but am not sure how to fix my code.
I actually tried a couple of things with no success. I removed the .CurrentMember from my code (see code snippet) and it worked for single select queries but it still hangs for multiselect.
Code Snippet
/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;
//**************************************************************************************
//**************************************************************************************
//**************************************************************************************
// SURVIVAL
// Censor flag = 1 if censored, 0 if noncensored (or true failure),
// NULL if there is data error (death date <= diagnosis date)
CREATE MEMBER CURRENTCUBE.[MEASURES].[N Noncensored Deaths]
AS [Measures].[N Total Deaths] - [Measures].[N Censored Deaths],
FORMAT_STRING = "#",
VISIBLE = 0 ;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Number at risk]
AS 0,
VISIBLE = 1;
SCOPE ([Measures].[Number at risk]); ([Survival Time].Members(1): Null) =
(
Case
/*
At the first survival time (defined as Null, i.e. 1st row with "Blank" time,
when there is no previous month; PrevMember is empty):
Number at risk is simply total number of valid death dates (both censored and uncensored)
in the dataset, which is the total number of patients with censor flag values of
either 0 or 1 (not null), i.e. Number at risk at t == 0 (date of diagnosis).
NOTE: Death dates are right-censored at 01 June 2006 - people who haven't died
by this date are censored and assigned a death date of June 1, 2006.
The censor flag is set to null only when death date is same as or EARLIER than
date of diagnosis, i.e. excludes patients diagnosed during autopsy.
*/
When IsEmpty([Survival Time].PrevMember )
Then ( Root( [Survival Time] ), [N Total Deaths] )
/*
This is the survival censor count at the top ("root") level of survival time,
*/
Else // At subsequent survival times ( t > 0, censor <> null ), Number at risk is:
(
// Number at risk from previous month,
// minus total number of deaths (both censored and uncensored)
// during current month
([Survival Time].PrevMember, [Number at risk] )
- [N Total Deaths]
)
End
);
END SCOPE;
//--
CREATE MEMBER CURRENTCUBE.[MEASURES].[Survival]
AS 100,
FORMAT_STRING = '0.0',
VISIBLE = 1;
SCOPE ([Measures].[Survival]); ([Survival Time].Members(1): Null) =
(
Case
// At the first survival time (defined as Null, i.e. 1st row with "Blank" time):
When IsEmpty([Survival Time].PrevMember )
Then (
(
(
( Root([Survival Time]),[N Total Deaths] ) - [N Total Deaths] ) / ( (Root([Survival Time]), [N Total Deaths] ) - ([Survival Time],[N Censored Deaths])
)
)*100
)
Else // At subsequent survival times ( t > 0, censor <> null ):
(
Case
// If everyone's dead (at right end of time(x) axis)
When [Number at risk] = 0
// Then use survival from previous month (PrevMember)
Then "Censored" //( [Survival Time].CurrentMember.PrevMember, [Survival] )
Else (
(
// Number at risk from previous month, minus true failures (noncensored deaths),
// divided by Number at risk from previous month ...
(
( ([Survival Time].PrevMember, [Number at risk] ) - ([Survival Time],[N Noncensored Deaths]) ) /
([Survival Time].PrevMember, [Number at risk]
)
)
// then multiplied by survival from previous month
* ([Survival Time].PrevMember, [Survival])
)
)
End
)
End
); END SCOPE;
I appreciate you helping me on this.
Cheers,
Steve
|||Sorry, I was not entirely clear. While removing the explicit CurrentMember calls is good, there is still an implied one for the .PrevMember calls. The query engine still needs a single member context to calculate a PrevMember. There is a way to do this, but it has the unforutnate side effect of slowing down your calc for single member select. What you would need to do is to wrap your case statement (the bit after the equals inside your scope statement) as follows:
GENERATE( EXISTING [Survival Time].Members, <case statement> )
The Existing operator returns the set of one or more survival time members that are in context for the current query and the generate function essentially loops over the set and evaluates the case statement for each one. The results are then aggregated together and returned.
|||Hi Darren,
My guess is that multi-select is not occurring on the [Survival Time] dimension, otherwise there should have been an error like: "The MDX function CURRENTMEMBER failed because the coordinate for .. attribute contains a set", as mentioned in Mosha's blog. The scenario described by Steve suggests that [Survival Time] is on columns, and some other dimension(s) (which aren't mentioned in the script) are being multi-selected in the filter field - but Steve could confirm this? It might be useful to know the client tool (presumably some flavor of Excel).
|||Hi Deepak,
You are right. We are selecting dimensions such as age, location etc.
We are using Dundas for our client tool.
Cheers,
Steve
|||Steve,
Thanks for the clarification - to confirm the exact Dundas product, is it Dundas Chart for .NET - OLAP Services?
|||Deepak,
It is Dundas Chart for .NET - OLAP Service (5.5).
Thanks,
Steve
|||Here are some initial ideas for improving script performance - with more information, these could be elaborated:
It might be possible to handle the first [Survival Time] member using static scoping, rather than a run-time case statement, as discussed in this article. What is the structure of [Survival Time] - how many levels does it have?
Not sure which measures are cube vs. calculated, but if [Measures].[N Total Deaths] and [Measures].[N Censored Deaths] are cube measures, [MEASURES].[N Noncensored Deaths] could be implemented as a cube measure as well. Could you describe the fact table/measure group and associated measures?
Using running sum calculations which leverage block computation - it looks like [MEASURES].[Number at risk] could be implemented using a running sum of [N Total Deaths]?
|||Hi Deepak,
I have run the query when it works and when it hangs. And the following MDX queries are what resulted:
Working:
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( ( [Year of diagnosis].[Year of diagnosis].[All] ), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
Not Working (hangs/uses up all resources):
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, VISUALTOTALS({ [Measures].[Survival] }) ) } ON ROWS FROM [Survival] WHERE ( VISUALTOTALS({ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }), ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
Thanks for your help so fay.
Steve
|||Hi Steve,
Just wanted to clarify a couple of things:
- Is [Survival Time] just a single level (month) dimension - and approx. how many members?
- The VisualTotals() seem to be superfluous, so could you check whether the 2nd query still hangs without VisualTotals(), like:
SELECT NON EMPTY {{[Survival Time].[Survival Time].[All]}, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS, NON EMPTY { Crossjoin({{[Cancer].[Cancers].[All]}, [Cancer].[Cancers].[Stream].members}, { [Measures].[Survival] } ) } ON ROWS FROM [Survival] WHERE ( { [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001], [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] }, ( [Age at diagnosis].[Age].[All] ), ( [Sex].[Sex].[All] ), ( [Residence].[Residence].[All] ), ( [Rurality].[Rurality].[All] ) )
|||Hi Deepak,
The [Survival Time] dimension is a single level dimension. It is a count of months from a starting date to a death date.
I also ran the query from the browser in SQL Server Management Studio which dropped Visual Totals and it still hung.
Thanks again for your help.
Cheers,
Steve
|||I'm not sure you need any of the VisualTotals functions. And unless you have overriden the default member settings on some of your dimensions, explicit referencing the default members would be redundant. The following two queries should be equivalent to the last 2 that you posted.
SELECT
NON EMPTY
{
{[Survival Time].[Survival Time].[All]}
, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS
, NON EMPTY
{[Cancer].[Cancers].[All]
, [Cancer].[Cancers].[Stream].members}
ON ROWS
FROM [Survival]
WHERE
( [Measures].[Survival],[Year of diagnosis].[Year of diagnosis].[All] );
--Was not Working
SELECT
NON EMPTY
{
{[Survival Time].[Survival Time].[All]}
, [Survival Time].[Survival Time].[Survival Time].members} ON COLUMNS
, NON EMPTY
{[Cancer].[Cancers].[All]
, [Cancer].[Cancers].[Stream].members}
ON ROWS
FROM [Survival]
WHERE ( [Measures].[Survival],
{ [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2001]
, [Year of diagnosis].[Year of diagnosis].[Year of diagnosis].&[2003] })
Subscribe to:
Posts (Atom)