Interlektual property

Hello all,

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

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

Thanks heaps for any advise.

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

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

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

|||

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

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

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

|||

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

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

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

Interlektual property

Hello all,

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

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

Thanks heaps for any advise.

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

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

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

|||

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

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

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

|||

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

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

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

Interface for Analysis Services 2005

Dear friends,

I have a problem…

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

Thanks!!

Pedro,

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

High level design:

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

- In Sharepoint you create report library(s)

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

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

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

Vidas Matelis

|||

Thanks Vidas for your help.

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

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

regards

|||

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

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

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

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

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

Vidas Matelis

|||

Thanks for your post...

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

Thanks

|||

Pedro,

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

Vidas Matelis

|||

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

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

|||

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

Mosha,

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

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

Thank you,

Vidas Matelis

|||

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

Thanks both!

|||

MOsha,

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

Regards!

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

Mosha,

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

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

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

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

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

Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

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

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

Did I understand right?

Thank you,

Vidas Matelis

|||

For Roles property your understanding is correct.

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

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

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

- I would disable access to direct measures to users.

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

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

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

|||

Mosha,

Thank you! This was very helpful.

Vidas Matelis

Interesting M-M Relationship

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

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

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

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

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

Dave Fackler
Hi Dave

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

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

Integration of Analysis service to sharepoint

Is there any sites or demo on how to use sharepoint with the analysis services in sql server 2005. Does the reports generated requires RS as well?

Thanks,

Regards

Alu

There is an effort underway to make AdventureWorks dashboard demo available publicly but I don't know the details yet. Most likely it will coincide with the SharePoint 2007 release.

You should be able to plug-in any URL-addressable report in SPS. Of course, RS reports will work best :-) More details in the forthcoming CTP 2 of SQL Server SP2.

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.

Integration between SSRS and SSAS

Any plans to improve the integration between Reporting Services and Analysis Services?

I‘ll try to be specific as possible. Below is a state of affairs for the SSRS and SSAS integration as of today (MS SQL Server 2005):

1. When querying Analysis Services cubes, the data can be returned in one of two ways: as a cellset or as a recordset. Reporting Services uses the recordset format, and as such, the data from Analysis Services goes through a flattening process to return the data in a two-dimensional grid.
   SSRS should accommodate SSAS native output format without flattening.
   This is obviously a very serious point of contention between SSRS and SSAS. This particular issue is affecting usefulness of the SSRS as reporting software for SSAS.
2. Quite often, depending on the MDX, SSRS produces the infamous error message “The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension”.
3. The error message above pushes to switch from the native "Microsoft SQL Server Analysis Services (AdomdClient)" to MS SQL Server 2005 Analysis Services 9.0 OLE DB Provider. However, OLEDB provider goes through the flattening process which negatively affects the received data overall format on the SSRS side.
   ADOMD native provider should support any kosher MDX statement. SSRS should be able to receive ADOMD output as is.
4. Graphical MDX Query Designer doesn’t accept manual changes tothe MDX.
   It should be 2-way communication.MDX Query Designer should analyze manually tweaked MDX and if it is legit accept it.

Hopefully, Tablix region as a UI control will be able to support native SSAS data outputs in MS SQL Server 2008.

I am talking here about straight reporting. I am not asking for the interactive OLAP client.

References:

1. Integrating Analysis Services with Reporting Services
   http://technet.microsoft.com/en-us/library/aa902647(sql.80).aspx
2. Rant: Reporting Services and Analysis Services
   http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
3. The state of Reporting Services UDM (Analysis Services) integration
   http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!185.entry

Regards,

Yitzhak Khabinsky

Brian Welcker/ Robert Bruckner: I hope that one of you can elaborate. This is a VERY important subject matter. Thank you.a

|||

I agree....from the very beginning I thought that SSRS's ability to work with cubes was a VERY important feature because it would allow access to the amazing aggregation power that cubes have...without this you have to spend lots of time writing complicated and bad performing SQL in order to put aggregates in your reports (and the fact is that reports are mostly about aggregating data). I would have thought this to be a simple, natural solution and am AMAZED that it doesn't work.

I actually work for a company where we currently use Cognos and Reporting Services, and one of the things I've told them is to consider using Analysis Services instead because then the cubes would be accessible to SSRS. I guess I bought into the marketing hype a little too early :-).

SB

|||

Thanks for your feedback. The core scenario you seem to want to paste an arbitrary MDX statement into RS and have the RS engine format it. One question would be - where does your MDX come from? Are you crafting it by hand? We felt that most users are not MDX experts and would simply use the query builder (which will form the MDX in a conformant form).

Anyway, while supporting arbitrary MDX is certainly a valid scenario, it is simply not what RS was designed to do. In order to understand the data that is being returned from a source, SSRS must have a fixed schema. Without a schema, RS cannot compute subtotals or sort or filter the data. A cellset based tool relies fully on the back-end for all interactivity (which negates all of the RS caching layer). Tablix in 2008 does nothing to change this approach - in fact several of the Tablix functions (side by side groups, for example) are not even possible to express in MDX.

We do understand that people would like add this functionality to RS and we will consider supporting arbitrary schema and round trip the interactivity for post-2008 future version.

Integrating KPI in Reporting Services and drill down reports

I'm interested to implement KPIs from Analysis Services 2005 in Reporting Services 2005 in a graphical way as it is seen inside Analysis Services.

how can I archieve this?

and

what is the best way for drilldowns in reporting services?

Thanks for your help,

Roger

OK, my question is general - thats because I'm new to SSRS... So I did some reading and found out that the Matrix is a good way to display drill-downs.

I'm wondering: Do charts offer a possibility for drilldowns?

and I did not found a place to read anything about KPI and presenting them in Reporting Services in a easy and efficient way like you can do it in SSAS.

Anybody can give me a hint?

Thanks, Roger

|||

One way to do support AS2005 KPI's is to place an image control within your data region (e.g. table) and use separate images to represent each state the KPI could be in. Then use an expression to map each KPI image to the correct state based on your the value of an expression as follows:

=iif(Fields!Product_Gross_Profit_Margin_Value.Value = -1, "gauge_asc0",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < -0.5, "gauge_asc1",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0, "gauge_asc2",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0.5, "gauge_asc3",

"gauge_asc4"))))

Here, guage_ascn represents the various states the KPI could be in. While not 100% integrated to "absorb" AS2005 KPI images, should still let you get the job done.

Alternatively, might want to start from a Report Builder report since that tool offers native support for showing AS2005 KPI's. To see how to edit a Report Builder report using the report authoring tool in VS2005 see http://blogs.msdn.com/bimusings/archive/2005/09/23/473379.aspx

|||Thanks! Regards Tom|||

How to include SQL Server 2005 KPI’s in Reporting Services Reports

(The following is originally a description with screendumps, which unfortunately are not supported. Hopefully usefull anyway ...)

There seems to be an issue about including SQL Server 2005 KPI's in Reporting Services Reports (in Visual Studio) - especially the images associated with the generated parameters, e.g. "-1" leads to "red gauge / traffic light" and so on.

However it is possible to make reports in Report Builder including SQL Server 2005 KPI's.

It has been suggested to include a data source and a Report Server Model in the Visual Studio Project. This should make it possible to build a report including KPI’s. However there still seems to be a problem accessing the measures even though the metadata can be viewed. The Report Wizard does not have an option for including graphics for the indicators – only the numbers to generate the images (“1”, ”-1”, ”0” etc.). Therefore the indicators are not included in the dataset and cannot be included in the reports.

This has lead to the suggestion, that there is an issue with the Report Model functionality in Visual Studio.

There is a work around though ...

First you must associate your KPI’s to measure groups in the Visual Studio project …

Establish an Analysis Services data source on your Report Server …

Note: the Connection String must be typed when the data source is established. (See next-next screen dump.)

From the data sources on the reportserver a Report Model can be generated.

(Double-click the data source and click on generate model.)

Based on this model, reports can be build in Report Builder - including SQL Server 2005 KPI's, including images.

Open Report Builder from the Report Manager. The established data model(s) will be shown as possible data sources.

Build the report …

The reports can be exported as a .rdl-file from Report Builder. Click Save to file … not Save as …

If you choose Save as … the report is saved on the server.

Import the .rdl-file in your Visual Studio project …

The layout of the report can be edited in Visual Studio. The Data and Preview panes return an error when clicked.

Save and deploy the report … Right-click the project name / Properties to view where the report is deployed to by default (e.g. http://localhost/reportserver/reports).

The described solution is definitely a work-around – it is not an optimal solution. There are two major downsides with the solution: the reports cannot be previewed in Visual Studio and the dataset cannot be changed in Visual Studio.

Integrating KPI in Reporting Services and drill down reports

I'm interested to implement KPIs from Analysis Services 2005 in Reporting Services 2005 in a graphical way as it is seen inside Analysis Services.

how can I archieve this?

and

what is the best way for drilldowns in reporting services?

Thanks for your help,

Roger

OK, my question is general - thats because I'm new to SSRS... So I did some reading and found out that the Matrix is a good way to display drill-downs.

I'm wondering: Do charts offer a possibility for drilldowns?

and I did not found a place to read anything about KPI and presenting them in Reporting Services in a easy and efficient way like you can do it in SSAS.

Anybody can give me a hint?

Thanks, Roger

|||

One way to do support AS2005 KPI's is to place an image control within your data region (e.g. table) and use separate images to represent each state the KPI could be in. Then use an expression to map each KPI image to the correct state based on your the value of an expression as follows:

=iif(Fields!Product_Gross_Profit_Margin_Value.Value = -1, "gauge_asc0",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < -0.5, "gauge_asc1",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0, "gauge_asc2",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0.5, "gauge_asc3",

"gauge_asc4"))))

Here, guage_ascn represents the various states the KPI could be in. While not 100% integrated to "absorb" AS2005 KPI images, should still let you get the job done.

Alternatively, might want to start from a Report Builder report since that tool offers native support for showing AS2005 KPI's. To see how to edit a Report Builder report using the report authoring tool in VS2005 see http://blogs.msdn.com/bimusings/archive/2005/09/23/473379.aspx

|||Thanks! Regards Tom|||

How to include SQL Server 2005 KPI’s in Reporting Services Reports

(The following is originally a description with screendumps, which unfortunately are not supported. Hopefully usefull anyway ...)

There seems to be an issue about including SQL Server 2005 KPI's in Reporting Services Reports (in Visual Studio) - especially the images associated with the generated parameters, e.g. "-1" leads to "red gauge / traffic light" and so on.

However it is possible to make reports in Report Builder including SQL Server 2005 KPI's.

It has been suggested to include a data source and a Report Server Model in the Visual Studio Project. This should make it possible to build a report including KPI’s. However there still seems to be a problem accessing the measures even though the metadata can be viewed. The Report Wizard does not have an option for including graphics for the indicators – only the numbers to generate the images (“1”, ”-1”, ”0” etc.). Therefore the indicators are not included in the dataset and cannot be included in the reports.

This has lead to the suggestion, that there is an issue with the Report Model functionality in Visual Studio.

There is a work around though ...

First you must associate your KPI’s to measure groups in the Visual Studio project …

Establish an Analysis Services data source on your Report Server …

Note: the Connection String must be typed when the data source is established. (See next-next screen dump.)

From the data sources on the reportserver a Report Model can be generated.

(Double-click the data source and click on generate model.)

Based on this model, reports can be build in Report Builder - including SQL Server 2005 KPI's, including images.

Open Report Builder from the Report Manager. The established data model(s) will be shown as possible data sources.

Build the report …

The reports can be exported as a .rdl-file from Report Builder. Click Save to file … not Save as …

If you choose Save as … the report is saved on the server.

Import the .rdl-file in your Visual Studio project …

The layout of the report can be edited in Visual Studio. The Data and Preview panes return an error when clicked.

Save and deploy the report … Right-click the project name / Properties to view where the report is deployed to by default (e.g. http://localhost/reportserver/reports).

The described solution is definitely a work-around – it is not an optimal solution. There are two major downsides with the solution: the reports cannot be previewed in Visual Studio and the dataset cannot be changed in Visual Studio.

Integrating Analysis Services 2005 with Reporting Services 2005

Hi,
I found an article
"Integrating Analysis Services with Reporting Services"
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp)
for SQL-Server 2000 saying:
"Reporting Services 2005 will also add support for server-side aggregates.
This feature will enable report authors to take advantage of aggregations as
they are defined in Analysis Services, in addition to using the aggregate
functions available in Reporting Services. Reporting Services will also
expose server-side formatting features such as the cell background
property."
How can these server-side aggregates be used in 2005-reports, that is how
can different levels of aggregation been taken from a cube an displayed in a
table without further calculations inside the report?
RalphUnfortunately, server-side aggregates didn't make it to RS 2005. RS
essentially flattens the cube query results into a two-dimensional dataset.
As a result, the (All) member aggregates are lost. That said, you can return
the All member values as columns by creating calculated members in the MDX
Query Designer.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
news:OI1icDT6FHA.2888@.tk2msftngp13.phx.gbl...
> Hi,
> I found an article
> "Integrating Analysis Services with Reporting Services"
> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp)
> for SQL-Server 2000 saying:
> "Reporting Services 2005 will also add support for server-side aggregates.
> This feature will enable report authors to take advantage of aggregations
> as they are defined in Analysis Services, in addition to using the
> aggregate functions available in Reporting Services. Reporting Services
> will also expose server-side formatting features such as the cell
> background property."
> How can these server-side aggregates be used in 2005-reports, that is how
> can different levels of aggregation been taken from a cube an displayed in
> a table without further calculations inside the report?
> Ralph
>|||Hi,
hmm, I tried the AddCalculatedmembers as proposed in the above article. If I
preview the query result in the query designer, everything works as expected
and i get all the desired levels. But if I try to display this dataset just
1:1 in a table inside a report, only the lowest level is shown. What am I
doing wrong?
Ralph
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> schrieb im Newsbeitrag
news:OpjMDTT6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Unfortunately, server-side aggregates didn't make it to RS 2005. RS
> essentially flattens the cube query results into a two-dimensional
> dataset. As a result, the (All) member aggregates are lost. That said, you
> can return the All member values as columns by creating calculated members
> in the MDX Query Designer.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Ralph Watermann" <Ralph.Watermann@.webtelligence.net> wrote in message
> news:OI1icDT6FHA.2888@.tk2msftngp13.phx.gbl...
>> Hi,
>> I found an article
>> "Integrating Analysis Services with Reporting Services"
>> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp)
>> for SQL-Server 2000 saying:
>> "Reporting Services 2005 will also add support for server-side
>> aggregates. This feature will enable report authors to take advantage of
>> aggregations as they are defined in Analysis Services, in addition to
>> using the aggregate functions available in Reporting Services. Reporting
>> Services will also expose server-side formatting features such as the
>> cell background property."
>> How can these server-side aggregates be used in 2005-reports, that is how
>> can different levels of aggregation been taken from a cube an displayed
>> in a table without further calculations inside the report?
>> Ralph
>>
>