December 8, 2009 at 1:18 pm
The query for my dataset on a report in sql reporting services is pulling data that looks like the following:
SYSTEM #SYSTEM NAMETICKET #COMMENTS BRANCH
xyz name 123 comments aaa
yyy name 1 234 comments aaa
zzz name 2 333 comments bbb
What I am attempting to do, using Microsoft Business Intelligence Studio is take that query data and create page breaks based on the BRANCH. Ie: there would be two pages in the report, the first would contain the two records for BRANCH aaa.. and the second page would contain the one record for BRANCH bbb.
I have attempted to create a Row Group By BRANCH.. and it almost gives me the desired result with the exception it is only pulling 1 record from each BRANCH for each page and not all records that belong to that BRANCH.
I have also tried changing my query for the report to SELECT the data for each BRANCH instead.. ie my dataset query looks like this:
SELECT * FROM TABLE WHERE BRANCH = 'aaa';
SELECT * FROM TABLE WHERE BRANCH = 'bbb';
If you run it in SSMS it shows the two queries with the two different data sets as needed, however if I try to run it as the DataSet for the report, the report only returns the first query and appears to ignore the second.
Please keep in mind that this is only an example and the could be x number of BRANCHES that will be constantly changing, so I can't hardcode this to a specific number of pages or BRANCHES.
Any help would be appreciated.
P.S. I would also like to display the BRANCH name in the Header of the report.. I attempted this as well however it only displays the FIRST occurrance. I need it to update each page with the BRANCH name that goes with the data.
December 9, 2009 at 6:42 am
What object(s) are you using in your report?
If you are using a table then you can put a group on the report grouping on branch and a page break at end of the group. Then you can also use a group header that will show the branch.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 9, 2009 at 7:33 am
Jack, I have 1 table (tablix1). There is of course a row with the header names and then a row with the fields from my dataset. I also then right clicked and inserted a page header. I have a text box on in the page header section.
Maybe i'm doing something wrong with the grouping? I do the following:
- Right click on my dataset row for the table object
- Click on Row Group > Group Properties
- Add Group Expression and select BRANCH
- Click on page breaks > click on Between each instance..
This gives me almost the desired results because it does break out each branch onto it's own separate report.. the issue is that it's only pulling 1 record for each page.. ie: there are supposed to be 2 records for branch 'aaa', however when you preview the report only 1 of those records appear. I believe this is due to the Grouping.
Also, i'm curious how to add a Group Header.. I don't see that option anywhere.
December 9, 2009 at 11:57 am
I don't have SSRS 2008 available at work, I'll have to try to take a look at home. I think the Tablix grouping should work the same way tho.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 9, 2009 at 1:08 pm
Well, I figured this out for the most part.
What I was doing was creating a Group on the Details, when apparently I needed to add a parent group instead. What i didn't like was that the Branch Name now became a column named Group1.. which I didn't want Visible to the user. For some reason you can't make that column not visible.. at least any easy way. I had to click on the individual cells for that column and change the Hidden attribute to True, and then set the Padding for that column to 0,0,0,0 and the Border to None.. as well as shrink that column down so small the users couldn't see it when the report ran.
Is there an easier way to hide it ? I tried to set the Visibility of the Group itself to Hide.. but then the report didn't return any results at all.
Also, I changed the Text box on the header to say =ReportItems!group1.Value and it's updating properly now.
December 10, 2009 at 4:59 am
Sounds overly complicated, can you not just remove the column from the table?
Or delete the expression in the textbox?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply