SSRS && SSAS 2005 Data Parameter

Aug 20, 2007

Hi all,

I'm sure what I am trying to do is very simple - but I just can't seem to figure it out. I have a report based on a SSAS cube (SQL 2005). The report shows sales based on the dates the user selects from the parameter field (the date parameter field comes from a Y-Q-M-D hierarchy). This all works fine.

What I would like to happen is for the members within the last 3 months to be automatically selected so that the report automatically executes for the last 3 months.

Can anyone help or offer any advice. If possible I would like to achieve this using the GUI features so that power users can use the "plug and pray interface".

Many thanks

Matt

View 1 Replies

Reporting Services :: Top N As Parameter In SSRS Report Populated By SSAS Cube?

May 21, 2015

I have an SSRS report that gets its data from an SSAS cube, and it essentially displays a list of students and absences. Everything works fine.

But the users only want to see the top N students with the highest absences. For example, today they want to see the top 100, but during summer vacation, they only want to see the top 5.

Is there a way to add this parameter to the SSRS report? So if I select "5" from the dropdown parameter, it will show the top 5 absences.

The query that I use to populate the report is something simple:

SELECT NON EMPTY { [Measures].[Absences] } ON COLUMNS, NON EMPTY { ([Dim Date].[Date Key].[Date Key].ALLMEMBERS * [Dim Student].[Lte Cell Key].[Lte Cell Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimDateDateKey, CONSTRAINED) ) ON COLUMNS FROM [Daily Cube]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

View 2 Replies View Related

Does SQL 2005 Come With SSIS, SSRS, SSAS, BIDS, VS 2005?

Dec 4, 2007

Hi everybody.

I'm looking to upgrade to SQL Server 2005 (Standard Edition). I'm interested in using SSIS, SSRS and SSAS. I hear that BIDS runs inside of Visual Studio 2005, and comes with the SQL 2005 software. I currently have VS 2003. My question is: Do I need to purchase anything besides SQL Server 2005 Standard, or do I need to buy anything separately, like VS 2005? (if so, which version would you recommend?)

Thank you very much!

- Trevor

View 7 Replies View Related

Best SSRS Data Source: SQL Or SSAS?

Jul 6, 2007

I have a vision of a beautifully interlocked Business Intelligence system in which I load dimensional fact tables through SSIS, build aggregations in SSAS cubes, and quickly and easily generate SSRS reports using the cubes as data marts.

I am now a month into trying to implement this vision and Reality is stomping all over it. Primarily, I am running into issues with the "cube as a data mart" idea. Each cube is taking up an additional gig or two of space, report queries against them seem to take longer than a straight SQL query against my fact tables, and I am running into serious problems trying to pass time-related calculations such as Year-To-Date to my reports.

Has anyone else tried setting up a BI system that uses cubes as the primary drivers for their reports? Have you seen any benefits to doing it that way? Or should I generate most of my reports through the SQL tables?

I'm nearing the point-of-no-return in choosing my final methodology, and any feedback would be greatly appreciated.

Thanks!

-- Steve

View 2 Replies View Related

Best Practices SQL Server 2005, SSRS, SSAS, SSIS Setup

Oct 8, 2007

I would like to know best practices for setting up my environment. To date, I've had everything running on a single server. That would include the database engine, SSIS, SSAS and SSRS. The box configuration is dual hyperthreaded 3.6GHz Xenon with 4GB of RAM on Windows Server 2003. I just received a much larger server and want to configure it to maximize our environment. The new box contains four 2.6GHz Quad Core processors with 16GB of RAM. I would like to know if I should split the ETL and database engine from SSAS and SSRS, or should this box have enough horse power to house it all and use my other box as a dev environment. Also, we are planning to purchase Performance Point 2007 primarily for PAS and Scorecard Manager so please take that into consideration as well. Any comments are greatly appreciated.

Thanks.

View 1 Replies View Related

SSRS 2005 V SSAS 2000 Mutli-Select Parameters

Nov 2, 2007

Has anyone managed to crack getting multi-select parameters to work from an SSRS 2005 report which is querying an SSAS 2000 cube.

SSRS 2005 does support Multiselect, however SSRS 2000 did not. Given that i am querying an SSAS 2000 cube, i get the impression that i am also limited to SSRS 2000 functionality regarding to multi-select parameters.

Is there anyone out there that ever managed to get a wor around, say by using filters on a table or anything really that coud simulate the same behaviour....

View 3 Replies View Related

List Of Parameters Used Together With Rs: Parameter In SSRS 2000 And SSRS 2005

Apr 24, 2007

Hi,

Does anyone know of a link or list that has all the parameters for the "rs:" section of the URL access parameter, except for the ones in the Microsoft books?

Thanks,

View 1 Replies View Related

Default Parameter Value Is No Longer Part Of Cascading Parameter In SSRS 2005?

Jan 30, 2007

Hi,

I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.

I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.

Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)

