Query Desiginer in Report Builder using a Report Model
Hello
I am trying to evaulate SSRS and Report Builder 3.0 however so far I am finding a number of limitations.
I have created a Data Source, Data Source View and Report Model in BIDS. I have been able to successfully deploy these to the SSRS server. The Data Source View (DSV) is very simplistic and contains just two tables - Customer and Sales.
The join in the DSV is Customer.Number (PK) to Sales.Soldto_Number (FK). Note that "Soldto_Number" is NOT a primary key on the Sales table. I am having major problems when I try and build a report using this model in the Report Builder. These are the steps
that I am taking:
1. I launch Report Builder and create a new/blank report.
2. I right-click on "Data Sources", choose "Add a Data Source" and browse to select the model that I have created in BIDS.
3. Then I right-click on "Datasets", choose "Add a Dataset", select "Use a dataset embedded in my report" and from the available list choose the Data Source that I created in the previous step.
4. I right-click on the Dataset that I have created in step 3 and select "Query....". This opens the Query Designer
The query that I wish to build is:
Customer.Number; Customer.Name; Sales.SalesID; Sales.SalesDate; Sales.Shipped
When I open "Query Designer" I can see both tables in the "Entities" pane and when I click on each table in turn I can see the fields that are on the table. I take the following steps to try and build the above simple query:
1. Click on the Customer table in the "Entities" pane
2. Drag Customer.Number into the design pane.
*****ERROR/ISSUE***** When I do this I lose sight of the other table (Sales) in the "Entities" pane and so I cannot add any fields into the query from the Sales table.
What is going wrong here??? Any replies are gratefully received.
TIA
Julian
April 14th, 2011 5:59am