Creating a table

I have a recordset that consists of among other things a list of values and
dates over the next 12 months, I want to display these dates in a table
ie
Jan Feb Mar Apr May Jun etc
12 80 120 5 65 56 etc
The months are on a 12 month rolling table, how would i achieve this,
perhaps using a case statement or ?
thanks in anticipation
JohnYes, I would do that to. Due to the fact that there will be no more
month (names) in the future this should be an appropiate solution. If
the tables is VERY big and the values seldom change in that table and
the query time must be fast you should thing about (if they are
aggregatable) using AS for this. This could speed up your performance
and let you (with the help of a time dimension) give you the possibilty
to aggregate the values fast from different views.
HTH, jens Suessmeyer.|||Can you give me some pointers of how to acheive this please ?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1137772267.696338.62730@.g49g2000cwa.googlegroups.com...
> Yes, I would do that to. Due to the fact that there will be no more
> month (names) in the future this should be an appropiate solution. If
> the tables is VERY big and the values seldom change in that table and
> the query time must be fast you should thing about (if they are
> aggregatable) using AS for this. This could speed up your performance
> and let you (with the help of a time dimension) give you the possibilty
> to aggregate the values fast from different views.
> HTH, jens Suessmeyer.
>|||Sure, do you need information for AS or the setbased solution ? For the
set based solution it would be interesting to get some DDL and sample
data from your database. http://www.aspfaq.com/5006, otherwise a
generic solution would be
SELECT
CASE MONTH(DateColumn)
WHEN 1 THEN SUM(SomeValue) END As January (...)
HTH, jens Suessmeyer.

Creating a Page Index and table of contents

This is the problem I am facing with the SQL server reporting services,
I am trying to create a report where in we have to
display Page index and the table of contents Along with the Page
Number. This report contains the list of products under a subcategory
which in turn are under particular Categories. The Page Index should
display the products names in the alphabetical order with the page
number where It falls, this is similar to the appendix at the end of
any textbook and the table of contents display the category and
its subcategories with page numbers Now, the problem is reading the
report dynamically to find out the page numbers where this product
falls and the categories falls . I want a solution for displaying the
Page index and the table of contents in SQL server reporting services
2005 version.
Waiting for quick sujjestions or help in this regardAre you using web service approach?
If so,you can always get page content before displaying it and by analyzing
the underlying HTML get all information you need -
page number, total number of pages, any internal error occurred, etc. Based
on that information you can build your own page header with a custom page
index.
"Aparna" <aparna.cirigiri@.gmail.com> wrote in message
news:1135255357.206805.159890@.g44g2000cwa.googlegroups.com...
> This is the problem I am facing with the SQL server reporting services,
>
> I am trying to create a report where in we have to
> display Page index and the table of contents Along with the Page
> Number. This report contains the list of products under a subcategory
> which in turn are under particular Categories. The Page Index should
> display the products names in the alphabetical order with the page
> number where It falls, this is similar to the appendix at the end of
> any textbook and the table of contents display the category and
> its subcategories with page numbers Now, the problem is reading the
> report dynamically to find out the page numbers where this product
> falls and the categories falls . I want a solution for displaying the
> Page index and the table of contents in SQL server reporting services
> 2005 version.
> Waiting for quick sujjestions or help in this regard
>

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<