January 11, 2008 at 6:20 am
Hello, All,
I'm hoping someone has a solution to this.
I have an MS Access Crosstab report which does Average Payments on a rolling 13 month basis. I have converted this to a PIVOT query in SQL 2k5. The crosstab report has a Total line for each group of Products and then a Grand Total line for everything. The problem is, the total lines (all of them) point to a completely different table in Access then the detail lines. And then code in the Access report uses the DLOOKUP term to associate each total with the proper month.
Now I need to port this over to SSRS, but can't figure out the best way to associate the two sets of data. I could do a Matrix (instead of a PIVOT) on the detail data set, but I haven't been able to figure out how to add subtotal or grand total lines that are not on the same row as the detail. If I leave the detail query as a dynamic PIVOT, I can't connect the other data in the PIVOT without messing up the detail lines (it becomes more granular than I want it to).
I've thought about a subreport, but I'm not sure if that would work or if the month columns would line up properly with the detail stuff.
I'm not terribly good at coding in SSRS, either... Does anyone have any thoughts on what would be the best way to do this?
Let me know if you need more information. Thanks in advance!
January 11, 2008 at 7:21 am
I'm thinking about this report and-ideally-here's what I'd like to do.
Have 2 datasets. DataSet1 is the detail. DataSet2 is the totals.
Write some sort of expression or code where I can do the equivlant of:
Select ProductSubTotal from Dataset2
where Dataset2.Product = Dataset1.Product
and Dataset2.Month = Dataset1.Month
Easy in T-SQL. Not so easy translating that T-SQL to SSRS. Again, any thoughts on how I can do this? Or any weblinks that might help point the way?
Thanks,
January 11, 2008 at 7:30 am
Have you looked at using the WITH ROLLUP option? It will allow you to put in sub-total lines "inline" with the rest of the query.
you might need to play some magic with formatting so that you can detect which lines are Sub-totals, which can be done with the GROUPING() predicate.
A quick example:
Select
Group,
Subgroup,
sum(amount) as Total,
Grouping(subGroup) as SubTotal line --This will be 1 when you're on the sub-total line
from
MyTable
Group by
Group,
SubGroup WITH ROLLUP
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2008 at 7:36 am
Brandie Tarvin (1/11/2008)
I'm thinking about this report and-ideally-here's what I'd like to do.Have 2 datasets. DataSet1 is the detail. DataSet2 is the totals.
Write some sort of expression or code where I can do the equivlant of:
Select ProductSubTotal from Dataset2
where Dataset2.Product = Dataset1.Product
and Dataset2.Month = Dataset1.Month
Now you're heading down the path towards a sub-report (which is also a good option for this). The lookups will be too inefficient otherwise.
If you don't like either the sub-report OR the WITH ROLLUP, my next thought would be to use a UNION (ALL) option with an extra field to track which are sub-totals (essentially a manual version of WITH ROLLUP, but it allows you to have sub-total levels be something distinct from one "level" to the next).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2008 at 8:10 am
Matt,
I'm not sure Rollup is going to work with this. The detail takes the PaidAmt and divides it by #Claims. I'm not quite sure what the load process that creates the total amounts does, but if you look below, the totals don't equal the average of the average or the sum of the average except where there's only one set of totals (these are real numbers, btw)...
The problem with a sub report is that the Access report looks like this:
Code Source M13 M12 M11...
(Group1)
01 cars 793.99 738.73 713.63...
10 Boats 1222.58 1222.58 0.0..
40 planes 0.0 691.23 0.0
(group1 total) 794.70 739.38 672.90
(Group2)
01 cars 15933.50 11437.23 13948.86...
10 Boats 0.0 0.0 0.0..
40 planes 0.0 0.0 0.0
(group2 total) 15933.50 11437.23 13948.86
And when I throw in the subreport, I get:
(Group1)
01 cars 793.99 738.73 713.63...
10 Boats 1222.58 1222.58 0.0..
40 planes 0.0 691.23 0.0
(group1 total) 794.70 739.38 672.90
(group2 total) 15933.50 11437.23 13948.86
(group3 total) ....
(Group2)
01 cars 15933.50 11437.23 13948.86...
10 Boats 0.0 0.0 0.0..
40 planes 0.0 0.0 0.0
(group1 total) 794.70 739.38 672.90
(group2 total) 15933.50 11437.23 13948.86
(group3 total) ....
ARGH. This is terribly frustrating.
January 11, 2008 at 8:11 am
Brandi,
I'll be very surprised if it's this simple but I'll ask anyway. Can't you use just the detail dataset and then group on product and month to let SSRS generate your subtotals and grand totals?
Having gotten the dumb question out of the way...
The only ways I know that'll allow you to 'associate' two separate datasets in a single report are:
1. By nesting a subreport (with your totals) into your main detail report. This is fine if you don't need to export out to Excel as the nested sub won't export.
2. Use two data regions, one for each dataset, then orient them so they match up in the report body.
Neither of these is optimal as they require careful formatting and arrangement so they match up
Your best bet, if it's at all possible, is to someway, somehow manipulate the two result sets into one prior to using them as data in SSRS. My experience (and I believe the SSRS users' general consensus) is to always do as much data processing as possible before using it in SSRS. There's usually less overhead plus there are usually better DP tools available outside SSRS.
HTH
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
January 11, 2008 at 8:38 am
Then try my suggestion #3: do the same thing as ROLLUP - but manually, with a UNION.
See if this jogs anything (manual version of previous example, but by changing the subtotal to be an average instead):
Select
Group,
subgroup,
Total,
SubtotalLine
from (
Select [Group],
SubGroup,
Sum(Amount) as Total
0 as SubtotalLine
from MyTable
Group by [Group], SubGroup
UNION ALL
Select [group],
'Subtotal line',
Avg(Amount),
1 as subTotal line
from Mytable
Group by [group]
)
order by [group], SubTotalLine, subgroup
Now - you could do something simpler than that since you already have the detail AND the subtotal queries set up. You just need to make the "subtotal query" have the same columns as the main one,
and you could do
select *
from (
Select
*,
0 as subtotalline
from MyDetailQuery
UNION ALL
Select
*,
1 as subtotalline
From mySubTotalQuery
) as x
ORDER by MyGroupField,Subtotalline,MySubGroupField
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2008 at 9:06 am
I just noticed - the reason your subreport solution isn't working is that you haven't parameterized the sub-report. You need to set up named parameters to pass the grouping info back and forth (not as nice as Access I know, but let's not go there).
So - the query in the subtotal report should look something like:
select *
from mySubTotals
where myGroupField=@myGroupFieldParam
Once you do that - go back to your main report, and look at the sub-report properties. You will see a spot to pass the group field into MyGroupFieldParam, which should get rid of your linking issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2008 at 10:09 am
I did actually have the subreport parameterized, but maybe I set it up wrong. Thanks, Matt. I'll look into it.
January 11, 2008 at 12:54 pm
did you put the sub-report in the group footer section (which you have to "show" through the "edit group" options)?
I just noticed you get REALLY funky stuff if you don't do that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2008 at 4:06 am
Matt,
Yes, I did put the subreport in the group footer, and ended up getting all total lines on each individual group.
I'm going back to the end users to see if we even need the totals. There's something strange going on with the numbers not being what I think they should be. Hopefully the end users can give me the specifics so I can manually code the totals with the detail numbers instead of having 2 different datasets. Or tell me the totals aren't needed at all.
Sometimes I wish I were still using Crystal....
January 14, 2008 at 9:37 am
There is something fubar there, that's for sure.
When you run the sub-report by itself what does it do? Does it prompt for a parameter and show you the total for just one group (one that would match a group in the main report?)
I've had more luck deleting an recreating sub-report objects in the main report than trying to move them around or updating them after I've made changes.
Still - that sounds pretty frustrating.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2008 at 9:46 am
Matt,
Part of my problem was that I was using a dynamic SQL string to create a PIVOT of the totals to get my group totals for the subreport. I've since gone back and just done the totals in a straight SQL String with a variable and am pulling them into a Matrix on the sub-report. I'm about to see if that works.
Of course, once I get the subreport working, I still have the totals of the totals to worry about, which will be hard to do since I can't pivot without an aggregate or use a matrix with only a column grouping...
This report is definitely going on my list of least favorites to work on.
January 17, 2008 at 4:41 am
Just an FYI for those who look at this thread for future reference.
I finally got my stuff working, but I had to do multiple subreports. One for the groups which passed in a parameter to a straight T-SQL statement and then had a matrix in the group subreport, and then another for the Grand Total line which didn't have any parameters in its T-SQL.
For the Grand Totals, I used a straight T-SQL statement and then a matrix in the subreport. Then I put the subreport in the table footer.
Lastly, I had to create a sidebar of totals (since months were the columns, the sidebar totals were the yearly totals). I had to do seperate subreports for this column based on Product & Class, then on Product (what I was grouping by) and then a total of the Grand Total line.
Mind you, the only reason I had to jump through hoops with this report was that it wasn't a straight SUM of all the totals in the group. Each detail was an average of the total Amount divided by the total # and each total was the total of the Classes per Product (subtotal) or total of the Products (grand) divided by the number in each group.
That's how I converted a Crosstab Access report with dimensional lookup (that pointed to different tables than the Crosstab details) to SSRS.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply