Feeds:
Posts
Comments

Archive for June, 2011

The answer is in the following blog:

http://blogs.msdn.com/b/jjameson/archive/2008/05/30/counting-rows-in-all-database-tables.aspx

SELECT
    sysobjects.Name
    , sysindexes.Rows
FROM
    sysobjects
    INNER JOIN sysindexes
    ON sysobjects.id = sysindexes.id
WHERE
    type = 'U'
    AND sysindexes.IndId < 2

 

 

 

 

Advertisements

Read Full Post »

I modified a few MDX expressions in the calculated members of my cube and the cube was processed successfully, however, one of the dimension hierarchy failed to be displayed. It returned with the above error. I then navigated to another dimension hierarchy and received the same error again. All of my dimension hierarchies were not working. Weirdly, my MDX query using the dimension members in the hierarchy still worked.

This took me a while to resolve. I recalled changing the MDX for a couple of the calculated members and was certain that I had not changed the MDX for the named sets. So I ended up checking the MDX for the calculated members instead of the named sets as the error implied.  It was the MDX expression of one of the namesets that has caused this error. Silly me, I should have read the error message properly. 😀

Read Full Post »

I have a main report with 4 subreports and these subreports are using shared datasets to retrieve the report parameters. The Shared Dataset is a new feature in SSRS 2008R2 and it is not supported in SSRS 2008 and it should increase the report performance. However, when I ran my main report in BIDS, all of subreports were failed to be displayed. I re-checked my subreports configuration making sure that the parameters were passed through correctly etc but it still did not resolve the error.

Google took me to the following website and gave me the answer, i.e. the main report will display the subreports if deployed. So I deployed all of them and can now run the main report with no problems. Is this a bug????

http://stackoverflow.com/questions/2974587/using-shared-dataset-in-subreport

Read Full Post »

I have a bar chart with data plotted on a Primary Axis. I added a second measure into the Values of the chart as a line chart but the secondary axis did not show. Clicking here and there didn’t get me anywhere. Googled it and found the following steps to turn the secondary axis on from Microsoft TechNet:

To plot a series on the secondary axis

  1. Right-click the series in the chart or right-click on a field in the Values area that you want to display on the secondary axis and click Series Properties. The Series Properties dialog box appears.
  2. Click Axes and Chart Area, and select which of the secondary axes you want to enable, the value axis or the category axis.

Read Full Post »

This report has 9 subreports and 4 report parameters i.e. Company, Subcontractor, From Date and To Date.  When I ran each subreport, it took instantly to return the resultset. However, it took 30-45 seconds to run the main report. Why?

The SQL Profiler trace indicated which MDX query that took the longest to run. It was the query to return a list of subcontractors. There are 4632 subcontractors, hence the ???

The Company parameter has only 1 record and so has the FromDate and ToDate parameters.

In order to optimise this, get a list of subcontractor where the Measure is greater than zero using FILTER function. That means, only retrieve the subcontractors that have some measures recorded against them. Make this change to all reports. My main report now only takes 5 seconds to run 🙂

Before:

WITH MEMBER [Measures].[ParameterCaption] AS [Organisations].[Organisation].CurrentMember.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS [Organisations].[Organisation].CurrentMember.UNIQUENAME

SELECT { [Measures].[ParameterCaption], [Measures].[ParameterValue] } ON COLUMNS

,[Organisations].[Organisations].ALLMEMBERS ON ROWS

FROM Cube

After:

 WITH MEMBER [Measures].[ParameterCaption] AS [Organisations].[Organisation].CurrentMember.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS [Organisations].[Organisation].CurrentMember.UNIQUENAME

SELECT { [Measures].[ParameterCaption], [Measures].[ParameterValue] } ON COLUMNS

, FILTER([Organisations].[Organisations].ALLMEMBERS, [Measures].[Injury Count] > 0)  ON ROWS

FROM Cube

Read Full Post »

The FORMAT() VBA function is very useful in formatting the date and time in the Reporting Services reports. I have used this a lot but very often I tend to forget certain codes. For example, I want to display the time as 16:00 instead of 04:00. I remembered the syntax is hh:mm but this one gives me 04:00. The following blog takes me to the answer, which is HH:mm.

http://www.geekzilla.co.uk/View00FF7904-B510-468C-A2C8-F859AA20581F.htm

DateTime.ToString() Patterns

All the patterns:

0 MM/dd/yyyy 08/22/2006
1 dddd, dd MMMM yyyy Tuesday, 22 August 2006
2 dddd, dd MMMM yyyy HH:mm Tuesday, 22 August 2006 06:30
3 dddd, dd MMMM yyyy hh:mm tt Tuesday, 22 August 2006 06:30 AM
4 dddd, dd MMMM yyyy H:mm Tuesday, 22 August 2006 6:30
5 dddd, dd MMMM yyyy h:mm tt Tuesday, 22 August 2006 6:30 AM
6 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07
7 MM/dd/yyyy HH:mm 08/22/2006 06:30
8 MM/dd/yyyy hh:mm tt 08/22/2006 06:30 AM
9 MM/dd/yyyy H:mm 08/22/2006 6:30
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
11 MM/dd/yyyy HH:mm:ss 08/22/2006 06:30:07
12 MMMM dd August 22
13 MMMM dd August 22
14 yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss.fffffffK 2006-08-22T06:30:07.7199222-04:00
15 yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss.fffffffK 2006-08-22T06:30:07.7199222-04:00
16 ddd, dd MMM yyyy HH’:’mm’:’ss ‘GMT’ Tue, 22 Aug 2006 06:30:07 GMT
17 ddd, dd MMM yyyy HH’:’mm’:’ss ‘GMT’ Tue, 22 Aug 2006 06:30:07 GMT
18 yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss 2006-08-22T06:30:07
19 HH:mm 06:30
20 hh:mm tt 06:30 AM
21 H:mm 6:30
22 h:mm tt 6:30 AM
23 HH:mm:ss 06:30:07
24 yyyy’-‘MM’-‘dd HH’:’mm’:’ss’Z’ 2006-08-22 06:30:07Z
25 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07
26 yyyy MMMM 2006 August
27 yyyy MMMM 2006 August

 

Read Full Post »

I recently deployed my cube on to localhost but failed to browse with the above error. I stumbled as in what could went wrong. I checked my laptop date and time and language and confirmed that all configured properly. I refreshed the cube but the error didn’t go away. I then decided to browse another cube on a different server and received the same error.

I then spent quite a bit googling for solutions. One solution is to changethe Language setting on the cube browse window from Default to your regional language, in my case English(Australia). The error disappeared as soon as I did this.

 

Read Full Post »

Older Posts »

%d bloggers like this: