Everyone thinks "I Can..."
Yes... Now You can

Design any Report

Set Printing Format

Design Vat Reports, Sale reports, Registers, Purchase Summaries, Detailed Reports... anything you think

For every student / engineer / software implementer / reseller
Now create your desired reports without any programming knowledge
No PDL, CDL

Because of different cultures and rules all over the world, every city / every state / every country have some specific requirements in software. The basic structure of Accounting, Inventory and billing remains same, but there is a need of some different kind of reports like VAT reports, GST Reports, Sale summaries, Accounting statements, Stock report formats etc. etc.

Now we are introducing a tool "Customized Reports Manager" to help you create any number of reports yourself. You can self define any kind of report whether it is related to sales or stock or account.

Every consumer is a prospective innovator. Every tool that's developed for mankind, when it reaches in the hands of public, so many innovative minds work on that tool. As an innovator, Using this tool, you can save number of hours of your customer. For example, if you create VAT reports of your state, First of all, you provide relief to your customer to save their valuable time. In the same way, there are so many persons in your state who are annoyed from their manual calculation of vat reports. You can provide same relief to them also by just sharing your creation with us.  We believe that if your innovative creation saves 1 hour life of 1 person and let's say 1000 persons are using it, then it means you have saved 1000 life hours. In other words we can say that you have lived 3 years of your  life more. 

In this blog, you will get a brief introduction about how this tool works, how you can define and design your desired report and how you can further share these reports with more and more people.

Click Help--> Customized Report Manager to Initialize

Create any kind of report as you wish

1)  Just Define an attractive Title of Report e.g. "List of Customers". its how it will appear in your customized reports menu

2)  Setup a Custom designed printout format in Crystal Reports 8.5 (RPT File) and enter name of RPT File in the second column  e.g. CustomersList.rpt. Your Report Output will be printed on this rpt format. This RPT File should be placed in your software folder

3) SQL Query is the whole crux of this tool. You have 3 Options to input this query

a) Manually Enter SQL Query  or b) use Ms Access to build an SQL Query

c) Click on "Design..." to activate "Solver Query Designer"

Design Your SQL Query Yourself

1)  List of Tables shows all tables in your current database. Double click table name from which you want to extract the data in your report. This table would be activated in workspace. In case of complex queries, you can activate 2 or 3 table and join them togather by right clicking on its primary key field in main table and drag drop on foreign key field in target table

2)  Just check the fields that you want to show in report one by one.

3) The fields you have checked would be shown in this area

4)  Click on "View SQL query" to show sql query

5) Your SQL Query designed by query designer is shown here. you can edit this query as you want

6) Click to apply this query in Custom report Manager

The Query you designed will be shown in SQL Query Box,

You can edit this query also


Click on Test to run this Customized Report

See your tailor made report in action

Let the user choose their customized report from menu

the report you have designed will appear in "Customized Reports" menu as the user open the software

Let's see current scenario of Software Implementation Life Cycle

At the time of Demo

Then series of discussions continues over the phone

Change has a new name --- "You"

Lesson No. 1:   How to create "Punjab VAT Reports" yourself

As per an estimate, there are appx. 2 Lac registered VAT Dealers in the state of Punjab. Every registered VAT dealer is required to file VAT return every quarter. In case of manual working, creating VAT Reports takes appx 3-4 days of a businessman to create their vat returns. it means you can save appx 20 lac manhours by understanding this simple concept. E-Filing of return means you need to copy your data in excel file given by taxation deptt. You can also setup format to be printed.

Format of FORM VAT 24

Vat 24 is a report in which we show List of all the party from where purchases are made within state. By default VAT 24 Format looks like.

In software, as we input a purchase entry, a record is created in VAT Table in the database, You need to make a query to bring that record on the screen. Along with this purchase record, you need to join this vat Table with Account table to take TIN No. In Query Designer, you can join any table with another. to join a table right click on the primary key in main table and drag drop in another table. 

You can see that Account and VAT Tables are joined together. Now you need to manually edit the SQL query to input "where" condition, grouping and order by clauses.

Copy and paste version of above query

Select Account.TINNo, VAT.PartyName, VAT.VatPercentage, Sum(VAT.TaxableAmt) As Basic, Sum(VAT.VatAmt) as VAT, Sum(VAT.Surcharge) as Srch from VAT inner join Account on VAT.PartyID = Account.AccountID  Where VAT.TransactionType = 'Purchase' AND InputTaxCredit = 'Y' Group By Account.TINNo, VAT.PartyName, Vat.VATPercentage Order By VAT.PartyName, VAT.VatPercentage

 

Click on Test to execute the Query

See you report in live action

The above query shows vat purchases made within selected period. Now lets see how all this happened

Structure of an SQL Query

An SQL query has 5 parts

1) Select : Columns to show in the query

2) From : Name of Table(s)

3) Where: Condition

4) Group By: Column names on which you want to group the query

5) Order By: Set Columns to Sort

The above query can be categorized as

In the same way, Let;s create one query for Retail Purchase Details of VAT 24. In case of Retail Purchase, there is no need to show party names

Form VAT 24 (Retail Purchase Details)

Copy and paste version of above query

Select VatPercentage, Sum(TaxableAmt) as Amt, Sum(VatAmt) as vat, Sum(Surcharge) as Srch, Sum(TaxableAmt + VatAmt+Surcharge) as Total From VAT where TransactionType = 'Purchase' AND InputTaxCredit = 'N' AND Region = 'Local' Group By VatPercentage Order By VatPercentage

Format of FORM VAT 23

Vat 23 is a report in which we show List of all the party to whom sales are made within state. By default VAT 23 Format looks like.

In software, as we input a sales entry, a record is created in VAT Table in the database just in the same way as in purchase entry, You need to make a query to bring that record on the screen. Along with this sale record, you need to join this Vat Table with Account table to take TIN No.

Just specify the sql query same as we defined above for VAT 24, the only difference here is in "Where" condition. Now this query will look for "Sale" Transactions only with Input Tax Credit = "Y". In case of VAT Sales / Purchase, Input Tax Credit is "Y", In case of Retail or CST Sale/Purchase, Input Tax Credit is not available, that;s why it is defined as "N".

Copy and paste version of above query

Select Account.TINNo, VAT.PartyName, VAT.VatPercentage, Sum(VAT.TaxableAmt) As Basic, Sum(VAT.VatAmt) as VAT, Sum(VAT.Surcharge) as Srch from VAT inner join Account on VAT.PartyID = Account.AccountID  Where VAT.TransactionType = 'Sale' AND InputTaxCredit = 'Y' Group By Account.TINNo, VAT.PartyName, Vat.VATPercentage Order By VAT.PartyName, VAT.VatPercentage

 

Click on Test to execute the Query

See you report in live action

Now let take an example of VAT-18

VAT 18 is List of sales invoices made out of state within the tax period

The sample format required to file VAT Form 18 is given below

To fetch this information you need to join Account and Voucher Table together

Copy & Paste version of the above query

Select Account.AccountName, Account.City, Account.TINNo, Voucher.EntryDate, Voucher.Transport, Voucher.GRRRNo, Voucher.PurchaseBillNo, Voucher.DescriptionOfItems, Voucher.TotalQty, Voucher.TotalAmount from Voucher inner join Account on Voucher.PartyID = Account.AccountID Where SaleTypeID IN(Select SaleTypeID From SaleType Where Region = 'Central') Order By Voucher.EntryDate, VchNo, Voucher.PartyName

Enter above query to see the result live

See Your VAT 18 No. Form is ready


Let's create VAT-19

VAT 19 is List of purchase invoices made out of state within the tax period

The sample format required to file VAT Form 19 is given below

To fetch this information you need to join Account and Voucher Table together

Copy & Paste version of the above query

Select Account.AccountName, Account.City, Account.TINNo, Voucher.EntryDate, Voucher.Transport, Voucher.GRRRNo, Voucher.PurchaseBillNo, Voucher.DescriptionOfItems, Voucher.TotalQty, Voucher.TotalAmount from Voucher inner join Account on Voucher.PartyID = Account.AccountID Where PurchaseTypeID IN(Select Purchase.PurchaseTypeID From PurchaseType Where Region = 'Central') Order By Voucher.EntryDate, VchNo, Voucher.PartyName

Enter above query to see the result live

See Your VAT 19 No. Form is ready


Lesson No. 2 : How To create UP VAT Reports

"Annexure - A" shows list of purchase transactions made from taxable persons within the state

The sample format required to file Annexure A is given below

To fetch this information you need to join Account and VAT Table together

Copy & Paste version of the above query

Select VAT.PartyName, Account.TINNo, Voucher.PurchaseBillNo, VAT.EntryDate, Voucher.DescriptionOfItems, Voucher.TotalQty, VAT.TaxableAmt, VAT.VatAmt, Voucher.TotalAmount from ( VAT inner join Account on VAT.PartyID = Account.AccountID) inner join Voucher on VAT.VoucherID = Voucher.VoucherID Where VAT.TransactionType IN('Purchase') AND VAT.InputTaxCredit = 'Y'

Enter above query and click on Test to see the result live

See Your Output of Annexure A is ready


UP VAT Annexure B

Annexure B is used to record list of all Sales invoices made to taxable persons within state between tax period

The sample format required to file Annexure B is given below

To fetch this information you need to join Account and VAT Table together

Copy & Paste version of the above query

Select VAT.PartyName, Account.TINNo, Voucher.PurchaseBillNo, VAT.EntryDate,Voucher. DescriptionOfItems, Voucher.TotalQty, VAT.TaxableAmt, VAT.VatAmt, Voucher.TotalAmount from ( VAT inner join Account on VAT.PartyID = Account.AccountID) inner join Voucher on VAT.VoucherID = Voucher.VoucherID Where VAT.TransactionType IN('Sale') AND VAT.InputTaxCredit = 'Y'

Enter above query to see the result live

See Your Annexure B is ready


Lesson No. 3 : How to Create MP VAT Reports

Local Sale Bills List contains List of all purchase invoices

The sample format required to file "Local Sale Bills List" is given below

To fetch this information you need to join Account, VAT and Voucher Table together

Copy & Paste version of the above query

Select Account.AccountName, Account.TINNo, Sum (VAT.TaxableAmt) as [NetAmt], VAT.VatPercentage, SUM(VAT.VatAmt) as [VATAmt], VAT.EntryDate, Voucher.PurchaseBillNo from (Voucher inner join VAT on Voucher.VoucherID = VAT.VoucherID) inner join Account on VAT.PartyID = Account.AccountID Where VAT.TransactionType IN('Sale') AND VAT.InputTaxCredit = 'Y' Group By VAT.EntryDate, Voucher.VoucherID, Voucher.PurchaseBillNo, Account.AccountName, Account.TINNo, VAT.VatPercentage Order By Vat.EntryDate, Voucher.VoucherID, VAT.VatPercentage

Enter above query to see the result live

See Your VAT 18 No. Form is ready


Local Sales Party Wise

Local Sales Party Wise is used to show list of all taxable customers within state between tax period

The sample format required to file "Local Sales Party Wise" is given below

To fetch this information you need to join Account and VAT Table together

Copy & Paste version of the above query

Select VAT.PartyName, Account.TINNo, Sum(VAT.TaxableAmt) As Basic, VAT.VatPercentage, Sum(VAT.VatAmt) as VAT
from VAT inner join Account on VAT.PartyID = Account.AccountID Where VAT.TransactionType = 'Sale' AND InputTaxCredit = 'Y'
Group By Account.TINNo, VAT.PartyName, Vat.VATPercentage Order By VAT.PartyName, VAT.VatPercentage

Enter above query to see the result live

See Your "Local Sales Party Wise " is ready


Local Purchase Bill Wise

List contains List of all purchase invoices made from taxable persons within the state between tax period

The sample format required to file "Local Purchase Bills List" is given below

To fetch this information you need to join Account, VAT and Voucher Table together

Copy & Paste version of the above query

Select Account.AccountName, Account.TINNo, Sum (VAT.TaxableAmt) as [NetAmt], VAT.VatPercentage, SUM(VAT.VatAmt) as [VATAmt], VAT.EntryDate, Voucher.PurchaseBillNo from (Voucher inner join VAT on Voucher.VoucherID = VAT.VoucherID) inner join Account on VAT.PartyID = Account.AccountID Where VAT.TransactionType IN('Purchase') AND VAT.InputTaxCredit = 'Y' Group By VAT.EntryDate, Voucher.VoucherID, Voucher.PurchaseBillNo, Account.AccountName, Account.TINNo, VAT.VatPercentage Order By Vat.EntryDate, Voucher.VoucherID, VAT.VatPercentage

Enter above query to see the result live

See Your VAT 18 No. Form is ready


Local Purchase Party Wise

Local Purchases Party Wise List is used to show list of all taxable suppliers within state between tax period

The sample format required to file "Local Purchase Party Wise" is given below

To fetch this information you need to join Account and VAT Table together

Copy & Paste version of the above query

Select VAT.PartyName, Account.TINNo, Sum(VAT.TaxableAmt) As Basic, VAT.VatPercentage, Sum(VAT.VatAmt) as VAT from VAT inner join Account on VAT.PartyID = Account.AccountID Where VAT.TransactionType = 'Purchase' AND InputTaxCredit = 'Y' Group By Account.TINNo, VAT.PartyName, Vat.VATPercentage Order By VAT.PartyName, VAT.VatPercentage

Enter above query to see the result live

See Your "Local Sales Party Wise " is ready


List of Imports

Shows List of Imports made out of country between tax period

The sample format required to file "List of Imports" is given below

To fetch this information you need to join Account & VAT Table together with a nested query to link with PurchaseType Table

Copy & Paste version of the above query

Select VAT.PartyName, Account.TINNo, Sum(VAT.TaxableAmt) As Basic, VAT.VatPercentage from VAT inner join Account on VAT.PartyID = Account.AccountID Where VAT.TransactionType = 'Purchase' AND VAT.PurchaseTypeID IN(Select PurchaseTypeID From PurchaseType Where Region = 'Central' AND TypeOfCentralTransaction = 'Import') Group By VAT.PartyName, Account.TINNo, Vat.VATPercentage Order By VAT.PartyName, VAT.VatPercentage

Enter above query to see the result live

See Your Imports List is ready


Lesson 4:  Rajasthan VAT Reports

Rajasthan VAT 7A Report is used to show list of all taxable suppliers from whom goods other than capital goods are purchased within state between tax period

The sample format required to file "Rajasthan VAT 7A" is given below

To fetch this information you need to join Account and VAT Table together

Copy & Paste version of the above query

Select VAT.PartyName, Account.TINNo, Sum(VAT.TaxableAmt) As Basic, Sum(VAT.VatAmt) as VAT from VAT inner join Account on VAT.PartyID = Account.AccountID Where VAT.TransactionType = 'Purchase' AND InputTaxCredit = 'Y' Group By Account.TINNo, VAT.PartyName Order By VAT.PartyName

Enter above query to see the result live

See Your "Rajsthan VAT 7A" is ready


Rajasthan VAT 8(a)

Shows Summary Statement of Sales made to against VAT Invoices to Taxable Persons within the state between tax period

The sample format required to file "Rajasthan VAT 8A" is given below

To fetch this information you need to join Account & VAT Table together 

Copy & Paste version of the above query

Select VAT.PartyName, Account.TINNo, Sum(VAT.TaxableAmt) As Basic, Sum(VAT.VatAmt) as VAT from VAT inner join Account on VAT.PartyID = Account.AccountID Where VAT.TransactionType = 'Sale' AND InputTaxCredit = 'Y' Group By Account.TINNo, VAT.PartyName Order By VAT.PartyName

Enter above query to see the result live

See Your Rajasthan VAT 8A is ready


Rajasthan VAT Form 48

Rajasthan VAT form 48 Report is used to show list of Purchases / Imports Bill Wise

The sample format required to file "Rajasthan VAT 48" is given below

To fetch this information you need to join Account and Voucher Table together

Copy & Paste version of the above query

Select Account.AccountName,Account.Address, Account.Address2,Account.City,Account.TINNo, Voucher.PurchaseBillNo,Voucher.EntryDate, Voucher.DescriptionOfItems, Voucher.TotalAmount from Voucher inner join Account on Voucher.PartyID = Account.AccountID Where PurchaseTypeID IN(Select PurchaseTypeID From PurchaseType Where Region = 'Central' AND TypeOfCentralTransaction = 'Import') Order By Voucher.EntryDate, VchNo, Voucher.PartyName

Enter above query to see the result live

See Your "Rajsthan VAT 48" is ready


Rajasthan VAT 50

Shows Summary Statement of Interstate Sales made within the tax period

The sample format required to file "Rajasthan VAT 50" is given below

To fetch this information you need to join Account & Voucher Table together 

Copy & Paste version of the above query

Select Account.AccountName, Account.Address, Account.Address2, Account.City, Account.TINNo, Voucher.PurchaseBillNo, Voucher.EntryDate, Voucher.DescriptionOfItems, Voucher.TotalAmount from Voucher inner join Account on Voucher.PartyID = Account.AccountID Where SaleTypeID IN(Select SaleTypeID From SaleType Where Region = 'Central') Order By Voucher.EntryDate, VchNo, Voucher.PartyName

Enter above query to see the result live

See Your Rajasthan VAT 50 is ready