Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS report with a "gridview" Expand / Collapse
Author
Message
Posted Monday, February 3, 2014 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 12:25 AM
Points: 3, Visits: 27
Afternoon,

I'm looking to create a report with a list of shops grouped by company area, the report would look something like.

Area 1
Shop1 || Shop 2 || Shop3
Shop 4 || Shop 5 || Shop 6

Area 2
Shop 7 || Shop 8 || Shop 9
etc

Is this possible to do in SSRS?

Thank you

Andrew



Post #1537392
Posted Sunday, February 16, 2014 9:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:45 PM
Points: 14, Visits: 18
Not very familiar with reporting things, but this seems to be something that a UI gridview can do. Any way, there are many gridview tutorials, it probably can be grasped easily and quickly. Good luck with you.
Post #1541980
Posted Monday, February 17, 2014 10:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 24, 2014 12:12 PM
Points: 1,219, Visits: 1,263
Is it always Shops 1 - 9? Could you use to tablixs on the report and limit the first to 1-6 and the second query to pull the last shops' data?

Rob
Post #1542226
Posted Tuesday, February 18, 2014 6:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 10:45 AM
Points: 763, Visits: 2,267
andrewt 30066 (2/3/2014)
Afternoon,

I'm looking to create a report with a list of shops grouped by company area, the report would look something like.

Area 1
Shop1 || Shop 2 || Shop3
Shop 4 || Shop 5 || Shop 6

Area 2
Shop 7 || Shop 8 || Shop 9
etc

Is this possible to do in SSRS?

Thank you

Andrew


You can use grouping but it would give you a slightly different format. If you have your SQL generate output like this:

Area | Shop
Area 1 | Shop 1
Area 1 | Shop 2
Area 1 | Shop 3
Area 1 | Shop 4
Area 1 | Shop 5
Area 1 | Shop 6
Area 2 | Shop 7
Area 2 | Shop 8
Area 2 | Shop 9
...

Then on your report you set your grid up to group on the Area column, it would give you something like this:

Area 1
---Shop 1
---Shop 2
---Shop 3
---Shop 4
---Shop 5
---Shop 6
Area 2
---Shop 7
---Shop 8
---Shop 9
...


(Edit - formatting)




The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1542528
Posted Tuesday, February 18, 2014 11:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 12:05 PM
Points: 339, Visits: 879
andrewt 30066 (2/3/2014)
Afternoon,

I'm looking to create a report with a list of shops grouped by company area, the report would look something like.

Area 1
Shop1 || Shop 2 || Shop3
Shop 4 || Shop 5 || Shop 6

Area 2
Shop 7 || Shop 8 || Shop 9
etc

Is this possible to do in SSRS?



I have done something similar before, it should be possible if you can add some stuff to your query.

I did mine by creating a CTE for the bulk of the query, in which there was a field (modified for your case) like:

-- some other columns from your query
CEILING(
(Row_number() over (order by ShopAreaNum ASC, ShopNum ASC)) / 3.0) AS RowGroup
--changing the divisor changes the number of columns

And then later in the final Select from the CTE I do another calc on that column, using that field:

SELECT 
ROW_NUMBER() OVER (
PARTITION BY YourCTE.RowGroup ORDER BY ShopAreaNum ASC, ShopNum ASC
) AS ColumnGroup
, YourCTE.*
FROM YourCTE
ORDER BY RowGroup ASC, ColumnGroup ASC

In your actual report you would then embed a Matrix, grouping the rows and columns by the RowGroup and ColumnGroup respectively, with your totals in the Details.

Then if you embed *that* matrix in a rectangle which is itself grouped by the ShopAreaNum, you should have what you need.

If not, hopefully that will put you down a path towards success.

Cheers,
EJM
Post #1542681
Posted Wednesday, February 19, 2014 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 12:25 AM
Points: 3, Visits: 27
Cheers EJM I will give that a go and let you know how I get on.

Thanks for your help

Andy
Post #1542872
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse