I have the following script where the select parameters is dependent on the "component" parameter list being send via the stored proc. Therefore the number of parameters returned by the proc is dynamic. These fields therefore do not show in the .Net design to be added to the report. Need some advise please.
CREATE PROCEDURE dbo.prcPerfAnalysisComponentTest
( @.Category varchar(50)
, @.Item varchar(50)
, @.Component1 varchar(250)
, @.Component2 varchar(250) = NULL
, @.Component3 varchar(250) = NULL
, @.Component4 varchar(250) = NULL
, @.Component5 varchar(250) = NULL
, @.Component6 varchar(250) = NULL
, @.Component7 varchar(250) = NULL
, @.Component8 varchar(250) = NULL
, @.Component9 varchar(250) = NULL
, @.Component10 varchar(250) = NULL
, @.DOW char(13) = NULL
, @.StartTime char(8) = NULL -- Eg. '00:00:00'
, @.EndTime char(8) = NULL -- Eg. '23:59:59'
, @.GroupInd char(7) = 'DAY' ) -- DAY, WEEK, MONTH, YEAR,
QUARTER
AS
DECLARE @.Command varchar(2000)
SELECT @.Command = 'SELECT ' +
CASE
WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeWeek))'
WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeMonth))'
WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeQuarter))'
WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear))'
ELSE 'LEFT(t.TimePortion,5)' --Default to daily
END
SELECT @.Command = @.Command +
', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component1 + '"'
IF @.Component2 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component2 + '"'
IF @.Component3 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component3 + '"'
IF @.Component4 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component4 + '"'
IF @.Component5 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component5 + '"'
IF @.Component6 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component6 + '"'
IF @.Component7 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component7 + '"'
IF @.Component8 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component8 + '"'
IF @.Component9 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component9 + '"'
IF @.Component10 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component10 + '"'
SELECT @.Command = @.Command + ' FROM DataSN3 p ' +
'JOIN vwServerComponent sc ON sc.ServerComponentID = p.ServerComponentID AND sc.MonitorItemID = p.MonitorItemID ' +
'JOIN TimeLineSN3 t ON p.MonitorDate = t.TimeLineID WHERE sc.CategoryDescription = ''' + @.Category + ''' ' +
'AND sc.ItemDescription = ''' + @.Item + ''' '
IF @.DOW IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDOW IN (' + @.DOW + ')'
IF @.StartTime IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimePortion >= (''' + @.StartTime + ''')'
IF @.EndTime IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimePortion <= (''' + @.EndTime + ''')'
IF @.Start IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDate >= CONVERT(datetime, ''' + CONVERT(char(19), @.Start, 121) + ''', 121)'
IF @.End IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDate <= CONVERT(datetime, ''' + CONVERT(char(19), @.End, 121) + ''', 121)'
SELECT @.Command = @.Command + ' GROUP BY ' +
CASE
WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeWeek)) '
WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeMonth)) '
WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeQuarter)) '
WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear)) '
ELSE 'LEFT(t.TimePortion,5) ' --Default to daily
END +
' ORDER BY 1'
EXECUTE (@.Command)You don't have to have the fields showing in the Designer to use them. You
can set the source of the textboxes to =Fields!<your field name>.Value. You
can also use the IsMissing property of the Field object to determine if the
stored procedure has returned the field or not. Finally, please not that
multiple select statements are not supported in RS; only the first is used.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Frans-sa" <Frans-sa@.discussions.microsoft.com> wrote in message
news:B7AB19DC-FB72-43C9-9EF6-665BA46F4CA2@.microsoft.com...
> I have the following script where the select parameters is dependent on
the "component" parameter list being send via the stored proc. Therefore
the number of parameters returned by the proc is dynamic. These fields
therefore do not show in the .Net design to be added to the report. Need
some advise please.
>
> CREATE PROCEDURE dbo.prcPerfAnalysisComponentTest
> ( @.Category varchar(50)
> , @.Item varchar(50)
> , @.Component1 varchar(250)
> , @.Component2 varchar(250) = NULL
> , @.Component3 varchar(250) = NULL
> , @.Component4 varchar(250) = NULL
> , @.Component5 varchar(250) = NULL
> , @.Component6 varchar(250) = NULL
> , @.Component7 varchar(250) = NULL
> , @.Component8 varchar(250) = NULL
> , @.Component9 varchar(250) = NULL
> , @.Component10 varchar(250) = NULL
> , @.DOW char(13) = NULL
> , @.StartTime char(8) = NULL -- Eg. '00:00:00'
> , @.EndTime char(8) = NULL -- Eg. '23:59:59'
> , @.GroupInd char(7) = 'DAY' ) -- DAY, WEEK, MONTH, YEAR,
> QUARTER
> AS
> DECLARE @.Command varchar(2000)
> SELECT @.Command = 'SELECT ' +
> CASE
> WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeWeek))'
> WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeMonth))'
> WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeQuarter))'
> WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear))'
> ELSE 'LEFT(t.TimePortion,5)' --Default to daily
> END
> SELECT @.Command = @.Command +
> ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1
ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' +
@.Component1 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN
sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) END AS "' +
@.Component1 + '"'
> IF @.Component2 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component2 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component2 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component2 + '"'
> IF @.Component3 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component3 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component3 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component3 + '"'
> IF @.Component4 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component4 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component4 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component4 + '"'
> IF @.Component5 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component5 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component5 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component5 + '"'
> IF @.Component6 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component6 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component6 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component6 + '"'
> IF @.Component7 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component7 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component7 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component7 + '"'
> IF @.Component8 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component8 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component8 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component8 + '"'
> IF @.Component9 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component9 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component9 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component9 + '"'
> IF @.Component10 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component10 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component10 + ''' THEN p.MonitorValue ELSE 0 END)
/ SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0
END) END AS "' + @.Component10 + '"'
> SELECT @.Command = @.Command + ' FROM DataSN3 p ' +
> 'JOIN vwServerComponent sc ON sc.ServerComponentID = p.ServerComponentID
AND sc.MonitorItemID = p.MonitorItemID ' +
> 'JOIN TimeLineSN3 t ON p.MonitorDate = t.TimeLineID WHERE
sc.CategoryDescription = ''' + @.Category + ''' ' +
> 'AND sc.ItemDescription = ''' + @.Item + ''' '
> IF @.DOW IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDOW IN (' + @.DOW + ')'
> IF @.StartTime IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimePortion >= (''' + @.StartTime +
''')'
> IF @.EndTime IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimePortion <= (''' + @.EndTime +
''')'
> IF @.Start IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDate >= CONVERT(datetime, ''' +
CONVERT(char(19), @.Start, 121) + ''', 121)'
> IF @.End IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDate <= CONVERT(datetime, ''' +
CONVERT(char(19), @.End, 121) + ''', 121)'
> SELECT @.Command = @.Command + ' GROUP BY ' +
> CASE
> WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeWeek)) '
> WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeMonth)) '
> WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeQuarter)) '
> WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear)) '
> ELSE 'LEFT(t.TimePortion,5) ' --Default to daily
> END +
> ' ORDER BY 1'
> EXECUTE (@.Command)
>
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Thursday, March 29, 2012
Dynamic Scripts
I've found that SSIS does not work well with running small dynamic scripts ...
For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.
The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.
Does anybody else do this, or have a better way of doing it?
I would be inclined to use:
package variables for my input values;|||I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?
Approach 1:
1, use an Execute SQL task the define an package variable
select last_modified_date as variable1
from table
2, use another Execute SQL task to define second package variable(variable2)
select * from sourceTable
where last_modified_date > ? --variable1
3, use variable2 in OLE db source in a data flow to get source data
Approach 2:
1, 1, use an Execute SQL task with dynimac sql to define an package variable
select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL
from table
2, use the variable (variable_SQL) to get source data in OLE db source in a data flow
|||
Jessie,
Why is this not working for you? Do you get an error message?
-Jamie
|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||This may work for you.
Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]
Look at the resulting value. The query and the date should now be together.
You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.
cheers,
Andrew
|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.Dynamic Scripts
I've found that SSIS does not work well with running small dynamic scripts ...
For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.
The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.
Does anybody else do this, or have a better way of doing it?
I would be inclined to use:
package variables for my input values;|||I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?
Approach 1:
1, use an Execute SQL task the define an package variable
select last_modified_date as variable1
from table
2, use another Execute SQL task to define second package variable(variable2)
select * from sourceTable
where last_modified_date > ? --variable1
3, use variable2 in OLE db source in a data flow to get source data
Approach 2:
1, 1, use an Execute SQL task with dynimac sql to define an package variable
select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL
from table
2, use the variable (variable_SQL) to get source data in OLE db source in a data flow
|||
Jessie,
Why is this not working for you? Do you get an error message?
-Jamie
|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||This may work for you.
Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]
Look at the resulting value. The query and the date should now be together.
You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.
cheers,
Andrew
|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.Dynamic Scripts
I've found that SSIS does not work well with running small dynamic scripts ...
For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.
The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.
Does anybody else do this, or have a better way of doing it?
I would be inclined to use:
package variables for my input values;|||I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?
Approach 1:
1, use an Execute SQL task the define an package variable
select last_modified_date as variable1
from table
2, use another Execute SQL task to define second package variable(variable2)
select * from sourceTable
where last_modified_date > ? --variable1
3, use variable2 in OLE db source in a data flow to get source data
Approach 2:
1, 1, use an Execute SQL task with dynimac sql to define an package variable
select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL
from table
2, use the variable (variable_SQL) to get source data in OLE db source in a data flow
|||
Jessie,
Why is this not working for you? Do you get an error message?
-Jamie
|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||This may work for you.
Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]
Look at the resulting value. The query and the date should now be together.
You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.
cheers,
Andrew
|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.sqlWednesday, March 21, 2012
Dynamic Linking - Crystal XI - Image doesnt change
Im using desktop Crystal XI - connecting to a SQL database via ODBC connection.
Here is the problem: I would like to display a different excel file with each record in the report using dynamic linking.
Each record has a related document (xls) that is stored on a different server than the database. I have access to the folder on the server that contains all of the documents (I connect to this server by mapping a drive via windows explorer). I can manually add a document from the folder but the document doesnt change for each record, it just repeats the document that was added manually.
Ive created a formula field (based on records in the SQL db) to generate the link/document location:
"E:\LCM-" + cstr({mwebDocument.Doc_Ent_ID}, 0, "") + "\" + ({mwebDocument.Doc_File_Name}) + "_" + cstr({mwebDocument.Doc_ID}, 0, "") + "_1.xls"
(example return: E:\LCM-289\KC 2554 Testing Backup NOPAs_9609_1.xls)
I then reference the formula field in the graphic location.
So I know I can access the server/files since I can manually add a file.
I know that the file does display because the manually added file displays for each record (even though it doesnt change).
I know that the link is changing for each record because Ive made it a separate field and can verify its correctness.
Can anyone think of a reason why the object/file is not changing for each record?
Appreciate the help.Why don't u use the formula directly in detail section as a hyperlink..|||Thanks for the response - The major reason for not using a hyperlink is that we would like to distribute this report to people outside of our company (who wouldn't have access to the server/files). This was the initial reason to display the file in the report.
Here is the problem: I would like to display a different excel file with each record in the report using dynamic linking.
Each record has a related document (xls) that is stored on a different server than the database. I have access to the folder on the server that contains all of the documents (I connect to this server by mapping a drive via windows explorer). I can manually add a document from the folder but the document doesnt change for each record, it just repeats the document that was added manually.
Ive created a formula field (based on records in the SQL db) to generate the link/document location:
"E:\LCM-" + cstr({mwebDocument.Doc_Ent_ID}, 0, "") + "\" + ({mwebDocument.Doc_File_Name}) + "_" + cstr({mwebDocument.Doc_ID}, 0, "") + "_1.xls"
(example return: E:\LCM-289\KC 2554 Testing Backup NOPAs_9609_1.xls)
I then reference the formula field in the graphic location.
So I know I can access the server/files since I can manually add a file.
I know that the file does display because the manually added file displays for each record (even though it doesnt change).
I know that the link is changing for each record because Ive made it a separate field and can verify its correctness.
Can anyone think of a reason why the object/file is not changing for each record?
Appreciate the help.Why don't u use the formula directly in detail section as a hyperlink..|||Thanks for the response - The major reason for not using a hyperlink is that we would like to distribute this report to people outside of our company (who wouldn't have access to the server/files). This was the initial reason to display the file in the report.
Monday, March 19, 2012
dynamic grouping
Hi,
I have a report with grouping. I am passing a parameter via asp.net and if
that parameter @.paramrep=7 then i want to group by type and by region
otherwise i want to group only by region.
I have looked at Chris Hay's example but I am still not sure i do get it to
work as I keep on getting an error: the expression referenced a non-existing
field in the fields collection.
This is my expression:
=iif(Parameters!Paramreport.Value
=7,1,Fields(iif(Parameters!Paramreport.Value =7,
"region",Parameters!Paramreport.Value)).Value)
I am not sure what I am doing wrong as basically I didn't really understand
the article.
I would appreciate any help offered.
ThanksWhile I haven't seen the example you mention, I often use functions for
dynamic grouping ie...( my syntax here might be bad...)
Public function GetGroups(Byref Groupid as integer, Byref Paramval as
Integer) as String
Switch Paramval
Case 7 If Groupid = 1 Then
Return("Price")
Else Return("Productname")
End IF
Case Else If Groupid = 1 Then
Return("Othercol1")
Else Return("Othercol2")
End IF
End
End
THen in the Grouping dialog
=Fields(GetGroups(1,Parameters!Parametername.Value)).Value
=Fields(GetGroups(2,Parameters!Parametername.Value)).Value
Hope this helps... By the way, sometimes I have to dynamically change the
sort to match the grouping at the highest level, otherwise I have gotten an
error...But the sort is handled the same way..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"collie" <collie@.discussions.microsoft.com> wrote in message
news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> Hi,
> I have a report with grouping. I am passing a parameter via asp.net and if
> that parameter @.paramrep=7 then i want to group by type and by region
> otherwise i want to group only by region.
> I have looked at Chris Hay's example but I am still not sure i do get it
to
> work as I keep on getting an error: the expression referenced a
non-existing
> field in the fields collection.
> This is my expression:
> =iif(Parameters!Paramreport.Value
> =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> "region",Parameters!Paramreport.Value)).Value)
> I am not sure what I am doing wrong as basically I didn't really
understand
> the article.
> I would appreciate any help offered.
> Thanks|||Hi,
Thanks so much for your response.
I have a few questions about your code if you could please clarify (I feel
stupid for asking but...)
Ok here goes :-)
What do you mean by groupid such as groupid=1?
What is price? A field name to group by if groupid=1?
Parmetername in my case would be the parameter that i send from asp.net
@.paramrep=7 correct?
Thanks
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>|||Wayne your code was a great help.
Thanks :-)
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>
I have a report with grouping. I am passing a parameter via asp.net and if
that parameter @.paramrep=7 then i want to group by type and by region
otherwise i want to group only by region.
I have looked at Chris Hay's example but I am still not sure i do get it to
work as I keep on getting an error: the expression referenced a non-existing
field in the fields collection.
This is my expression:
=iif(Parameters!Paramreport.Value
=7,1,Fields(iif(Parameters!Paramreport.Value =7,
"region",Parameters!Paramreport.Value)).Value)
I am not sure what I am doing wrong as basically I didn't really understand
the article.
I would appreciate any help offered.
ThanksWhile I haven't seen the example you mention, I often use functions for
dynamic grouping ie...( my syntax here might be bad...)
Public function GetGroups(Byref Groupid as integer, Byref Paramval as
Integer) as String
Switch Paramval
Case 7 If Groupid = 1 Then
Return("Price")
Else Return("Productname")
End IF
Case Else If Groupid = 1 Then
Return("Othercol1")
Else Return("Othercol2")
End IF
End
End
THen in the Grouping dialog
=Fields(GetGroups(1,Parameters!Parametername.Value)).Value
=Fields(GetGroups(2,Parameters!Parametername.Value)).Value
Hope this helps... By the way, sometimes I have to dynamically change the
sort to match the grouping at the highest level, otherwise I have gotten an
error...But the sort is handled the same way..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"collie" <collie@.discussions.microsoft.com> wrote in message
news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> Hi,
> I have a report with grouping. I am passing a parameter via asp.net and if
> that parameter @.paramrep=7 then i want to group by type and by region
> otherwise i want to group only by region.
> I have looked at Chris Hay's example but I am still not sure i do get it
to
> work as I keep on getting an error: the expression referenced a
non-existing
> field in the fields collection.
> This is my expression:
> =iif(Parameters!Paramreport.Value
> =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> "region",Parameters!Paramreport.Value)).Value)
> I am not sure what I am doing wrong as basically I didn't really
understand
> the article.
> I would appreciate any help offered.
> Thanks|||Hi,
Thanks so much for your response.
I have a few questions about your code if you could please clarify (I feel
stupid for asking but...)
Ok here goes :-)
What do you mean by groupid such as groupid=1?
What is price? A field name to group by if groupid=1?
Parmetername in my case would be the parameter that i send from asp.net
@.paramrep=7 correct?
Thanks
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>|||Wayne your code was a great help.
Thanks :-)
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>
Dynamic Graphs
I want let the end user decide on which type of graphic(via a report
parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
couldn't find any option to define the chart type e.g. with the aid of an
expression. Do I have to place any possible chart type in my report layout
and control the appearance by defining an expression for the 'visibility'
property?Niklas,
You can't do it with an expression. Instead, the option I would go for is
building a custom app to get the user selection and update the report RDL.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> I want let the end user decide on which type of graphic(via a report
> parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
> couldn't find any option to define the chart type e.g. with the aid of an
> expression. Do I have to place any possible chart type in my report layout
> and control the appearance by defining an expression for the 'visibility'
> property?|||Thanks Teo,
I often see answers, dealing with generating the RDL dynamically. Please
correct me, if I'm wrong, but I don't think this is a solution in a
multi-user environment. I need fixed RDL files, guaranteeing the same
behaviour. So, I cannot change the RDL, every time a user wants to access the
report?!?!
"Teo Lachev [MVP]" wrote:
> Niklas,
> You can't do it with an expression. Instead, the option I would go for is
> building a custom app to get the user selection and update the report RDL.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> > I want let the end user decide on which type of graphic(via a report
> > parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
> > couldn't find any option to define the chart type e.g. with the aid of an
> > expression. Do I have to place any possible chart type in my report layout
> > and control the appearance by defining an expression for the 'visibility'
> > property?
>
>|||Niklas,
Not really. You basically need to generated the report RDL and upload it to
the report catalog under an unique name, e.g. the user logon id. The way I
addressed this on a similar occasion in the past was to have the report
template RDL file included in my web application. My web app will load the
template, change it as needed and upload it to the report catalog. Then, it
will render the report. Finally, when the user logs out, the web app will
purge the report.
Does this make sense?
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:E1B5C271-5C8A-4C15-9C75-615ADFFF1678@.microsoft.com...
> Thanks Teo,
> I often see answers, dealing with generating the RDL dynamically. Please
> correct me, if I'm wrong, but I don't think this is a solution in a
> multi-user environment. I need fixed RDL files, guaranteeing the same
> behaviour. So, I cannot change the RDL, every time a user wants to access
the
> report?!?!
> "Teo Lachev [MVP]" wrote:
> > Niklas,
> >
> > You can't do it with an expression. Instead, the option I would go for
is
> > building a custom app to get the user selection and update the report
RDL.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> > news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> > > I want let the end user decide on which type of graphic(via a report
> > > parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.)
I
> > > couldn't find any option to define the chart type e.g. with the aid of
an
> > > expression. Do I have to place any possible chart type in my report
layout
> > > and control the appearance by defining an expression for the
'visibility'
> > > property?
> >
> >
> >
parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
couldn't find any option to define the chart type e.g. with the aid of an
expression. Do I have to place any possible chart type in my report layout
and control the appearance by defining an expression for the 'visibility'
property?Niklas,
You can't do it with an expression. Instead, the option I would go for is
building a custom app to get the user selection and update the report RDL.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> I want let the end user decide on which type of graphic(via a report
> parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
> couldn't find any option to define the chart type e.g. with the aid of an
> expression. Do I have to place any possible chart type in my report layout
> and control the appearance by defining an expression for the 'visibility'
> property?|||Thanks Teo,
I often see answers, dealing with generating the RDL dynamically. Please
correct me, if I'm wrong, but I don't think this is a solution in a
multi-user environment. I need fixed RDL files, guaranteeing the same
behaviour. So, I cannot change the RDL, every time a user wants to access the
report?!?!
"Teo Lachev [MVP]" wrote:
> Niklas,
> You can't do it with an expression. Instead, the option I would go for is
> building a custom app to get the user selection and update the report RDL.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> > I want let the end user decide on which type of graphic(via a report
> > parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
> > couldn't find any option to define the chart type e.g. with the aid of an
> > expression. Do I have to place any possible chart type in my report layout
> > and control the appearance by defining an expression for the 'visibility'
> > property?
>
>|||Niklas,
Not really. You basically need to generated the report RDL and upload it to
the report catalog under an unique name, e.g. the user logon id. The way I
addressed this on a similar occasion in the past was to have the report
template RDL file included in my web application. My web app will load the
template, change it as needed and upload it to the report catalog. Then, it
will render the report. Finally, when the user logs out, the web app will
purge the report.
Does this make sense?
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:E1B5C271-5C8A-4C15-9C75-615ADFFF1678@.microsoft.com...
> Thanks Teo,
> I often see answers, dealing with generating the RDL dynamically. Please
> correct me, if I'm wrong, but I don't think this is a solution in a
> multi-user environment. I need fixed RDL files, guaranteeing the same
> behaviour. So, I cannot change the RDL, every time a user wants to access
the
> report?!?!
> "Teo Lachev [MVP]" wrote:
> > Niklas,
> >
> > You can't do it with an expression. Instead, the option I would go for
is
> > building a custom app to get the user selection and update the report
RDL.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> > news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> > > I want let the end user decide on which type of graphic(via a report
> > > parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.)
I
> > > couldn't find any option to define the chart type e.g. with the aid of
an
> > > expression. Do I have to place any possible chart type in my report
layout
> > > and control the appearance by defining an expression for the
'visibility'
> > > property?
> >
> >
> >
Friday, March 9, 2012
Dynamic Email Attachment Name
I have a report subscription set up to email a report via a Data
Driven Subscription. I have been asked to make the file name unique
with a date stamp in the email message. I do not see the FileName
field as one of the options in the email delivery. I know that I can
do this via the FileShare extension, but this report is going to an
outside company that does not have access to our file share.
Is there any way to dynamically set the file name in the email
attachment?On Dec 14, 5:06 pm, dachrist <dachris...@.gmail.com> wrote:
> I have a report subscription set up to email a report via a Data
> Driven Subscription. I have been asked to make the file name unique
> with a date stamp in the email message. I do not see the FileName
> field as one of the options in the email delivery. I know that I can
> do this via the FileShare extension, but this report is going to an
> outside company that does not have access to our file share.
> Is there any way to dynamically set the file name in the email
> attachment?
Probably the best option you have for emailing a report with a dynamic
naming convention is to use the Reporting Services Web Service (http://
msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to call, export and dynamically name the report. Then use the built in
functionality of the .NET framework via a custom ASP.NET/Exe
application to email the reports (using System.Net.Mail). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Driven Subscription. I have been asked to make the file name unique
with a date stamp in the email message. I do not see the FileName
field as one of the options in the email delivery. I know that I can
do this via the FileShare extension, but this report is going to an
outside company that does not have access to our file share.
Is there any way to dynamically set the file name in the email
attachment?On Dec 14, 5:06 pm, dachrist <dachris...@.gmail.com> wrote:
> I have a report subscription set up to email a report via a Data
> Driven Subscription. I have been asked to make the file name unique
> with a date stamp in the email message. I do not see the FileName
> field as one of the options in the email delivery. I know that I can
> do this via the FileShare extension, but this report is going to an
> outside company that does not have access to our file share.
> Is there any way to dynamically set the file name in the email
> attachment?
Probably the best option you have for emailing a report with a dynamic
naming convention is to use the Reporting Services Web Service (http://
msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to call, export and dynamically name the report. Then use the built in
functionality of the .NET framework via a custom ASP.NET/Exe
application to email the reports (using System.Net.Mail). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Dynamic Destination Flat File Loading
Here's what I want to do -
Dynamically load a flat file from a dynamic source table-
The source table metadata is known via the SYSOBJECTS and SYSCOLUMNS tables- I can pull the column names, type and lengths from these tables based on the table name. (my goal is pretty simple- pull data from a table in our database, and save it down to a flat file)
Would this be enough to dynamically create the destination flat file? If so, how do I do it?
Thanks
-rob
The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||This is actually the answer!
Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!
Took some work, but the script task was able to do it all!
I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:
C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB
Dynamic Destination Flat File Loading
Here's what I want to do -
Dynamically load a flat file from a dynamic source table-
The source table metadata is known via the SYSOBJECTS and SYSCOLUMNS tables- I can pull the column names, type and lengths from these tables based on the table name. (my goal is pretty simple- pull data from a table in our database, and save it down to a flat file)
Would this be enough to dynamically create the destination flat file? If so, how do I do it?
Thanks
-rob
The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||This is actually the answer!
Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!
Took some work, but the script task was able to do it all!
I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:
C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB
Dynamic Destination Flat File Loading
Here's what I want to do -
Dynamically load a flat file from a dynamic source table-
The source table metadata is known via the SYSOBJECTS and SYSCOLUMNS tables- I can pull the column names, type and lengths from these tables based on the table name. (my goal is pretty simple- pull data from a table in our database, and save it down to a flat file)
Would this be enough to dynamically create the destination flat file? If so, how do I do it?
Thanks
-rob
The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||This is actually the answer!
Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!
Took some work, but the script task was able to do it all!
I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:
C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB
Friday, February 24, 2012
Dynamic connection to sql database via Access 2k7 project
I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.
Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:
>I am using SQL server 2005 with multiple client databases with the identical
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.
Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:
>I am using SQL server 2005 with multiple client databases with the identical
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.
Dynamic connection to sql database via Access 2k7 project
I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:
>I am using SQL server 2005 with multiple client databases with the identica
l
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:
>I am using SQL server 2005 with multiple client databases with the identica
l
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.
Subscribe to:
Posts (Atom)