Passing Multivalue Parameters Through a Report Link

I've created a line chart in my report which lists number of transactions by month. I've also created a report to list transactions by day. My goal is for the user to select a month and link the report to the graph with the selected month divided up into days (in other words, the user wants to see the number of transactions for each day in the selected month).

Anyway, everything is working perfectly except for one thing. In the list of parameters that I am passing to the report for each day, I want to pass a multivalue parameter which contains all the transaction IDs the user selected in creating the report (these are supplied by a multivalue parameter). However, in the parameter dialog box where it asks for a value to send to the awaiting parameter, I do not know how to supply more than one value. If I need to pass one value, it will work fine. However, I would like to do something like:

JOIN(Parameters!TransactionID.value, ",")

for the value of TransactionID, but when I generate the report it is not accepting the values. I'm pretty sure its just a format issue, and I just need to know how exactly I should pass these values.

I already understand that when passing multivalue parameters in a URL you need something like:

TransactionID=1&TransactionID=2

...in order to select multiple values. However, this is a slightly different situation. I'm really running out of ideas, so any help would be much appreciated.

Thank you guys so much

can you please paste your select statement, multivalue parameters are automatically rendered if you have something like this in your select

select * from employees where managerid IN(select managerid from managers)

|||

Have you tried:

SELECT *

FROM Managers

WHERE ManagerID in (@.ManagerParm)

This works in Oracle (except for : instead of @.) and I would be surprised if it did not work in SQL server.

NOTE you will not be able to test it using the !, but it it will work for the report.

|||

I'm sorry I wasn't clear. The project I have is in RS 2005 and uses an analysis services data source and the select statement is entirely in MDX. However, the problem really isn't in the select statement. My problem is that I am creating a report link and I want to pass all the values selected in a multivalue parameter to the other report.

If you want to see reproduce the problem, try these steps:

Create a report with a multivalue parameter. Add a textbox. Right-click and select properties. Click the Navigation tab and select "Jump to Report". Select a valid report that accepts a multivalue parameter. Click the parameters button. In the parameter name column, select the multivalue parameter to receive the values. In the parameter value column, I need to pass the values for the current multivalue parameter. This is the problem I am having.

|||

Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

It describes the various scenarios of passing multi value parameters to a drillthrough or subreport. In your case, it should be sufficient to just specify =Parameters!ParameterName.Value to pass all values along.

However, note that if the target report contains a parameterized MDX query and you want to use the passed-in parameter values directly in that query, the parameter values in the main report must represent the UniqueNames (not the caption which is usually the parameter label) - otherwise the target MDX query will most likely not work.

-- Robert

|||Thank you, it works perfectly!!sql

passing date parameters between reports changing from dd/mm/yyyy to mm/dd/yyyy

I have a report which is based on a start and end date that are passed
as parameters at run time. This works fine. However, in the report is
a link to another report, which needs to run based on the same date
parameters. However, the following happens...
Report 1 runs fine with date parameters and report is generated.
When report 2 is selected the same dates are passed, but the date
format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
report 2 will either run with the wrong dates, or it will crash and not
run at all if swapping month and day gives an invalid date.
I have my report languages set to default so it should pick up my
regional setting ok. But when passing these dates between reports the
settings seem to be lost somehow.
Can anyone help'Check the data settings on the machine, and the report server configuration.
That may be the culprit.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<gearoid_healy@.yahoo.com> wrote in message
news:1102350682.082943.295010@.c13g2000cwb.googlegroups.com...
>I have a report which is based on a start and end date that are passed
> as parameters at run time. This works fine. However, in the report is
> a link to another report, which needs to run based on the same date
> parameters. However, the following happens...
> Report 1 runs fine with date parameters and report is generated.
> When report 2 is selected the same dates are passed, but the date
> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
> report 2 will either run with the wrong dates, or it will crash and not
> run at all if swapping month and day gives an invalid date.
> I have my report languages set to default so it should pick up my
> regional setting ok. But when passing these dates between reports the
> settings seem to be lost somehow.
> Can anyone help'
>|||Oops. Not "data settings" but "date settings", as in Control Panel.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:er8cQd82EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Check the data settings on the machine, and the report server
> configuration. That may be the culprit.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> <gearoid_healy@.yahoo.com> wrote in message
> news:1102350682.082943.295010@.c13g2000cwb.googlegroups.com...
>>I have a report which is based on a start and end date that are passed
>> as parameters at run time. This works fine. However, in the report is
>> a link to another report, which needs to run based on the same date
>> parameters. However, the following happens...
>> Report 1 runs fine with date parameters and report is generated.
>> When report 2 is selected the same dates are passed, but the date
>> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
>> report 2 will either run with the wrong dates, or it will crash and not
>> run at all if swapping month and day gives an invalid date.
>> I have my report languages set to default so it should pick up my
>> regional setting ok. But when passing these dates between reports the
>> settings seem to be lost somehow.
>> Can anyone help'
>|||The date settings on my pc are correct. How do I check the report
server configuration? I've taken a look at RSReportServer.config and
the other .config files but can't see anything there that look like
regional settings, apart from
<Render>
<Extension Name=...>
<Configuration>
<OWCConfiguration>
<OWCDownloadLocation
language="en">http://office.microsoft.com/downloads/2002/owc10.aspx</OWCDownloadLocation>
but I don't think this has anything to do with it? Like I said, it
renders fine the first time, but when the dates are passed as part of
the url in a query string to the second report the date format changes,
so it must be some kind of report server configuration setting alright.
I just don't know where to go to check or fix this...|||ok, finally found a solution to this. Apparently it's a known issue
that was addressed in SP1 -
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
...which states "The rs:ParameterLanguage URL access parameter
alleviates a problem in which culture-sensitive report parameters, such
as dates, times, currency, and numbers, are interpreted using the
browser language"
Basically you have to add
...&rs:ParameterLanguage=<regionalSetting>
where regional setting is en-ie or en-us or whatever...|||Great, thanks for posting your solution back to the newsgroup.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102422016.683757.198040@.z14g2000cwz.googlegroups.com...
> ok, finally found a solution to this. Apparently it's a known issue
> that was addressed in SP1 -
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
> ...which states "The rs:ParameterLanguage URL access parameter
> alleviates a problem in which culture-sensitive report parameters, such
> as dates, times, currency, and numbers, are interpreted using the
> browser language"
> Basically you have to add
> ...&rs:ParameterLanguage=<regionalSetting>
> where regional setting is en-ie or en-us or whatever...
>|||I posested my solution to this before I actually tested it. This still
doesn't solve the problem I was having...
I call all my reports through a custom .net application, and display
them in my own application, building up my own URLs to display them.
However, when I link from one report (by clicking on an an image) it
calls the second report. But this is done within the report manager
and as such I don't get the option to append my own parameters to the
query string - namely the &rs:ParameterLanguage=en-ie part that I need.
Can anyone tell me how I might overcome this? I tried adding it as a
parameter in the Image Properties - Navigation - Hyperlink action -
Parameters but got the following error
...Parameter names must be CLS-compliant identifiers.
I'd appreciate a response from someone from Microsoft on this as it's a
known issue. How do I get around this bug in Reporting Services'|||It sounds like you are using Jump to Report. Use Jump to URL. You can put an
expression in Jump to URL that can be anything you want so you should have
no problem appending this onto it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102509563.514657.75340@.z14g2000cwz.googlegroups.com...
> I posested my solution to this before I actually tested it. This still
> doesn't solve the problem I was having...
> I call all my reports through a custom .net application, and display
> them in my own application, building up my own URLs to display them.
> However, when I link from one report (by clicking on an an image) it
> calls the second report. But this is done within the report manager
> and as such I don't get the option to append my own parameters to the
> query string - namely the &rs:ParameterLanguage=en-ie part that I need.
> Can anyone tell me how I might overcome this? I tried adding it as a
> parameter in the Image Properties - Navigation - Hyperlink action -
> Parameters but got the following error
> ...Parameter names must be CLS-compliant identifiers.
> I'd appreciate a response from someone from Microsoft on this as it's a
> known issue. How do I get around this bug in Reporting Services'
>|||Thanks Bruce!
Exactly what I was looking for. One slight problem... As I said, I'm
running these reports from a .net app. Is there anyway of reading the
URl of the web server from my web.config for my .net app'|||Use the global variable for this. Here is an example of a working jump to
url expression of mine:
=Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
First(Fields!manifstdocno.Value, "LoadID") &"&WasteIDNum=" &
First(Fields!wasteidnum.Value, "LoadID")
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:1102529644.706986.86770@.z14g2000cwz.googlegroups.com...
> Thanks Bruce!
> Exactly what I was looking for. One slight problem... As I said, I'm
> running these reports from a .net app. Is there anyway of reading the
> URl of the web server from my web.config for my .net app'
>|||brilliant - thanks a million for that Bruce!! Problem sorted.|||I have something to share with everyone for this problem.
If you are using Jump to Report method then to solve this problem simply
format the date you are passing to linked report as "dd-MMM-yyyy" in
paraemeters window.
This worked for me.
Cheers,
Harry
"gearoid_healy@.yahoo.com" wrote:
> I have a report which is based on a start and end date that are passed
> as parameters at run time. This works fine. However, in the report is
> a link to another report, which needs to run based on the same date
> parameters. However, the following happens...
> Report 1 runs fine with date parameters and report is generated.
> When report 2 is selected the same dates are passed, but the date
> format is changed from dd/mm/yyyy to mm/dd/yyyy. And as a result,
> report 2 will either run with the wrong dates, or it will crash and not
> run at all if swapping month and day gives an invalid date.
> I have my report languages set to default so it should pick up my
> regional setting ok. But when passing these dates between reports the
> settings seem to be lost somehow.
> Can anyone help'
>

Passing a selected row column value to the stored procedure

I have a simple Gridview control that has a delete command link on it.

If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...

The grid
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"
ReadOnly="True" SortExpression="member_id" />
<asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />
<asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />
<asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />
<asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"
SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"
DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure"
OldValuesParameterFormatString="original_{0}"
>

<DeleteParameters>

<asp:Parameter Name="member_id" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource
the SP

CREATE PROCEDURE renMemberDelete
@.member_id as int
As UPDATE [renMembers]
SET member_status=1
WHERE [member_id] = @.member_id
GO

Try:GridView2.DataKeyNames="member_id"

or

<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2" DataKeyNames="member_id">

Passing a parameter from a query in a link to another report

Hi,
I am trying to link to reports together, I want to be able to pass a ID
number returned from a query in this link. THe link currently looks
like this:
http://offsite/ReportServer?/LondonResults/Detailed Report by
LEA&LEA=(Fields!IDNumber.Value)&rs:Parameters=true
But in my parameter box in the second report the value I am getting is
(Fields!IDNumber.Value), when really I wan to see an ID number.
ANy ideas?
StephenI'm not exactly sure where you are entering it but if it is an expression it
should be something like:
="http://offsite/ReportServer?/LondonResults/Detailed Report by LEA&LEA="
&(Fields!IDNumber.Value) & "&rs:Parameters=true"
Neil
"stephen.adams@.forvus.co.uk" wrote:
> Hi,
> I am trying to link to reports together, I want to be able to pass a ID
> number returned from a query in this link. THe link currently looks
> like this:
> http://offsite/ReportServer?/LondonResults/Detailed Report by
> LEA&LEA=(Fields!IDNumber.Value)&rs:Parameters=true
> But in my parameter box in the second report the value I am getting is
> (Fields!IDNumber.Value), when really I wan to see an ID number.
> ANy ideas?
> Stephen
>

Pass value of report parameter from URL

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

You mean something like this? http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

It says it is for SQL (80), so I am not sure what problems carrying that over to 2005 will present.

Hope that helps.

|||well it looks like a step towards the solution, but it didn't really get me far...

the URL to my report looks like this:
http://reports.mycompany.com/Reports2005/Pages/Report.aspx?ItemPath=MyReport

in MyReport, I only have a textfield called "project" which I have to fill. When I put in a sample value "ABC", run the report and then take a look at the html code, I have something like this:

<tr IsParameterRow="true">
<td class="ParamLabelCell">
<span>project :</span>
</td>
<td class="ParamEntryCell" style="padding-right:0px;">
<span><input name="ctl137$ctl00$ctl03$ctl00" type="text" value="ABC" size="30" id="ctl137_ctl00_ctl03_ctl00" />
</span>
</td>

