Report design scenario

Hey guys,

I want to create a report called "Report X", which has four reports under it. Assume the following scenario.

1. Report X

1.1. Bar chart report

1.2. Pie chart report

1.3. Matrix report

1.4. Pie chart report

In addition to this, there are about four parameters that should be available for all reports. So my question is: what approach should I follow to implement/design a report for the above requirement.

Thank you for your cooperation in advance.

Sincerely,

Amde

The question is discussed/answered in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=374405&SiteID=1

Report Builder problem with no All level on Scenario?

At first glance, this appears to be a bug in Reporting Services...
I have a 2005 cube with a Scenario dimension. On the Scenario attribute I
set IsAggregatable to False (since it doesn't make sense to add Actuals and
Budgets). This effectively removes the All level, as I understand it. I've
set the DefaultMember to be [Scenario].[Scenario].[Actual]. Now I create a
datasource in SSRS and generate a model off it. When I build a Report Builder
report off the model, I have a problem. If the report does not specify
scenario as a row, column, or filter, then the MDX that gets generated
(captured this via Profiler) starts...
WITH
MEMBER [Scenario].[Scenario].[Agg_0] AS 'Aggregate( [CS_Set0] ) '
SET [CS_Set0] AS [Scenario].[Scenario].Levels(0).Members
And since [Agg_0] is used in the body of the query inside an Exists()
function, it results in the query returning no cells (so the report says that
no data was returned). This may be a problem with the engine that translates
a model query into MDX. For some reason it's not paying attention to the
DefaultMember. (That's why I posted to this newsgroup, not the SSAS
newsgroup.)
When I flip IsAggregatable back to True such that there's an All level,
everything works fine. It gives me the correct numbers. But I don't want to
show an All level for the Scenario dimension.
Please let me know if you have any suggestions or if you can confirm this is
a bug. And please let me know if the SSRS guys feel this is an SSAS issue
that should be posted in that newsgroup.
(As a side note, though I get the right numbers by turning IsAggregatable to
True, I think the MDX is still not quite right because it references
[Scenario].[Scenario].[All] in the Exists function when it really should be
referencing [Scenario].CurrentMember. But that's another topic and not nearly
as crucial. The worst that could happen there is that you show some unneeded
rows. And I may be wrong on whether it should use CurrentMember.)"FurmanGG" wrote:
> At first glance, this appears to be a bug in Reporting Services...
> I have a 2005 cube with a Scenario dimension. On the Scenario attribute I
> set IsAggregatable to False (since it doesn't make sense to add Actuals and
> Budgets). This effectively removes the All level, as I understand it. I've
> set the DefaultMember to be [Scenario].[Scenario].[Actual]. Now I create a
> datasource in SSRS and generate a model off it. When I build a Report Builder
> report off the model, I have a problem. If the report does not specify
> scenario as a row, column, or filter, then the MDX that gets generated
> (captured this via Profiler) starts...
> WITH
> MEMBER [Scenario].[Scenario].[Agg_0] AS 'Aggregate( [CS_Set0] ) '
> SET [CS_Set0] AS [Scenario].[Scenario].Levels(0).Members
> And since [Agg_0] is used in the body of the query inside an Exists()
> function, it results in the query returning no cells (so the report says that
> no data was returned). This may be a problem with the engine that translates
> a model query into MDX. For some reason it's not paying attention to the
> DefaultMember. (That's why I posted to this newsgroup, not the SSAS
> newsgroup.)
> When I flip IsAggregatable back to True such that there's an All level,
> everything works fine. It gives me the correct numbers. But I don't want to
> show an All level for the Scenario dimension.
> Please let me know if you have any suggestions or if you can confirm this is
> a bug. And please let me know if the SSRS guys feel this is an SSAS issue
> that should be posted in that newsgroup.
> (As a side note, though I get the right numbers by turning IsAggregatable to
> True, I think the MDX is still not quite right because it references
> [Scenario].[Scenario].[All] in the Exists function when it really should be
> referencing [Scenario].CurrentMember. But that's another topic and not nearly
> as crucial. The worst that could happen there is that you show some unneeded
> rows. And I may be wrong on whether it should use CurrentMember.)|||Hello,
Did you get any response to this problem or find any solution? I have about
the same problem; I am building an OLAP cube with a time dimension and stock
number. Those stock figures cannot be aggregated over time, so I set the
IsAggregatable option on the time dimension to False. When I do that, Report
Builder does not work as I would expect...
Thanks, Hans.
"FurmanGG" wrote:
> At first glance, this appears to be a bug in Reporting Services...
> I have a 2005 cube with a Scenario dimension. On the Scenario attribute I
> set IsAggregatable to False (since it doesn't make sense to add Actuals and
> Budgets). This effectively removes the All level, as I understand it. I've
> set the DefaultMember to be [Scenario].[Scenario].[Actual]. Now I create a
> datasource in SSRS and generate a model off it. When I build a Report Builder
> report off the model, I have a problem. If the report does not specify
> scenario as a row, column, or filter, then the MDX that gets generated
> (captured this via Profiler) starts...
> WITH
> MEMBER [Scenario].[Scenario].[Agg_0] AS 'Aggregate( [CS_Set0] ) '
> SET [CS_Set0] AS [Scenario].[Scenario].Levels(0).Members
> And since [Agg_0] is used in the body of the query inside an Exists()
> function, it results in the query returning no cells (so the report says that
> no data was returned). This may be a problem with the engine that translates
> a model query into MDX. For some reason it's not paying attention to the
> DefaultMember. (That's why I posted to this newsgroup, not the SSAS
> newsgroup.)
> When I flip IsAggregatable back to True such that there's an All level,
> everything works fine. It gives me the correct numbers. But I don't want to
> show an All level for the Scenario dimension.
> Please let me know if you have any suggestions or if you can confirm this is
> a bug. And please let me know if the SSRS guys feel this is an SSAS issue
> that should be posted in that newsgroup.
> (As a side note, though I get the right numbers by turning IsAggregatable to
> True, I think the MDX is still not quite right because it references
> [Scenario].[Scenario].[All] in the Exists function when it really should be
> referencing [Scenario].CurrentMember. But that's another topic and not nearly
> as crucial. The worst that could happen there is that you show some unneeded
> rows. And I may be wrong on whether it should use CurrentMember.)|||Should be fixed in SP1...
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=77da7b11-f20a-41ee-a18a-a74fd5ac4650
"Hans Geurtsen" wrote:
> Hello,
> Did you get any response to this problem or find any solution? I have about
> the same problem; I am building an OLAP cube with a time dimension and stock
> number. Those stock figures cannot be aggregated over time, so I set the
> IsAggregatable option on the time dimension to False. When I do that, Report
> Builder does not work as I would expect...
> Thanks, Hans.
> "FurmanGG" wrote:
> > At first glance, this appears to be a bug in Reporting Services...
> >
> > I have a 2005 cube with a Scenario dimension. On the Scenario attribute I
> > set IsAggregatable to False (since it doesn't make sense to add Actuals and
> > Budgets). This effectively removes the All level, as I understand it. I've
> > set the DefaultMember to be [Scenario].[Scenario].[Actual]. Now I create a
> > datasource in SSRS and generate a model off it. When I build a Report Builder
> > report off the model, I have a problem. If the report does not specify
> > scenario as a row, column, or filter, then the MDX that gets generated
> > (captured this via Profiler) starts...
> >
> > WITH
> > MEMBER [Scenario].[Scenario].[Agg_0] AS 'Aggregate( [CS_Set0] ) '
> > SET [CS_Set0] AS [Scenario].[Scenario].Levels(0).Members
> >
> > And since [Agg_0] is used in the body of the query inside an Exists()
> > function, it results in the query returning no cells (so the report says that
> > no data was returned). This may be a problem with the engine that translates
> > a model query into MDX. For some reason it's not paying attention to the
> > DefaultMember. (That's why I posted to this newsgroup, not the SSAS
> > newsgroup.)
> >
> > When I flip IsAggregatable back to True such that there's an All level,
> > everything works fine. It gives me the correct numbers. But I don't want to
> > show an All level for the Scenario dimension.
> >
> > Please let me know if you have any suggestions or if you can confirm this is
> > a bug. And please let me know if the SSRS guys feel this is an SSAS issue
> > that should be posted in that newsgroup.
> >
> > (As a side note, though I get the right numbers by turning IsAggregatable to
> > True, I think the MDX is still not quite right because it references
> > [Scenario].[Scenario].[All] in the Exists function when it really should be
> > referencing [Scenario].CurrentMember. But that's another topic and not nearly
> > as crucial. The worst that could happen there is that you show some unneeded
> > rows. And I may be wrong on whether it should use CurrentMember.)

