.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

What if scenario SSAS, SSRS

Posted By:      Posted Date: October 10, 2010    Points: 0   Category :Sql Server
 

Hello,

Can i make a interactive report with using ssas and ssrs? can i do scenario analysis (what-if) with this report? (like excel but i want to use ssrs).

Thanks,




View Complete Post


More Related Resource Links

What if scenario SSAS, SSRS

  

Hello,

Can i make a interactive report with using ssas and ssrs? can i do scenario analysis (what-if) with this report? (like excel but i want to use ssrs).

Thanks,


Shared dataset in SSRS 2008 R2 running on SSAS 2005

  
I am trying to create a report in SSRS 2008 R2 using SSAS 2005 as a datasource. My dataset has a date parameter. When I create an embedded dataset in my report everything works fine, but when I create a shared dataset with the exact same mdx query, I get the following error: An error occurred during local report processing. The definition of the report 'xxxx' is invalid. The Value expression for the report parameter 'DateCalendarHierarchy' contains an error: [BC30034] Bracketed identifier is missing closing ']'. Does anybody have any ideas? Is this a bug? Thanks, Louis

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

  
I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

U?sing Forms Authentication and connecting between SSRS ans SSAS

  
In our current production environment, we have SSRS setup to use Forms Authentication to verify the users. For our data sources, we are using 2 different data models - 1 is used to point at the "raw" sql in the database - and the second model is to point at a SSAS cube. currently, all users can see all data. we now have a requirement to limit the data the a user can see from the Fact table dependant on the Portion that they are assigned to (and a user might be assigned to more than one Portion. we have set this new requirement in the "raw" SQL model using a security filter - and all working well we have set the filtering up in the cube  - using a stored procedure in the Role "dimension data" tab which receives the user name and retruns the MDX required for the filtering - and it works if we connect to the cube via SQL management studio. However, if we connect from Reporting Services, we can not get the UserName "transferred over" and therefore the filtering doesnt work. n.b. we are using Shared data sources for our reports does anyone out there know of a way of making this work - or do we go for Kerberos ??    

SSRS 2005 with SSAS Parameter is not declared

  
Hello, I'm builind a mdx query to my report in ssrs 2005 with ssas 2005 all on SP3. I'm having the hardest time to add a custom parameter to the query, I add it to the dataser parameters tab ("..." button). Then when I try to use it in my query I get an error: The query uses a parameter, which is not declared. I've seen some examples on the goo.. internet but none seems to work. My query runs fine like this STRTOMEMBER("[dimension].[entity].&[16]") but it fails if I try to do this.. STRTOMEMBER("[dimension].[entity].&[" + @entityid + "]") Am I trying to do impossible? :) Any idea on how to get such filter to work? My filters are a bit complex that's why I can't simply use the "Parameter" checkbox in the query designer mode. Thanks!

SSAS - calculation of ssrs report should be calculated before calculation in MDX script

  
Hi All I have: - A calculated member defined in an mdx query of an SSRS report (using the aggregate function). - A calculated member defined in the MDX Script of the cube The calculated member of the cube calculates a ratio. What we should achieve is the ratio of the aggregated members (defined in the report) and not the aggregation of the ratios. I thought that it would be automatically enforced by: http://msdn.microsoft.com/en-us/library/ms145539.aspx :"The exception to this precedence is the Aggregate function. Calculated members with the Aggregate function have a lower solve order than any intersecting calculated measure." I tried to play with the solve_order of the two calculated members (defining explicitly a lower solve_order for the aggregation defined in the report), but the calculation of the cubes is always executed first => I end up with the aggregation of the ratios. Are calculations defined in the MDX script always executed first? This doesn't seem logical to me.. Thanks a lot for your help. Regards Stefan    

How to enable caching on SSRS that using SSAS cube to bind data

  

Hi All 

 How to set Cach refersh option for a SSRS report that using SSAS cube to bind data 

when i tried to store credential in data source getting error as show below 

"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."

 

 

Regards

Sooraj


Default date selection in SSRS with SSAS cube as a source

  

Hi,

 

I am fairly new to SSAS and I am hoping that someone can help me with an issue I am experiencing.  I am create a SSRS report that goes against a SSAS cube.  My cube has a Date dimension with a Fiscal Year - Fiscal Quarter - Fiscal Month hierarchy.  In my report I have created filters for a From Date and a To Date, which are populated with the hierarchy of the Date dimension.

 

I need to default the filters as follows:

 

To Date = current quarter (that is the last quarter with fact data)

From Date = the To Date quarter from the previous year (e.g. if To Date quarter = Q1 2008, then From Date quarter needs to be Q1 2007)

 

I found some SSAS code that allows me to get the index of the last quarter with data, but I am stuck at this point.  I am not sure how I can translate this into a default selection for the To Date SSRS report parameter.  I also realize that there may be a better approach.

 

CREATE MEMBER CURRENTCUBE

SSAS Calculated Member not working as expected in a SSRS report

  

Dear forum guys, I have a simple cube to analyze my customer's sales. I have a Time Dimension, a Product Dimension (with a few hierarchys) and a couple measures (sales and purchase amounts).

 

I also have defined a few Calculated Members, in particular the average sales and purchase amount over the last 12 months from the selected month.

 

I need to show the results in a SSRS Report: the user must select a month and the report needs to show the sales and purchase amount of the selected month and the average of the previous 12 months (both for sales and purchases).

 

Everything works perfectly if I test the cube using the Cube Browser or Excel 2007 as a client.

 

When I define the SSRS Report I cannot get to work with the calculated members (the average): I get strange numbers which are much higher than the real numbers.

 

I've tried both Matrix and Table reports.

 

Can anyone suggest me what I'm doing wrong: I think this should be a very common mistake.

 

Best regards

 

Alessandro


SSRS clients and SSAS clients ?

  

Hello.

I am new to SSRS and SSAS

My requirement is to have  few static reports generated on a montly basis to be used by several users.  Which clients should i consider giving to the users to see these reports. Having a sharepoint for just few reports seem to be a overkill ? i dont plan to do any frontend development to hold these reports ?  What is the simplest way i can allow the users to view these reports whenver they want ? 

Will be grateful if you can respond asap.


Licensing for SSRS and SSAS on different servers

  

I've not been able to find a categorical answer to this question.  Apologies if it has been answered before.

We have a scenario where SSRS standard edition is installed on one server with a processor licence and we have SSAS Enterprise Edition installed on a different server using a User CAL license model.  Reports access the SSAS database using a single specified user.  So all reports that are run on the SSRS server can be run by specified people but only the 'report user' accesses the SSAS database on behalf of those reports. 

So the question is, do all the people who run the SSRS Reports need to have a CAL for the SSAS server - as well as them being covered by the processor licence for the SSRS server.  Or is it just the SSRS server that needs a CAL (either as a device CAL or as the 'reporting user' User CAL)?

Thanks in advance.


SSAS Licensing to allow access from SSRS on another server

  
I've not been able to find a categorical answer to this question. Apologies if it has been answered before. We have a scenario where SSRS standard edition is installed on one server with a processor licence and we have SSAS Enterprise Edition installed on a different server using a User CAL license model. Reports access the SSAS database using a single specified user. So all reports that are run on the SSRS server can be run by specified people but only the 'report user' accesses the SSAS database on behalf of those reports. So the question is, do all the people who run the SSRS Reports need to have a CAL for the SSAS server - as well as them being covered by the processor licence for the SSRS server. Or is it just the SSRS server that needs a CAL (either as a device CAL or as the 'reporting user' User CAL)? Thanks in advance.

howto: display only hierarchy-members (levels) which are selected in the parameter (SSAS and SSRS)

  

Hi all,

I'm a SSRS/MDX beginner, so this might be a basic question. Sorry for that.

I have a hierarchy, which I use as a parameter. The Hierarchy has 3 levels (level1, level2, level3).

Sample Hierarchy
-----------
CH
 CH1
  CH11
  CH12
  CH13
 CH2
  CH21
  CH22
IT
 IT1
  IT11
 IT2
  IT21
  IT22

Now I want to achieve the following:

  • If a user selects CH1 --> the report should show only the CH1 (which is the sum of CH11 + CH12 + CH13)
  • If a user selects CH11 and CH12 and CH13 --> the report should show only these leafs (no aggregations)
  • If a user selects CH1 and CH11 and CH12 and CH13 --> the report should show the leafs and CH1 (seperate)

I guess, this is not that hard, but somehow I don't get it.

Here my sample mdx-query (the hierarchy I'm writing about is "Lieferantenstruktur" and the parameter "@Lieferant" (BOLD)):
(right now, only the leaf-level [Lieferant] is returend by the query)

SELECT NON EMPTY { [Measures].[Einheiten] } 
ON COLUMNS

, NON EMPTY { ([Lieferantenstruktur].[Lieferantenstruktur].[Lieferant].ALLMEMBERS 
* [Artikel].[Artikel Kategorie].[Artikel Kategorie].ALLMEMBERS 
* [Artikel].[Artikel Gruppe Code Name].[Artikel Gruppe Code Name].ALLMEMBERS 
* {[Kundengruppen].[Kundengruppen].[Verkaufskanal].ALLMEMBERS 

Are there rounding differences in Excel vs SSRS vs SSAS

  

We are starting to implement a project where we create some static reports and allow power users to browse an SSAS cube to interrogate how the canned report came up with certain amounts.  Both data sources orignate the base data from a central data mart.  Also, part of the worklflow is to allow for the ability to export to Excel or created pivot tables in Excel with a cube as a data source.

The main issue that the technical side needs to figure out is if SSRS, SSAS and Excel utilize the same functions to perform division/rounding.  The decision will affect the development path that must be taken.  The fear is that if this technology stack produces different results for the same methodology then we need to develop a solution that will not allow end users to be exposed to multiple versions of "correct data". 

An example is when someone codes a calculated measure in SSAS where one uses division and then aggregates calculated measure.  Will this amount be exactly the same as if the same process were performed with the functionality in Excel or SSRS. 

The versions of SSRS and SSAS will be the SQL 2008 R2 platform and the Excel version is 2007 or higher.

So the big question is do all of these technologies divide, round and aggregate the exact same way using the sane .net functions or protocol

YTD with custom query parameter (SSRS with SSAS)

  

Hi all,

I have a report which has SSAS as DataSource.

I also created a custom query parameter for date.
I am using the parameter type "date/time" (so I get the nice calendar for the parameter). Of course I have to convert this to a valid member of the SSAS hierarchy:

="[Datum].[Jahr - Halbjahr - Quartal - Monat - Datum].[Datum].[" + Format(CDate(Parameters!Datum.Value), "MMM d, yyyy") + "]"

So far, so good.

But I don't get the YTD function to work.
It should work like this.

WITH MEMBER [Measures].[Current YTD] AS

SUM(YTD([Datum].[Jahr - Halbjahr - Quartal - Monat - Datum].CurrentMember),[Measures].[Einheiten]) MEMBER [Measures].[Last YTD] AS SUM(YTD(ParallelPeriod([Datum].[Jahr - Halbjahr - Quartal - Monat - Datum].[Jahr],1,[Datum].[Jahr - Halbjahr - Quartal - Monat - Datum].CurrentMember)),[Measures].[Einheiten])

I tried to use STRTOMEMBER(@Datum) and STRTOSET(@Datum). But I guess reference to the hierarchy is missing.

Any tipps, how to get that working.

Thx a lot.

Reto E.


range inclusive ssrs report builder using SSAS as source unsolved

  

Hello,

I am trying to use MDX query designer in SSRS report builder. I choose a date dimension and i need to include a range for it . So i selected range inclusive as the filter.

When i do that and give default dates it works fine. When i select parameters it and hit ok. it immediately throws a error.

"Query (1, 216) The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated."

So i went ahead changed it to scripting mode and removed the "constrainted" flag and ran it . but then a different error comes up

SELECT ( STRTOMEMBER(@FromDate) : STRTOMEMBER(@ToDate) ) ON COLUMNS

then i get this error.

"Query (1, 216) The STRTOMEMBER function expects a member expression for the 1 argument. A tuple set expression was used."

what do i need to do to make this compile correctly. Please suggest. I am working on this for 2 weeks. 


How to set SearchRoot Path in Active Directory in this scenario

  

How to set SearchRoot Path in Active Directory in this scenario:

Functionality: We have scenario that 1<sup>st</sup> hit one LDAP server with some 'fixed user name' &' fixed password ' and filter data with specific User name(which given by user) getting 'User dn'.

After that we hit Next LDAP server based on the 'User dn' getting from 1<sup>st</sup> server.

(So, in my case 1<sup>st</sup> LDAP server works like as Load balancing server but functionalitywise it is different)

|-------------------------------------------------------------------------------|
|                                         

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend