Prologika Forums

OLAP Report using a calculating member as filter

mial14 — Fri, 10 Sep 2010 13:05:29 GMT

Hi Teo

I'm in the following situation : olap parameterized report - using an Analysis Services data provider - with 4 filters and parameters.

One of the filters - Time dimension and one of its hierarchies - includes 2 calculated members called: Primary and Secondary, which basically describe a time interval of a day based on some business rules, and it was appropriate - as you argumented in the Analysis Services book - to place them there.

An Excel pivot tabel retrieves data without any problems. But a SSRS 2008 matrix report fails when picking up one of the calculated members , as it does not support it, just like olap browseren in AS 2008 does not support filtering by any calculated member. So it's difficult to find a good explanations to the users ...

in section 16.2.6 regarding using the ole db provider for analysis services, but I'm not sure that using it will solve my problem. That's why following question :

Are you absolutely sure that using the ole db provider for analysis services will solve my issue ?

If not ,is it any other work around ?

If yes, besides the tip on page 584 of using / typing within the ole db provider for analysis services editor, do you have some other adivises of accomplishing my query. ( I tried it once without any succes and found it quite complicated when it comes to handling expressions with parameters.

Thank a lot for your answer.

Best regards,

Mihai

OLAPQueryLog - Unable to run Aggregation usage wizard

rambal — Thu, 02 Sep 2010 18:39:02 GMT

Hi Teo, I took your SSAS online class last month, from SAIF corporation

I have a wealth of data in this table OLAPQueryLog from the SSAS queries in production.

I tried to create a new project in BIDS with datasources pointing to TEST server, but the SSAS service pointing to the production OLAP query table.

However when I run the Aggregation usage wizard in BIDS , I get

"There are no queries in the log for selected measure group."

Question - how can I run Aggregation usage wizard against production OLAPquerylog data while in BIDS connected to a test/dev server?

I am nervous about connecting directly to the production SSAS database and running the OLAP design usgae wizard

Ramesh Bala

Analysis Services 2005 Filter web part - default member can be a mdx expression or not

mial14 — Thu, 02 Sep 2010 13:54:42 GMT

Hi Teo

I have designed a dashboad that reminds of the one you described in the book : a sharepoint dashboard, some parametrized reports ( on date and time) in my case with a cube as datasource, added an analysis services 2005 filter, connected it to the reports.

The issue concerns the default value for the filter .

I f I choose [Date Local].[Calendar Hierarchy].[All] it works well.

But the business requirement is to use the last day where there have been registered a measurement which in my case looks like the following mdx expression: Tail( Filter ([Date Local].[Date Key].[Date Key].Members , ( ( [Advisor Organisation].[Advisor organisation].[Contact Center].&[Denmark]&[Kundelinjen], [Measures].[Calls Offered] , [Date Local].[DK Holiday].&[False] , [Date Local].[Week Day].&[True] ) > 0 ) ), 1).Item(0).Item(0)

The expression is all right as I use it in my report as an mdx filter expression.

So the question is :

Does the filter supports an mdx expression ? If yes, is there another syntax or change in order to make it work ?

Thank you a lot for your answer.

Best regards,

Mihai

Suppressing Small values

Acius — Thu, 26 Aug 2010 06:14:40 GMT

I'm trying to suppress small values for a measure.

Using a scope and format string appears to work

Scope ([Measures].[Actual]);

format_string(This)=IIf( [Measures].[Actual]<5,"LT 5","#,#");

END SCOPE;

but the users can get at the real values from the client by hovering and waiting for the tool tip.

If I replace the value

Scope ([Measures].[Actual]);

This=IIf( [Measures].[Actual]<5,NULL,[Measures].[Actual]);

END SCOPE;

The measure is aggregating up the hierarchies so the values at higher levels are incorrect.

One of the hierarchies is parent-child so the level the measure hits the limit can vary.

Any ideas?

Thanks

Reporting Services Toolbox items settings

kf4dgk — Tue, 31 Aug 2010 20:44:01 GMT

In a Reporting Services BIDS project the Toolbox panel in Visual Studio has about a dozen tools under the "Report Items" group. Is it possible to set /change the default values of those tools? For example, anytime a textbox is dropped onto the report surface, I want all the padding to be 0, and I want the font to be Arial Unicode, 8 point.

Michael

optional dropdown parameter

ajelena — Mon, 05 Jul 2010 09:58:58 GMT

I have a parameter that provides a dropdown list in the report, and I would like to make it optional, that is, I want a report to run even if I don't select anything from the list, and to have a Null checkbox next to it, that regulates if the parameter is optional or not.

The parameter is declared in stored procedure and is = null, and in the query is WHERE (p.somename = @parameter or @parameter is Null), also in the parameter properties in the ssrs I checked "allow blank value" and "allow null value", for the available values it's set to "get values from the query" (second dataset) and default values are set to "no default values" (in ssrs 2008 there is no option for null for the default values like in ssrs 2005). Even tried with some expressions for the default value for example:

IsNothing(Parameters!User.Value) or Iif(Parameters!User.Value is Nothing,Nothing,Parameters!User.Value)

and still nothing, not to mention other stuff that i've tried, and now i'm out of ideas.

When I run the report I have to select the values from the dropdown list which I want it optional, to run the report whether or not anything is selected.

Thanks in advance

Report Server Custom Security Extesion and Analysis Server

mperciun — Wed, 25 Aug 2010 13:48:20 GMT

Hi,

we are currently building a BI solution that involves Microsoft Reporting Server and Analysys Server.

1. We have a SSO solution to access the Report Manager from a 3rd party portal (this works fine). Implemented a custom security extension for the Report Server which uses Forms based authentication. Customized this to work with our SSO system.

2. Every report deployed to the Report Server has an embedded datasource with the connection string dinamically built in an assembly (expression based connection string). Here we pass as CustomData the user name who sees the report.

The problem i have is that if i set in the BI studio for the datasource to use Windows Credentials and then run the report it works fine (report is previewed in the BI studio)

If i deploy the report to the report server and then go to Report Manager (at this step i used the custom security extension) and try to view it i get this:

An error has occurred during report processing. (rsProcessingAborted)
- Cannot create a connection to data source 'PurchasingIntelligence'. (rsErrorOpeningConnection)
  - Non-Windows user credentials were supplied for a non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integrated Security unless connecting via http or https.

The only workaround for this was to manually go in the Report Manager and modify the report's datasource with:

1. set Credentials stored securely in the report as the domain account i have configured to access the analysis service

2. check Use as Windows Credentials

How can i run a report with an embedded datasource in a report server with custom a custom security extension for forms authentication?

I run Reporting Windows Service and Analysis Windows service under the same account: Network Service

Prevent colomn merging in Excel

Simon — Sun, 29 Aug 2010 06:12:12 GMT

Hi,

When I create reports in BIDS and want to make subscriptions for it in Excel format, there is always problem with the columh merging, the user can not make summary on it and he need to unmerge and delete unneeded columns, in some cases it was resolved by removing the page header and images.

Is there is any other good way to see the reports in Excel format without problem with merging?

Thanks

Saving the report while prviewing it in BIDS 2008 R2

Simon — Wed, 25 Aug 2010 08:02:24 GMT

I realized that when I made some changes and whnat just to preview it, it saves the changes.

Is there is any way to prevent it?

Thanks

Column header while scroling or moving to next page

Simon — Tue, 24 Aug 2010 13:44:50 GMT

Hey,

I use BIDS 2008 R2, I have problem of keeping page header while scrolling or moving to next page. Do someone familiar with it and know hot to solve it in BIDS 2008 R2?

Thanks

Change reference of shared Data Source in RDCE

ber142 — Tue, 24 Aug 2010 13:58:15 GMT

Hi Teo,

Do you known if it's possible to the change the reference of a shared data source in a report via RDCE?

I've tried (in SQL2008R2 via Linq to XML), but after changing the definition in the IReportDefinitionCustomizationExtension.ProcessReportDefinition method, the data sets still uses the original Data Source as saved in the report (however the XML was changed by changing the DataSourceReference element).

Other changes via RDCE works fine, but changing Data Sources not. Also I would expect a DataSources token in the RdceCustomizableElementId, but I couldn't find it.

Regards, Hanno.

BIDS 2008 R2

Simon — Mon, 23 Aug 2010 14:50:57 GMT

Hi,

When I wnat to preview some report in BIDS R2, it builds all the reports in the current project.

Is there is any way to define the BIDS to preview and byild only the specific report and not the all reporst in the project?

Thanks

What is the Default Page Size in the Report Preview?

Ness — Wed, 11 Aug 2010 13:59:29 GMT

Hi Teo,

I have problem with the header of the table during report preview and table header when exported to excel.

I have several table elements in my report and what I want is to have the table header in each page. So what I did is I set the margins under report properties to .75in (left & right) and 1in (bottom & top) to make it the same with the default margins in excel. I manually insert a table header in each page base on the margins that I set in excel.

But the problem is when I preview the report some of the table header appears on the same page. This one works fine when exported to excel (the table header appears in each page in excel as what I wanted). What I want is to make it the same during report preview that the table header appears in each page.

You know what is the default page size use by SSRS 2008 during preview?

I know that this one is easier to do using page break but my client don't like that it makes a separate sheet for each page when exported it to excel.

Your idea will be appreciated.

Thanks much,

Ness

RDL DataSet Field Type

bo_dong — Tue, 17 Aug 2010 17:08:11 GMT

I am extracting dataset field type out of an existing RDL using RdlObjectModel. But Field object doesn't include type. How do I get to <rd:TypeName>System.Double</rd:TypeName> using Report object?

<DataField>Total</DataField>

<rd:TypeName>System.Double</rd:TypeName>

</Field>

Thanks,

Sums Incorrect on Page 169?

cganahl — Thu, 12 Aug 2010 14:10:31 GMT

Chapter 5 is more fun than Chapter 4 - only because I tried to build some of the layout in 4, only to find I needed to read more of the book to figure out what was going on.

The Product Sales Stepped layout is coming along fine, but I noticed that the figures from Extended Amount on don't seem to be correct. However, what I see on my screen is that same as what's in the book on page 169. I guess the fields are incorrect within the database.

It's no matter - the lesson is about the format.

Chris Ganahl

Filtering Accounting dimension

Olga Kornilova — Tue, 10 Aug 2010 21:38:05 GMT

Teo,

I need your advice on generating “Executive Summary” report which is based on Accounting dimension.

For example I have Profit and Loss (P&L) report which uses Accounting dimension (with rollups) on rows and Actual and Budget measures on columns. It is typical GL report.

Now I need to create a report which is taking only certain rollup groups from Accounting dimension and groups them additionally for Executive summary (with their own rollups).

During out training session you mentioned that you had a similar real project, but I cannot recall how it was implemented. I tried different ways, but nothing worked.

I created a new dimension with Executive groups and linked it to Accounting dimension by special key. I added this dimension to the fact table using Referenced relationship type, but I measure amounts got empty. Can you suggest another approach or point me to the right dirrection?

The last option I would consider to do some calculation and filtering on the report itself instead of OLAP cube.

Thanks,

Olga

Upcoming SSAS Class

bmclennan — Thu, 12 Aug 2010 23:54:53 GMT

I sent an email but haven't heard back...is the SSAS 2008 class still on in two weeks?

Thanks!

Ben

SCD 2 and Attribute relations.

vovasurkov — Sun, 08 Aug 2010 18:28:52 GMT

Hi Teo.
I have some problems with SCD 2 and Attribute relations.

We have
ScdProductId   AlternateProductId   SubCategory   Category
1           50       bread        food
2           100       milk       food
And we have attribute relationship:
ScdProductId -> AlternateProductId -> SubCategory -> Category

AlternateProductId many to one SubCategory/

And we have one new row ScdProductId = 3

ScdProductId   AlternateProductId   SubCategory   Category
1           50       bread        food
2           100       milk       food
3           100       drink       food

AlternateProductId is the same = 100 but SubCategory has changed.

After changing we get Error
“A duplicate attribute key has been found when processing: Column: 'AlternateProductId , Value: 100”.

AlternateProductId have two different parents milk and drink it is wrong
because of attribute relationship AlternateProductId -> SubCategory. (many to one ).

I see than in Product Dimension in the Adventure Works we haven’t AlternateProductId at all.
And we don’t get such Errors. But we also haven’t possibility to group sales by AlternateProductId(this is requirement of our project)

What is the best way of modeling (Especially attribute relationship and hierarchy)
in this case ?.

Multiply Rows

bo_dong — Thu, 05 Aug 2010 23:49:17 GMT

We have a table look like this:

Date Rate

2010/01 1.10%

2010/02 1.05%

2010/03 0.98%

We need to multiply Rate column from all rows above and display the result in the summary row like this:

Quarter1 1.13%

But there is no Product function like in Excel. There is a =Aggregate(Fields!Order_Count.Value), but we are not using Analysis Services at all. How can this be done in a RDL table object?

Thanks,

Adding Tablix to a Header

cganahl — Tue, 03 Aug 2010 16:18:01 GMT

I added the layout on page 138, and the Data Sets, but I couldn't get it to work in Preview. Perhaps the new version of AdventureWorks has some differences. So I decided to build the layout from scratch.

It seems you have a tablix region in the header with a single row and column, but when I try to add a table there, I get the not allowed icon. The body works fine, but I need to designate the dataset for the header.

Thank you,

Chris Ganahl

Recursive Hierarchy in ADOMD Result Set

Grace2k07 — Tue, 27 Jul 2010 21:59:11 GMT

Hi Teo, we have a situation right now where we have a set of RDL reports that we would like to convert into RDLC format so we can switch report execution from server mode to local mode. Our RDL reports were built on top of OLAP cubes and we plan to use ADO MD to provide data set in our asp.net web application for local mode of RDLC execution. We don't have any problems with regards to conversion until we reached the reports that contain recursive hierarchical grouping. The original RDL reports use built-in analysis data provider to collapse the parent-child hierarchy into one column while the ADO MD uses OLE DB to provide a set of flattening result sets. We cannot show hierarchical data or do any kinds of collapsing/expanding on the reports right now. At this point, we cannot afford to modify existing RDL to use flattened data sets returned from ADO MD. So I'm wondering if there is a way for ADO MD in my web application to return a collapsed parent-child hierarchy so we can run the report in local mode successfully.

Thank you.

How to find out which report is currently executing on the server?

bo_dong — Mon, 26 Jul 2010 16:18:04 GMT

We have some reports taking long time to finish and consuming all the CPU power during its execution. ReportServer.dbo.ExecutionLog2 gets updated only after the execution has finished or timed out, not during the execution, which can take up to 30 minutes. ReportServerService_xxx.log records "INFO: RenderForNewSession" soon after the request has been received, but it doesn't have enough details such as who launched the request and what parameters were used.

Has anyone find a solution to this problem?

Thanks,

Tablix Category/Sub Category in a single column

AdrianHM — Tue, 27 Jul 2010 10:31:35 GMT

Hi,

In the blog post "Tablix – The Crown Jewel" Teo mentions that SSRS 2008 with a Tablix control allows stepped columns with cross tab reports with Category and Sub Category sharing the same column. This looks great and is just what I'm after, except I can't find anything anywhere giving a how to. Can anyone point me at a link please?

Cheers

Adrian

How to test SSAS security role in "canned" SSRS report?

Olga Kornilova — Tue, 20 Jul 2010 17:06:09 GMT

Hi, Teo

I am working on implementing users security in SSAS and used "fact-less" approach (as you suggested during the training in Module 8). I created users dimension and fact-filter tables. I had to restrict access to the users on several dimensions. Everything works great. I could be able to test security restrictions in SSAS under different user accounts. Ad-hoc reports shows only the data the users have access to.

Now I am trying to test several "canned" reports designed in SSRS. And I am having a problem to run them under different users and check if the security filters work via Reports.

The problem I have with my local PC accounts. As you understand I cannot test the credentials of the real users. I don't have their passwords. So I created a few local accounts and assigned them to SSAS role.These accounts I used for testing in SSAS. I was trying to run IE browser as local accounts, but the reports shows all the data without filtering based on user's role and "Fact Security Filter" table.

So my questions are:

1. Do I need to do something special in the "canned" reports and modify MDX query to implement SSAS security filtering? Or it should work automatically based on authenticated user?

2. How can I run SSRS report under local user account (which is not in AD) to test security restrictions?

Thanks,

Olga

Issues implementing many-to-many currency conversion

Sandy — Fri, 23 Jul 2010 10:03:00 GMT

Hello,

I am relatively new to Analysis Services and am having some issues implementing many-to-many currency conversion in SSAS 2005.

I have created a "proof of concept" solution (with core components based upon the solution in Teo's book "Applied Microsoft Analysis Services 2005") which seems to confirm that the proposed architecture delivers the functionality we require.

However I have noticed that when the same components are integrated into our development solution (where there is a larger number of dimensions, currencies and rates, etc) there are certain scenarios where building queries through the cube browser causes the Analysis Services process to consume a large amount of memory and hang the machine until such a point that the process is restarted. The same queries seem to function as expected whenever the currency conversion MDX is commented out from the cube.

I have a couple of questions that I was looking for some advice on:

1) The issues I have encountered is making me question whether the goal I am trying to achieve is actually functionally correct.

To support currency conversion I have created and populated three tables:
   DimCalendar - holding date records on a day granuraity
   DimCurrency - holding currencies supported in our application
   FactCurrencyRate - holding conversion rates per currency per calendar date.

Currently we have multiple dimensions which have financial values associated with them (stored in the relevant fact tables).
Each fact table contains a CurrencyKey and numerous date keys, one of which is the 'date created'.

I am attempting to convert the financial values for each fact to a currency specified by the user based upon the rate corresponding to the 'date created'.
Does this sound logical/correct?

I looked at the Adventure Works solution and it has a similar architecture where each Sales fact table has an OrderDateKey.

2) The issue seems to be caused whenever I try and report on any date from the fact table other than the 'date created'.

In looking at a possible cause I stripped out a large number of the unused entries in the FactCurrencyRate table and this did seem to cause the issue to be rectified. However I believe that this may just be indicating that the MDX is incorrect or I haven't configured the Dimension Usage correctly.
Have you any advice on how I can best set about tracking down the possible cause?

Many thanks,

Sandy