how can I pass the "ABC" text to this textfield over the URL (the report doesn't need to be run, I only want the textbox filled when I load the report for the first time!)|||still no idea?|||

I know it is kind of a late response, but your aspx page with the dropdown listbox should be able to pass the information. So, on the report page where you want the info, add a textbox and set the value to: Request.Form ("myfield") where myfield is the name of the dropdown box on the previous page.

The page the data is sent from will need to post to the report page I think.

Pass value of report parameter from URL

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

You mean something like this? http://msdn2.microsoft.com/en-us/library/aa256630(SQL.80).aspx

It says it is for SQL (80), so I am not sure what problems carrying that over to 2005 will present.

Hope that helps.

|||well it looks like a step towards the solution, but it didn't really get me far...

the URL to my report looks like this:
http://reports.mycompany.com/Reports2005/Pages/Report.aspx?ItemPath=MyReport

in MyReport, I only have a textfield called "project" which I have to fill. When I put in a sample value "ABC", run the report and then take a look at the html code, I have something like this:

<tr IsParameterRow="true">
<td class="ParamLabelCell">
<span>project :</span>
</td>
<td class="ParamEntryCell" style="padding-right:0px;">
<span><input name="ctl137$ctl00$ctl03$ctl00" type="text" value="ABC" size="30" id="ctl137_ctl00_ctl03_ctl00" />
</span>
</td>

how can I pass the "ABC" text to this textfield over the URL (the report doesn't need to be run, I only want the textbox filled when I load the report for the first time!)|||still no idea?|||

I know it is kind of a late response, but your aspx page with the dropdown listbox should be able to pass the information. So, on the report page where you want the info, add a textbox and set the value to: Request.Form ("myfield") where myfield is the name of the dropdown box on the previous page.

The page the data is sent from will need to post to the report page I think.

Partitions OLAP SQL 2005

Hi,
How I can improve particions on Analysis services?
Somebody can recommend me a link on this...?
Thanks,
Cecilia
Chile
My implementation is on SQL 2005
Thanks,
Cecilia
Chile
|||is your question more about "How to use partitions to improve performance?"
"Hoody" <Hoody.cu@.gmail.com> wrote in message
news:1163432116.400716.46580@.h48g2000cwc.googlegro ups.com...
> Hi,
> How I can improve particions on Analysis services?
>
> Somebody can recommend me a link on this...?
>
> Thanks,
> Cecilia
> Chile
>

Partitions OLAP SQL 2005

Hi,
How I can improve particions on Analysis services?
Somebody can recommend me a link on this...?
Thanks,
Cecilia
ChileMy implementation is on SQL 2005
Thanks,
Cecilia
Chile|||is your question more about "How to use partitions to improve performance?"
"Hoody" <Hoody.cu@.gmail.com> wrote in message
news:1163432116.400716.46580@.h48g2000cwc.googlegroups.com...
> Hi,
> How I can improve particions on Analysis services?
>
> Somebody can recommend me a link on this...?
>
> Thanks,
> Cecilia
> Chile
>

Partitions OLAP SQL 2005

Hi,
How I can improve particions on Analysis services?
Somebody can recommend me a link on this...?
Thanks,
Cecilia
ChileMy implementation is on SQL 2005
Thanks,
Cecilia
Chile|||is your question more about "How to use partitions to improve performance?"
"Hoody" <Hoody.cu@.gmail.com> wrote in message
news:1163432116.400716.46580@.h48g2000cwc.googlegroups.com...
> Hi,
> How I can improve particions on Analysis services?
>
> Somebody can recommend me a link on this...?
>
> Thanks,
> Cecilia
> Chile
>

Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1
DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1
|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/functions
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>
|||sys.partitions - will give the row-count for the table/individual parititons,
so that is not an issue, Im more interested in a join based query for all the
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>
|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
--F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual parititons,
> so that is not an issue, Im more interested in a join based query for all the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particular
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resides
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:

Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1|||Hi!

> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
Partitions do not map one-to-one to filegroups. You can have multiple
partitions on a single filegroup. You can find basic info about partitions
in sys.partitions view or with $PARTITION function, like
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
To find number of rows in different filegroups, the query gets complicated:
WITH
PartitionRowCount(Partition, NumberOfRowsInPartition) AS
(
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
),
PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
DataSpaceName) AS
(
SELECT dds.partition_scheme_id,
dds.destination_id,
dds.data_space_id,
ds.name
FROM sys.destination_data_spaces dds
INNER JOIN sys.indexes i
ON dds.partition_scheme_id = i.data_space_id
INNER JOIN sys.data_spaces ds
ON dds.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('PartitionTable')
AND i.index_id < 2
)
SELECT pds.DataSpaceName,
SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
FROM PartitionRowCount prc
INNER JOIN PartitionsDataSpaces pds
ON prc.Partition = pds.Partition
GROUP BY pds.DataSpaceName;
I hope I did not miss something.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
--
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]
')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links
to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/function
s
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>|||sys.partitions - will give the row-count for the table/individual parititons
,
so that is not an issue, Im more interested in a join based query for all th
e
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated
:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>|||> this does not list the filegroup names.
It lists data space names for filegroups. Data space names include filegroup
& partition scheme names.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
-- F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual paritito
ns,
> so that is not an issue, Im more interested in a join based query for all
the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particula
r
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resid
es
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:
>