Accounting Format for numbers in SSRS report

    SSRS report does not have Accounting-formatting feature like MS Excel have. But some banking and Financial Client asks Accounting Formatting to numbers in SSRS, here is the solution for this

Image shows Differences of accounting right align currency format

To achieve this you need to Tweak Expression as shown below, give space after 0 for accounting format and after – to align no data in same format. you can change this expression as per your Need, Space is the Solution to get Accounting Format.

#,0;(#,0);'-'    

#,0 ;(#,0);'- '

Please look into below image

Thank you, Please give your reply, question,  Feedback and any additional Tweaks 

Msg 5094, The operation cannot be performed on a database with database snapshots or active DBCC replicas

Error:
Msg 5094, Level 16, State 2, Line 2
The operation cannot be performed on a database with database snapshots or active DBCC replicas.

Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally, .

This error basically comes when there is Snapshot file present for same DB, solution for this is very simple, go to the data file location Eg. C:\MSSQL\DATA\ or wherever you have stored your data files. and Delete YourDatabase.SS file.

Then you can restore your database .

Database restore Command as shown below.


sp_helpdb MY_DATABASE
--to see Logical file name of Data and Log file and location


ALTER DATABASE [MY_DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Take your database on Single user for safe side.


RESTORE DATABASE [MY_DATABASE]
FROM DISK = 'D:\PROD Backup\MY_DATABASE.bak'
WITH
MOVE 'MyDatabase_Datafile' TO 'D:\MSSQL\DATA\MY_DATABASE.mdf',
MOVE 'MyDatabase_Logfile' TO 'D:\MSSQL\LOG\MY_DATABASE_log.ldf'


ALTER DATABASE [MY_DATABASE] SET MULTI_USER
-- Back to multi user 

SSRS Double border does not work in Export or Browser,

SSRS Double border does not work in Export or Browser, It renders to Single border

SSRS has this issue, Double border doesn’t work when we render to browser, it shows single border

Resolution- make lower border to 2,2.5 or 3 and make it double, then it works.

How to use CSV file as Data Source in SSRS Report

How to use CSV file as Data Source in SSRS Report

SSRS report with CSV Data Source

  In one of our development scenario I got requirement to develop one functionality of SSRS report from CSV Data source, I did searched online but, didn’t get proper article with easy steps, then I thought to write this article for all developers who are searching for CSV as a SSRS report Data Source

• Add new report, when you are creating data source select OLE DB Driver, and click Edit button.

•          Select “Microsoft.Jet.OLEDB.4.0” driver and give only your file path, check Test Connection, after Succeeded hit ok, ok, Or copy paste this connection string            Provider=Microsoft.Jet.OLEDB.4.0;Data Source="U:\\SSRS CSV Source";Extended        Properties="text;HDR=YES;FMT=Delimited"

•  Create Data Set and write your query as shown below, don’t keep space in between your file name   Select * from Fund_groups.csv  

• You can also write Where conditional and filter CSV records, @Fund is report parameter,   SELECT  * FROM Fund_groups.csv  where Fund_Family in (@Fund)  This works in Query Designed

 But this won’t work in report execution, see below error message ,for multi value with where condition.

(It Does not support by Data extension)

• Solution for this is, Filter records on Data set, remove (0) from parameter                                       Eg.  Parameters!Fund.Value(0) use only Parameters!Fund.Value SSRS takes multiple values internally

   

• Now your report is ready, 

This way you can achieve csv as a data source,
we can also join 2 CSV files and use as data source for SSRS reports, please see this new article
"How to join two CSV files for SSRS reporting"

please leave your message, comments, feedback. Thank you!

Talk to Anup: Difference between Bill Inmon and Ralph Kimball Da...

Talk to Anup: Difference between Bill Inmon and Ralph Kimball Da...: Difference between Bill Inmon and Ralph Kimball Data warehouse model Bill Inmon is first author to introduce Data warehouse design and...

Talk to Anup: Bill Inmon Data warehouse model

Talk to Anup: Bill Inmon Data warehouse model: Bill Inmon Data warehouse model and Development approach Bill Inmon is called Father of Data warehouse concepts, he is the first author ...

Talk to Anup: Ralph Kimball Data warehouse model

Talk to Anup: Ralph Kimball Data warehouse model: Ralph Kimball Data warehouse model and Development approach Ralph Kimball is well known author who introduced new pattern of data stor...