Can Matrix Control handle this? -- Revised Again and Again

I don't know how to solve this reporting services requirements using
Matrix control.
Can someone help me please?
1. The number of columns is unknown until run-time.
2. % Of Grand Total = Row Total / Grand Total
3. Display grouping of stores by city without using a separate
column, only padded by spaces.
4. Display sub-total for each city without using a separate column,
only padded by spaces.
Col1 Col2 Row Total % Of Grand Total
City1
Store1 11 12 23 10.85%
Store2 21 22 43 20.28%
Total 32 34 66 31.13%
City2
Store3 31 32 63 29.72%
Store4 41 42 83 39.15%
Total 72 74 146 68.87%
Grand Total 104 108 212 100.00%
Thanks a million!On Jan 31, 7:53 pm, bo_d...@.yahoo.com wrote:
> I don't know how to solve this reporting services requirements using
> Matrix control.
> Can someone help me please?
> 1. The number of columns is unknown until run-time.
> 2. % Of Grand Total = Row Total / Grand Total
> 3. Display grouping of stores by city without using a separate
> column, only padded by spaces.
> 4. Display sub-total for each city without using a separate column,
> only padded by spaces.
> Col1 Col2 Row Total % Of Grand Total
> City1
> Store1 11 12 23 10.85%
> Store2 21 22 43 20.28%
> Total 32 34 66 31.13%
> City2
> Store3 31 32 63 29.72%
> Store4 41 42 83 39.15%
> Total 72 74 146 68.87%
> Grand Total 104 108 212 100.00%
> Thanks a million!
Please refer to my previous response.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec85ea4fc96df4ac#72a05c6e103948d0
Regards,
Enrique Martinez
Sr. Software Consultant

Can Matrix Control handle this? -- Revised Again

I don't know how to solve this reporting services requirements using
Matrix control.
Can someone help me please?
1. The number of columns is unknown until run-time.
2. PctOfGrandTotal = RowTotal / GrandTotal
3. Display grouping of stores by city without using a separate
column,
only padded by spaces.
4. Display sub-total for each city without using a separate column,
only padded by spaces.
Col1 Col2 Col3 RowTotal
PctOfGrandTotal
City1
Store1 11 12 13 36 11.11%
Store2 21 22 23 66 20.37%
Total 32 34 36 102 31.48%
City2
Store3 31 32 33 96 29.63%
Store4 41 42 43 126 38.89%
Total 72 74 76 222 68.52%
GrandTotal 104 108 112 324 100.00%
Thanks a million!On Jan 31, 7:43 pm, bo_d...@.yahoo.com wrote:
> I don't know how to solve this reporting services requirements using
> Matrix control.
> Can someone help me please?
> 1. The number of columns is unknown until run-time.
> 2. PctOfGrandTotal = RowTotal / GrandTotal
> 3. Display grouping of stores by city without using a separate
> column,
> only padded by spaces.
> 4. Display sub-total for each city without using a separate column,
> only padded by spaces.
> Col1 Col2 Col3 RowTotal
> PctOfGrandTotal
> City1
> Store1 11 12 13 36 11.11%
> Store2 21 22 23 66 20.37%
> Total 32 34 36 102 31.48%
> City2
> Store3 31 32 33 96 29.63%
> Store4 41 42 43 126 38.89%
> Total 72 74 76 222 68.52%
> GrandTotal 104 108 112 324 100.00%
> Thanks a million!
Please refer to my previous response, since this question has been
posted multiple times.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec85ea4fc96df4ac#72a05c6e103948d0
Regards,
Enrique Martinez
Sr. Software Consultant

Can Matrix Control handle this? -- Revised

I don't know how to solve this reporting services requirements using
Matrix control.
Can someone help me please?
1. The number of columns is unknown until run-time.
2. % of Grand Total = Row Total / Grand Total
3. Display grouping of stores by city without using a separate
column,
only padded by spaces.
4. Display sub-total for each city without using a separate column,
only padded by spaces.
Column1 Column2 Column3 Row Total % of Grand Total
City1
Store1 11 12 13 36
11.11%
Store2 21 22 23 66
20.37%
Total 32 34 36 102
31.48%
City2
Store3 31 32 33 96
29.63%
Store4 41 42 43 126
38.89%
Total 72 74 76 222
68.52%
Grand Total 104 108 112 324
100.00%
Thanks a million!On Jan 31, 7:37 pm, bo_d...@.yahoo.com wrote:
> I don't know how to solve this reporting services requirements using
> Matrix control.
> Can someone help me please?
> 1. The number of columns is unknown until run-time.
> 2. % of Grand Total = Row Total / Grand Total
> 3. Display grouping of stores by city without using a separate
> column,
> only padded by spaces.
> 4. Display sub-total for each city without using a separate column,
> only padded by spaces.
> Column1 Column2 Column3 Row Total % of Grand Total
> City1
> Store1 11 12 13 36
> 11.11%
> Store2 21 22 23 66
> 20.37%
> Total 32 34 36 102
> 31.48%
> City2
> Store3 31 32 33 96
> 29.63%
> Store4 41 42 43 126
> 38.89%
> Total 72 74 76 222
> 68.52%
> Grand Total 104 108 112 324
> 100.00%
> Thanks a million!
Please refer to my previous response.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec85ea4fc96df4ac#72a05c6e103948d0
Regards,
Enrique Martinez
Sr. Software Consultant

Can Matrix Control handle this?

I don't know how to solve this reporting services requirements using
Matrix control.
Can someone help me please?
1. The number of columns is unknown until run-time.
2. % of Grand Total = Row Total / Grand Total
3. Display grouping of stores by city without using a separate column,
only padded by spaces.
4. Display sub-total for each city without using a separate column,
only padded by spaces.
Column1 Column2 Column3 Row Total % of
Grand Total
City1
Store1 11 12 13
36 11.11%
Store2 21 22 23
66 20.37%
Total 32 34 36
102 31.48%
City2
Store3 31 32 33
96 29.63%
Store4 41 42 43
126 38.89%
Total 72 74 76
222 68.52%
Grand Total 104 108 112
324 100.00%
Thanks a million!On Jan 31, 7:27 pm, bo_d...@.yahoo.com wrote:
> I don't know how to solve this reporting services requirements using
> Matrix control.
> Can someone help me please?
> 1. The number of columns is unknown until run-time.
> 2. % of Grand Total = Row Total / Grand Total
> 3. Display grouping of stores by city without using a separate column,
> only padded by spaces.
> 4. Display sub-total for each city without using a separate column,
> only padded by spaces.
> Column1 Column2 Column3 Row Total % of
> Grand Total
> City1
> Store1 11 12 13
> 36 11.11%
> Store2 21 22 23
> 66 20.37%
> Total 32 34 36
> 102 31.48%
> City2
> Store3 31 32 33
> 96 29.63%
> Store4 41 42 43
> 126 38.89%
> Total 72 74 76
> 222 68.52%
> Grand Total 104 108 112
> 324 100.00%
> Thanks a million!
1. This is inherent with a matrix report.
2. Row Total can either be determined in the stored procedure/query
that is sourcing the report via a while loop or cursor -or- whatever
group comprises a row can be unioned as a select statement and summed.
I'm assuming that the Grand Total is the overall total in the report/
query; so, you can just sum the entire result -or- return the sum of
the entire result set to the report as a separate column with repeated
values and use an expression like the following to display it in the
report.
=Max(Fields!GrandTotal.Value)
Of course the % of Grand Total would be the division of the two of
these.
3. You might be able to create the City group and then move the extra
column expression in the same row into the same vertical column and
remove the new column.
4. You should be able to union the same query together in the stored
procedure/query that is sourcing the report and group by city. Then
include this data in the report; however, solve it in the same way as
for item 3.
In SQL Server 2008 SSRS, the introduction of the tablix control
(combination of table and matrix control) should resolve the work
around in Items 3 and 4 above.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

can I use the value of field in last record?

I handle a output by a function with three parameters

the first is a value of Field [a1] in current record,

the second is value of Field [a1] in last record,

the third is value of Field [a1] in next record,

is it possible in RS2005?

Yes you can use them in a given scope (table/list):

Fields!a1.Value

Last(Fields!a1.Value, "Dataset1")

Fields!a1.Value(RowNumber("Dataset1")+1)

Shyam

Can I make this code better using SQLParamater object?

I wrote a class to handle my sqlDataReaders:

Namespace CommonFunctions

PublicClass DataAccess

PublicSubNew()

EndSub

Private ConnStrAsString ="connectionString"

PublicFunction returnDR(ByVal strSQLAsString)As SqlClient.SqlDataReader

Dim drAs SqlClient.SqlDataReader

Dim myCnAsNew SqlClient.SqlConnection(ConnStr)

Dim myCmdAsNew SqlClient.SqlCommand(strSQL, myCn)

myCn.Open()

dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Return dr

EndFunction' returnDR

EndClass' Public Class DataAccess

EndNamespace' Namespace CommonFunction

The way I invoke this is:

Imports CommonFunctions

Dim daAsNew DataAccess

Dim sqlDataReaderAs SqlDataReader

Dim strSQLAsNew StringBuilder

strSQL.Append("Some SQL Query")

sqlDataReader = da.returnDR(strSQL.ToString())

DoWhile sqlDataReader.Read()

strVar = sqlDataReader("columnName")

Loop' Do While sqlDataReader.Read()

sqlDataReader.Close()

What I would like to do, is use the SQL Paramater Object instead of passing my method a String. One so that I dont have to explicitly test for SQL Injection and two, cause I have never worked with the SQL Paramater Object before. :)

J

Sure, put this in your class:

PublicFunction returnDR(ByVal cmd AS SqlCommand)As SqlClient.SqlDataReader

Dim drAs SqlClient.SqlDataReader

Dim myCnAsNew SqlClient.SqlConnection(ConnStr)
mycmd.connection=myCn
myCn.Open()

dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Return dr

EndFunction' returnDR

Then you can pass returnDR either a SQLString, or a SqlCommand object.

like this:

dim cmd as new SqlCommand("SELECT * FROM MyTable WHERE ID=@.ID")
cmd.parameters.add("@.ID",sqdbtype.int).value={Some ID}
sqlDataReader = da.returnDR(cmd)

and of course:
sqlDataReader=da.returnDR("SELECT * FROM MyTable WHERE ID=" & {Some ID})

will continue to work as well. That way you can move forward with using SqlCommand's for new queries, and covert the old queries when you have time.

You can then also change your old function to:

PublicFunction returnDR(ByVal sqlcmd AS String)As SqlClient.SqlDataReader
dim cmd as new sqlCommand(sqlcmd)
return returnDR(cmd)
End Function