It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.

Is this a SSRS 2005 bug? is there any other work arounds or suggestions?

Thanks.

Kong

View 6 Replies View Related

What Version Of SQL Server To Choose In Order To Develop SSAS, SSIS And SSRS 2005 Projects On Xpsp2?

Oct 27, 2007

Good day community. I have a question about SQL Server 2005 Anywhere edition which i currently installed on my desktop. Does it fit my needs which stated on the subject? If no, what should i select for my projects?

View 1 Replies View Related

Reporting Services :: Refresh SSRS Report As Soon As Its SSAS Cube Data Source Is Processed?

Oct 12, 2015

how to trigger report's refresh following its underlying SSAS cubes process?

I want to keep reports data updated at all times, especially when this happens while user is already browsing the report..

I don't want to set an auto refresh for the report every 5 minutes as my cube is processed only one time during the day...

View 8 Replies View Related

SSRS 2005 Report Parameter Multi Value

Apr 1, 2008

I know this is the code to add a single valued report parameter to a report via asp.net

Dim paramList As New Generic.List(Of ReportParameter)

paramList.Add(New ReportParameter("MyParameter", "MyValue", False))

Me.ReportViewer1.ServerReport.SetParameters(paramList)

But how do I go about adding a multi valued parameter?

View 8 Replies View Related

How To Pass Unnamed Parameter In SSRS 2005

Dec 4, 2007

hi all
m newbie for SSRS 2005 . when i ran the folowing query it gives an error like "An error occurred while executing the query.Parser: The syntax for '.' is incorrect".

WITH
SET [FilteredAccountSet] AS 'order(Parameter!Account.Value,[Measures].[_MeasureBillingCode],ASC)'

SELECT {
[FilteredAccountSet]
} ON COLUMNS

FROM [Profitability]

may i know the reson?????

View 1 Replies View Related

SSRS 2005 Multivalue Parameter Not Working On The Asp Page.

Oct 30, 2007

Hi,
i am working on SSRS 2005. i have one problem while accessing report throgh ASP.net pages.

i have used multivalue parameters in the report. on the report server it works fine but on the .asp page when that report is accessed, drop down is not working,

can anybody guide me regarding this , what to do?

thanks and Regards,
Vishruti.

View 1 Replies View Related

Data Validation For Datetime Parameter In SSRS

Aug 29, 2007

Hi,

I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:

Public Function Validate( ByVal startdate As String) As Boolean
If IsDate(startdate) = True Then
Return True
Else
Return False
End If
End Function

And my report has a textbox which has the expression property set to;

=Code.Validate(Parameters!startdate.Value)
the textbox on the report has to display if the entered date is valid or not.

But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.

Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.

Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

View 10 Replies View Related

Multi Value For Single Parameter (reporting Services SSRS 2005)

May 1, 2008

Hi,

I'm using SSRS 2005 which connects to SQL Server 2005. I need to pass Multi valus for single parameter...

