SSRS report with a "gridview"

Question

SSRS report with a "gridview"

andrewt 30066

SSC Veteran

Points: 267
More actions
February 3, 2014 at 9:44 am

#396211

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

alex_2356 SSC Veteran Points: 298 More actions · Answer 1

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.

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 2

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

LightVader Hall of Fame Points: 3810 More actions · Answer 3

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.

eric.muller SSCommitted Points: 1998 More actions · Answer 4

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

andrewt 30066 SSC Veteran Points: 267 More actions · Answer 5

Cheers EJM I will give that a go and let you know how I get on.

Thanks for your help

Andy