SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trouble Converting Excel Report into Reporting Services


Trouble Converting Excel Report into Reporting Services

Author
Message
swhedges47
swhedges47
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 64
I've been given the task of converting a set of about 25 manually generated Excel reports into RS (2008R2).

A number of them have a (seemingly simple) pivot table layout that I'm finding especially frustrating to duplicate.

Here's an example where data is grouped by Division and then Dealer Name and the Dealer Name is positioned underneath the Division (which is essentially a placeholder with no data returned).



I can easily replicate the gist of the report by adding a row grouping that appears horizontally to the right of the Division, but I can't replicate the above format, with Dealer Name appearing underneath the Division but still grouped within it. The closest I have come is getting Dealer Name under Division, but all the Divisions appear on top first, with all the Dealers listed underneath and not within.

I'm pretty new to Reporting Services, so I'm wondering if there's something simple I'm missing here or whether this is just a case of non-translatable formatting between Excel and RS.

Any suggestions would be much appreciated.

Stuart
happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8733 Visits: 3281
Have a look at the properties of the text box that contains the dealer name - there should be a property called "Indent" (not sure which versions of SSRS this is in but it is availabl in 2008r2). Try a value of "5pt" for the property "HangingIndent".

If "Indent" is not available in the version you are using, try inserting a column 2 the right of Division. For the line that Division is on, merge the existing cell and the one to the right. On the line where the dealer name is shown, move the value to the new column. Finally, make the original column narrower (until you get the indent effect from the original spreadsheet). Hope that makes sense.



swhedges47
swhedges47
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 64
Thanks for the reply.

I should have known there would be an indent property for the text, that will come in handy.

I'm not sure the snip of the Excel report was clear enough, what I'm trying to replicate is something like this:

Division1
Dlr1
Dlr2
Division2
Dlr3
Dlr4
Division3
Dlr5

And I can't seem to get past:

Division1
Division2
Division3
Dlr1
Dlr2
Dlr3
Dlr4

How do I get the output without having to resort to something like this (which I know I can do for this report, but will not work nearly as well for others I know I have to convert in the future:

Dlr1
Division1 Dlr2
Dlr3

Dlr4
Division2 Dlr5
Dlr6

I don't need the Division line to hold any detail data, if that makes a difference ...

[Edit] Argh. The formatting (indenting) I was trying doesn't seem to have worked. I hope it still makes sense.
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10689 Visits: 7891
Yes, you can do it.

You need to make sure that your Dealer Name group is a Child Row Group of the Division group, then it's just formatting (like the indent).



Edit: Obviously the image is not meant to reflect your columns/data, but to show the idea...

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Attachments
    Grouping.PNG (36 views, 14.00 KB)
    swhedges47
    swhedges47
    SSC Rookie
    SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

    Group: General Forum Members
    Points: 28 Visits: 64
    Hmmm, I've tried that before and what I end up with is:



    Is there some hidden step to adding Dealer as a Child Row Group that will move it underneath the Division Row Group that I'm overlooking, or am I just being dense about what I can do with the formatting?

    The only way I can get the Dealer Row Group under the Division Row Group is to add it as a Row Group Adjacent Below, but that obviously just outputs all the Divisions first and then all of the Dealers underneath that.
    swhedges47
    swhedges47
    SSC Rookie
    SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

    Group: General Forum Members
    Points: 28 Visits: 64
    Ah, turns out that I was trying to create a report with "stepped" layout.

    Here's a good overview of the process, if anyone ever needs to know:

    http://sqldusty.wordpress.com/2011/07/23/creating-stepped-reports-with-ssrs-2008/

    Thanks to all who helped!
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search