E.g. Command type : Text
SELECT * FROM COUNTRY where CountryId IN ( @CountryID)

If i tried to preview using single parameter, it shows the record without any problem. However when i tried to put multi values, it gives error message?? I read somewhere which says, need to set to multivalue. But, i could not find the place to set the multi value option...

How to solve it?? Your suggestion/recommendations greatly appreciated.

Thanks
rama

View 5 Replies View Related

Problem With Forecasting With Excel 2007 DM Add-in Using Drill-through Data From SSAS 2005!

Sep 18, 2007

I��m having a problem with Excel 2007 DM and SQL 2005 and I hope someone out there has a solution.

Consider the following environment:

Windows XP SP2 or Windows Vista, Excel 2007, Data Mining Add-in, SSAS 2005 (with session mining models enabled, an AdventureWorksDW cube deployed and drill-through actions available).

Now take the following steps:

1. In Excel 2007 set up a connection to SSAS

2. Connect to the cube and create a new pivot table report (drag and drop whatever you like)

3. Right-click on one of the cell values in the data region and either select a drill-through action, or, select Show Details in the context menu

4. Ensure that you have at least 10 detailed records that are generated on a new worksheet page; you should have a time-based column in your detailed records

5. Select the table of detailed data, then select the Analyze tab (within the Table Tools grouping) which appears in the topmost menu above the ribbon

6. Click the Forecast button in the ribbon and choose both the field which you want to predict as well as the time-based column (from step 4) as well as the number of time periods to forecast

7. Finally click OK.

1. Having followed these steps on both WinXP SP2 and Vista, I keep coming across the exception: HResult:0x800A03EC. Any ideas as to why this exception pops up? If I was using a normal table of data (which was not generated from a Show Details or drill-through action), then the Forecast button works fine.

I googled it and thought the localization settings for SSAS 2005 and Excel 2007 needed to be the same (initially they weren��t). I��ve tried removing the auto-filters which appear atop each column in the detailed data table prior to clicking the Forecast button, and, I��ve also tested for a series of data across a number of time periods with the same result.

Also, a colleague of mine discovered that the column headers that appear by default from a drill-through start with "$[", and, in removing them the Forecast function appears to work.

I would have thought there would be a seamless transition in Excel 2007 between data retrieved from a cube and the DM Add-in featueres (or at the very least, a more meaningful exception message than the one presented).

Is there something I��ve missed, or, is there a KB article I haven��t come across yet? As I know for a fact that the problem is reproducible, is there a fix to this problem on its way to us? Is there a useful workaround that doesn't require manual intervention?

Thanks,
A.

View 3 Replies View Related

RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It

Feb 19, 2007

Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.

I have to display the result of a parameterized query and I created a SP that based in the parameter does something:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''

AS
BEGIN

declare @slqSelectQuery nvarchar(MAX);

SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end

Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.

Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried

the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!

What is wrong? What I forgot??

Thankx for any help!
Marina B.

View 3 Replies View Related

SSRS With SSAS

Aug 24, 2007

Hi,

I'm using SSAS 2005 cube as Data Source for my SSRS 2005 report. The report has parameters as well.

The requirement is that I want to pass parameters to the report using my own UI.

My questions are:
1. "How to pass parameters to my report using my UI?" I'm developing the UI using VS2003 (the client's requirement).

2. Is there any ReportViewer control available in Framework 1.x?

3. I'm also using a drilldown sub-report with the same report. I'm passing parameter values to the sub-report selecting the fields of my data. But when the sub-report is opened, it gives an error saying "The parameter 'X' is not assigned a value".

Can anybody help on any or all of these?

---
Thanks in Advance,
Manjunath HK

View 1 Replies View Related

SSRS && SSAS On Same Box

Aug 22, 2007

We're setting up a test and development envorinment and would like some advice on the best deployment of the SQL 2005 components.

Some people seem to advise to have

Machine1 SSIS & Database engine
Machine2 SSAS
Machine3 SSRS

Others say it's OK to put SSRS & SSAS on same box.

Others say it's fine to run all on one box.

Anyone have any rule of thumb advice or links to such ?

Cheers,

Will

View 7 Replies View Related

SSRS -- Data Driven Subscription And Pivoting For Dynamic Parameter Mapping

Feb 12, 2007

Hi,

For the Data Driven Subscription in SSRS we are using the following stored procedure

In Step 3 - Create a data-driven subscription

create procedure spRSGetReportSettings

(

@ReportID as integer

) as

begin

set nocount on

declare @t as table(y int not null primary key)

declare

@cols as nvarchar(max),

@y as int,

@sql as nvarchar(max)

set @cols=stuff(

(select N',' + quotename(y) as [text()]

from (select ParameterName as y from Reportsettings where reportid=1) as Y

order by y

For XML Path('')),1,1,N'');

set @sql=N'select * from

(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D

pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'

exec sp_executesql @sql

end

Basically the idea is to maintain a single report parameter setting table for multiple reports.

Structure of the table is as given below

ReportID, ParameterName, ParameterValue.

Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)

But, in SSRS it is giving any results.

In Step 4 - Create a data-driven subscription,

Get the value from the database drop down, I am not getting any database columns.

Please help.

Kumar

View 3 Replies View Related

Analysis :: Ssas And Ssrs On Same Server?

Sep 2, 2015

I've see twice this year two different companies either already or planning on having ssas and ssrs running on the same server.

View 7 Replies View Related

Dev Edition Includes SSRS And SSAS?

Mar 14, 2008

Does the SQL Server 2005 Developer Edition include both of these:

Reporting Services?
Analysis Services?

( Also -- Anyone know where I can see a list of all the differences between Dev Edition and other SQL Server editions?)

View 1 Replies View Related

Aggregation In SSAS Not Working In SSRS

Jul 5, 2007

I have a measure in a SSAS cube that uses AggregateFunction "Sum". Dimension usage seems to be right so that it sums correctly.

But when I try to use same measure in SSRS report it comes out all wrong.

TO be precise the measure is called "CalendarDays" and it's the number of days in a month (28,29,30 or 31). Say I have 5 products and 4 years, If I look at this in the cube browser then the CalendarDays column aggregates correctly to 365 or 366 for each year even with the five products and 12 months in the rows.

However if I do a SSRS report by product and month with subtotals of years, then SUM(CalendarDays) adds up all the CalendarDays for each month and each product (ie 5 x 365 or 366). If I leave out "SUM" and just enter =Fields!CalendarDays in the subtotal then it doesn't aggregate at all, it jsut gives the first value (ie 31).

How can I get SSRS to aggregate the same way as the cube?

View 1 Replies View Related

Multi-Language - In SSAS/SSRS

Mar 7, 2007

Hi

I've made my Cube having two language support - Japanese and English. For that I'm keeping two description columns relavent to language and mapping in dimenstion attribute translation. Till that it is fine. In SQL 2005 Analysis Services Browser it working very fine. But when I use this cube with SSRS, I've made Custom parameter for Language. And based on that I'm chaning Report.Language property for the selected language. It is working fine in case of Report Data. It is converting data in Japanese when I select Japanese Language and translating in English when I select English. But Problem is that Parameter's data is not chaning when I change the language. I supposed to that when I'm changing Report.Language to Japanese then Parameter's data should be changed in Japanese. Please help how it can be possible.

Thanks in Advance.

View 2 Replies View Related

Multi-Language SSAS/SSRS Problem

Mar 7, 2007

View 1 Replies View Related

Reporting Services :: Integration Between SSRS And SSAS

Jun 4, 2015

I'm doing a BI project and I already develop the DataWarehouse and the OLAP Cube. Next step are the creation of a report using SQL Server Reporting Services.

I am used to use a query from Data Warehouse as the DataSet to implement the report. Is usual ou benefit to use the cube directly on report as DataSource?

View 3 Replies View Related

Creating Subscriptions To SSRS That Connects To SSAS

Oct 30, 2007

Hi,

I would like to ask how can I create a subscription for a report that connects to Analysis Services?

I was able to create a subscription but the report's datasource is SQL Server 2005 using the built-in "sa" credential. But when I am connected to SSAS the option to use the SQL login is greyed out.

Please help!

Thanks

View 1 Replies View Related

Browser Similar To SSAS Required For SSRS

Jun 29, 2007

Hi,

How to import analysis browser in SSRS. I need features similar to SSAS Cube browser and SSAS dimention browser.

Regards

Spandan

View 1 Replies View Related

Analysis :: SSAS Tabular And SSRS - Implementing Security

Oct 1, 2015

i would like to pose a question, when a user do a login in SSRS server,is it possible to pass the username to SSAS tabular, to implement security? I've tested using USERNAME() creating a role that relates USERNAME() with a dim user username, but now i need to implement it with the username used in SSRS server.

View 2 Replies View Related

Parent-child Hierarchy In A SSRS Project Using A SSAS Datasource

May 4, 2007

Hi everybody,

I would like to know how to create a hierarchy in a SSAS project using a SSRS datasource?

All values are displayed when I drag the field "parent" in the MDX generator...

Thanks in advance for any idea...

View 1 Replies View Related

Analysis :: To Get Value Of A Member Passed As Parameter In SSAS MDX

May 27, 2015

I have a requirement to show the parent value which is passed and also all the leaf member details in MDX. For example in Adventure works, if I pass Bikes as the value in MDX, the results should display Bikes along with All the child member names as a measure value in SSAS MDX. I only have one hierarchy with Product Category-> Subcategory-> Product.

I am trying to use the following MDX but I am getting all the members. I need to get only Bikes in my report.

WITH MEMBER [Prod_Passed] AS SETTOSTR(AXIS(1))
MEMBER [Prod_Child] AS DESCENDANTS ([Dim Product].[Hierarchy].CURRENTMEMBER,,LEAVES).item(0).NAME
MEMBER [Child_Value] AS ([Dim Product].[Hierarchy].CURRENTMEMBER,[Measures].[Sales Amount])
SELECT [Dim Product].[Hierarchy].[Product Category].&[1] * [Dim Product].[Product Key].[Product Key].MEMBERS ON ROWS,
{[Measures].[Sales Amount],[Prod_Passed],[Prod_Child],[Child_Value]} ON COLUMNS FROM
[AdvWorks]

View 10 Replies View Related

Reporting Services :: Show Empty Column Values In SSAS / SSRS?

May 6, 2014

In My cube I have :

A Fact table <<Company Revenue>>
2 Dimension <<Month.Dim>> , <<Company>>

I have 4 company say (A,B,C,D) but i have record for only company A & D that too only for December month .

When I create a SSRS report out of the cube i want a matrix where all the company names to be displayed in the rows & all the months in the columns and company's revenue in data

What my report is displaying :

Company | December
-------------------------------------------------------
A | 20000
-------------------------------------------------------
D | 3900
-------------------------------------------------------

what is needed :

Company | January | February | March | ........... | December
-----------------------------------------------------------------------------------
A | | | | | 20000
----------------------------------------------------------------------------------
B | | | | |
----------------------------------------------------------------------------------
C | | | | |
----------------------------------------------------------------------------------
D | | | | | 3900
-----------------------------------------------------------------------------------

How do i achieve this either in SSAS or SSAS.

View 5 Replies View Related

Reporting Services :: Preview Has One Extra Parameter Than Parameter List - SSRS

Aug 21, 2015

I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.

How do I know , when this parameter is coming in the preview ?

View 5 Replies View Related

SSRS &&amp; SSAS 2005 Data Parameter

SSRS && SSAS 2005 Data Parameter