Dynamically creating variable names

Ok, here it goes. I'll try and explain my situation as best as I can.
I would like to dynamically create variable names. We store our data in
monthly partitioned tables.......table_200501, table_200502...
I am creating a view that is a union of all of the partitioned tables. So
in order to do this I am creating a cursor that selects all tables from
information_schema where table_name like 'table_%'
Then once I have the table name I am creating a varchar that does my select
from the table that is passed into the cursor variable. In the end I will
have a varchar that looks like this
select....................from table_200501
union all
select...................from table_200502
Over time the varchar that I have created will run out of space since it can
only hold 8000 characters. So once the size of the varchar gets near 8000 I
would like to create a new variable named sqlstringn.........with n being
the next number available.
So just for sample I tried doing this but it wont work
declare @.counter int
set @.counter = 5
declare @.SQLString + @.counter as varchar(8000)
Obviously this doesn't work, but is there any other way to dynamically
create variable names?
Any help or suggestions are very much appreciated.
ThanksI did that once (dynamically creating variables), and it required creating
dynamic SQL within dynamic SQL.
You probably don't want that in a production application.
What you can do is to allocate, say three, variables and concatenate them on
execution. (ie: Execute (@.Var1 + @.Var2 + @.Var3) I know you are doing this
in a cursor, but the techinque should be the same.
I don't mean to stray, but is it possible to modify your design from
horizontal to vertical so you don't have to deal with so many tables?
"Andy" wrote:

> Ok, here it goes. I'll try and explain my situation as best as I can.
> I would like to dynamically create variable names. We store our data in
> monthly partitioned tables.......table_200501, table_200502...
> I am creating a view that is a union of all of the partitioned tables. So
> in order to do this I am creating a cursor that selects all tables from
> information_schema where table_name like 'table_%'
> Then once I have the table name I am creating a varchar that does my selec
t
> from the table that is passed into the cursor variable. In the end I will
> have a varchar that looks like this
> select....................from table_200501
> union all
> select...................from table_200502
> Over time the varchar that I have created will run out of space since it c
an
> only hold 8000 characters. So once the size of the varchar gets near 8000
I
> would like to create a new variable named sqlstringn.........with n bei
ng
> the next number available.
> So just for sample I tried doing this but it wont work
> declare @.counter int
> set @.counter = 5
> declare @.SQLString + @.counter as varchar(8000)
> Obviously this doesn't work, but is there any other way to dynamically
> create variable names?
> Any help or suggestions are very much appreciated.
> Thanks
>

Dynamic Visibility Problem

I think, first I better explain the characteristic of the report:
? It?s a report of categories and their items(or products)
? the report has a lot of information
? At first only appears the list of all categories(which fulfill all the requirements)
+ Category Nº1
+ Category Nº2
When I clicked, I?ll appear:
Category Nº1
P1
P2
Category Nº2
P2
P4
The problem is, that at first appears 18 pages instead of 1 page, and I don?t know why, I really hope that you can?t help me.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comSo all your drill down are expanded by default ?
"Rita Fonseca" <ritafonsecasa@.hotmail.com> wrote in message
news:f46b26c3-6b9f-4992-a716-b9cb7e34bc5d@.developmentnow.com...
>I think, first I better explain the characteristic of the report:
> . It's a report of categories and their items(or products)
> . the report has a lot of information
> . At first only appears the list of all categories(which fulfill all the
> requirements)
> + Category Nº1
> + Category Nº2
> When I clicked, I'll appear:
> Category Nº1
> P1
> P2
> Category Nº2
> P2
> P4
>
> The problem is, that at first appears 18 pages instead of 1 page, and I
> don't know why, I really hope that you can't help me.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Your "hidden" should be true then it will hide all the values and on toggle
it comes up.
Amarnath, MCTS
"Rita Fonseca" wrote:
> I think, first I better explain the characteristic of the report:
> â?¢ Itâ's a report of categories and their items(or products)
> â?¢ the report has a lot of information
> â?¢ At first only appears the list of all categories(which fulfill all the requirements)
> + Category Nº1
> + Category Nº2
> When I clicked, Iâ'll appear:
> Category Nº1
> P1
> P2
> Category Nº2
> P2
> P4
>
> The problem is, that at first appears 18 pages instead of 1 page, and I donâ't know why, I really hope that you canâ't help me.
>
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>|||I have found a kind of answer,
A put -> =FALSE , In the table visibility field
but now the problem is q goes has to leave everything in a single page
I try to used a function --Ceiling-- but doesn?t work, It doesn?t create a break page
From http://www.developmentnow.com/g/115_2007_3_0_0_949975/Dynamic-Visibility-Problem.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Re: Dynamic Visibility Problem Julien Bonnier
So all your drill down are expanded by default ?
Rpta:
No, not really only is the user wante
From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=115&threadid=94997
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Dynamic sub-reports with parameter passing?

Hello -

I wanted to know if we can have a fully dynamic sub-reports in CR. I will explain my scenario below..please let me know how i should proceed. I have two sub-reports which uses two different stored procedures to get the required data. Both the stored procedures will take three parameters each.

Sub-Report1: AIM.rpt which uses stored procedure "aim_sp"
Sub-Report1: IND.rpt which uses stored procedure "ind_sp"

aim_sp will take "start_dt","end_dt" and "unit" as parameters
ind_sp will take "start_dt","end_dt" and "unit" as parameters as well.

My requirement is to get the required parameters from the main report(main.rpt). These two sub-reports are shown using this blank report file. The only functionality of the main.rpt is to embed the two sub-reports and get the three parameters. And then, pass the parameters from main report to the two sub-reports. And then, the sub-reports will use the two sub-reports to run the stored procedures and get the data dynamically.

What i did??
----

I created the blank main.rpt file and created three parameters. And then i attached the sub reports to the main report. The sub-reports are themselves seperate report files which have stored procedure as database fields. But when i refresh the main report, it is totally looking for nine parameters. Three are from the main report file. Next three are from the stored procedure "aim_sp" and the next are from "ind_sp".

How can we link the main report parameters to all the sub-report parameters in the back ground and let it ask only three parameters?

Please advice me...
THanks,Right-click on subreport whilst its embedded on main report, select Change Subreport Links option. I will use date fields as an example.
Select the ? fields (paramater fields) from the "Available Fields" list, and when you add them to "Fields To Link To", you should be able to link them to a data field in your subreport. That's provided you have a date field in your subreport...you'll need one, otherwise you can't do the selection. If you don't want to show the date field in the subreport, suppress it, it will still be evaluated against.
Then in subreport's Report menu, select Edit selection Formula and select Record... option.
formula would look something like this:
{Database.DateField} >={?dteStart} AND {Database.DateField} <={?dteEnd}

That should work, from what I can recall.

dave