Report Builder Connection Error

Okay, here is the scenario. I have an analysis services cube and a report
model generated from a OLAP datasource in reporting services. My first
problem is that when I try to create a report using the report builder from
my local pc I get data source connection errors, but if I remote desktop into
the dev server where reporting services is installed an complete the same
procedure everything works great. My second problem is that I cannot create
a report model from an OLAP datasource unless I am remoted into the report
server.
Thanks, Andrewwhen you create report model, you need to use default in your data source
properties, so that it takes the same and connects.
second problem is: have you deployed your report model, only then you can
see the entities and then you can create the reports.
Amarnath
"chabotwvu" wrote:
> Okay, here is the scenario. I have an analysis services cube and a report
> model generated from a OLAP datasource in reporting services. My first
> problem is that when I try to create a report using the report builder from
> my local pc I get data source connection errors, but if I remote desktop into
> the dev server where reporting services is installed an complete the same
> procedure everything works great. My second problem is that I cannot create
> a report model from an OLAP datasource unless I am remoted into the report
> server.
> Thanks, Andrew|||When I navigate to my OLAP datasource using SQL Server Management Studio and
generate the model my datasource security is set to windows integrated
security. The OLAP datasource impersonation information is set to use the
service account; when I do this from SSMS on my local laptop I get connection
errors, but if I remote desktop into the reporting services server and
connect to reporting services using the SSMS it will create the model and you
can create and run a report from the report builder no problem.
"Amarnath" wrote:
> when you create report model, you need to use default in your data source
> properties, so that it takes the same and connects.
> second problem is: have you deployed your report model, only then you can
> see the entities and then you can create the reports.
> Amarnath
>
> "chabotwvu" wrote:
> > Okay, here is the scenario. I have an analysis services cube and a report
> > model generated from a OLAP datasource in reporting services. My first
> > problem is that when I try to create a report using the report builder from
> > my local pc I get data source connection errors, but if I remote desktop into
> > the dev server where reporting services is installed an complete the same
> > procedure everything works great. My second problem is that I cannot create
> > a report model from an OLAP datasource unless I am remoted into the report
> > server.
> >
> > Thanks, Andrew|||Here is the error message from the Report Server Log Files:
e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'dataSource1'., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'dataSource1'.
--> Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException:
The connection either timed out or was lost. --> System.IO.IOException:
Unable to read data from the transport connection:
An existing connection was forcibly closed by the remote host. -->
System.Net.Sockets.SocketException: An existing connection was forcibly
closed by the remote host
"chabotwvu" wrote:
> When I navigate to my OLAP datasource using SQL Server Management Studio and
> generate the model my datasource security is set to windows integrated
> security. The OLAP datasource impersonation information is set to use the
> service account; when I do this from SSMS on my local laptop I get connection
> errors, but if I remote desktop into the reporting services server and
> connect to reporting services using the SSMS it will create the model and you
> can create and run a report from the report builder no problem.
> "Amarnath" wrote:
> > when you create report model, you need to use default in your data source
> > properties, so that it takes the same and connects.
> >
> > second problem is: have you deployed your report model, only then you can
> > see the entities and then you can create the reports.
> >
> > Amarnath
> >
> >
> > "chabotwvu" wrote:
> >
> > > Okay, here is the scenario. I have an analysis services cube and a report
> > > model generated from a OLAP datasource in reporting services. My first
> > > problem is that when I try to create a report using the report builder from
> > > my local pc I get data source connection errors, but if I remote desktop into
> > > the dev server where reporting services is installed an complete the same
> > > procedure everything works great. My second problem is that I cannot create
> > > a report model from an OLAP datasource unless I am remoted into the report
> > > server.
> > >
> > > Thanks, Andrew

Report builder and Report Designer

Hi,
I am new to SQL reporting service (2005).
Will like to find out can report builder open a report created by report
designer.
My scenario is like this, I will like to create some reports for my
clients using report designer. Afterwhich, I will like to a allow end-user
to modify the report using report builder. How should I solution this?
I notice that Report Designer can create 2 types of projects (i.e. model
and report server)... When I use the report server, I am not able to use the
model created.
Is there anything that I miss?
Thanks
Regards
eforceIs there a way for End user to amend the report that is created by Report
designer, just like using report builder.
We tried creating a report using report designer, but report builder is not
able to open it.
Is there a walkaround to do so?
It is because our end user will like to have the flexibility to make some
cosmetic changes to those reports that we created using Report designer.
Thanks.
"eforce" <eforce@.newsgroup.nospam> wrote in message
news:OOvXMSZIGHA.344@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am new to SQL reporting service (2005).
> Will like to find out can report builder open a report created by
> report designer.
> My scenario is like this, I will like to create some reports for my
> clients using report designer. Afterwhich, I will like to a allow end-user
> to modify the report using report builder. How should I solution this?
> I notice that Report Designer can create 2 types of projects (i.e.
> model and report server)... When I use the report server, I am not able to
> use the model created.
> Is there anything that I miss?
> Thanks
> Regards
> eforce
>
>|||Report Builder can not open RDL files
It needs a model that you have to design (in BI studio),, and the end
user will use this model to design his own reports
EH
"eforce" <eforce@.newsgroup.nospam> wrote in message
news:%231gVbXtJGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Is there a way for End user to amend the report that is created by
> Report
> designer, just like using report builder.
> We tried creating a report using report designer, but report builder
> is not
> able to open it.
> Is there a walkaround to do so?
> It is because our end user will like to have the flexibility to make
> some
> cosmetic changes to those reports that we created using Report
> designer.
> Thanks.
> "eforce" <eforce@.newsgroup.nospam> wrote in message
> news:OOvXMSZIGHA.344@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> I am new to SQL reporting service (2005).
>> Will like to find out can report builder open a report created
>> by report designer.
>> My scenario is like this, I will like to create some reports for
>> my clients using report designer. Afterwhich, I will like to a
>> allow end-user to modify the report using report builder. How
>> should I solution this?
>> I notice that Report Designer can create 2 types of projects
>> (i.e. model and report server)... When I use the report server, I
>> am not able to use the model created.
>> Is there anything that I miss?
>> Thanks
>> Regards
>> eforce
>>
>|||You can, however, create a report in Report Designer based on a model using
the Report Model Query Designer. Then, you can save this report and open it
in Report Builder. Be aware though that some Report Designer functionality
cannot be consumed by Report Builder.
For more information at the Report Model Query Designer, see
http://msdn2.microsoft.com/en-us/library/ms345239(SQL.90).aspx
"EH" wrote:
> Report Builder can not open RDL files
> It needs a model that you have to design (in BI studio),, and the end
> user will use this model to design his own reports
> EH
> "eforce" <eforce@.newsgroup.nospam> wrote in message
> news:%231gVbXtJGHA.1032@.TK2MSFTNGP11.phx.gbl...
> > Is there a way for End user to amend the report that is created by
> > Report
> > designer, just like using report builder.
> >
> > We tried creating a report using report designer, but report builder
> > is not
> > able to open it.
> > Is there a walkaround to do so?
> >
> > It is because our end user will like to have the flexibility to make
> > some
> > cosmetic changes to those reports that we created using Report
> > designer.
> >
> > Thanks.
> >
> > "eforce" <eforce@.newsgroup.nospam> wrote in message
> > news:OOvXMSZIGHA.344@.TK2MSFTNGP11.phx.gbl...
> >> Hi,
> >> I am new to SQL reporting service (2005).
> >>
> >> Will like to find out can report builder open a report created
> >> by report designer.
> >>
> >> My scenario is like this, I will like to create some reports for
> >> my clients using report designer. Afterwhich, I will like to a
> >> allow end-user to modify the report using report builder. How
> >> should I solution this?
> >>
> >> I notice that Report Designer can create 2 types of projects
> >> (i.e. model and report server)... When I use the report server, I
> >> am not able to use the model created.
> >>
> >> Is there anything that I miss?
> >>
> >> Thanks
> >>
> >> Regards
> >> eforce
> >>
> >>
> >>
> >
> >
>
>

Report Builder / sorting

It is not possible to sort by value which is not selected as report
field.
In my scenario, I've selected monthname as field and I can not sort it
chronologically :(
KamelI don't know if this will help, but in the UDM for each attribute you can
specify the data that will be displayed and you can specify differnt data to
sort on.
So you could display the month name, and sort it on the year, month values
of the date.
Look for the sort attributes of the attributes properties.
hth, Mike
"kamel" <kwiciak@.gmail.com> wrote in message
news:1146557598.692677.326160@.u72g2000cwu.googlegroups.com...
> It is not possible to sort by value which is not selected as report
> field.
> In my scenario, I've selected monthname as field and I can not sort it
> chronologically :(
> Kamel
>

Report Authenication

Hi,
I have the following scenario:
1) A database server (machine A, Windows 2000 Server Standard) within a
domain
2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
same domain; the Reporting-Services-Database is also on the Database-server
3) My Client (windows XP) within the same domain.
My Problem: When I publisch a Report to the Reporting Server and I use
"Windows integrated Authentication", I get an error "NT-Authority/Anonymous
User" cant access the database. That's right, but why wouldn't be used my
Username/password instead of Anonymous? What have I to do, to pass my
Logonuser form XP over the Reporting-Services to the Database ? A always
thought "windows integrated authentication" in the shared Connection object
would be enough?
Thanks
HansIt sounds to me like you have IIS setup for anonymous access. When you do
this all users are the same user (the anonymous user as you see below). You
should only be doing this if you are implementing custom authorization.
Otherwise this is a really bad idea.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hans" <sqlserver@.strengberg.net> wrote in message
news:O0FIBcazEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the
Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error
"NT-Authority/Anonymous
> User" cant access the database. That's right, but why wouldn't be used my
> Username/password instead of Anonymous? What have I to do, to pass my
> Logonuser form XP over the Reporting-Services to the Database ? A always
> thought "windows integrated authentication" in the shared Connection
object
> would be enough?
> Thanks
> Hans
>|||If you are trying to authenticate in a scenerio which involves more than two
endpoints eg: client connects to remote RS web service which connects to a
remote database (3 endpoints) and tou're using Integrated security, NTLM
authentication will not work, you will have to setup a delegation scenerio
using kerberos authentication which supports more than one network hop. Look
here for help about setting up a asp.net app for delegation.
http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
To test that this is your problem first access the report from the reporting
services console, this seems to cache your credential locally, now try from
your remote client try the same report, it should work until you log off the
rs server.
HTH,
Dan
"Hans" wrote:
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error "NT-Authority/Anonymous
> User" cant access the database. That's right, but why wouldn't be used my
> Username/password instead of Anonymous? What have I to do, to pass my
> Logonuser form XP over the Reporting-Services to the Database ? A always
> thought "windows integrated authentication" in the shared Connection object
> would be enough?
> Thanks
> Hans
>
>|||Thanks to all for the answers. I have disabled all anonymous and I think,
its the problem, Dan reports, because I have 3 Endpoints. I will test this.
Thanks again.
Hans
"Hans" <sqlserver@.strengberg.net> schrieb im Newsbeitrag
news:O0FIBcazEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have the following scenario:
> 1) A database server (machine A, Windows 2000 Server Standard) within a
> domain
> 2) A Reporting Server (machine B, Windows 2003 Server Standard) within the
> same domain; the Reporting-Services-Database is also on the
> Database-server
> 3) My Client (windows XP) within the same domain.
> My Problem: When I publisch a Report to the Reporting Server and I use
> "Windows integrated Authentication", I get an error
> "NT-Authority/Anonymous User" cant access the database. That's right, but
> why wouldn't be used my Username/password instead of Anonymous? What have
> I to do, to pass my Logonuser form XP over the Reporting-Services to the
> Database ? A always thought "windows integrated authentication" in the
> shared Connection object would be enough?
> Thanks
> Hans
>