Grouping order conditionally?!

Hi,
I have a matrix with some groups and data.
I am passing a parameter called "theSort" to the report which is either "T"
or "C"
Two groups are TITLE and COUNTRY
If "T" is passed I would like the matrix to put TITLE first (in the first
column in matrix) and then COUNTRY after it. If "C" is passed then vice versa.
Is this possible' It didn't seem to work when I tried the following:
In the matrix's group tab, I put expressions for each group like so...
For Title group i put:
=IIF(Parameters!theSort Is "T",Fields!Title.Value,Fields!Country.Value)
For Country group i did:
=IIF(Parameters!theSort Is "T",Fields!Country.Value,Fields!Title.Value)
Didnt work.
Any help appreciated.
regards
KSTry =IIF(Parameters!theSort.Value ="T",Fields!Title.Value,Fields!Country.Value)
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"saleek" <saleek@.discussions.microsoft.com> wrote in message
news:7787C07F-EF50-4E21-83C8-EA2E37DF3531@.microsoft.com...
> Hi,
> I have a matrix with some groups and data.
> I am passing a parameter called "theSort" to the report which is either
"T"
> or "C"
> Two groups are TITLE and COUNTRY
> If "T" is passed I would like the matrix to put TITLE first (in the first
> column in matrix) and then COUNTRY after it. If "C" is passed then vice
versa.
> Is this possible' It didn't seem to work when I tried the following:
> In the matrix's group tab, I put expressions for each group like so...
>
> For Title group i put:
> =IIF(Parameters!theSort Is "T",Fields!Title.Value,Fields!Country.Value)
> For Country group i did:
> =IIF(Parameters!theSort Is "T",Fields!Country.Value,Fields!Title.Value)
> Didnt work.
> Any help appreciated.
> regards
> KS

Group Total..

I'm working on a Financial Report which contains a column "XYZ" , its value
is calculated from a formula by passing the row's record id and commission
rate. (the formula is inside a custom dll). The values are correctly
computed. Now, the footer should display the total of all the rows in the
group.
for instance:
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
Pepsi $361,000 gfyeefyefffee
Coca Cola $475,250 djfdfjdfddddd
RCola $28,757 re8reruejreerr
fdfsfnfsfssf
_________________________________________
Total: $ 865,007
Each of the "XYZ Total" in the above example, uses an expression as = FindTotal(recID!value, comm_rate!value)
In this case, how do I get the total in the footer? How to recursively add
the FindTotal expression when it contains the row's unique record id?
Thanks
P.S. The above data is a sample data. The actual report contains 3 different
levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
Transaction TitleI recently came across a new software, that I think you might want to look into.
www.simx.com/simx/home_report%20manager.htm
Works with SQL Server, and I was able to do reporting much like what you are describing.
"newmem" <"" wrote:
> I'm working on a Financial Report which contains a column "XYZ" , its value
> is calculated from a formula by passing the row's record id and commission
> rate. (the formula is inside a custom dll). The values are correctly
> computed. Now, the footer should display the total of all the rows in the
> group.
> for instance:
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> Pepsi $361,000 gfyeefyefffee
> Coca Cola $475,250 djfdfjdfddddd
> RCola $28,757 re8reruejreerr
> fdfsfnfsfssf
> _________________________________________
> Total: $ 865,007
> Each of the "XYZ Total" in the above example, uses an expression as => FindTotal(recID!value, comm_rate!value)
> In this case, how do I get the total in the footer? How to recursively add
> the FindTotal expression when it contains the row's unique record id?
> Thanks
> P.S. The above data is a sample data. The actual report contains 3 different
> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> Transaction Title
>
>|||While I appreciate your eagerness to help, I think that most people would
prefer that you refrain from advertising other products in a forum dedicated
to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
not to post there. :)
That being said, you should be able to define a custom field that does the
calculation and then referce the custom field in a sum in the group footer.
Presumably, you only need to add values from the inner group as the outer
group is just summary. If you want it to do parent / child hierarcy
aggregates, you need to use the recursive keyword.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>I recently came across a new software, that I think you might want to look
>into.
> www.simx.com/simx/home_report%20manager.htm
> Works with SQL Server, and I was able to do reporting much like what you
> are describing.
> "newmem" <"" wrote:
>> I'm working on a Financial Report which contains a column "XYZ" , its
>> value
>> is calculated from a formula by passing the row's record id and
>> commission
>> rate. (the formula is inside a custom dll). The values are correctly
>> computed. Now, the footer should display the total of all the rows in the
>> group.
>> for instance:
>> "Unit" "BrandName" "XYZ Total" "Comments"
>> Sodas
>> Pepsi $361,000 gfyeefyefffee
>> Coca Cola $475,250 djfdfjdfddddd
>> RCola $28,757 re8reruejreerr
>> fdfsfnfsfssf
>> _________________________________________
>> Total: $ 865,007
>> Each of the "XYZ Total" in the above example, uses an expression as =>> FindTotal(recID!value, comm_rate!value)
>> In this case, how do I get the total in the footer? How to recursively
>> add
>> the FindTotal expression when it contains the row's unique record id?
>> Thanks
>> P.S. The above data is a sample data. The actual report contains 3
>> different
>> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> Transaction Title
>>|||Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as => >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>

Gridview & SQL data source

I have a few Stored procedures that return values, some need parameters passing and others don't.

Up to now to access that data in a Web App I have called this procedure using VB after seting up a data block etc.

However I notice that the Web Developer express edition has some tools that look like they should help. I have used the in-built tools to create a SQL data source linked to a details view. I then specified that this should connect to my database (sql2005 hosted) and then specified Custom SQL or Stored procedure.

From the drop down I can then select the SP that purely returns values. When I try to test this query I get an error to say that the query did not return any data tables.

Is there a way to get values returned from a SP in this way?

Regards

Clive

You can use events like (Updating, Updated, Selecting, Selected etc) to do your cutomization. For example, if your method returns an output parameter then:

Sub OnDSUpdatedHandler(ByVal sourceAs Object,ByVal eAs SqlDataSourceStatusEventArgs)If e.AffectedRows > 0Then' Perform any additional processing, such as setting a status label. Label1.Text = Request.LogonUserIdentity.Name & _" changed user information sucessfully!" dim outputvalue asString outputvalue = e.Command.Parameters("yourparamname").Value.ToString()Else Label1.Text ="No data updated!"End If End Sub'OnDSUpdatedHandler

As you can see, I haven't done any kind of error checking. Please ensure that your code does. If you need more help, reply to the post explaining the problem.

|||

I think my problem is earlier than this stage to be honest.

For instance I have a SQL 2005 Stored Procedure that returns values from a table I can run this in SQL server itself and it works fine.

When definign this SP as a datasource using the detailsview object in Visual Web Developer I get the error I mentioend above when runnign the test option while I am stil in the wizard. So it looks liek some problem with callign the SP, yet it works OK natively.

|||

DetailsView, FormView, Gridview etc controls are designed to show a record/records. So, if you are using a SqlDataSource that just returns a single value, you cannot bind that to abovementioned controls. Can you explain why do you need to have a Detailsview that shows only single value?

Whenever asking a question, please add as much info as possible. Otherwise, we have to guess other parts (as you can see from my previous post) which is obviously not what you are looking for. Provide more info about your problem/scenario.

|||

I have a stored procedure that takes no paramteres but returns some values which are calculated from a number of tables to returns some summary results. if I execute the SP from SQL manager I get 5 values returned which appear as output parameters.

I was hoping to be able to link these quickly and easily into a web page using a SQL data source linking to this SP as it's datasource.

I was hoping that the details view would then display the values returned.

There maybe an easier or better way to do this. I am a beginner with all this, and wanted to explore how to use a stored procedure as a datasource.

Regards

Clive

|||

If the stored procedure returns resultset (table with columns and rows) then it would show nicely in the DetailsView, GridView etc. I don't think these controls have builtin ability to look for output parameters and bind those values. I think you should do this programmatically.

Connect to db, run the storedproc, get the results. Then you can either manually populate the values in controls or you can create a collection of values and bind it to a dataaware control. If you need sample code, then reply to the post mentioning your preference.