Easy Stuff

hi.. guys this is simple for you guys how can i make ms access to display the current date in a col... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??I bet that you'll get a lot better response if you post this question in the MS-Access (http://www.dbforums.com/f84) forum.

-PatP|||thnx Pat...|||That didn't hurt much, now did it ?!?!

Anywho, while I'm sure that somebody here could have answered your question, why bother to ask here when there are oodles of folks that are readily available that can answer you? Better still, they can offer lots of insight because they actually USE MS-Access as their tool of choice.

-PatP

Easy Insert Statement ... Hopefully!

In Oracle I have an insert statement where Im selecting from one table and isnerting into another. One of the columns Im inserting is the current date/time

Ex.

Insert Into NEW_TABLE

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',SYSDATE,'1'

from REGION;

In SQL Server 2005 I cannot seem to find the right fit for the SYSDATE command: Please help

Insert Into NEW_TABLE

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',?,'1'

from REGION;

use GETDATE()

If you can use the same datetime for every row I would do this because it is much faster:

DECLARE @.curdate DATETIME
SET @.curdate = GETDATE()

Select DISTINCT REGION_ID,' SBA','Standard Rule','Upgrade',@.curdate,'1'

from REGION;

|||AND not subject ot time variations.|||

Great!!! Exactly what I was looking for

Thanks

Dynmaic parameter on Subscription report

Is it possible to have a dynamic parameter value such as current date
to a subscription base report?
Thanks,
Voss.Yes. You can set the default parameter =today() in the designer.
"Voss" wrote:
> Is it possible to have a dynamic parameter value such as current date
> to a subscription base report?
> Thanks,
> Voss.
>sql

Dynamically define the height of a chart

Current Situation:
I have a "Stacked Bar" chart and it has a certain ammount of lines depending on its data source. Sometimes if there are too many lines, only every other label shows up for that line on the left hand side (See image).

What I need:
Basically I need a solution that fixes my current situation. My first thouts were to dynamically size the height of the chart, but I haven't had much luck doing that. Also, I have tried to find properties for the char item that might let it grow.

If anyone has any suggestions, or know where I can get more information on this, I would appreciate it very much. Thanks in advance.

It looks like you have just a list of items to show in the chart.

Here is one approach:
* Add a list to the report, put the chart inside the list
* Add a (detail) group to the list with the following grouping expression:
=Int((RowNumber(Nothing)-1)/15)
This should result in the desired grouping of 15 items per (repeating) list instance.

Another approach is to define multiple charts of different sizes and use the Visibility.Hidden property on the chart to dynamically hide all charts but one. Note: you can use =CountRows("DatasetName") to determine the number of rows in a particular dataset and hide one chart e.g. if the total number of dataset rows is greater than 20: Visibility.Hidden property setting: =CountRows("DatasetName") > 20

-- Robert

|||Thanks for the reply. This seems like a good solution, however other problems were present that I didn't notice before. If you look at the x-axis of my graph it too does not display the labels correctly. My boss would like me to now explore using image creation on the fly to possibly fix this problem. Do you think that using a list would fix my problem with the x-axis?|||

You didn't explain why you think the x-axis labels are displayed incorrectly - but if the problem is that lots of data is shown in one chart and the x-axis looks crowded, splitting the underlying dataset into multiple charts will help.

-- Robert

dynamically changing default parameter?

I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.

Can this be done?

here is how i'm setting my "from date" (datetime) param to be the first of the current month
=DateSerial(Year(Now()), Month(Now()),1)

for today, just use
=Today()

this is for a whole date, you can just pick the year part

DYNAMIC USE

I would need go along the current sql server using a cursor (another
alternatives will be welcomed) and changing of database
Something like that:
DECLARE @.BD AS CHAR(20)
declare cursorbd cursor fast_forward for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status = 0
begin
USE @.BD
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
fetch next from cursorBD into @.BD
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
But it isn't working.
Regards,The GO command signals the end of a batch of T-SQL statements. You put yours
in the middle of what you wanted to be the batch, splitting it in two.
But that's not the real problem. The trouble starts with you considering
using a cursor. You don't need to.
I could very well guess what you're trying to do, but I'd instead prefer you
to tell us.
So, here it goes:
What are you trying to do? Display a list of table names for all your
databases?
ML
p.s. if you answer 'yes' to my last question, we're half way there. :)|||DECLARE @.BD varCHAR(100)
declare @.string nvarchar(100)
set @.bd=3D''
declare cursorbd cursor for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT
IN('MASTER','PUBS','MODEL','TE=ADMPDB','
MSDB','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status =3D 0
begin
set @.string =3D N' USE '+@.BD+''
--print @.string
exec sp_executesql @.string
SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES
fetch next from cursorBD into @.BD
END=20
CLOSE CURSORBD=20
DEALLOCATE CURSORBD|||Hi
You shoudl read http://www.sommarskog.se/dynamic_sql.html on issues
regarding dynamic SQL. For you query use three part naming instead of the US
E
statement
declare cursorbd cursor fast_forward for
SELECT 'SELECT TABLE_NAME FROM ' + QUOTENAME(NAME) +
'.INFORMATION_SCHEMA.TABLES' FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
declare @.sqlstmt nvarchar(4000)
open cursorbd
fetch next from cursorBD into @.sqlstmt
while @.@.fetch_status = 0
begin
exec (@.sqlstmt)
fetch next from cursorBD into @.sqlstmt
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
John
"Enric" wrote:

> I would need go along the current sql server using a cursor (another
> alternatives will be welcomed) and changing of database
> Something like that:
> DECLARE @.BD AS CHAR(20)
> declare cursorbd cursor fast_forward for
> SELECT NAME FROM MASTER.DBO.SYSDATABASES
> WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
> open cursorbd
> fetch next from cursorBD into @.BD
> while @.@.fetch_status = 0
> begin
> USE @.BD
> GO
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> fetch next from cursorBD into @.BD
> END
> CLOSE CURSORBD
> DEALLOCATE CURSORBD
> But it isn't working.
> Regards,|||Since this sounds like an administrative task, and not production code,
then you could use one of Microsoft's undocumented stored procedures to
help: sp_MSforeachdb
EXEC sp_MSforeachdb @.command1="SELECT TABLE_NAME as ? FROM
INFORMATION_SCHEMA.TABLES"
Of course, this will return all databases (including system and test),
and I would definitely NOT use this in production, as it may change in
future releases, yada, yada.
If you want to use a cursor, then you'll need to use dynamic SQL to
build your SQL statement:
DECLARE @.BD AS VARCHAR(20)
DECLARE @.SQL as varchar (200)
declare cursorbd cursor fast_forward for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status = 0
begin
SET @.SQL = 'USE ' + @.BD + '
SELECT TABLE_NAME AS ' + @.BD + '
FROM INFORMATION_SCHEMA.TABLES '
EXEC (@.SQL)
fetch next from cursorBD into @.BD
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
Finally, you could use SQL-DMO and a scripting language to do this
task; this option is the most powerful, because it allows you to treat
every SQL entity as an object, and expose their properties through an
evet-drive interface. I use this to script out my development
databases in order to check them into source control.
http://msdn.microsoft.com/library/d...br />
3tlx.asp
VBScript (abbreviated)
SET SQLServer = CreateObject("SQLDMO.SqlServer")
SET Database = CreateObject("SQLDMO.Database")
SET Table = CreateObject("SQLDMO.Table")
SET View = CreateObject("SQLDMO.View")
SET Proc = CreateObject("SQLDMO.StoredProcedure")
SET Func = CreateObject("SQLDMO.UserDefinedFunction")
SET Index = CreateObject("SQLDMO.Index")
SQLServer.LoginSecure = TRUE
SQLServer.Connect ServerName
For each Database in SQLServer.Databases
If Database.SystemObject = False Then
For Each Table In Database.Tables
'write out the tables to a file here
Next
End if
Next
HTH
Stu

Dynamic Summing

I have a 4th level grouping that has just 3 different values to group on (No
Cat, New and Current). I want to sum a field on that level, but only if the
grouping is not = "current". The sum can be in the 3rd or 4th level grouping,
whichever works. So something like this, but this doesn't work:
=SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Sum(Fields!CurrMthSales.Value),0))I think it should be something like this. (You don't need the extra sum in
your formula)
=SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Fields!CurrMthSales.Value,0))
"Chris Patten" wrote:
> I have a 4th level grouping that has just 3 different values to group on (No
> Cat, New and Current). I want to sum a field on that level, but only if the
> grouping is not = "current". The sum can be in the 3rd or 4th level grouping,
> whichever works. So something like this, but this doesn't work:
> =SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Sum(Fields!CurrMthSales.Value),0))
>