Showing posts with label showing. Show all posts
Showing posts with label showing. Show all posts

Tuesday, March 27, 2012

Calculated Measures not showing in SSRS.

Hi,
I have two cubes , One is fairly large Cube and the other one is Smaller one.
When I try to Use SSRS to report on these two cubes, i could see the calculated measures only in the Smaller Cube and there are no such things in the Large Cube.
I could Report on the Large Cube with base Measures.
I see the below message at the end of the Measures List.
"More Measures Exists than can Be shown."

Am i missing something?
Can anyone point me to some clue .

Thankyou
Vidya

Hi,

did you reprocess the large cube after,adding the calculated measures?

|||

I have not used SSRS yet, but I came accross a similar issue when I was creating a cube.

When you create a cube on SSAS, it automatically creates a script called "CALCULATE." You can view it on Caluculation tab on the cube design. It contains the following code:

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE

If this script is modified or deleted, you may not see your measures correctly in your cube.

|||Thankyou for the Reply,
But I Have that CALCULATE untouched.
Another intresting note is that i can see all the calculated measures with the values from EXCEL 2007.
When i try querying the cube for the Calculated Measure then i am getting the values ; Only problem is that i am not able to see them through SSRS.
I have tried re-processing the Cube but no luck.
Thankyou
vidya

Tuesday, March 20, 2012

calculate running total field?

Hi,

I'm using the CR v 11 and I did a 3D bar chart that showing two series of data for each salesman , one is their outstanding orders and the other one is the closed orders.

Both bars are get the value from two running totals that I defined in the report by a distrinct count on salesman name.

For example for salesman_A , the bars are showing like

15 outstanding orders
70 closed orders

Now I want to show the percentage for the outstanding and closed orders. How can I do it?

Based on the example above.

The calculation is 15 / (15 +70) * 100 = 17.64%

Any suggetion? ideally can put that percentage on the report somewhere or as another data series on x aixes.

Thank you!I think I understand your question,

I would create a formula 'f_percent' -(under formula fields in the field explorer)

then define it as....

({TABLE_NAME.OUTSTANDING_ORDERS}/({TABLE_NAME.OUTSTANDING_ORDERS}+{TABLE_NAME.CLOSED_ORDERS})*100)|||I think I understand your question,

I would create a formula 'f_percent' -(under formula fields in the field explorer)

then define it as....

({TABLE_NAME.OUTSTANDING_ORDERS}/({TABLE_NAME.OUTSTANDING_ORDERS}+{TABLE_NAME.CLOSED_ORDERS})*100)

Thank you for your reply, but the problem is those two values (# of outstanding, closed orders) are not part of the table fileds, it is two running total that I defined and calculate at runtime.

But now I need to get the value of those two running totals in order to calcuate the percentage between the outstanding/closed orders.

Any idea?sql

Wednesday, March 7, 2012

Cache prevents showing real-time data?

Hello.

I'm tying to make my cube show real-time data. Besides MOLAP partitions for historical data I have small ROLAP partition (no proactive caching, no aggregations at all).

When underlying data is changed, MDX queries not always show these changes.

I've found that switching data cache off (UseDataCacheRegistry=false) helps.

Unfortunately I've found no way to switch it off for one measure group only, but for the whole AS.

Could you please advice me something better?

Thank you.

Is SSAS 2005/OLAP the correct solution for you. If you run Reporting Services, TSQL-based, reports directly against your star schema or data warehouse, you will have real time data?

Regards

Thomas

|||

Thomas Ivarsson wrote:

Is SSAS 2005/OLAP the correct solution for you. If you run Reporting Services, TSQL-based, reports directly against your star schema or data warehouse, you will have real time data?

Regards

Thomas

Thank you for participating.

We actually already have pretty big and successfull datawarehouse based on AS.

Now users want real-time data too. Presented in the same way as historical data. I can't argue with this requirement as this is actually best opportunity from user perspective.

It is stated that AS is capable of showing real-time data. And I've managed to make it so.

I only wonder if there is any better (recommended) way which I have missed?

If I only could switch off data cache for ROLAP partition only..

Thanks again.

|||

Well, just wondering why you disabled proactive caching, because that feature should (in theory) enable you to display real-time data for the partition, without entirely switching server caching off. It sounds like you're trying to achieve the behavior of the standard Real-time ROLAP mode:

http://msdn2.microsoft.com/en-us/library/ms175646.aspx

>>

SQL Server 2005 Books Online

Choosing a Standard Storage Setting

Standard Storage Setting

Description

Real Time ROLAP

OLAP is in real time. Detail data and aggregations are stored in relational format. The server listens for notifications when data changes and all queries reflect the current state of the data (zero latency).

This setting would typically be used for a data source with very frequent and continuous updates when the very latest data is always required by users. Depending on the types of queries generated by client applications, this method is liable to give the slowest response times.

>>

Of course, the key to this working correctly is setting up the data change notification mechanism:

http://msdn2.microsoft.com/en-us/library/ms183681.aspx

>>

SQL Server 2005 Books Online

Notifications (Storage Options Dialog Box) (SSAS)

Use the Notifications tab of the Storage Options dialog box in Business Intelligence Development Studio to set the notification method and related settings for a dimension, cube, measure group, or partition.

...

Enable proactive caching

Enables proactive caching.

Note:

If this option is not selected, all options except Storage mode are disabled.

>>

This TechNet paper explains how the Proactive Cache settings work in more detail:

http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx

>>

SQL Server 2005

Real-time Business Intelligence Using Analysis Services

Published: April 1, 2005

This paper covers features in SQL Server 2005 Analysis Services that overcome many of the barriers to gathering and analyzing real-time business intelligence data faced by companies today.

...

Notification Schemes

There are three notification schemes by which Analysis Services can be notified of updates to the underlying source:

?

Trace Events. This only applies where the underlying data source is SQL Server. Analysis Services will register to receive trace events on the necessary tables. This scheme requires that the service account of Analysis Services has Administrator privileges on the SQL Server database. Event delivery is not guaranteed if the database is unavailable (e.g., due to network issues) at the time of update.

?

Client Initiated. A client can simply send a notification message to Analysis Services, indicating that a specified table has been updated. This is relevant in cases where the application that is actually performing the update is aware of the impact on the Analysis Services cubes

?

Polling. The most generally applicable approach is to use polling. Polling queries are defined for each table, returning a single value (i.e., a single row, with one column), a change to which indicates an update to the table. For example, a query returning the maximum value of the LastUpdated timestamp column would be a suitable polling query. Along with the query, the designer also specifies the frequency with which Analysis Services should send the polling query.

>>

|||

Now I understand. Thank you.

Without data cache queries become way too slow. To make use of cache AND show real-time data we need some kind of notification to refresh cache.

I'll try to setup proactive caching with notifications once more.

Sunday, February 12, 2012

Business Intelligence

The Business Intelligence isn't showing up in the menu with ( Visual Basic, Visual C##, etc). A teckie installed the pro version and said she installed everything, can some one point me in the right direction. We purchase the media and installed from that.

If you are referring to the Business Intelligence Projects not showing up in Business Intelligence Development Studio (BIDS), then please refer to the link below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1479717&SiteID=1

|||The BI projects are part of SQL tools not Visual Studio install

Business Days between 2 Dates

I'm new to Reporting services and I would like to be able to dispay a
calculated field showing the number of business days, or business hours
betweeen 2 dates. Anyone have some thoughts? Or better yet, a code
snippet?Paste this in the Code window in report properties dialog and Refer to it in
the report items as: =Code.NumberOfWeekDays( Parameters!InitialDate.Value ,
Parameters!FinalDate.Value )
public shared Function NumberOfWeekDays(InitialDate As DateTime, FinalDate
As DateTime) As Integer
Dim totalDays As Integer
Dim workingDays As Integer
Dim dateTemp As Date
Dim i As Integer
totalDays = DateDiff(DateInterval.Day, InitialDate, FinalDate)
workingDays = 0
dateTemp = InitialDate
For i = 0 To totalDays
dateTemp = DateAdd(DateInterval.Day, i, InitialDate)
Select DateAdd(DateInterval.Day, i, InitialDate).DayOfWeek
Case DayOfWeek.Saturday:
Case DayOfWeek.Sunday:
workingDays += 0
Case Else:
workingDays += 1
End Select
Next i
return workingDays
End Function
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave Bodenheimer" <dave@.edgeinformation.com> wrote in message
news:%23g3EQ8RgEHA.2916@.TK2MSFTNGP12.phx.gbl...
> I'm new to Reporting services and I would like to be able to dispay a
> calculated field showing the number of business days, or business hours
> betweeen 2 dates. Anyone have some thoughts? Or better yet, a code
> snippet?
>