Archive for category: Blog

I spent a couple of hours today trying to get SSRS 2005 working on Vista. I was getting the infamous IIS 500 error when browsing the Report Manager (http://localhost/reports) and Report Server (http://localhost/reportserver) virtual roots. I triple-verified the Brian Welcker’s recommendations and the Vista considerations in the SQL Server 2005 readme file. I couldn’t figure what’s going on especially given the fact that I installed SSRS on my home Vista machine with no problems. The only difference this time was that I performed file-only install of SSRS because I wanted to use an existing report catalog. In a moment of a Google eureka, I came across the How to install SQL Server 2005 Reporting Services on a Windows Vista-based computer KB article. It turned out that the Reporting Services Configuration Utility put the IIS applications in the default application pool which in IIS 7.0 is running in Integrated Managed Pipeline Mode. I created a new ReportServer application pool in Classic Managed Pipeline Mode, put the ReportServer and Reports applications in, and the issue went away.

Back to my When (null) is not Null post, there is a undocumented breaking change in SQL Server 2005 SP2 which you should be aware of. In response to the customer demand, SP2 treats aggregate rows as detail rows.  As a result, if the report doesn’t use the Aggregate() function, the report will show all rows. For those using the Aggregate function, there will be no change.

If you target the SSRS Report Builder as an UDM client you may find the following two workarounds (aka hacks) useful. Both hacks require manually updating the SMDL definition. Alas, so much about model auto-generation L.

Multi-grain Relationships Don’t Appear

If a dimension joins a measure group at a higher grain the corresponding Report Builder role is excluded from the model. Steps to repro:

1. Open the Adventure Works SSAS project in the BIDS cube designer and switch to the Dimension Usage tab.
2. Note that the Date dimension joins the Sales Targets measure group at the Calendar Quarter level (not the Date level). That’s because the FactSalesQuota (the fact table Sales Targets is based on) stores the sales quotas at the quarter level in AW.
3. Generate a report model from a data source pointing to the Adventure Works SSAS database
4. Open the Report Builder and connect to the Adventure Works cube.
5. From the Sales Targets folder, drag the Sales Amount Quota on the report canvas.
6. Note that the Entities pane excludes the Date dimension.

Hack: Change the SMDL definition manually to include the dropped relationship. To do so, compare the SMDL definitions side-by-side before and after the grain was changed using a file comparison tool such as SourceSafe or ExamDiff. For example, the first missing role entry should look like the one below:

<RoleID>udm:Role_MeasureGroupDimension_Entity_MeasureGroup_RPM_Profitability_Measures_Entity_Dimension_Date_Entity_Dimension_RPM_Date</RoleID>

</RolePathItem>

</Path>

<AttributeID>udm:Attribute_Hierarchy_Date.Date</AttributeID>

</AttributeReference>

<AttributeReference>

<Path>

<RolePathItem>

 

In my case, I had to restore three entries caused by raising the grain of a dimension relationship. Once the entries have been restored manually and the model was deployed, the reports worked as expected.

Text-based Calculated Members Are No-Show

An UDM text-based calculated member comes back as empty in a Report Builder report. That’s because the Report Builder defaults the data type of the member to Float. Steps to repro:

1. In the Adventure Works sample cube add the following calculated member:
   
   

   Create Member CurrentCube.[Measures].[Hello]
   As “Hello”,
   VISIBLE = 1;
   

2. Assign the member to a measure group so it will show up in the Report Builder.
3. Create a table report and uses the Hello member. The column comes back empty.

Hack: Overwrite the data type of the member in the auto-generated SMDL:

<Attribute ID=”udm:Attribute_Measure_Hello”>

<Name>Hello</Name>

<DataType>String</DataType>

<Nullable>true</Nullable>

…

</Attribute>

 

Deploy the model and observe that the text-based calculated member now does show.