Combining 2 sql records on one detail line

Hi,
Is there a way to take 2 sql records from a data set and combine them
on one detail line. My data set looks like
sales lane store_no week year
9930.04 2 C196 50 2006
7276.24 3 C196 50 2006
In reporting services I want to have a table that shows store, lane2
sales, lane3 sales, week, year. When I do this now I get 2 detail rows
one that shows lane2 sales and another that shows lane 3 sales. I have
tried using a matrix which does a nice job of pivoting the data but
then throws off the way I want my report to be layed out. In Crystal
report I could do calculations and running totals behind the scenes
and then drop the result of that on the report the way I want it. How
can I do that with RS? As a side note I have tried using calculated
fields in RS but they crash my Visual Stuido if I do any kind of
calulation or IIF statement.
Any help is appreciatedHey you can do this pivoting using sql query itself, so that the result set
will look like the way you want
Amarnath
"mcgrawc@.checkers.com" wrote:
> Hi,
> Is there a way to take 2 sql records from a data set and combine them
> on one detail line. My data set looks like
> sales lane store_no week year
> 9930.04 2 C196 50 2006
> 7276.24 3 C196 50 2006
> In reporting services I want to have a table that shows store, lane2
> sales, lane3 sales, week, year. When I do this now I get 2 detail rows
> one that shows lane2 sales and another that shows lane 3 sales. I have
> tried using a matrix which does a nice job of pivoting the data but
> then throws off the way I want my report to be layed out. In Crystal
> report I could do calculations and running totals behind the scenes
> and then drop the result of that on the report the way I want it. How
> can I do that with RS? As a side note I have tried using calculated
> fields in RS but they crash my Visual Stuido if I do any kind of
> calulation or IIF statement.
> Any help is appreciated
>|||I realize that using SQL I could pivot the data and I have written
some code to do just that however I was hoping to find a way in
Reporting Services to do this just like I could in Crystal Reports. My
company wants to use RS to replace our crystal reports but I am
finding either through my lack of experience with RS that some of our
reports are beyond what RS can currently give. I personally love some
of the RS features but miss my the ease of my Crystal formulas,
running totals, etc. I have several other reports that I have stopped
working on temporarily that have this same problem and I really am not
looking to write code for all of them to pivot the data into something
RS can use. If anyone has a report solution I would love to hear it.
Chadwick|||Have you tried using the RuningValue function in RS?
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"chadwick" wrote:
> I realize that using SQL I could pivot the data and I have written
> some code to do just that however I was hoping to find a way in
> Reporting Services to do this just like I could in Crystal Reports. My
> company wants to use RS to replace our crystal reports but I am
> finding either through my lack of experience with RS that some of our
> reports are beyond what RS can currently give. I personally love some
> of the RS features but miss my the ease of my Crystal formulas,
> running totals, etc. I have several other reports that I have stopped
> working on temporarily that have this same problem and I really am not
> looking to write code for all of them to pivot the data into something
> RS can use. If anyone has a report solution I would love to hear it.
> Chadwick
>