April 21, 2008 at 2:34 pm
I have two reports...
the query from Rpt A has:
RowType Section Quantity
1 section Name ###
1 section Name ###
2 Total sum(quantity)
3 Available ###
(RowType is used to control which rows have drill-through enabled.)
Rpt B is set up where the Section is a multi-value parameter. There are no default values for this parameter.
When drilling through on the section (from RptA), the parameter sets the one section and runs RptB just fine.
What I'd like to do is enable the "Total" so that it will drill through to the report, passing all of the sections in RptA to RptB.
Optionally, have RptA somehow tell RptB to "Select All".
Any ideas on how to accomplish this?
Thanks!
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 21, 2008 at 3:09 pm
Alright so I got this to work but I'm not 100% confident it's the best answer. What I did:
1.) Created separate data set that returns comma delimited string of all values wishing to be passed to the drill through report
2.) Under the navigation settings of the 'Total' row used this comma delimited string as the value being passed to the drill through reports multi-value input parameter
This could be better if:
1.) You could use some inline function to create the comma delimited string inside the table.
1a.) I tried to use the Join() function to do this but it only appears to work with parameter values in the header
2.) Return the comma delimited string as an additional field in data set (would eliminate the need for another call to the DB)
I used the below stored proc (SQL 2005/8 only) to compose the comma delimited string of values from my table.
IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[usp_join]'),N'IsProcedure')=1
DROP PROCEDURE [dbo].[usp_join]
GO
CREATE PROCEDURE [dbo].[usp_join]
(
@p_table sysname
,@p_column sysname
,@p_delimiter char(1) = N ','
)
AS
BEGIN
-- Command to hold dynamic statement
DECLARE @v_cmd nvarchar(255);
-- Prepare command template
SELECT @v_cmd = N'SELECT SUBSTRING(
(SELECT N''%d%'' AS "*"
,[%c%] AS "*"
FROM [%t%]
FOR XML PATH(N''''))
,2
,16000); ' ;
-- Replace tokens with parameter values
SELECT @v_cmd = REPLACE(
REPLACE(
REPLACE(@v_cmd ,N'%t%',@p_table)
,N'%c%'
,@p_column)
,N'%d%'
,@p_delimiter);
-- Execute and check for errors
BEGIN TRY
exec sp_executesql @v_cmd;
END TRY
BEGIN CATCH
RAISERROR(N'Failed to execute "%s"',16,1,@v_cmd);
END CATCH
RETURN (1);
END
GO
If that's too much for you just try using the SQL it dynamically produces, eg...
SELECT
SUBSTRING(
(
SELECT
N',' AS "*"
, [FieldName] AS "*"
FROM
[TableName]
FOR
XML PATH(N'')
)
,2
,16000
)
Hope this helps!
Ben Sullins
bensullins.com
Beer is my primary key...
April 21, 2008 at 4:21 pm
I think I'm onto something, but I'm having a problem.
In RptA, I've added a hidden parameter: Sections (default="")
I then added some custom code:
Public Function SectionList(Section as String, ByRef Sections as String) As String
If (Section <> "Total") and (Section <> "Available")
Sections = Sections & IIF(Len(Sections)>0, ",", "") & Section
End If
Return Section
End Function
and for the section textbox in the table, I changed it from:
=Fields!Section.Value
to
=Code.SectionList(Fields!Section.Value, Parameters!Sections.Value)
The function seems to be working (if I change the return Section to return Sections I can see what it's doing),
BUT
the Sections parameter is not being updated. It's like it is read-only, but doesn't generate any write errors...
So, for each call, all Sections contains is the one Section passed to it.
So,
1. Can the report parameter be written to?
2. Can I have my own variable in the report?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2008 at 8:56 am
In case this can help anyone else, here's my solution
In the Report Properties | Code section:
Public Sections as String
Public Function SectionList(Section as String) As String
If (Section <> "Total") and (Section <> "Available")
If IsNothing(Sections)
Sections = Section
Else
Sections = Sections & "," & Section
End If
End If
Return Section
End Function
in the Textbox being used to Jump to the report, select Properties and go to the Navigation tab.
Click the Parameters box.
For the parameter, use the expression:
=IIF(Fields!RecordType.Value=1, Fields!Section.Value, Split(Code.Sections, ","))
For the value of the textbox, use the expression:
=Code.SectionList(Fields!Section.Value)
Explanation:
The report code sets a public string variable "Sections"
The SectionList function adds the current section to the public "Sections" variable, and returns the passed in Section, which is displayed in the table.
When you jump to the report, if you are on a section (RecordType = 1), then just the section value is passed.
If you are on the "Total" line, then the Sections variable is passed as a string array. Since this is being passed to a multi-value parameter, this works both ways.
HTH,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2008 at 9:08 am
Great Job!
Is your 'Total' line in the footer section of the table?
Ben Sullins
bensullins.com
Beer is my primary key...
April 22, 2008 at 9:16 am
Ben Sullins (4/22/2008)
Great Job!Is your 'Total' line in the footer section of the table?
Thanks!
The Total and Available lines are being returned by the query, so they end up being in the detail rows.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2008 at 9:30 am
Ah okay...I am going to play with this a little and see if i can make something a little more general for my use. Generally when I am developing reports I calculate the totals in the table footer, having the query just return the detail values.
Ben Sullins
bensullins.com
Beer is my primary key...
April 22, 2008 at 11:07 am
I generally do the totals that way also, but this report specified having an "Available" after the "Total". The Total does not include the available. It seemed easier to have the query return the data in this way.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply