Report Builder: Filter drop down list unspecified

I have a report model which functioned correctly until I copied the project
over to another server and changed the data source for it. I can still build
a report that returns the correct data, but when I add a filter to the report
data, the ddl for the filter never loads the data and says 'unspecified'.
I'm not sure what the problem could be. Any ideas? ThanksAfter doing some hunting around I found this:
One of the most commonly used features in ad-hoc reporting is filtering. To
add filters to a report, select a field, and then click the Filter button. If
the InstanceSelection attribute enables drop-down, filter values will be
automatically populated. In article
http://msdn2.microsoft.com/en-us/library/aa964121.aspx#adhocrptsol_topic5
This is not the issue asthe InstanceSelection property is properly configured.
"Jason" wrote:
> I have a report model which functioned correctly until I copied the project
> over to another server and changed the data source for it. I can still build
> a report that returns the correct data, but when I add a filter to the report
> data, the ddl for the filter never loads the data and says 'unspecified'.
> I'm not sure what the problem could be. Any ideas? Thanks

Report Builder, SSAS, and Many-to-Many Relationships

Does Report Builder handle many-to-many relationships within an SSAS cube correctly? I can't seem to get it to. Using Adventure Works, here's an example of the problem I'm seeing.

- Create a new report in RB, opening the Adventure Works DW model

- Select one of the perspectives that includes the Sales Reason dimension (like Direct Sales)

- Select the Sales Reason entity in the Entities list and put the Sales Reason Type attribute into the report (making Sales Reason the primary entity)

- The Entities list will be reduced to show the Adventure Works Sales Reasons relationship (which maps to that underlying measure group)

- If you select the Adventure Works Sales Reasons relationship, you’ll see the Internet Sales Order Details entity below it

- If you select the Sales Order Details entity, however, you’ll only see the attributes that are in that dimension; you won’t see any of the measures that exist in the measure groups that are related to it

- Based on that fact, you can’t get to any of the measures in the cube (since the Sales Reason dimension is related to the other measure groups only through M-to-M relationships)

Of course, it may be that I just don't understand how the auto-generated report model for the SSAS cube is exposing the relationships and the entities that correspond to the Sales Reason dimension, the Sales Reasons measure group (the intermediate measure group for the M-to-M relationships to the other measure groups), etc.

Anyone seen this before or know that this is defined behavior? Seems that for any SSAS cube that includes M-to-M relationships, Report Builder just won't work...

Thanks!

Dave Fackler

Unfortunately, the SMDL model in the Report Builder sometimes gets in the way when SSAS is used as a data source. To get around similar limitations, I added calculated members in the cube which mapped to the original measures and I assigned them to the first measure group. The net result is that all measures were mapped to only one measure group. Then, I had to manually change the SMDL model to hide the measure groups/measures I don't need.|||

Hi Teo,

I understand most of the limitations with RB against an SSAS-based SMDL model. However, I've never tested trying to build a report that includes dimensions/measure groups that are related via a many-to-many relationship. The fact that this doesn't seem to work is troubling, so I'm trying to confirm that it is indeed an issue (or bug?) with RB and not just me doing something silly... Have you ever tried to create a report using RB that hits the Sales Reason dimension in the AW cube? If not, give it a try and let me know what your experience is...

Thanks!

Dave F.

|||

The fact that this doesn't seem to work is troubling, so I'm trying to confirm that it is indeed an issue (or bug?) with RB and not just me doing something silly.

Not the latter for sure . M-M relationships is not the only case. The same thing happens if you have a multi-grain relationship, e.g. Sales Target in the Adventure Works. Then the model drops the Date relationship so you can't slice by date. I'd recommend you file a bug report with product services for all Report Builder issues that you encounter (I've done my fair share doing so). The more people complain about the SSAS integration scenario the more likely is to get fixed/improved. Meanwhile, consider opening the SMDL model and adding the dropped relationships manually. This isn't a supported scenario but it will probably get you going.

Report Builder - Filter Dropdown Order Post 2005 SP2 Incorrect

Hi All,

Hope someone can help here. Since installing Service Pack 2 onto our SQL 2005 instances our report models are not working correctly. When selecting fields within the models as filters, and getting them to prompt when the report is run, the ordering of the data within the filters does not match the selected ordering in the model definition. The ordering is random each time the report is run.

This issue does not happen when setting the filter up, the data appears in the correct order, but when running the report the filter data is incorrect.

I have spent the last 4 hours building a new system and testing this with each stage of service packing. The RTM version of SQL 2005 is ok, SP1 is also OK, but when SP2 is applied the ordering fails.

Any ideas?

Many thanks in advance.

Regards

Nick

An update to this issue just in case it may help others.

I logged a call with Microsoft relating to this issue and the product support specialist managed to reproduce the issue at their end. It was passed up to the product teams and the reponse has come back that it is now designed this way even though it worked prior to SP2.

The specific issue is:- Dropdown parameter boxes on reports created using report builder do not adhere to the ordering specified in the report model at runtime, although they do at design time.

As we were adding parameter boxes allowing users to select from almost 1000 branch codes the fact that it isn't ordered and appears in a random order is obviously an issue that has meant we can no longer use report builder.

The workaround suggested by Microsoft is to index the underlying tables so that the table scans of the branch codes scan indexes instead and return ordered lists, as the index is already ordered. Unfortunately I cannot do this as my tables are indexed for performance, and I can't change them to fix an issue that shouldn't (in my personal opinion) be there in the first place.

Hope this helps anyone else in the same situation.

Nick

Report - preview looks fine, but exporting to PDF splits controls!!!Help!!

Hi, I've build correctly my report using the most efficient tools. I've made the preview and it worked just fine!However, when I export to PDF, it splits the report heigth into two pages, having the columns of my table and other controls splitted.

I'm already using the font size as 7 or 8 (depends), really small, I've tried hard to reduce their size, however the result remains the same...no resolution!!

Is there anybody who may have or had this situation?Any guests or help?Thanks a lot!

is your report in landscape or potrait layout?

|||I've tried both, however the problem remains. I guess I'll try to change a little bit the report to see any change...Thanks anyway!|||

hi

I had a similar situation when i exported my report to PDF and it inserted blank spaces and spilt the report. My solution was not to change the font but the margin size of the report. Try changing the size of the margins and see if it works...

hope this helps.

|||

Hi,

It may not be just the margin size but more combination of margin and paper size. Drag the back ground paper smaller untill it fits within the size of you paper less the margin.

You can also try printing the report. Does this give the same result as export to pdf. Putting a borad arround the page can help see where the edge is.

hope this helps

dkldev

|||Reorganizing the controls solved out the problem. The issu now is, that I have a footer on which ir presents the current and total nr of pages, such as "page 1 of 2", and the report appears always with 2 pages, even if the report only has one page. The preview shows one page, and the footer shows "page 1 of 1", however, if I export to PDF appears "1 of 2! and the second page only has the footer saying "page 2 of 2"...any guess?Thanks a lot for helping!|||

http://blogs.msdn.com/bwelcker/archive/2005/08/19/Alien-Lanes-_2800_Logical-and-Physical-Pagination-Rules_2900_.aspx

hope this helps